How to capture output from sqlplus window by running sqlplus using Runtime.exec() in java

Question

I am trying to run an sql script(memo_222dataFERG.out here) from java and trying to capture the output from sqlplus window. Here my Code is

Runtime rt = Runtime.getRuntime();
                                String runString1 = "cmd /c start sqlplus pecok/pecok@xe @C:\workspace\PeCok_Tool\memo_222dataFERG.out";  
                                Process proc = null; 
                                proc = rt.exec(runString1);
                            //  proc = rt.exec("java -version");
                                InputStream is = proc.getErrorStream();
                                //InputStream is = proc.getInputStream();
                                int bufSize = 4096; 
                                BufferedReader in = new BufferedReader(new InputStreamReader(is), bufSize);
                                String currentLine = null; 
                                while ((currentLine = in.readLine()) != null) {
                                    System.out.println(" "  + currentLine); 
                                }
                                is.close();
                                int a = proc.waitFor();
                                System.out.println(" proc.waitFor() :: " +a);

Sqlplus executes. SQLPlus window opens and and it start executing the insert satements which I have written in C:\workspace\PeCok_Tool\memo_222dataFERG.out file. But I am not able to capture the output from sqlplus window and print that in eclipse console. Somewhare in the while loop it hangs and not printing anything in eclipse console. But when I am running "java -version"(Commented in the code) It is succesfully printing java version as output in console. Now my question is how can I read the output from sqlplus window? I tried with both getErrorStream() and getInputStream().

What happens if you run the command directly from a command prompt? Does your script end with 'exit'? — Alex Poole, Apr 27 '17 at 07:17
Well, there's your problem; if you don't exit then SQL\*Plus sits there waiting for more input. [See this](http://stackoverflow.com/q/2809267/266304). — Alex Poole, Apr 27 '17 at 09:48
Thanks Alex. I put exit in the script. Now it is working fine. — Soubhab Pathak, Apr 27 '17 at 12:40
Possible duplicate of [Problem running oracle script from command line using sqlplus](http://stackoverflow.com/questions/2809267/problem-running-oracle-script-from-command-line-using-sqlplus) — Alex Poole, Apr 27 '17 at 12:55
Give this a try instead : https://stackoverflow.com/a/49639432/3715100 — Kris Rice, Apr 04 '18 at 13:10

score 0 · Answer 1 · answered Apr 04 '18 at 15:56

I know you are asking specifically about sqlplus and process forking however SQLcl runs anything that sqlplus does and is completely written in java so easy to integrate.

I have a github repo here targeted on how to do this : https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/java

Here's one of the java examples:

import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.dbtools.raptor.newscriptrunner.ScriptExecutor;
import oracle.dbtools.raptor.newscriptrunner.ScriptRunnerContext;

public class RunMyScript {

  public static void main(String[] args) throws SQLException, UnsupportedEncodingException {
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/XE", "klrice", "klrice");
    conn.setAutoCommit(false);

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

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

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

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


    // # run a whole file 
    // adjust the path as it needs to be absolute
    sqlcl.setStmt("@/Users/klrice/workspace_commons/sqlcl-java/myfile.sql");
    sqlcl.run();


    String results = bout.toString("UTF8");
    results = results.replaceAll(" force_print\n", "");
    System.out.println(results);
  }

}

How to capture output from sqlplus window by running sqlplus using Runtime.exec() in java

1 Answers1