How to execute SQL file from CMD prompt using Java ProcessBuilder

Question

I have written a java program using ProcessBuilder to launch CMD prompt & connect SQLPLUS to execute some SQL files.

public class OracleConnect {

    public static void main(String[] args) throws IOException {
        String[] cmd = new String[] { "sqlplus", "<USER>/<PASSWORD>@<INSTANCE>" };
        ProcessBuilder builder = new ProcessBuilder(cmd);
        Process process = builder.start();
        BufferedReader br = new BufferedReader(new InputStreamReader(process.getInputStream()));
        BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(process.getOutputStream()));
        new ReadFromConsole(br).start();
        new WriteToConsole(bw).start();
    }
}

class AbsThread extends Thread {
    boolean read = true;
    boolean write = false;
}

class ReadFromConsole extends AbsThread {

    BufferedReader processOut;

    public ReadFromConsole(BufferedReader processOut) {
        this.processOut = processOut;
    }

    @Override
    public void run() {
        while (read) {
            int i = -1;
            try {
                while ((i = processOut.read()) != -1) {
                    char ch = (char) i;
                    System.out.print(ch);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            write = true;
            read = false;
        }
    }

}

class WriteToConsole extends AbsThread {

    BufferedWriter processIn;

    public WriteToConsole(BufferedWriter processIn) {
        this.processIn = processIn;
    }

    @Override
    public void run() {
        while (write) {
            System.out.print("Enter command ");
            String cmd = new Scanner(System.in).nextLine();
            try {
                processIn.write(cmd);
                processIn.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
            read = true;
            write = false;
        }
    }
}

Output in Console :

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 19 14:07:53 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options

SQL>

The program hangs on reading the BLINK_CHARACTER, I tried both read() and readLine() it doesn not read BLINK_CHARACTER.

If I do manual, getting following response in CMD prompt

C:>sqlplus USER/PASSWORD@INSTANCE

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 19 12:29:44 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options

SQL> _[BLINK_CHARACTER]

How to make it interactive by giving username and password at runtime? and also i need to implement the same kind interactive CMD prompt for Informatica process also.

PS : there are options to login & execute SQL files in a single command itself in SQLPLUS but i want to make it interactive

Peter Lang · Accepted Answer · 2015-01-19 09:00:26Z

1

If you remove the username/password and the @ character from your connect string, SQL*Plus will prompt for the username/password:

sqlplus <INSTANCE>

SQL*Plus: Release 11.2.0.0.0 - Production on Mon Jan 19 14:07:53 2015
(c) Copyright 1982, 2008 Oracle Corporation. All rights reserved.
Enter user-name:

answered Jan 19, 2015 at 9:00

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

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

2 Comments

Sylvain Leroux Over a year ago

@Saravana For security reason, many tools don't read password from stdin, but from the attached tty instead. I don't know if this is the case for SQL*Plus but it looks like...

Saravana Over a year ago

I don't want it to read password from CMD, I can login to SQLPLUS just in a single line by executing sqlplus USER/PASSWORD@INSTANCE, after login i will get a SQL > prompt, here I want to execute some SQL files

Collectives™ on Stack Overflow

How to execute SQL file from CMD prompt using Java ProcessBuilder

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related