[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2455
  • Last Modified:

Handle bulk collect collection in Java

Hi to all!

I am trying to use PL/SQL to add search functionality to my Java WebApp. To do this I make the whole database manipulations (search, retrieval and results storing in PL/SQL) and show the results on a webpage using a resultset in a bean. Among all the PL/SQL routines I have a stored function that returns more than one record. To read this records in Java I used cursors in PL/SQL and the OracleTypes.CURSORS in Java. The problem with this is that I need to leave the cursors open in order for this to function at all, whcih leaves me with an error when the maximum count of open cursors is exceeded. So my question is, if I use "bulk collect into" in PL/SQL to get the records and return a "table of" type of variable from the function, how can I handle this in Java?

Thanks for the help in advance.

Greetings,

al
0
alnasl
Asked:
alnasl
  • 8
  • 8
  • 3
  • +1
2 Solutions
 
CEHJCommented:
>>The problem with this is that I need to leave the cursors open in order for this to function at all

Why so?
0
 
aman123_123Commented:
>> to get the records and return a "table of" type of variable from the function, how can I handle this in >>Java?

Well to return table type of variable return a multi dimensional array or a Map.
0
 
alnaslAuthor Commented:
@CEHJ

Because, if I close the cursor, the return variable becomes a closed cursor. I've had this problem before.

@aman123_123

Could you give me more hints? Do you mean to return array or Map from a PL/SQL stored function?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CEHJCommented:
>>Because, if I close the cursor, the return variable becomes a closed cursor. I've had this problem before.

The return type from your function, as far as Java is concerned, should be a ResultSet. Your cursor should be closed when *it* is closed
0
 
alnaslAuthor Commented:
Sounds logical, I admit, but it doesn't work. I was pretty amazed as well. The Exception "java.sql.SQLException: Cursor is closed." is returned.
0
 
CEHJCommented:
>>The Exception "java.sql.SQLException: Cursor is closed." is returned.

At what point does this happen? Here is an example of an Oracle db object returning a cursor, with calls to it various languages, including Java/JDBC:

http://asktom.oracle.com/tkyte/ResultSets/

0
 
objectsCommented:
Make sure you register your output param as a cursor, and close the returned ResultSet from your Java code.

0
 
alnaslAuthor Commented:
@CEHJ

If the cursors is closed in the stored function, I get "java.sql.SQLException: Cursor is closed". Otherwise, if I leave the cursor open in the stored function, I get "java.sql.SQLException: ORA-01000: maximum open cursors exceeded". My bean is built exactly like in the example from asktom.oracle.com, but it doesn't work, because I iterate through >600 records, each and every one of which opens a cursor. Therefore I need to close the cursors. Total gridlock. The whole thing works only for 20 search results or so. And that's only because of the cursors.

@objects

If by "close the returned ResultSet from your Java code" you mean theResultSetThatHoldsTheCursor.close(), yes, I did that. It didn't work. I guess that means that there is no reverse relation between Java and PL/SQL - closing the java resultset does not close the cursor.
0
 
objectsCommented:
Sounds like a bug in the driver, are u using the latest?
Perhaps raise the issue with the driver developers.

0
 
CEHJCommented:
>>but it doesn't work, because I iterate through >600 records, each and every one of which opens a cursor.

I don't understand. *One* cursor should be iterating through the records, how ever many there may be, although 600 is a very small number. I think you should probably post the code
0
 
alnaslAuthor Commented:
@objects

I kinda have limited options. I am using connecion pool on a BEA WebLogic Server that is not under my control. Currently I am using Oracle Thin Driver, Versions 9.0.1, 9.2.0, 10. As the Oracle version is 10g, I suppose this driver should be OK. The only other options that support Version 10 are an Oracle Thin XA driver and an Oracle OCI driver. Should I try one of them?

@CEHJ
There is one Oracle table that holds some results (>600). That's one resultset. For every record of this table I execute a plsql stored function that opens the cursor that makes the problem. That makes two nested while(rs.next()) loops.


code.txt
0
 
CEHJCommented:
This looks a little strange - you seem to be operating upon metadata rather than data...

>>For every record of this table I execute a plsql stored function that opens the cursor that makes the problem.

It shouldn't be a problem - that's still only two open cursors, the inner one being closed in the right place

How many columns are there usually in 'tblName'

What does this do (might be an idea to post the code)?

>> if (this.getRowCount(con, query) > 0)
0
 
alnaslAuthor Commented:
The variable 'tblName' holds the name of a table that stores some search results. The record count varies from 0 to more than 600 (600 is the biggest number I've reached so far). For every record in this table I execute some queries (as part of an plsql stored function - retrieve_tech_id(?, ?)) that returns a cursor with the results of the queries.

getRowCount(con, query) is a member function hat returns the record count of a query result. In my caase I am just checking if there is something in 'tblName'.

What do you mean by MetaData? These are real records.
code2.txt
0
 
CEHJCommented:
>>The record count varies from 0 to more than 600

No, i was asking about the number of columns, not of rows

>>In my caase I am just checking if there is something in 'tblName'.

That's what i was afraid of. There's no need to do that: the ResultSet won't iterate if there isn't anything. That is an expensive operation, usually requiring a cursor of its own.

>>What do you mean by MetaData? These are real records.

The outer query appears to operate on a table of table names - that's metadata, otherwise you could/should be executing a join


0
 
alnaslAuthor Commented:
I mean, when I test the stored function under plsql, it works perfectly. The table that has the records is also OK.
0
 
alnaslAuthor Commented:
The number of columns is 5.

The table name is Meta Data, yes. And it varies. The idea is to be more or less universal. The Joins are made by the stored function that returns the open cursor that causes the problem.

I agree that the counting of the records is expensive. But even if I get rid of the counting I get the errors caused either by the open cursor or by the closed cursor.

0
 
CEHJCommented:
You need to close both Statement objects. Actually there is no need/room for a PreparedStatement so just use a standard one
0
 
objectsCommented:
you don't show in your code where you close  the outer result set.

>                         if(this.getRowCount(con, query) > 0)

get rid of that line, it appears to serve no purpose.

0
 
alnaslAuthor Commented:
thanks a lot for the help. it worked!
0
 
CEHJCommented:
:-)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 8
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now