How to execute sql file from java

Question

I have an ORACLE SQL sctipt with several queries and tables, and I wan to run that script from my java program at the starting of the program to ensure that everything is on the right place. I found a code to run the script, but it doesn't work for some reason. Can anyone provide me samples so that I can follow it.

This is what I found :

try {
    String line;
    Process p = Runtime.getRuntime().exec ("psql -U sas -d oracle -h @localhost -f Lab_05_Tables.sql");
    BufferedReader input =new BufferedReader(new InputStreamReader(p.getInputStream()));

    while ((line = input.readLine()) != null) {        
        System.out.println(line);
    }
    input.close();
}
catch (Exception err) {
    err.printStackTrace();
}

But it doesn't work though.

Error

java.io.IOException: Cannot run program "psql": CreateProcess error=2, The system     
cannot find the file specified

Isn't `psql` the PostgreSQL command line? Can it handle Oracle, too? — Thilo, Nov 29 '11 at 01:08
The problem is that the example you took is using the program psql that is used for PostgreSQL script, not Oracle. — talnicolas, Nov 29 '11 at 01:09
The error indicates that `psql` could not be found. Maybe you need to specify the full path. And it is doubtful if psql can handle Oracle. You probably want `sqlplus`. — Thilo, Nov 29 '11 at 01:10
possible duplicate of [Simple way to run sqlplus script from java](http://stackoverflow.com/questions/6923482/simple-way-to-run-sqlplus-script-from-java) — Thilo, Nov 29 '11 at 01:12
either use sqlplus, or better use JDBC (http://www.razorsql.com/articles/oracle_jdbc_connect.html) — talnicolas, Nov 29 '11 at 01:14
it's IOException, so mostly because of inadequate of file path (for psql program and file Lab_05_Tables.sql also). — Thinhbk, Nov 29 '11 at 02:00

score 5 · Accepted Answer · answered Nov 29 '11 at 09:00

5

It would be much better, if you have resources, to port SQLs from your script into Java program itself.

See Java JDBC tutorial.

answered Nov 29 '11 at 09:00

Victor Sorokin

11,878
2
35
51

1

writing SQL inside your java program is a bad idea! You will need to recompile your program every time you change the SQL statements or your database schema. – GETah Feb 23 '12 at 23:07
@GETah How will you parametrize SQLs which are out of your program? E.g., if you added new table column, how can you use it in your `WHERE new_table = ?` part of SQL from JDBC? It can be done using some metadata language, but most of the time I had no need for such level of complexity, since, anyway, every change to DB scheme is done because of new feature added to Java code, so you need to recompile it anyway. Still can be done if you need it, though. – Victor Sorokin Feb 24 '12 at 15:46
I would rather put my SQL statements inside a text file. Imagine a scenario where you need to rename a table in your DB. In this case, the code does not require recompilation which is not the case when you embed SQL directly in your production code. – GETah Feb 24 '12 at 19:43

score 2 · Answer 2 · answered Nov 29 '11 at 08:52

2

You are trying to run external program from your code, which is not a good solution.

If you use Ant, you can use sql task in Ant: http://ant.apache.org/manual/Tasks/sql.html

If you don't use Ant, you should just use the regular jdbc connection.

answered Nov 29 '11 at 08:52

user1070859

61
2

score 2 · Answer 3 · answered Nov 29 '11 at 08:54

It is possible you're running EnterpriseDB Postgres Plus Advanced Server, which would allow you to run Oracle code using psql. The filename "Lab_05_Tables.sql" suggests an educational environment, so forgive me if it seems I'm unduly treating you as a novice.

Before getting this to work in Java, you should get your executed statement to run by itself at the command line.

psql -U sas -d oracle -h @localhost -f Lab_05_Tables.sql

Lots of things may be wrong here and you need to figure that out. Your command line suggests that you are expecting to run the java program on the same server that is running your database server and has the database client installed, and that you're using the user name "sas" to log into a database named "oracle" with no password required. If any of that is not correct then you should find help getting set up on your database before working on your Java app. I would omit the "-h @localhost".

If you're certain all that's working and the scope of your problem is just getting Java to execute your command, then you may just need to add psql to your PATH environment variable.

`psql` is for PostgreSQL, it will not be available if only Oracle is installed - and if it was, it wouldn't be able to connect to Oracle. (and the arguments for sqlplus are completly different) — , Nov 29 '11 at 09:36
I get that, and it's true for the ordinary open source PostgreSQL, which is different from the product I mentioned. See http://enterprisedb.com/products-services-training/products/postgres-plus-advanced-server/advanced-server-oracle-features. — phatfingers, Nov 29 '11 at 15:44

How to execute sql file from java

3 Answers3