5

I've got sql file that contains sqlplus specific script: it includes / or ; as statement terminators, EXEC to execute stored procedures, etc.

I need to execute this script from java (jdbc) without the need for sqlplus.

sql ant task / maven sql plugin can't handle the different terminators or EXEC commands.

Do you know any good way to do so without running sqlplus ?

m_vitaly
  • 11,856
  • 5
  • 47
  • 63

2 Answers2

3

We've bumped into the same problem... 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.

There are indeed ANTLR parsers for PL/SQL, such as Alexandre Porcelli's one—those are very close, but still not ready to be used as a drop-in solution.

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.

alf
  • 8,377
  • 24
  • 45
1

I can think of two options. One you don't want - to Runtime.exec() it to sqlplus. The other is to read in the file removing end of line slashes and sending to JDBC when you hit a semicolon. Or adding to a batch when that happens to reduce trips to the database.

Jeanne Boyarsky
  • 12,156
  • 2
  • 49
  • 59
  • That's exactly what I'm trying to do, but I need a good SQLPLUS-like parser that will split the entire file to smaller parts. Simple split by / or ; chars won't work – m_vitaly Aug 16 '11 at 17:57