Does Oracle DB support multiple (parallel) operations per connection?

Question

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.

Answer 1

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.

Answer 2

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.