How to execute Oracle sql script through java code [duplicate]

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.

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

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();
        }
    }
}

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)