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
– Thilo, Commented Nov 29, 2011 at 1:08
The problem is that the example you took is using the program psql that is used for PostgreSQL script, not Oracle. — talnicolas
– talnicolas, Commented Nov 29, 2011 at 1: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
– Thilo, Commented Nov 29, 2011 at 1:10
either use sqlplus, or better use JDBC (razorsql.com/articles/oracle_jdbc_connect.html) — talnicolas
– talnicolas, Commented Nov 29, 2011 at 1:14

Victor Sorokin · Accepted Answer · 2011-11-29 09:00:37Z

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, 2011 at 9:00

Victor Sorokin

12k2 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GETah Over a year ago

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.

Victor Sorokin Over a year ago

@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.

GETah Over a year ago

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.

user1070859 · Accepted Answer · 2011-11-29 08:52:36Z

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, 2011 at 8:52

user1070859

612 bronze badges

Comments

phatfingers · Accepted Answer · 2011-11-29 08:54:07Z

2

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.

answered Nov 29, 2011 at 8:54

phatfingers

10.3k3 gold badges34 silver badges47 bronze badges

2 Comments

user330315 Over a year ago

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)

phatfingers Over a year ago

I get that, and it's true for the ordinary open source PostgreSQL, which is different from the product I mentioned. See enterprisedb.com/products-services-training/products/….

Collectives™ on Stack Overflow

How to execute sql file from java

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related