3

I have a JDBC connection to an Oracle database. I create a Statement. The SQL query String contains multiple statements separated by a semicolon, and is provided by a different system.

Example:

connection.prepareStatement("SELECT * FROM A; SELECT * FROM B");

According to ddimitrov it isn't possible.

But all other databases I've tried support it. And JDBC even has support to retrieve multiple results.

Does anyone have either pointers to Oracle documentation explicitly stating that it is not supported or have a way to make it work (without using of stored procedures)?

Community
  • 1
  • 1
Roel Spilker
  • 32,258
  • 10
  • 68
  • 58

3 Answers3

4

For executing multiple statements:

JDBC 2.0 lets you submit multiple statements at one time with the addBatch method

See here.

Óscar López
  • 232,561
  • 37
  • 312
  • 386
  • Thanks for your pointer. In my case, I get the query string from a different system. I've updated the question to reflect this. – Roel Spilker Nov 30 '11 at 14:17
  • You're welcome. But still, I think `addBatch` would be useful for you, simply split the string and send each statement individually in a batch – Óscar López Nov 30 '11 at 15:23
  • Splitting the string might be less trivial than you would assume. Eg with Firebird the isql utility (and some admin tools) allow/require you to redefine the statement termination symbol when you execute a statement which creates a stored procedure (see [SET TERM](http://www.destructor.de/firebird/storedproc.htm)) so you can execute multiple statements in a script. Finding the right termination character to split on might therefor require non-trivial parsing. – Mark Rotteveel Nov 30 '11 at 16:22
4

No, this is not possible with the Oracle JDBC driver.

You will have to parse and split the string into their individual statements.

Btw: I think the only databases that allow this are Microsoft SQL Server and MySQL. Which also makes them vulnerable to certain kind of SQL injection attacks that would not work Oracle or PostgreSQL.

1

AFAIK most databases only allow you to execute / prepare one statement per execute or prepare call. Although not very explicitly expressed, the intent of the JDBC methods is to execute a single SQL statement:

sql - **an** SQL statement that may [...]

The retrieval of multiple resultsets is for (very rare) single(!) statements or stored procedures which return multiple resultsets (as explained in the javadoc of Statement#execute).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197