Solved

Handle bulk collect collection in Java

Posted on 2008-06-25
20
2,151 Views
Last Modified: 2013-12-07
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
Comment
Question by:alnasl
  • 8
  • 8
  • 3
  • +1
20 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 21864034
>>The problem with this is that I need to leave the cursors open in order for this to function at all

Why so?
0
 
LVL 4

Expert Comment

by:aman123_123
ID: 21864373
>> 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
 

Author Comment

by:alnasl
ID: 21864479
@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
 
LVL 86

Expert Comment

by:CEHJ
ID: 21864664
>>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
 

Author Comment

by:alnasl
ID: 21864992
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 21868896
>>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
 
LVL 92

Accepted Solution

by:
objects earned 125 total points
ID: 21870350
Make sure you register your output param as a cursor, and close the returned ResultSet from your Java code.

0
 

Author Comment

by:alnasl
ID: 21873786
@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
 
LVL 92

Expert Comment

by:objects
ID: 21873825
Sounds like a bug in the driver, are u using the latest?
Perhaps raise the issue with the driver developers.

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 21873861
>>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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:alnasl
ID: 21874078
@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
 
LVL 86

Expert Comment

by:CEHJ
ID: 21874319
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
 

Author Comment

by:alnasl
ID: 21874532
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 21874661
>>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
 

Author Comment

by:alnasl
ID: 21874687
I mean, when I test the stored function under plsql, it works perfectly. The table that has the records is also OK.
0
 

Author Comment

by:alnasl
ID: 21874731
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
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 125 total points
ID: 21878401
You need to close both Statement objects. Actually there is no need/room for a PreparedStatement so just use a standard one
0
 
LVL 92

Expert Comment

by:objects
ID: 21879831
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
 

Author Comment

by:alnasl
ID: 21926241
thanks a lot for the help. it worked!
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 21926384
:-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
The viewer will learn how to implement Singleton Design Pattern in Java.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now