| Pro*C: cursor leak with cursor array [message #265790] |
Fri, 07 September 2007 07:12  |
donnadoe
Messages: 2
Registered: September 2007
|
Junior Member |
|
|
Hi:
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER) IS
BEGIN
OPEN curs FOR SELECT -1, '*', -1 FROM DUAL WHERE 1=dept_num;
END;
END emp_demo_pkg;
/
And here is the Pro*C code:
/*
* The cursor is opened in the stored PL/SQL procedure
* open_cur, in the EMP_DEMO_PKG package.
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>
#define MAXCURSORS 50
/* Error handling function. */
void sql_error(void)
{
EXEC SQL WHENEVER SQLERROR continue;
printf("%.70s\n", sqlca.sqlerrm.sqlerrmc);
exit(1);
}
void main()
{
char temp[32];
EXEC SQL BEGIN DECLARE SECTION;
char * uid = "scott/tiger";
SQL_CURSOR * cursor_array;
int dept_num;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR do sql_error();
/* Connect to Oracle. */
EXEC SQL CONNECT :uid;
/* Allocate the cursor variables. */
cursor_array = new sql_cursor[MAXCURSORS];
for (int j = 0; j < MAXCURSORS; j++)
{
EXEC SQL ALLOCATE :cursor_array[j];
}
for (;;)
{
printf("\nEnter number of loops (0 to exit): ");
gets(temp);
if (atoi(temp) <= 0)
break;
for (dept_num=0; dept_num < atoi(temp); dept_num++)
{
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR * s_cursor = &(cursor_array[dept_num%MAXCURSORS]);
EXEC SQL END DECLARE SECTION;
EXEC SQL EXECUTE
begin
emp_demo_pkg.open_cur(:s_cursor, :dept_num);
end;
END-EXEC;
/* Close the cursor. */
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL CLOSE :s_cursor;
}
}
exit(0);
}
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops".
To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50.
If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
Am I correct?. Are there any other cursors I should be aware of?.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
