run oracle sql script from java [duplicate]

Use below solution for your reference , i have tried and tested and running successfully.

private static String script_location = "";private static String file_extension = ".sql";private static ProcessBuilder processBuilder =null;public static void main(String[] args) {    try {        File file = new File("C:/Script_folder");        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",        "UserName/Password@database_name", 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();    }}

Use this snippet code and try and run.

Thanx to user mentioned the solution in the below link:

http://forums.sun.com/thread.jspa?threadID=5413026

Regards | Nitin

The iBATIS ScriptRunner has a setDelimiter(String, boolean) method. This allows you to have a string other than ";" to be the separator between SQL statements.

In your Oracle SQL script, separate the statements with a "/" (slash).

In your Java code, before calling the runScript do a setDelimter("/", false) which will instruct the ScriptRunner to recognize "/" as statement separator.

Had the same problem not so long ago, bumped into your question several times while googling for a solution, so I think I owe you—here are my findings so far:

In brief, there are no ready solutions for that: if you open Ant or Maven sources, you'll see they are using a simple regexp-based script splitter which is fine for simple scripts, but usually fails on e.g. stored procedures. Same story with iBATIS, c5 db migrations, etc.

The problem is, there's more than one language involved: in order to run "SQL Scripts" one must be able to handle (1) SQL, (2) PL/SQL, and (3) sqlplus commands.

Running sqlplus itself is the way indeed, but it creates configuration mess, so we tried to avoid this option.

There are ANTLR parsers for PL/SQL, such as Alexandre Porcelli's one—those are very close, but no one prepared a complete drop-in solution based on those so far.

We ended up writing yet another ad hoc splitter which is aware of some sqlplus commands like / and EXIT— it's still ugly, but works for most of our scripts. (Note though some scripts, e.g., with trailing -- comments, won't work—it's still a kludge, not a solution.)