This discussion is archived
7 Replies Latest reply: Nov 16, 2012 7:41 AM by rukbat RSS

How to display sqlplus result in java?

880186 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    It works ,thanks a lot
  • 5. Re: How to display sqlplus result in java?
    974669 Newbie
    Currently Being Moderated
    Its not working hanging :(
  • 6. Re: How to display sqlplus result in java?
    gimbal2 Guru
    Currently Being Moderated
    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?
    rukbat Guru Moderator
    Currently Being Moderated
    Moderator Action:
    Thread locked to prevent any additional resurrection hijacks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points