6

My Java application needs to hold cursor to Oracle DB for some time. During it other DB statements have to be made. Does this require separate DB connections or same (cursor's one) can be used?

Thanks.

Ralkie
  • 3,736
  • 3
  • 25
  • 25
  • did you try? what results did that give? – Mat May 10 '11 at 08:32
  • What sort of Java application are you talking about? Oracle is perfectly fine with dedicated sessions which handle a series of statements. However, most Java applications are web apps, and web apps generally use *stateless* protocols and connection pooling; this means it is hard to reserve one database session for a specific user to issue commands over a period of time. So, more details please. Also did you mean *"parallel"* or should that be "sequential"? Oracle supports Parallel query/DML but it is a different question. – APC May 10 '11 at 09:26

4 Answers4

6

The only restriction is that a single statement can only have a single ResultSet at a given time. Note that a statement can produce multiple ResultSets but you have to access them sequentially (using getNextResult())

To be able to have multiple open ResultSets/Cursors you need multiple java.sql.Statement objects.

A single connection can only have a single active (i.e. running) statement. So if you have need multiple open cursors (ResultSets) you need to run them sequentially (one after the other) each with their own Statement object.

4

Oracle has no problem with what the MSSQL folks call MARS (Multiple active result sets).

You can see this kind of thing in a lot of PL/SQL code, and for that matter PL/SQL is "just" a client to the SQL engine as is your Java code:

for a in (select field1, field2 from table1) loop
  for b in (select * from table2 where SomeField = a.Field1) loop
    ...
  end loop;
end loop;

Don't take my word for it, though. You can create a nested loop like this yourself in Java.

Robert Giesecke
  • 4,314
  • 21
  • 22
1

Of course you can hold multiple open cursors while you're issuing other queries on the same connection. However, it's not possible to issue other queries or statements while the first cursor is beeing opened. That's because only one request can be active (i.e. beeing executed) in an Oracle session at any point in time.

HAL 9000
  • 3,877
  • 1
  • 23
  • 29
0

You can use the concept of database pooling.

Click Here

It provides a pool of database connections so whenever needed you can get a database connection from pool.

It is also memory optimized since database connection and closing is a heavy process.

Ankit
  • 2,753
  • 1
  • 19
  • 26
  • Entire point of the question was the OP does not want multiple connections, but, wants to know if he can execute other sqls at the same time as keeping a cursor open -- which he can! – James Anderson May 10 '11 at 09:23
  • Yes, I can (and probably will) use pooling, but, as James pointed out, question was about using same connection. – Ralkie May 10 '11 at 09:26