Max Open Cursor! Please Help.

[rohan prakrit]

Hi !

I have a problem with MAX Open Cursors !

My requirement is like this, i need to query 50,000 odd records from one database and then query depending on primary key to the same table in another database. If the data exists in db2 then i need to update else i need to insert the record from db1 into db2.

****************************************************************************My psuedo code looks like this :

connection object 1 for db1
connection object 2 for db2

preparedstatement1(query on db1)
iterate through resultset1 - this iterates 50,000 times.
{

preparedstatement2(query if data for this particular record exists in db2)
get the count
close the preparedstatement2 and it's corresponding resultset.
explicitly set this as null too

if(count>0)
{
preparedstatement3(form the update query on db2 with values from resultset1)
resultset3

add the preparedstatement3 to the batch

if batchsize exceeds the limit then [the batch limit is set as 50]
{
execute the batch in one shot
close preparedstatement3 and resultset3
also set them as null
}
}
else
{
preparedstatement4(form the insert query on db2 with values from resultset1)
resultset4

add the preparedstatement4 to the batch

if batchsize exceeds the limit then [the batch limit is set as 50]
{
execute the batch in one shot
close preparedstatement4 and resultset4
also set them as null
}
}
}

close preparedstatement1 and resultset1
also set them as null

close connection object 1 and 2
also set them as null

******************************************************************************************************************

My open_cursor limit is 450 and diligently my code fails when updating/inserting 460th odd record !!! why does this happen when i closed my prepared statement and resultset as frequently as i could ?

Please help me with this issue ! am at my wits end !

Thanks !
-Rohan
[ November 18, 2004: Message edited by: Bear Bibeault ]

[rohan prakrit]

Gosh !

How could i be soo stupid. I was defeating the very purpose of Prepared Statement and that was the reason why i encountered this Max Open Cursor Problem.

Now am creating all the prepared statements before iterating through the resultset1. Inside the iteration i just bind the variables to prepared statement 2,3 and 4 and that solved the issue for me

Anyways, Thanks Guys !
-Rohan

[Jeanne Boyarsky]

Rohan,
Thanks for posting the solution for people who read this in the future.

And welcome to JavaRanch!