posted 20 years ago
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 ]