Open Cursor Problem in Insert into oracle Database

  • 2 January 2024
  • 2 replies


While inserting the data in oracle database through loop then insert command after some entries bot showing maximum cursor limit exceed although, I am using begin and end database command but no effect of it,


Please help in it

2 replies

Userlevel 4
Badge +6

Check with your DBA to see what the cursor limit is. You may have to commit every X number of records.

Badge +1

The error could be related to exceeding the maximum number of open cursors. To address this:

Firstly, ensure that you are closing the cursor after each iteration of the loop using the CLOSE statement. Failure to close cursors can lead to cursor limit problems.

Consider optimizing your code for bulk insert operations instead of individual inserts within the loop. This can significantly reduce the number of open cursors.

Check the cursor sharing settings; adjusting the cursor sharing parameter to "FORCE" or "SIMILAR" may encourage cursor reuse.

If you have a large number of insert statements, commit the transaction at intervals using the COMMIT statement to release resources.

Review the Oracle database configuration to ensure that the OPEN_CURSORS parameter is appropriately set. Adjust it if necessary in the database initialization file.

Here's a basic example structure of code with cursor management:

DECLARE CURSOR c_data IS SELECT * FROM your_table; BEGIN FOR data_record IN c_data LOOP -- Your insert statements here CLOSE c_data; -- Close the cursor after each iteration END LOOP; COMMIT; -- Commit changes at the end END; /

You can check here 


Thank you