Solution to an error where the ORA-01000 exceeds the maximum number of opened cursors when the Code accesses Oracle

A ORA-01000 error occurred when a colleague was importing data in batches from excel the day before.

This is a similar phenomenon that occurs on Google. It is Java.CodeWhen accessing Oracle, we use C # for the same reason.

The Java scenario is as follows (pseudocode description ):

1. Create a connection and a transaction

2. Create statement cyclically, attach SQL statements, and execute statement.

3. transaction commit and conn close release

 

C # The scenario is as follows (pseudocode description ):

1. Create a connection and a transaction

2. Create Commander cyclically, attach SQL, and execute commander

3. transaction commit and conn close release

However, all results are incorrect. When multiple batch operations are performed.

Default Number of connections to downstream targets: Show paramenter cursor, 300

The number of batch import executions performed by our colleagues is also greater than 300. It can be determined that it is related to the setting of cursor.

Solution:

1. Increase the default number of cursor instances)

2,ProgramSolve the problem in the code.

Analysis: In the above scenario code, the statement and Commander objects in the loop body are created each time. Is it caused by this: it should be recycled after each loop (. net & Java can automatically recycle objects), but because GC is too late to process, it is too late to release, resulting in cursor exceeding the default limit?

Try: manually close the statement and Commander objects in the loop body each time. The error message returned is not...

Cause: as we imagine, a statement/commander corresponds to a cursor on a connection.

Solution: It is very simple. Now that you know the reason. Well, disable statement/commander in each loop body, or declare this object in vitro, and assign values to SQL in the loop body to solve the problem. Pai_^