Run SQL File on Java with SqlPlus

Question

I am trying to run sql file on sqlplus with java. Almost tried everything on internet but still did not find any clue about this issue. Below code runs cmd , connects sqlplus with my credentials and processes my file but the console logs still hang, could not write table created or something. On db side the table is not created at all. I simulated on cmd manually when I try my steps manually, after @pathtoscriptfile command I need to "/" and hit enter for create table. I am not able to simulate on java this purpose.

Any help?

public static void main(String[] args) throws IOException {

           String[] command = {"sqlplus", "username/password@ip:port/servicename", "@C:/Users/erkan.erkisi/Desktop/Jenkins/123.sql"};
            ProcessBuilder probuilder = new ProcessBuilder( command );           

            Process process = probuilder.start();

            //Read out dir output
            InputStream is = process.getInputStream();
            InputStreamReader isr = new InputStreamReader(is);
            BufferedReader br = new BufferedReader(isr);
            String line;
            System.out.printf("Output of running %s is:\n",
                    Arrays.toString(command));
            while ((line = br.readLine()) != null) {
                System.out.println(line);
            }

            //Wait to get exit value
            try {
                int exitValue = process.waitFor();
                System.out.println("\n\nExit Value is " + exitValue);
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }



}

The script in that file which I am trying to do (This is sample code, it may be package,procedure or etc);

create table xxer_erkan (
id number,
tarih date)

Kris Rice · Accepted Answer · 2018-03-08 02:49:20Z

2

You can avoid calling out to sqlplus at all by including SQLcl ( sql scripting engine of sqldev ) into your java code and call these features. Here's a sample Then run them with:

sqlcl.setStmt(<SCRIPT  HERE>);
sqlcl.run();

I wrote a blog post about it here:

http://krisrice.io/2016-11-15-sqlcl-as-library-in-existing-programs/

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

//#change the format
sqlcl.setStmt("@C:/Users/erkan.erkisi/Desktop/Jenkins/123.sql");
sqlcl.run();


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

answered Mar 8, 2018 at 2:49

Kris Rice

3,43017 silver badges34 bronze badges

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

1 Comment

Raigormator Over a year ago

Thank you for this. I am using IntelliJ so what I did was 1. Download sqlcl zip file 2. Extract it in a folder 3. In IntelliJ, import all the jar files in the library. 4. Copy the code and edit the path and SQL file 5. Run the code and it works!

Erkan Erkişi · Accepted Answer · 2018-02-22 18:29:18Z

0

I solved issue with putting ";" end of the script.

create table xxer_erkan (
id number,
tarih date);

Thanks

answered Feb 22, 2018 at 18:29

Erkan Erkişi

1591 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

Run SQL File on Java with SqlPlus

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related