0

I am new to Oracle DB and am using Oracle 11g, I want to execute Oracle sql script through java code. my SQL script may contain SQL statements(DDL or DML) or PL/SQL blocks, so I don't want to parse the script in my java code, but would prefer executing the complete script in one go. Hope my question is clear enough, if not let me know and I can clarify.

Would iBatis ScriptRunner work in my scenario?

Kuldeep Jain
  • 8,409
  • 8
  • 48
  • 73
  • Solution mentioned http://stackoverflow.com/a/3055008/948268 works for works for `insert`, `create table`. But for script having `create or replace trigger` it fails with **java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement**. Any clue why? – Kuldeep Jain Dec 20 '13 at 11:26
  • Could not find any better solution, so I used this: http://stackoverflow.com/a/20786458/948268 – Kuldeep Jain Jan 09 '14 at 13:18
  • 1
    @samuel-liew this is not a dupe as this is for Oracle and the one you marked is a psql ( postgres ) – Kris Rice Apr 04 '18 at 13:26

4 Answers4

1

You might consider using Liquibase, which is a lightweight framework for executing database changes (DDL, DML) in a defined way. Its main goal is to support DB schema migration.

Existing SQL scripts can be easily converted to liquibase scripts by simply adding some comments containing liquibase keywords. See this example

The only downside I can think of is that liquibase uses two additional tables to log information about the database changes it has already executed.

Michael Schaefers
  • 707
  • 4
  • 17
1

One option is to include SQLcl and use it from java

Step 1. Download and unzip SQLcl > http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

Step 2. Added everything to the classpath

# all files in sqlcl/lib/*
# adjust to your install ( unzip ) of sqlcl
LIB=sqlcl/lib/
CP=
for  f in $(ls $LIB/*.jar); do
 echo $f
 CP=$CP:$f
done
echo --- $CP ---

Step 3. Write some java

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/XE","klrice","klrice");

//#get a DBUtil but won't actually use it in this example
DBUtil util = DBUtil.getInstance(conn);

//#create sqlcl
ScriptExecutor sqlcl = new ScriptExecutor(conn);

// Capture the results without this it goes to STDOUT
ByteArrayOutputStream bout = new ByteArrayOutputStream();
BufferedOutputStream buf = new BufferedOutputStream(bout);
sqlcl.setOut(buf);

//#setup the context
ScriptRunnerContext ctx = new ScriptRunnerContext();

//#set the context
sqlcl.setScriptRunnerContext(ctx);
ctx.setBaseConnection(conn);

//# run a whole file
sqlcl.setStmt("@myfile.sql");
sqlcl.run();

//#run 1 statement
sqlcl.setStmt("select * from user_objects");
sqlcl.run();

String results = bout.toString("UTF8");
System.out.println(results);

Updated a GitHub repo of doing exactly this here: https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/java

Kris Rice
  • 3,300
  • 15
  • 33
0

I haven't tried it myself but does this work for you? It is the code for a PostgresDB but you may be able to use the Oracle SQL Command Line, too. Here is the link where I found this solution.

import java.io.*;
public class CmdExec {

    public static void main(String argv[]) {
        try {
            String line;
            Process p = Runtime.getRuntime().exec
                    ("psql -U username -d dbname -h serverhost -f scripfile.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();
        }
    }
}
Community
  • 1
  • 1
mvieghofer
  • 2,846
  • 4
  • 22
  • 51
0

I'm assuming that you want to run some "onetime scripts" to create a initial db setup. If this is the case then the solution provided by mvieghofer could work but it assumes that you have the psql tool installed and available in the system path. So this impose some requirements on your development system.

Better and more portable solution would be to indeed use the ibatis scriptrunner in conjunction with Resource. You could write a simple commandline tools taking the script location as an input. Downside of this approach is that your code will have a dependency on Ibatis (large framework dependency to use only one feature)

Ben Ooms
  • 151
  • 4