Run Oracle Sql Script From Java

i have a bunch of sql scripts that should upgrade the database when the java web application starts up. i tried using the ibatis scriptrunner, but it fails gloriously when defining

Solution 1:

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.)

Solution 2:

sqlplus : yes you can. I run sqlplus from within Xemacs(editor) all the time. So, you can run sqlplus in an interpreted mode and then provide it commands and read the output as well.

Another ways is to download the free java based SQL developer tool from oracle (http://www.oracle.com/technology/software/products/sql/index.html). it comes with a sqlcli.bat utility which is a wrapper over a java program. You might want to use this command line utility to do your work.

summary, I would try running sqlplus in the background and provide it's input and reading its output (like emacs does).

Solution 3:

If you want to write your own script runner, you can use Spring JDBC's SimpleJdbcTemplate (http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html).

You can, of course, load the scripts as you would any resource in Spring as well.

Solution 4:

You can see other people's implementations. See this resource: "Open Source SQL Clients in Java" http://java-source.net/open-source/sql-clients