Create sequence of sqlplus commands in Java

Question

How can I create a Java code to connect to SQLPlus and execute a sequence of commands? I need to execute the commands below to generate a Oracle AWR.

//Connect to DB thru SQLPlus

sqlplus username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)))

//After connected

//Execute SQL Query to capture SID1 and SID2 vards

set heading off feedback off lines 800 pages 5000 trimspool on trimout on
set termout off
spool C:\\Temp\\AWR_TEST.html
select output from table(dbms_workload_repository.awr_global_report_html(4194236182,'',SID1,SID2,0))
spool off
set termout on
set heading on feedback 6 lines 100 pages 45

I have the following code, but I don't know how to adapt the complete SQL commands here.

processBuilder = new ProcessBuilder(
                "sqlplus",
                "username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)))"); //ORACLE            

        processBuilder.redirectErrorStream(true);

        Process process = processBuilder.start();

        BufferedReader in = new BufferedReader(new InputStreamReader(process.getInputStream()));

        while ((currentLine = in.readLine()) != null) {
            appendLineToStringBuilder(responseBuilder,String.format("Result Each Line: %s", currentLine));
        }

Put your script in a file and run it with the sqlplus through java. https://stackoverflow.com/questions/10485443/how-to-use-sqlplus-in-windows-command-script-to-control-flow?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — Jorge Campos, Apr 03 '18 at 02:53
Thanks Jorge! Actually the SQL code will be dynamic, so I believe this option does not work. — acg, Apr 03 '18 at 11:04
It does if you generate it in the Java code. Just put your script into a file, save it and run it. — Jorge Campos, Apr 03 '18 at 12:00
Take a look here: https://stackoverflow.com/a/49639432/3715100 — Kris Rice, Apr 04 '18 at 13:09

score 0 · Answer 1 · answered Apr 04 '18 at 16:02

This could be an easier option than doing a processbuilder/fork/capture stdout.

SQLcl is based in java and can be leveraged in a java application

github repo of example/details here: https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/java

Example Java:

import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.dbtools.raptor.newscriptrunner.ISQLCommand;
import oracle.dbtools.raptor.newscriptrunner.ScriptParser;
import oracle.dbtools.raptor.newscriptrunner.ScriptRunner;
import oracle.dbtools.raptor.newscriptrunner.ScriptRunnerContext;

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

            InputStream stream = new ByteArrayInputStream("your sql here".getBytes(StandardCharsets.UTF_8));

            ScriptParser parser = new ScriptParser(stream);

            ISQLCommand cmd;
            // #setup the context
            ScriptRunnerContext ctx = new ScriptRunnerContext();
            ctx.setBaseConnection(conn);


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


            ScriptRunner sr = new ScriptRunner(conn, buf, ctx);         
            while ( (  cmd = parser.next() ) != null ) {
                // do something fancy based on a cmd
                sr.run(cmd);
                // check success/failure of the command

                String errMsg = (String) ctx.getProperty(ctx.ERR_MESSAGE);
                if ( errMsg != null   ){
                    // react to a failure
                    System.out.println("**FAILURE**" + errMsg);
                }               
            }

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

      }

}

Create sequence of sqlplus commands in Java

1 Answers1