Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Handle bulk collect collection in Java

Posted on 2008-06-25
20
Medium Priority
?
2,344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 500 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
 

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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

704 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