Summary of the relationship between Oracle cursor and jdbc

An exception occurs when the maximum number of jdbc cursors is exceeded. The relationship between oracle and jdbc has been improved. Record it for future reference. If you have any errors, please note. Oral

An exception occurs when the maximum number of jdbc cursors is exceeded. The relationship between oracle and jdbc has been improved. Record it for future reference. If you have any errors, please note. Oral

An exception occurs when the maximum number of jdbc cursors is exceeded. The relationship between Oracle and jdbc has been improved. Record it for future reference. If you have any errors, please note.

Oralce cursors can be divided into display cursors and implicit cursors:

The two cursors are displayed in v $ open_cursor.

However, the two cursors are different from each other:
Most of the implicit cursors are created through stored procedures. For example, if you Select a query during the stored procedure, an implicit cursor is opened, and a record is recorded in Open_cusor. This type of cursor will not be closed with the closure of the CallableStatement object. He will stay in the cache all the time. This is one of the reasons why stored procedures are executed much faster. Only when the number of opened cursors is greater than the maximum number of opened cursors you set will the call be closed according to the implicit cursors in the cache. The maximum number of cursors is set in system parameters. Oracle8i is 300 connections by default. Such a cursor can only improve the row but not reduce the performance.

The displayed cursor is better explained. For example, you can use JDBC to create a statement, preparestatement, and execute some query statements. Each time you execute a query, a display cursor is opened. Close Statement will close these cursors. If you do not close them, the number of displayed cursors will increase. When the number of opened cursors is greater than the maximum number of cursors, you will check whether there are any hermit cursors, if yes, the cursor is displayed. When the maximum number of displayed cursors exceeds the maximum value, an error is reported: the maximum number of allowed cursors is exceeded. So as long as you ensure that all Statement is disabled, there will be no problem.

At the same time, the system will end this session and release all cursor. The JDBC Standard specifies the statement. close ....

NOTE: For the connection pool technology, only the connection is returned to the connection pool. If statement is disabled and the resultset resource is not returned, the cursor exceeds the maximum number.

Exception: java. SQL. SQLException: ORA-01000: maximum open cursors exceeded

1. getConnection is equivalent to opening a database session,

View the session Statement: select * from v $ session ss where ss. USERNAME = 'iskk _ Test'

2. statement, prepareStatement, and callableStatement obtain a cursor, which is not displayed in v $ open_cursor. A cursor is opened only after execute, which can be queried in v $ opent_cursor.

Select COUNT (*) from v $ open_cursor o
Where o. USER_name = 'iskk _ Test'
Order by o. LAST_ SQL _ACTIVE_TIME desc

3. view the maximum number of cursors for each connection

Show parameter session_cached_cursors;

4. view the maximum number of cursors cached by the session

Show parameter session_cached_cursors;

Ojdbc14.jar (oracle10.2) Test Result:

Execute the same statement cyclically. Only one cursor is used, and no new cursor is generated to execute different statements cyclically. Multiple cursor instances are generated.