7 Replies Latest reply: Nov 16, 2012 9:41 AM by rukbat RSS

    How to display sqlplus result in java?

    880186

      I want to call sqlplus to execute some sql commands in java,but how can i print the result of sqlplus?

      In java code ,i call sqlplus like:

      Process p;
      //execute the command
      p = Runtime.getRuntime().exec(commandString);
      //print return result of the command
      InputStream inputStream = p.getInputStream();

      How to display the execution result?

      Edited by: Yi on Feb 26, 2012 11:41 PM

        • 1. Re: How to display sqlplus result in java?
          rp0428
          Use the streams provide by the Process returned by the exec.

          http://docs.oracle.com/javase/1.5.0/docs/api/java/lang/Process.html
          Process p = Runtime.getRuntime().exec("sqlplus user/pwd@connect @test.sql"); 
          OutputStream os = p.getOutputStream();
          • 2. Re: How to display sqlplus result in java?
            880186
            Sorry i forgot to clarify that i have used getInputStream. It works for common command like "copy","dir"...
            But the return result in sqlplus console is special,which cannot be displayed.
            • 3. Re: How to display sqlplus result in java?
              rp0428
              You can use ProcessBuilder. This code launches sql*plus and runs the script in the 'filename' variable. It displays the results in NetBeans console.
              import java.io.BufferedReader;
              import java.io.InputStreamReader;
              import java.io.File;
              import java.util.Map;
              
              public class test1 {
              
                  public static void main (String args []) {
              
                      test_script();
                  }
              
                  public static void test_script () {
              
                      String fileName = "@test_table.sql";
                      String sqlPath = "E:\\";
              
                      String sqlCmd = "sqlplus";
              
                      String arg1   = "user/password@sid"; -- plug in your user, password and db name
                      String arg2   = fileName;
                      try {
                          String line;
                          ProcessBuilder pb = new ProcessBuilder(sqlCmd, arg1, arg2);
                          Map<String, String> env = pb.environment();
                          env.put("VAR1", arg1);
                          env.put("VAR2", arg2);
                          pb.directory(new File(sqlPath));
                          pb.redirectErrorStream(true);
                          Process p = pb.start();
                        BufferedReader bri = new BufferedReader
                          (new InputStreamReader(p.getInputStream()));
                        BufferedReader bre = new BufferedReader
                          (new InputStreamReader(p.getErrorStream()));
                        while ((line = bri.readLine()) != null) {
                          System.out.println(line);
                        }
                        bri.close();
                        while ((line = bre.readLine()) != null) {
                          System.out.println(line);
                        }
                        bre.close();
                        System.out.println("Done.");
                      }
                      catch (Exception err) {
                        err.printStackTrace();
                      }
              
                  }
              
              }
              Here is the contents of the script placed at E:\\test_table.sql
              Prompt drop TABLE ANOTHER_TEST;
              DROP TABLE ANOTHER_TEST CASCADE CONSTRAINTS
              /
              
              Prompt Table ANOTHER_TEST;
              CREATE TABLE ANOTHER_TEST
              (
                BATCH_SEQ             NUMBER,
                BATCH_GROUP_ID        NUMBER,
                STATUS_FLAG           VARCHAR2(30 BYTE),
                OBJ_BEING_PROCESSED   VARCHAR2(80 BYTE),
                BATCH_RUN_START_DTTM  DATE,
                BATCH_RUN_END_DTTM    DATE,
                CREATE_DTTM           DATE,
                CREATE_USER           VARCHAR2(30 BYTE),
                UPDATE_DTTM           DATE,
                UPDATE_USER           VARCHAR2(30 BYTE)
              )
              LOGGING 
              NOCOMPRESS 
              NOCACHE
              PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
              MONITORING
              /
              
              COMMENT ON TABLE ANOTHER_TEST IS 'This is a test table.'
              /
              
              EXIT
              /
              • 4. Re: How to display sqlplus result in java?
                880186
                It works ,thanks a lot
                • 5. Re: How to display sqlplus result in java?
                  974669
                  Its not working hanging :(
                  • 6. Re: How to display sqlplus result in java?
                    gimbal2
                    Then you did something wrong. Create a new thread in stead of hijacking an existing one and actually provide information, such as the code that is hanging. Be sure to use \
                     tags when posting code.                                                                                                                                                                                                                                                                                                                                                                                                                    
                    • 7. Re: How to display sqlplus result in java?
                      Moderator Action:
                      Thread locked to prevent any additional resurrection hijacks.