Wednesday, December 10, 2025

Blog List

Posted by at

Three Oracle cursor error Tests

In this Blog, we will test 3 Oracle cursor errors: 

  ORA-01001: invalid cursor
  ORA-06511: PL/SQL: cursor already open
  ORA-21780: Maximum number of object durations exceeded
Note: Tested in Oracle 19.28


1. Test Setup 



drop table test_tab;

create table test_tab as select level id, 'Name_'||level name from dual connect by level <= 10;

create or replace package test_cursor_errors is
  cursor p_curs(p_id number) is select * from test_tab where id = p_id;
  p_ORA_21780_Maximum number := 0;
  procedure test_ORA_01001(p_id number, p_recur_depth number);
  procedure test_ORA_06511(p_id number);
  procedure test_ORA_21780(p_id number, p_recur_depth number);
end;
/

create or replace package body test_cursor_errors is
  procedure prt(p_recur_depth number, text varchar2) is
  begin
    dbms_output.put_line(rpad(chr(32), 4*p_recur_depth, chr(32))||text);
  end;
  
  --ORA-01001: invalid cursor
  procedure test_ORA_01001(p_id number, p_recur_depth number) is
    l_name varchar2(1000);
    l_chg  integer := 1;  
  begin
    prt(p_recur_depth, '1-1 Start --- p_id = '||p_id||', recur_depth = '||p_recur_depth||', curs%isopen = '||bool_to_char(p_curs%isopen));

    if p_curs%isopen then
      prt(p_recur_depth, '2-1 Close p_curs  --- p_id = '||p_id||', curs%isopen = '||bool_to_char(p_curs%isopen));
      close p_curs;   --Close previous Implicit Cursor
      prt(p_recur_depth, '2-2 p_curs Closed --- p_id = '||p_id||', curs%isopen = '||bool_to_char(p_curs%isopen));
    end if;
    
    for c in p_curs(p_id) loop
      l_name :=c.name;
      prt(p_recur_depth, '3-1 Recur Entry --- p_id = '||p_id||', Implicit Cursor Opened'||', curs%isopen = '||bool_to_char(p_curs%isopen));
      test_ORA_01001(p_id - l_chg, p_recur_depth + 1);
      prt(p_recur_depth, '3-2 Recur Exit  --- p_id = '||p_id||', Close  Implicit Cursor'||', curs%isopen = '||bool_to_char(p_curs%isopen));
    end loop;

    if p_curs%isopen then
      prt(p_recur_depth, '4-1 Close p_curs --- p_id = '||p_id||', curs%isopen = '||bool_to_char(p_curs%isopen));
      close p_curs;
      prt(p_recur_depth, '4-2 p_curs Closed  --- p_id = '||p_id||', curs%isopen = '||bool_to_char(p_curs%isopen));
    end if;
   
    prt(p_recur_depth, '1-2 End --- p_id = '||p_id||', recur_depth = '||p_recur_depth||', curs%isopen = '||bool_to_char(p_curs%isopen));
  end;

  --ORA-06511: PL/SQL: cursor already open
  procedure test_ORA_06511 (p_id number) is
    l_name varchar2(1000);
  begin
    for c in p_curs(p_id) loop
      dbms_output.put_line('1. Loop p_id = '||p_id||', curs%isopen = '||bool_to_char(p_curs%isopen));
      l_name :=c.name;
      for c in p_curs(p_id -1) loop
        dbms_output.put_line('2. Loop p_id = '||p_id||', curs%isopen = '||bool_to_char(p_curs%isopen));
        l_name :=c.name;
      end loop;
    end loop;
  end;
  
  --ORA-21780: Maximum number of object durations exceeded. ORA_21780_Maximum = 65325 (Oracle 19.28), ORA_21780_Maximum = 65318 (Oracle 19.10)
  procedure test_ORA_21780 (p_id number, p_recur_depth number) is
    l_name varchar2(1000);
    l_chg  integer := 0;  
  begin
    if p_curs%isopen then
      close p_curs;   --Close previous Implicit Cursor
    end if;
    
    for c in p_curs(p_id) loop
      begin
	      l_name := c.name;
	      p_ORA_21780_Maximum := greatest(p_ORA_21780_Maximum, p_recur_depth + 1);
	      test_ORA_21780(p_id - l_chg, p_recur_depth + 1);
	    end;
    end loop;

    if p_curs%isopen then
      close p_curs;
    end if;
  end;
end;
/


2. Test Run 



exec test_cursor_errors.test_ORA_01001(2, 1);

exec test_cursor_errors.test_ORA_06511(2);

begin
  test_cursor_errors.p_ORA_21780_Maximum := 0;
  test_cursor_errors.test_ORA_21780(2, 1);
  exception when others then
    dbms_output.put_line('ORA_21780_Maximum = '||test_cursor_errors.p_ORA_21780_Maximum);
    raise;
end;
/


3. Test Outcome


3.1. ORA-01001: invalid cursor


Here the indentation print of recursive calls: 

SQL > exec test_cursor_errors.test_ORA_01001(2, 1);

    1-1 Start --- p_id = 2, recur_depth = 1, curs%isopen = false
    3-1 Recur Entry --- p_id = 2, Implicit Cursor Opened, curs%isopen = true
        1-1 Start --- p_id = 1, recur_depth = 2, curs%isopen = true
        2-1 Close p_curs  --- p_id = 1, curs%isopen = true
        2-2 p_curs Closed --- p_id = 1, curs%isopen = false
        3-1 Recur Entry --- p_id = 1, Implicit Cursor Opened, curs%isopen = true
            1-1 Start --- p_id = 0, recur_depth = 3, curs%isopen = true
            2-1 Close p_curs  --- p_id = 0, curs%isopen = true
            2-2 p_curs Closed --- p_id = 0, curs%isopen = false
            1-2 End --- p_id = 0, recur_depth = 3, curs%isopen = false
        3-2 Recur Exit  --- p_id = 1, Close  Implicit Cursor, curs%isopen = false
BEGIN test_cursor_errors.test_ORA_01001(2, 1); END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 20
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 20
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 20
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 20
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 23
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 23
ORA-06512: at line 1
Elapsed: 00:00:00.00
In Oracle. cursor for loop starts with one implicit cursor open and finishes with one implicit cursor close.
In above test, we have closed cursor in Step 2, and in Step 3-2 of cursor for loop Exit,
Oracle wants to close the implicit cursor again, and throws "ORA-01001: invalid cursor".
("invalid cursor" signifies error when closing an already closed cursor).

Here 10046 trace file with "err=1001" and 'SQL*Net break/reset to client': 

alter session set events '10046 trace name context forever, level 12';  
exec test_cursor_errors.test_ORA_01001(2, 1);
alter session set events '10046 trace name context off';


=====================
PARSING IN CURSOR #140151808236992 len=52 dep=0 uid=49 oct=47 lid=49 tim=6880035008376 hv=4277818309 ad='9d4835d0' sqlid='7g4pxwzzgnny5'
BEGIN test_cursor_errors.test_ORA_01001(2, 1); END;

END OF STMT
PARSE #140151808236992:c=15,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=6880035008375
=====================
PARSING IN CURSOR #140151808572984 len=38 dep=1 uid=49 oct=3 lid=49 tim=6880035009339 hv=1133886660 ad='77b15960' sqlid='c8b161d1tbg64'
SELECT * FROM TEST_TAB WHERE ID = :B1 
END OF STMT
PARSE #140151808572984:c=558,e=848,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=6880035009339
BINDS #140151808572984:

 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f77a2c284c0  bln=22  avl=02  flg=05
  value=2
EXEC #140151808572984:c=932,e=1170,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,plh=3962208483,tim=6880035010574
FETCH #140151808572984:c=65,e=65,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=3962208483,tim=6880035010672
STAT #140151808572984 id=1 cnt=1 pid=0 pos=1 obj=5983275 op='TABLE ACCESS FULL TEST_TAB (cr=3 pr=0 pw=0 str=1 time=58 us cost=3 size=315 card=3)'
CLOSE #140151808572984:c=0,e=0,dep=1,type=3,tim=6880035010802
BINDS #140151808572984:

 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f77a2c284c0  bln=22  avl=02  flg=05
  value=1
EXEC #140151808572984:c=55,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3962208483,tim=6880035010880
FETCH #140151808572984:c=18,e=18,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=3962208483,tim=6880035010927
CLOSE #140151808572984:c=0,e=0,dep=1,type=3,tim=6880035010963
BINDS #140151808572984:

 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f77a2d11a98  bln=22  avl=01  flg=05
  value=0
EXEC #140151808572984:c=62,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3962208483,tim=6880035011043
FETCH #140151808572984:c=13,e=13,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=1,plh=3962208483,tim=6880035011068
CLOSE #140151808572984:c=0,e=0,dep=1,type=3,tim=6880035011100
EXEC #140151808236992:c=2237,e=2765,p=0,cr=10,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=6880035011182
ERROR #140151808236992:err=1001 tim=6880035011196
WAIT #140151808236992: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=528 tim=6880035011230
WAIT #140151808236992: nam='SQL*Net break/reset to client' ela= 5553 driver id=1413697536 break?=0 p3=0 obj#=528 tim=6880035016797
WAIT #140151808236992: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=528 tim=6880035016842
WAIT #140151808236992: nam='SQL*Net message from client' ela= 711 driver id=1413697536 #bytes=1 p3=0 obj#=528 tim=6880035017582


3.2 ORA-06511: PL/SQL: cursor already open


In this test, the same cursor opened twice, hence "ORA-06511: PL/SQL: cursor already open". 

SQL > exec test_cursor_errors.test_ORA_06511(2);

1. Loop p_id = 2, curs%isopen = true
BEGIN test_cursor_errors.test_ORA_06511(2); END;

*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 2
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 43
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 43
ORA-06512: at line 1
Here 10046 trace file with "err=6511" and 'SQL*Net break/reset to client': 

alter session set events '10046 trace name context forever, level 12';  
exec test_cursor_errors.test_ORA_06511(2);
alter session set events '10046 trace name context off';


=====================
PARSING IN CURSOR #140498746628816 len=49 dep=0 uid=49 oct=47 lid=49 tim=6880557312015 hv=243630832 ad='72f8ab68' sqlid='1cu3xcc78b0rh'
BEGIN test_cursor_errors.test_ORA_06511(2); END;

END OF STMT
PARSE #140498746628816:c=50,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=6880557312014
=====================
PARSING IN CURSOR #140498750073448 len=38 dep=1 uid=49 oct=3 lid=49 tim=6880557312222 hv=1133886660 ad='77b15960' sqlid='c8b161d1tbg64'
SELECT * FROM TEST_TAB WHERE ID = :B1 
END OF STMT
PARSE #140498750073448:c=77,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3962208483,tim=6880557312222
BINDS #140498750073448:

 Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fc869ff9388  bln=22  avl=02  flg=05
  value=2
EXEC #140498750073448:c=52,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3962208483,tim=6880557312308
FETCH #140498750073448:c=62,e=62,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=3962208483,tim=6880557312393
STAT #140498750073448 id=1 cnt=1 pid=0 pos=1 obj=5983275 op='TABLE ACCESS FULL TEST_TAB (cr=3 pr=0 pw=0 str=1 time=58 us cost=3 size=315 card=3)'
CLOSE #140498750073448:c=0,e=1,dep=1,type=3,tim=6880557312532
EXEC #140498746628816:c=512,e=513,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=6880557312562
ERROR #140498746628816:err=6511 tim=6880557312573
WAIT #140498746628816: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=528 tim=6880557312605
WAIT #140498746628816: nam='SQL*Net break/reset to client' ela= 696 driver id=1413697536 break?=0 p3=0 obj#=528 tim=6880557313324
WAIT #140498746628816: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=528 tim=6880557313347
WAIT #140498746628816: nam='SQL*Net message from client' ela= 5220 driver id=1413697536 #bytes=1 p3=0 obj#=528 tim=6880557318586


3.3 ORA-21780: Maximum number of object durations exceeded


In this test, we make an infinitive recursive calls, in each call, open the same cursor,
then hit "ORA-21780: Maximum number of object durations exceeded".
(The only difference of this test to "ORA-01001" code is that "l_chg = 0" instead of "l_chg = 1").

The test shows that the maximum value is around 65339. 

SQL > begin
        test_cursor_errors.p_ORA_21780_Maximum := 0;
        test_cursor_errors.test_ORA_21780(2, 1);
        exception when others then
          dbms_output.put_line('ORA_21780_Maximum = '||test_cursor_errors.p_ORA_21780_Maximum);
          raise;
      end;
      /
            
ORA_21780_Maximum = 65339
begin
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 59
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 59
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 59
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 59
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 59
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 59
ORA-01001: invalid cursor
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 59
ORA-21780: Maximum number of object durations exceeded.
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 51
ORA-06512: at "K.TEST_CURSOR_ERRORS", line 63

Elapsed: 00:00:03.00


4. Pass Cursor as Integer Input


In one application, client program wants to receive cursor input represented by an integer.
Here is one implementation: 

create or replace package test_cursor_number as 
  --p_recordset     sys_refcursor;  -- PLS-00994: Cursor Variables cannot be declared as part of a package
  function get_id_cur (p_id in test_tab.id%type) return integer;
end;
/

create or replace package body test_cursor_number as 
  function get_id_cur (p_id in test_tab.id%type) return integer as
    l_cursor_number   integer;
  begin
    l_cursor_number := dbms_sql.open_cursor;
    dbms_sql.parse (l_cursor_number, 'SELECT id, name from test_tab where id <= '||p_id, dbms_sql.NATIVE);
    return l_cursor_number;
  end;
end;
/

declare
  l_cursor_number   integer;
  l_return          integer;
  l_cursor          sys_refcursor;
  l_id              test_tab.id%type;
  l_name            test_tab.name%type;
begin
  l_cursor_number := test_cursor_number.get_id_cur(p_id => 3);
  dbms_output.put_line('cursor_number = '||l_cursor_number);
  
  l_return := dbms_sql.execute (l_cursor_number);
  dbms_output.put_line('execute return = '||l_return);
  
  l_cursor := dbms_sql.to_refcursor (l_cursor_number);
  --dbms_output.put_line('l_cursor = '||l_cursor);      
  loop
    fetch l_cursor
    into l_id, l_name;

    exit when l_cursor%notfound;
    dbms_output.PUT_LINE (l_id || ' | ' || l_name);
  end loop;

  close l_cursor;
end;
/

-- Test Output
cursor_number = 357706397
execute return = 0
1 | Name_1
2 | Name_2
3 | Name_3
By the way, Oracle Magazine On Connection Pools, Cursor Differentiation, and Optimal Ordering (By Tom Kyte May/June 2012) discussed the regular cursor vs. ref cursor, and showed the performance difference.
Posted by at
Subscribe to: Comments (Atom)