Why can't I execute the main part of the .sql file without using exit at the end of the sql file through java

Question

I am trying to execute a .sql file from java. It starts successfully when I put exit at the end of the .sql file. Is it possible to run without providing output in .sql?

Java code

import java.io.BufferedReader; import java.io.File; import java.io.FileFilter; import java.io.IOException; import java.io.InputStreamReader; public class Test { private static String script_location = ""; private static String file_extension = ".sql"; private static ProcessBuilder processBuilder =null; public static void main(String[] args) { try { //D:/Tset is the folder that contains the.sql files File file = new File("D:/Tset"); File [] list_files= file.listFiles(new FileFilter() { public boolean accept(File f) { if (f.getName().toLowerCase().endsWith(file_extension)) return true; return false; } }); for (int i = 0; i<list_files.length;i++){ script_location = "@" + list_files[i].getAbsolutePath(); //ORACLE processBuilder = new ProcessBuilder ("sqlplus","user56/password", script_location); //ORACLE //script_location = "-i" + list_files[i].getAbsolutePath(); // processBuilder = new ProcessBuilder("sqlplus", "-Udeep-Pdumbhead-Spc-de-deep\\sqlexpress-de_com",script_location); processBuilder.redirectErrorStream(true); Process process = processBuilder.start(); BufferedReader in = new BufferedReader(new InputStreamReader(process.getInputStream())); String currentLine = null; while ((currentLine = in.readLine()) != null) { System.out.println(" " + currentLine); } } } catch (IOException e) { e.printStackTrace(); } catch(Exception ex) { ex.printStackTrace(); } } } 

Oracle script file:

createtable.sql

 createtable.sql create table t1(empname varchar2(20),address varchar2(20)) / create table t2(name varchar2(20),lname varchar2(20)) / exit; 

insertvalue.sql

 insert into t1 values('aaaaa','chennai') / insert into t2 values('bbbbb','ddddd') / exit; 

If I do not lay out exit, it just runs only the first file. Is there a solution for this?

Answer 1

In your code, you run the sqlplus command in a loop, so you must end each command (using exit to exit sqlplus).
If you want to call many scripts in a single sqlplus instance, perhaps you can create an on-the-fly script that has commands such as:
onTheFlyAcript.sql

 @createtable.sql <your params> @insertvalue.sql <your params> exit; 

So you can do something like:

 try { Printwriter out = new PrintWriter(new FileWriter("D:/Test/onTheFlyScript.sql")); for (int i = 0; i<list_files.length;i++){ script_location = "@" + list_files[i].getAbsolutePath(); out.println("START script_location"); } out.close(); } catch (IOException e){ e.printStackTrace(); } // note that this command is not in the loop, it just for running the new script you've created processBuilder = new ProcessBuilder ("sqlplus","user56/password", "D:/Test/onTheFlyAcript.sql"); 

This will probably have to be debugged, but the idea is that instead of executing all your scripts one by one using sqlplus, you create a new script that contains calls to all your scripts.
Now you can remove the exit sqlplus exit from all of your scripts since you do not need to exit sqlplus (only onTheFlyScript.sql)