Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Dee.

Asked: October 11, 2001 - 8:58 pm UTC

Last updated: March 28, 2012 - 9:06 am UTC

Version: 8.0.5

Viewed 10K+ times! This question is

You Asked

I know from experience that you should always close cursors. Does this apply to ref cursors? I find that if I close ref_cursors I loose my results set. It does not seem to matter where I put the close cursor statement.

Is there a standard for ref_cursors?



and Tom said...

you close cursors AFTER you use them. Hence you close a ref cursor after you fetch from it. If you lose your result set -- that indicates you are not done with the ref cursor.

just treat a ref cursor as you would any other cursor, close it after you are finished with it.

Rating

  (39 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 12, 2001 - 1:07 pm UTC


A reader, October 12, 2001 - 4:20 pm UTC

How, would I close a ref cursor explicitly, after I fetch from it.

I thought this was implicit functionality.

Tom Kyte
October 12, 2001 - 4:24 pm UTC

It depends on the language.

Pro*c: EXEC SQL CLOSE :ref_cursor_variable;

SQLPlus: Implicit

PLSQL: Close ref_cursor_variable;

Java: rset.close();

and so on.

example

A reader, October 12, 2001 - 4:32 pm UTC

Give us an example and show how we have to do it in

PLSQL: Close ref_cursor_variable;

For example I have a function which returns a ref cursor, where do you want me to close it.


Tom Kyte
October 12, 2001 - 6:44 pm UTC

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package types
  2  as
  3      type rc is ref cursor;
  4  end;
  5  /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function foo return types.rc
  2  as
  3      l_cursor types.rc;
  4  begin
  5      open l_cursor for select * from dual;
  6          return l_cursor;
  7  end;
  8  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace  procedure bar
  2  as
  3      l_cursor types.rc;
  4      l_rec    dual%rowtype;
  5  begin
  6      l_cursor := foo;
  7      loop
  8          fetch l_cursor into l_rec;
  9          exit when l_cursor%notfound;
 10          dbms_output.put_line( l_rec.dummy );
 11      end loop;<b>
 12      close l_cursor;</b>
 13  end;
 14  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec bar
X

PL/SQL procedure successfully completed. 

why cursor leak?

A reader, October 12, 2001 - 6:13 pm UTC

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class curvar
{
public static void showOpenCnt( Connection conn, String msg)
throws SQLException
{
Statement stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery
( "select a.value, b.name "+
"from v$mystat a, v$statname b "+
"where a.statistic# = b.statistic# "+
"and a.statistic#= 3");
System.out.println( msg );

while( rset.next() )
System.out.println( rset.getString(1)+ " " +
rset.getString(2) );
System.out.println( "-----------------------" );
rset.close();
stmt.close();
}

public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String query =
"begin :1:=getemp( :2 ); end;";
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn=
DriverManager.getConnection
("jdbc:oracle:thin:@harps:1521:wpdb",
"scott", "tiger");
CallableStatement cstmt=null;
showOpenCnt( conn, "Before Anything" );
cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
ResultSet rset=null;

for (int j=1;j<10;j++)
{

cstmt.setString(2,"USER1");
cstmt.execute();
showOpenCnt( conn, "After prepare and execute" );
rset = (ResultSet)cstmt.getObject(1);
showOpenCnt( conn,
"After prepare and execute and getObject" );

for(int i = 0; rset.next(); i++ )
{
if ( i == 10 )
showOpenCnt(conn,"After fetching 10 rows...");
}

showOpenCnt( conn, "After but before any close" );

rset.close();
showOpenCnt( conn, "After rset.close" );
}
cstmt.close();
showOpenCnt( conn, "After cstmt.close" );
}
}





function getemp(a in varchar2)
return types.rc
is
l_cursor types.rc;
begin
open l_cursor for select * from emp where ename=a;
return l_cursor;
end;







Before Anything
1 opened cursors current
-----------------------
After prepare and execute
3 opened cursors current
-----------------------
After prepare and execute and getObject
3 opened cursors current
-----------------------
After but before any close
3 opened cursors current
-----------------------
After rset.close
3 opened cursors current
-----------------------
After prepare and execute
4 opened cursors current
-----------------------
After prepare and execute and getObject
4 opened cursors current
-----------------------
After but before any close
4 opened cursors current
-----------------------
After rset.close
4 opened cursors current
-----------------------
After prepare and execute
5 opened cursors current
-----------------------
After prepare and execute and getObject
5 opened cursors current
-----------------------
After but before any close
5 opened cursors current
-----------------------
After rset.close
5 opened cursors current
-----------------------
After prepare and execute
6 opened cursors current
-----------------------
After prepare and execute and getObject
6 opened cursors current
-----------------------
After but before any close
6 opened cursors current
-----------------------
After rset.close
6 opened cursors current
-----------------------
After prepare and execute
7 opened cursors current
-----------------------
After prepare and execute and getObject
7 opened cursors current
-----------------------
After but before any close
7 opened cursors current
-----------------------
After rset.close
7 opened cursors current
-----------------------
After prepare and execute
8 opened cursors current
-----------------------
After prepare and execute and getObject
8 opened cursors current
-----------------------
After but before any close
8 opened cursors current
-----------------------
After rset.close
8 opened cursors current
-----------------------
After prepare and execute
9 opened cursors current
-----------------------
After prepare and execute and getObject
9 opened cursors current
-----------------------
After but before any close
9 opened cursors current
-----------------------
After rset.close
9 opened cursors current
-----------------------
After prepare and execute
10 opened cursors current
-----------------------
After prepare and execute and getObject
10 opened cursors current
-----------------------
After but before any close
10 opened cursors current
-----------------------
After rset.close
10 opened cursors current
-----------------------
After prepare and execute
11 opened cursors current
-----------------------
After prepare and execute and getObject
11 opened cursors current
-----------------------
After but before any close
11 opened cursors current
-----------------------
After rset.close
11 opened cursors current
-----------------------
After cstmt.close
1 opened cursors current


Tom Kyte
October 12, 2001 - 6:52 pm UTC

whats the platform and versions and other salient information. Works as advertised for me.

show i put cstmt.close inside the loop?

A reader, October 12, 2001 - 6:18 pm UTC

in previous question
if i put preparecall and cstmt.close inside the loop
then the number of opened cursor don't increase.

but i think just like preparestatement
we should use preparecall only one time and use it again and agin to improve performance. right



Tom Kyte
October 12, 2001 - 6:53 pm UTC

Like i said -- version and other info -- I don't get your results at all.

store procedure or prepared statement?

A reader, October 12, 2001 - 6:21 pm UTC

when use storeprocedure , can i parse once and execute multiple times? seems like each time i execute it i need parse once. how can i aviod it?

like the example in previous quesion, getemp parsed 10 times and run 10 times, how to decrease it to parse only 1
time?

A reader, October 15, 2001 - 2:23 pm UTC

my version is 8.1.6
os is solaris 2.7.
i tried several machine and get the same result.
however
when i try the same program at
oracle 8.1.6 and winnt4.0
i get different result.
it open up to 3 open cursors.
why the result is different?

Closing Ref Cursors

SubbaRao, April 24, 2002 - 2:16 pm UTC

Tom,
I was facing the same problem right now where my Java code is giving ORA-01000 (Maximum open cursors exceeded).
The java code calls a stored procedure and closes all the resources. The stored procedure simply executes a query and returns the result set using the ref cursor.
Do I need to close the refcursor in PL/SQL.
My code looks like this

BEGIN
OPEN rc FOR SELECT * FROM EMPLOYEE WHERE EMPL_ID = 'ABC';
EXCEPTION;
END;
Here rc is the Out parameter to the stored procedure.
Do I need to close "rc" in my StoredProcedure.
Pl. shed some light on this.
Thanks,

Tom Kyte
April 24, 2002 - 4:25 pm UTC

No, you need to close that ref cursor in the JAVA.

What about Forms?

Paul Sharples, April 25, 2002 - 5:07 am UTC

I base many of my Forms data blocks on stored procs and invariably use ref cursors. I'm assuming that Forms takes care of closing the cursor - but when? Is it after the execute_query completes, a post, commit or exit_form?

If the Forms runtime crashes (we're still using Win95), do ref cursors get left hanging around, or does Oracle close them when the session terminates?

As a final aside, is there any way I could query the database to see if any ref cursors have been left open?

I realise that this is a lot of questions (and I apologise if I'm breaking etiquette), but I'm hoping that there's just a simple concept in there that will make things click into focus.

FWIW, I work mainly in Forms 5 on Win95 against 8.1.6.0. I couldn't find these answers in any of the documentation available to me.

Regards,




Tom Kyte
April 25, 2002 - 7:23 am UTC

forms takes care of it -- when the block is cleared or otherwise "goes away"

cursors belong to a session, when the session goes - so go the cursors, all of them.

You can look in v$open_cursor to see the cursors your session currently opened and cached cursors (cached by PLSQL -- static sql in plsql gets cached).



A reader, April 25, 2002 - 1:20 pm UTC

Ok, some of my packages are doing the same:

------------
BEGIN
OPEN rc FOR SELECT * FROM EMPLOYEE WHERE EMPL_ID = 'ABC';
EXCEPTION;
END;
Here rc is the Out parameter from the stored procedure.
-------------------

application is VB using Oracle Oledb 8.1.7.3/auto commit, connection pooling, but not using transactions (No MTS)

connect string look like:
Provider=OraOLEDB.Oracle; User ID=scott; Password=tiger; Data Source=qdev; DistribTX=0

and we set objCmd.Properties ("PLSQLRSet"=True) when getting a recordset back.

Should we make a additional call to close the cursor after we are done with it? I thought that OleDB uses disconnected recordsets so you did not have to close them.

Thank you for the help once again.







Followup:
No, you need to close that ref cursor in the JAVA



Tom Kyte
April 25, 2002 - 6:35 pm UTC

Don't know about VB, never used it -- don't have it -- cannot test it.

Perhaps you can setup a test, similar to the java ones supplied here, and test it out?

A reader, May 14, 2003 - 4:18 pm UTC

Tom,

I have a query which takes 2 inputs and displays data. What I want to do is, after taking the input, the query should tell me how many rows have been fetched and if required I will print the refcursor.

Thanks !

A reader, May 14, 2003 - 4:26 pm UTC

Tom -- here is the script that I tried but get an error when I try to print. Is there a way out ???

-- t1.sql
set verify off

set serveroutput on
variable rc refcursor
set autoprint off

declare
  type eList is table of number index by binary_integer;
  aList eList;
begin
  open :rc for 
    select empno from emp;

  fetch :rc bulk collect into aList;
  dbms_output.put_line('Rows fetched : '||aList.count);
  dbms_output.put_line('Type ''print'' to view the data.');

end;
/

set verify on

SQL> @t1
Total rows fetched : 14
Type 'print' to view the data.

PL/SQL procedure successfully completed.

SQL> print
ERROR:
ORA-01002: fetch out of sequence



no rows selected

SQL> 

Is the cursor lost after fetching all the values ???

Thanks ! 

Tom, can I please get a feedback on the above question ???

A reader, May 21, 2003 - 4:47 pm UTC

Thanks !!!

Tom Kyte
May 21, 2003 - 5:12 pm UTC

yes, the results are "lost" after you fetch them.

think of a cursor like a bucket of water. how many times do you get to dump a bucket of water out? once.




praveen, May 21, 2003 - 10:28 pm UTC


Why... on Oracle9i 9.2.0.3

Kamal Kishore, May 21, 2003 - 10:46 pm UTC

Hi Tom,
Why in the first case the CLOSE statement does not generate any error while in the second case, it raises "invalid cursor"? Does that mean the refcursor was closed when the PL/SQL block ended? Does this, then, mean that it may not be session bound?

SQL> variable rc refcursor
SQL> begin
  2    open :rc for select * from emp ;
  3    close :rc ;
  4  end ;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> begin
  2    open :rc for select * from emp ;
  3  end ;
  4  /

PL/SQL procedure successfully completed.

SQL> begin
  2    close :rc ;
  3  end ;
  4  /
begin
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 2


SQL> 
 

Tom Kyte
May 23, 2003 - 7:40 am UTC

you cannot bind the ref cursor back into another block.

once the cursor goes to the client (SQLPLUS), SQLPlus owns it and cannot send it back to the server.

REFCURSOR

Reader - Dublin, May 27, 2003 - 1:56 pm UTC

What is the syntax for defining ref cursor in pro*c?


Tom Kyte
May 27, 2003 - 5:24 pm UTC

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
typedef char asciiz;
EXEC SQL TYPE asciiz IS STRING(100);
SQL_CURSOR my_cursor;
asciiz ename[40];
int mgr;
short mgr_i;
int deptno;
asciiz dname[50];
asciiz loc[50];
int i;
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();


EXEC SQL ALLOCATE :my_cursor;

EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 10 ); end;
END-EXEC;

for( ;; )
{
EXEC SQL WHENEVER NOTFOUND DO BREAK;
EXEC SQL FETCH :my_cursor
INTO :ename, :mgr:mgr_i, :deptno, :dname, :loc;

printf( "%s, %d, %d, %s, %s\n",
ename, mgr_i?-1:mgr, deptno, dname, loc );
}
EXEC SQL CLOSE :my_cursor;


REFCURSOR

Reader - Dublin, May 27, 2003 - 1:56 pm UTC

What is the syntax for defining ref cursor in pro*c?


Excellent

kamran, July 09, 2003 - 1:06 am UTC

A brief discussion on topic and very clear reviews.

Great Tom!

A reader, December 28, 2003 - 8:40 pm UTC


I can't see what I'm doing wrong

Fernando Sanchez, April 27, 2004 - 11:22 am UTC

Hello Tom.

I'm getting the ORA-01000 error and according to v$open_cursor, it seems that the problem is in the following function (8.1.7) when it is run several times:

FUNCTION FN_GET_SMS_Vector
(
p_CO_CFG_N IN IR_TB_CFG_SMS.CO_CFG_N%TYPE,
p_lengthVector OUT INTEGER,
p_ErrCode OUT NUMBER,
p_ErrMsg OUT VARCHAR2
)
return T_SMS_Vector
IS
v_lengthVector INTEGER;
v_SMS_Vector T_SMS_Vector;
v_Query VARCHAR2(500);
BEGIN

/* Averiguo el número de SMS's a devolver */
select count(*)
into v_lengthVector
from IR_TB_CFG_SMS a, IR_CT_SMS b
where a.CO_SMS_N = b.CO_SMS_N
and a.CO_CFG_N = p_CO_CFG_N
and b.IN_BORR_N = c_NO_BORRADO
and b.CO_PER_INIC_D <= SYSDATE
and b.CO_PER_FIN_D >= SYSDATE;

p_lengthVector := v_lengthVector;

v_Query := 'select CO_SMS_N, DS_SMS_V, CO_REMITE_V, CO_MODO_V, CO_TEXT_V, CO_PER_ENVIO_N, CO_NUM_REIN_N, CO_TIPO_MSJ_N ' ||
'from IR_CT_SMS where CO_SMS_N in (select CO_SMS_N from IR_TB_CFG_SMS where CO_CFG_N = :p_CO_CFG_N ) ' ||
'and CO_PER_INIC_D <= SYSDATE ' ||
'and CO_PER_FIN_D >= SYSDATE ' ||
'and IN_BORR_N = :c_NO_BORRADO '||
'order by CO_PER_INIC_D';

OPEN v_SMS_Vector FOR v_Query USING p_CO_CFG_N, c_NO_BORRADO;
return v_SMS_Vector;
EXCEPTION
WHEN OTHERS THEN
p_ErrCode := sqlcode;
p_ErrMsg := 'ERROR: PA_MNG_EVENTS.FN_GET_SMS_Vector' || CHR(10) || sqlerrm;
return v_SMS_Vector;
END FN_GET_SMS_Vector;

where the type T_SMS_Vector is defined as ref cursor;


This function is always called from this pro*C function:
int CSRIRDB_getSMS(char* in_CO_MSDN_V,
unsigned int* out_length,
TipoVectorSMS** out_VectorSMS)
{

int var_Error = CSRIRDB_OK;
int var_CO_CFG_N;
unsigned int var_out_length;
TipoVectorSMS* var_puntero;
int var_CO_SMS_N;
char var_DS_SMS_V[100+1];
/*int var_CO_PER_INIC_N;*/
/*int var_CO_PER_FIN_N;*/
char var_CO_REMITE_V[100+1]; /* NULL */
short indicator_CO_REMITE_V; /* indicator variable */
char var_CO_MODO_V[15+1];
char var_CO_TEXT_V[160+1];
int var_CO_PER_ENVIO_N;
int var_CO_NUM_REIN_N; /* NULL */
short indicator_CO_NUM_REIN_N; /* indicator variable */
int var_CO_TIPO_MSJ_N;
int var_CO_PER_CLT_N; /* para trazas */
int var_CO_MCC_N; /* para trazas */
int var_CO_MNC_N; /* para trazas */
char var_SQ_GZL_01_V[15+1]; /* para trazas */
char var_Sqlerrmc[5000];
char var_msg[512]; /* 512 es la longitud máxima del mensaje oracle incluyendo saltos de línea */
size_t var_buf_len, var_msg_len;


EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR cur_SMS;
EXEC SQL END DECLARE SECTION;

trace_write(TRACE_LEVEL_LOW,"INICIO CSRIRDB_getSMS");
trace_write(TRACE_LEVEL_LOW,"Cursor declarado ");
/*printf("INICIO CSRIRDB_getSMS\n");*/
/*printf("Cursor declarado \n");*/

EXEC SQL ALLOCATE :cur_SMS;

var_out_length = 0;

trace_write(TRACE_LEVEL_LOW,"Memoria Cursor declarada ");
/*printf("Memoria Cursor declarada \n");*/


EXEC SQL EXECUTE

DECLARE

CSRIRDB_OK Constant NUMBER(1) := 0;
CSRIRDB_NON_EXPECTED_ERROR Constant NUMBER(4) := 4000;
CSRIRDB_NO_CONFIGURATION Constant NUMBER(2) := 26;
CSRIRDB_NO_SMS Constant NUMBER(2) := 27;
CSRIRDB_NO_CONFIGURATION_TEXT Constant VARCHAR2(200) := 'No hay ninguna configuración activa para el cliente';

v_RoamerOut BOOLEAN;
v_CO_CFG_N IR_TB_CFG_ENVIOS.CO_CFG_N%type;
v_regCliente CSR_TB_CLTS%rowtype;
v_SQ_GZL_01 CSR_CT_GT_ZLOC.SQ_GZL_01%TYPE;
v_GT_OK BOOLEAN;

v_ErrCode NUMBER;
v_ErrMsg VARCHAR2(5000);

NON_EXPECTED_ERROR EXCEPTION;
NO_CLIENT_FOUND EXCEPTION;
NO_CONFIGURATION_FOUND EXCEPTION;

BEGIN

/* Obtengo los datos del cliente */
BEGIN
select *
into v_regCliente
from CSR_TB_CLTS
where CO_MSDN_V = :in_CO_MSDN_V;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE NO_CLIENT_FOUND;
END;

:var_CO_PER_CLT_N := v_regCliente.CO_PER_CLT_N;
:var_CO_MCC_N := v_regCliente.CO_MCC_N;
:var_CO_MNC_N := v_regCliente.CO_MNC_N;

/* Obtengo la zona de localización a partir del GT */
v_GT_OK := PA_MNG_EVENTS.FN_GET_GT_OK(v_regCliente.CO_GT_REG_V,v_SQ_GZL_01,v_ErrCode,v_ErrMsg);
if (v_ErrCode<0) THEN
RAISE NON_EXPECTED_ERROR;
END if;

:var_SQ_GZL_01_V := to_char(v_SQ_GZL_01);

/* Como el conjunto (CO_MCC_N,CO_MNC_N,CO_PER_CLT_N,SQ_GZL_01) es único en IR_TB_CFG_ENVIOS, obtengo a partir de él el código de la configuración */
BEGIN
select CO_CFG_N
into v_CO_CFG_N
from IR_TB_CFG_ENVIOS
where CO_MCC_N = v_regCliente.CO_MCC_N
and CO_MNC_N = v_regCliente.CO_MNC_N
and SQ_GZL_01 = v_SQ_GZL_01
and CO_PER_CLT_N = v_regCliente.CO_PER_CLT_N
and IN_BORR_N = PA_MNG_EVENTS.c_NO_BORRADO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE NO_CONFIGURATION_FOUND;
END;

:var_CO_CFG_N := v_CO_CFG_N;

:cur_SMS := PA_MNG_EVENTS.FN_GET_SMS_Vector(v_CO_CFG_N,:var_out_length,v_ErrCode,v_ErrMsg);
if (v_ErrCode<0) THEN
RAISE NON_EXPECTED_ERROR;
END if;

if (:var_out_length=0) THEN
:var_Error := CSRIRDB_NO_SMS;
else
:var_Error := sqlcode;
END if;


EXCEPTION
WHEN NO_CLIENT_FOUND THEN
:var_Error := -1403; /* NO_DATA_FOUND error code */
:var_Sqlerrmc := 'NO DATA FOUND';
WHEN NO_CONFIGURATION_FOUND THEN
:var_Error := CSRIRDB_NO_CONFIGURATION; /* NO_DATA_FOUND error code */
:var_Sqlerrmc := CSRIRDB_NO_CONFIGURATION_TEXT;
WHEN NON_EXPECTED_ERROR THEN
:var_Error := CSRIRDB_NON_EXPECTED_ERROR;
:var_Sqlerrmc := trim(v_ErrMsg);
WHEN OTHERS THEN
:var_Error := CSRIRDB_NON_EXPECTED_ERROR;
:var_Sqlerrmc := trim(sqlerrm);
END;

END-EXEC;

if (var_Error==CSRIRDB_OK)
{
trace_write(TRACE_LEVEL_LOW,"CSRIRDB_OK ");
/*printf("CSRIRDB_OK \n");*/

/* Reservo memoria para el resultado */
if (var_out_length>0)
{
*out_VectorSMS=(TipoVectorSMS*) malloc(var_out_length*sizeof(TipoVectorSMS));
var_puntero = *out_VectorSMS;

trace_write(TRACE_LEVEL_LOW,"CURSOR ASIGNADO OK ");
/*printf("CURSOR ASIGNADO OK \n"); */

if (var_Error == CSRIRDB_OK)
{
EXEC SQL WHENEVER NOT FOUND DO BREAK ; /* Ejecuta el siguiente código hasta que el FETCH no devuelva nada */
while (sqlca.sqlcode==0)
{
/*EXEC SQL FETCH :cur_SMS INTO :var_CO_SMS_N, :var_DS_SMS_V, :var_CO_PER_INIC_N, :var_CO_PER_FIN_N,
:var_CO_REMITE_V:indicator_CO_REMITE_V, :var_CO_MODO_V, :var_CO_TEXT_V,
:var_CO_PER_ENVIO_N, :var_CO_NUM_REIN_N:indicator_CO_NUM_REIN_N, :var_CO_TIPO_MSJ_N;*/

EXEC SQL FETCH :cur_SMS INTO :var_CO_SMS_N, :var_DS_SMS_V,
:var_CO_REMITE_V:indicator_CO_REMITE_V, :var_CO_MODO_V, :var_CO_TEXT_V,
:var_CO_PER_ENVIO_N, :var_CO_NUM_REIN_N:indicator_CO_NUM_REIN_N, :var_CO_TIPO_MSJ_N;

trace_write(TRACE_LEVEL_LOW,"Código SMS: (%d)",var_CO_SMS_N);

var_puntero->Codigo = var_CO_SMS_N;
strcpy(var_puntero->Descripcion,var_DS_SMS_V);
var_puntero->PeriodoInicial = 0; /* NO SE USA */
var_puntero->PeriodoFinal = 0; /* NO SE USA */
if (indicator_CO_REMITE_V != -1)
strcpy(var_puntero->Remite,var_CO_REMITE_V);
else
strcpy(var_puntero->Remite,"");
strcpy(var_puntero->Modo,var_CO_MODO_V);
strcpy(var_puntero->Texto,var_CO_TEXT_V);
var_puntero->PeriodoEnvio = var_CO_PER_ENVIO_N;
if (indicator_CO_NUM_REIN_N != -1)
var_puntero->NumeroReintentos = var_CO_NUM_REIN_N;
else
var_puntero->NumeroReintentos = 0;
var_puntero->CodigoTipoMensaje = var_CO_TIPO_MSJ_N;

var_puntero++;

}
EXEC SQL WHENEVER NOT FOUND CONTINUE; /* Si el FETCH no ha devuelto nada seguir */
}
}
}


*out_length = var_out_length;

trace_write(TRACE_LEVEL_LOW,"NUMERO REGISTROS CURSOR (%d) \n",var_out_length);
/*printf("NUMERO REGISTROS CURSOR (%d) \n",var_out_length);*/

/* Cierro el cursor en cualquier caso */
EXEC SQL CLOSE :cur_SMS;

trace_write(TRACE_LEVEL_LOW,"CURSOR CERRADO OK ");
/*printf("CURSOR CERRADO OK \n"); */

trace_write(TRACE_LEVEL_LOW,"Perfil Cliente: (%d) MCC (%d) MNC (%d) SQ_GZL_01 (%s)",var_CO_PER_CLT_N,var_CO_MCC_N,var_CO_MNC_N,var_SQ_GZL_01_V);

trace_write(TRACE_LEVEL_LOW,"FINAL CSRIRDB_getSMS");
/*printf("FINAL CSRIRDB_getSMS \n");*/


if ((sqlca.sqlcode == 0) && (var_Error == CSRIRDB_OK))
{
trace_write(TRACE_LEVEL_HIGH,"Obtenidos SMS's OK");
/*printf("Obtenidos SMS's OK\n"); */

return CSRIRDB_OK;
}
else if ((sqlca.sqlcode == 0) && (var_Error == CSRIRDB_NO_CONFIGURATION))
{
trace_write(TRACE_LEVEL_HIGH,"NO_CONFIGURATION_FOUND");
/*printf("NO_CONFIGURATION_FOUND. \n"); */

return CSRIRDB_NO_CONFIGURATION;
}
else if ((sqlca.sqlcode == 0) && (var_Error == CSRIRDB_NO_SMS))
{
trace_write(TRACE_LEVEL_HIGH,"CSRIRDB_NO_SMS");
/*printf("NO_SMS_FOUND. (%s) \n"); */

return CSRIRDB_NO_SMS;
}
else if ((sqlca.sqlcode == 0) && (var_Error == -1403)) /* NO_DATA_FOUND */
{
trace_write(TRACE_LEVEL_HIGH,"NO_DATA_FOUND. Ningún registro devuelto");
/*printf("NO_DATA_FOUND. Ningún registro devuelto \n");*/

return CSRIRDB_OK;
}
else if (sqlca.sqlcode != 0)
{
var_buf_len = sizeof (var_msg);
sqlglm(var_msg, &var_buf_len, &var_msg_len);

trace_write(TRACE_LEVEL_HIGH, "TRACE_LEVEL: (%s) (%d)",var_msg,sqlca.sqlcode);
/*printf("TRACE_LEVEL: (%s) (%d)\n",var_msg,sqlca.sqlcode);*/

return CSRIRDB_FATAL_ERROR;
}
else /* var_Error != CSRIRDB_OK */
{
trace_write(TRACE_LEVEL_HIGH, "TRACE_LEVEL: (%s)",var_Sqlerrmc);
/*printf("TRACE_LEVEL: (%s)\n",var_Sqlerrmc);*/

return CSRIRDB_NON_EXPECTED_ERROR;
}

}

And I can't see the problem, I think I close cur_SMS in any case. Am I wrong? Appart from closing cur_SMS, do I need to close the cursor opened in the function ? How ?

Thanks in advance.

Tom Kyte
April 28, 2004 - 1:06 pm UTC

we could have come up with a "smaller" example :)

building test cases -- to demonstrate an issue -- I might need to write a book on that topic!

I find, once we clear out the clutter -- many times the problem stares at us in the face. Here is my small test case that emulates your issue:

create or replace procedure open_cursor( p_cursor in out sys_refcursor )
as
begin
open p_cursor for select * from dual;
end;
/



static void process( int iteration )
{
EXEC SQL BEGIN DECLARE SECTION;
SQL_CURSOR x;
varchar dummy[5];
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
exec sql allocate :x;

exec sql execute
begin
open_cursor(:x);
end;
end-exec;

for( ;; )
{
exec sql whenever notfound do break;
exec sql fetch :x into :dummy;
exec sql whenever notfound continue;
printf( "Iteration %d, row fetched...\n", iteration );
}
exec sql close :x;
}



that'll blow up after it is called a couple of times. It is missing the:

exec sql free :x;

after the close. sure you closed the cursor, but you just closed the result set, you still have the cursor allocated. Your code is leaking cursors every time that routine is called. It allocated but did not close them.


add a free -- or just call allocate ONCE (that'll be more efficient). Make the sql_cursor a static variable and just initialize it once.

Retriving Ref cursor value in Oracle Forms PL/sql block

Nikunj Thaker, September 23, 2004 - 11:07 am UTC

Dear Tom,

My oracle database version Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production & Oracle Developer 6i.

I had created on function which return ref. cursor as below.

create or replace function get_data(g in varchar2)
return p_ref_cursor.ret_ref_cursor is
l_cursor p_ref_cursor.ret_ref_cursor;
begin
open l_cursor for
'select edpno,leavedate,leavefrom,leaveto,reason,no_of_days,srno
from leave_app_mast where edpno IN
(select edpno from leave_user
where APP_BY='''||g||''')
and (app_not <>''Y'' OR app_not is null)
and srno not in
(select distinct(srno) from leave_app_tran
where app_by='''||g||''')
and
(select nvl(app_seq_no,0)-1 from leave_user
where leave_user.edpno=leave_app_mast.edpno and app_by='''||g||''')
=
(select NVL(max(app_seq_no),0) from leave_app_Tran
where leave_app_tran.srno=leave_app_mast.srno)';
return l_cursor;
end;
/

now in forms 6i i have below pl/sql code
declare
type rctyp is ref cursor;
myrc1 rctyp;
begin
myrc1:=get_data(:global.g);
loop
fetch myrc1 into :di1,:di2,:di3,:di4,:di5,:no_of_days,:srno;
exit when myrc1%notfound;
down;
end loop;
close myrc1;
end;

but when i run forms i get forms error no.
frm-40735 when-new-form-instance trigger raised unhandled exeception ORA-01001.

when i run it on my server as below i get the result
declare
type rctyp is ref cursor;
myrc1 rctyp;
myst varchar2(800);
d1 NUMBER;
d2 DATE;
d3 DATE;
d4 DATE;
d5 VARCHAR2(80);
no_of_days NUMBER;
tsrno NUMBER;
begin
myrc1:=get_data('CDP');
loop
fetch myrc1 into d1,d2,d3,d4,d5,no_of_days,tsrno;
exit when myrc1%notfound;
dbms_output.put_line(d1);
end loop;
close myrc1;
end;

3
PL/SQL procedure successfully completed.

1. so why i am not able to get result on forms 6i ?
2. i also would like to know how can i pass my above query direcly in forms 6i. which at present i am passing in ref. cusor becoase i tried but it give me error in plsql block.

thanks,
Nikunj

Tom Kyte
September 24, 2004 - 8:46 am UTC

sorry, the last time I programmed a form -- ref cursors were so new, they weren't even supported.

Please try otn.oracle.com -> discussion forums, there are groups for forms/reports.

Returning Ref Cursors with Other Parameters

Ravi Thapliyal, December 08, 2004 - 10:43 pm UTC

Dear Tom,
I want to find out that how the ref-cursor works, because through VB or any application, i am not able to retrieve both cursors and other out parameters in the same recordset.I am only able to get normal out values not cursor values, for e.g. here is the specification of that procedure.

package p1 is .......
TYPE CursorRef IS REF CURSOR;
procedure pr1( a1 IN xyz.x1%type,
a2 IN xyz.x2%typenumber,
a3 IN abc.x1%type,
a4 OUT pqr.y1%type,
a5 OUT xyz%x4%type,
a6 OUT varchar,
cursorout out CursorRef);

Q1) Is this the provider or s/w connection problem, we are using "msdaora.1" as a provider and MDAC 2.8
Q2) Or how the cursor should be implement in this case. The Error we are getting is "ORA-01023 Cursor context not found(Invalid cursor number)"

Tom Kyte
December 09, 2004 - 1:06 pm UTC

</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

is my list of examples -- all i can say is "it can be done", but I don't code in VB so I cannot go further than that.




Kate, June 13, 2006 - 10:50 pm UTC

you close cursors AFTER you use them. Hence you close a ref cursor after you
fetch from it. If you lose your result set -- that indicates you are not done with the ref cursor.
----
I wrote a "Stored Procedure" using ref cursor,and be a Data Set of "Reporting Service".I can't close ref cusor becasue of losing my result set.
After the clients fetch the result set ,does it close automatic?
If it doesn't close automatic,in a short time a great deal clients connect to the service,the server resource will be out of memory???

Tom Kyte
June 14, 2006 - 8:42 am UTC

you close cursors after you use them.

hence, you - yourself - your application - closes a ref cursor after you fetch from it.

consider a ref cursor like a file, you called a routine to open a file, you read from it (fetch). IF you do not close that file, you will eventually run out of file handles (open_cursors will be exceeded)


"fetch out of sequence"

Bharath, August 01, 2006 - 12:29 pm UTC

I have a package which contains a procedure which returns sys_refcursor result set.
I am getting a "fetch out of sequence" error when I execute it from Java. Could it be because I am passing around the
sys_refcursor ?

The following is the code.

CREATE OR REPLACE PACKAGE BODY testing AS

Procedure getResults(result1 OUT SYS_REFCURSOR, result2 OUT SYS_REFCURSOR)
BEGIN
getResult1(result1);
processSomething(result1);
END;

PROCEDURE getResult1(result1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN result1 FOR
SELECT empid,ename FROM EMP WHERE SALARY >= 5000;
END;

PROCEDURE processSomething(result IN SYS_REFCURSOR) AS
empid NUMBER;
ename VARCHAR2(100);
BEGIN
LOOP
FETCH result INTO empid,ename;
EXIT WHEN deals%NOTFOUND;

-- do something (not inserting, deleting or updating)
END LOOP;
END;

END;





Tom Kyte
August 01, 2006 - 7:12 pm UTC

You cannot send a ref cursor BACK to the server.

Once a client gets it, it is gotten - and cannot be returned to the server.

refcursor still seems to be open after closing it in an exception block

ht, October 23, 2006 - 4:02 pm UTC

Hi Tom,
Over the weekend, I encountered an ora-01001. After scouring your site, it seemed that I needed to close my refcursors in the exception section of my stored procs.

In setting up a test case, it seems that v$open_cursor indicates that the refcursor isn't closing after the third execution of sample_rc_open_close_pkg1.run_refcursor1 eventhough I'm closing the cursor in the "exception" section of the stored proc.

Question 1: How do I close my cursor when an exception occurs - my test case below doesn't seem to do it.

Question 2: open_cursors is set to 1000 but the count of v$open_cursor is more than twice that size. When will I experience another ORA-01001 - maybe this isn't the parameter I should be using as a metric? Is open_cursors a session or database limit?

Thank you in advance.
ht

>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

>set serveroutput on;
>@sample_rc_open_close_pkg1.sql
>create or replace package sample_rc_open_close_pkg1
2 as
3 procedure run_refcursor1;
4 procedure check_open_cursor;
5 end;
6 /

Package created.

>show errors;
No errors.
>
>create or replace package body sample_rc_open_close_pkg1
2 as
3 ----------------------------------------------------------------
4 procedure run_refcursor1
5 as
6 type rc is ref cursor;
7
8 c1 rc;
9 dual_rec1 dual%rowtype;
10 sql_stmt1 varchar2(4000);
11
12
13 begin
14
15 sql_stmt1 := ' select * from dual@does_not_exist';
16
17 open c1 for sql_stmt1;
18 loop
19 fetch c1 into dual_rec1;
20 exit when c1%notfound;
21 end loop;
22 close c1;
23
24 exception when others then
25
26 close c1;
27
28 dbms_output.put_line(sqlerrm);
29
30
31
32 end run_refcursor1;
33 ----------------------------------------------------------------
34 procedure check_open_cursor
35 as
36 l_cursor_open number;
37
38 begin
39 select count(*) into l_cursor_open from v$open_cursor where user_name=user and sql_text like '%sample_rc_open_close_pkg1.run_refcursor1%';
40 dbms_output.put_line('# of open cursors created by run_refcursor1: ');
41 dbms_output.put_line(l_cursor_open);
42 end check_open_cursor;
43 ----------------------------------------------------------------
44
45 end sample_rc_open_close_pkg1;
46
47 /

Package body created.

>
>show errors;
No errors.
>
>
>--exit;
>begin sample_rc_open_close_pkg1.check_open_cursor;end;
2 /
# of open cursors created by run_refcursor1:
0

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.run_refcursor1;end;
2 /
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.check_open_cursor;end;
2 /
# of open cursors created by run_refcursor1:
0

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.run_refcursor1;end;
2 /
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.check_open_cursor;end;
2 /
# of open cursors created by run_refcursor1:
0

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.run_refcursor1;end;
2 /
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.check_open_cursor;end;
2 /
# of open cursors created by run_refcursor1:
1

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.run_refcursor1;end;
2 /
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.check_open_cursor;end;
2 /
# of open cursors created by run_refcursor1:
1

PL/SQL procedure successfully completed.

>begin sample_rc_open_close_pkg1.check_open_cursor;end;
2 /
# of open cursors created by run_refcursor1:
1

PL/SQL procedure successfully completed.

>


Tom Kyte
October 23, 2006 - 5:27 pm UTC

did you think about printing out the cursors to see what is there?? - and wouldn't you expect the number to keep going up and up?

printed a count of the cursor

ht, October 23, 2006 - 5:56 pm UTC

Hi Tom,
By calling a proc that counts the number of cursors that contain my proc name (
select count(*) into l_cursor_open from v$open_cursor where
user_name=user and sql_text like '%sample_rc_open_close_pkg1.run_refcursor1%';
)
am I not doing the same thing as printing the cursor contents?

Calling the run_refcursor1 proc displays no recs in v$open_cursor (that reference the proc) until the 3rd run which shows that the cursor is not being released.

Can you elaborate on what I should be doing differently in the test case provided?

Thanks.

Tom Kyte
October 24, 2006 - 12:30 am UTC

but that isn't your ref cursor - so, how does this apply to ref cursors precisely?

trying to figure out why a ref cursor isn't closing

ht, October 24, 2006 - 2:19 am UTC

Tom,
Sorry for not being clearer. The proc I referenced is showing that the ref cursor from the proc below doesn't seem to be closing even after adding the "close" syntax in the "exception" area. It seems strange that the ref cursor isn't closing only after the first 2 runs of the stored proc.

I'm using the test case below to figure out if this is the model to implement to avoid the "ora-01001" I received over the weekend.

Does the test case below properly close the cursor after the stored proc encounters an error? If so, why does the 3rd run of the proc seem to leave the cursor open (evidenced by querying v$open_cursor for the stored proc)?

procedure run_refcursor1
5 as
6 type rc is ref cursor;
7
8 c1 rc;
9 dual_rec1 dual%rowtype;
10 sql_stmt1 varchar2(4000);
11
12
13 begin
14
15 sql_stmt1 := ' select * from dual@does_not_exist';
16
17 open c1 for sql_stmt1;
18 loop
19 fetch c1 into dual_rec1;
20 exit when c1%notfound;
21 end loop;
22 close c1;
23
24 exception when others then
25
26 close c1;
27
28 dbms_output.put_line(sqlerrm);
29
30
31
32 end run_refcursor1;

Tom Kyte
October 24, 2006 - 9:13 am UTC

can you make this such that you actually hit an ora error
what is your session_cached_cursors set to
are you sure this is actually the cause the problem here at all


I do not see the ref cursor query appear in v$open_cursor myself

ops$tkyte%ORA10GR2> @test

Procedure created.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.


SQL_TEXT
------------------------------------------------------------
select smontabv.cnt, smontab.time_mp,    smontab.scn, smonta
select smontabv.cnt, smontab.time_mp,    smontab.scn, smonta
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
update sys.mon_mods$ set inserts = inserts + :ins, updates =
update sys.mon_mods$ set inserts = inserts + :ins, updates =
select sql_text from v$open_cursor where sid = (select sid f
BEGIN run_refcursor1; END;
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
table_1_ff_14f_0_0_0

9 rows selected.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.


SQL_TEXT
------------------------------------------------------------
select smontabv.cnt, smontab.time_mp,    smontab.scn, smonta
select smontabv.cnt, smontab.time_mp,    smontab.scn, smonta
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
update sys.mon_mods$ set inserts = inserts + :ins, updates =
update sys.mon_mods$ set inserts = inserts + :ins, updates =
select sql_text from v$open_cursor where sid = (select sid f
BEGIN run_refcursor1; END;
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
table_1_ff_14f_0_0_0

9 rows selected.
 

Seems only 1 cursor is open - maybe I'm not leaking cursors?

ht, October 24, 2006 - 11:15 am UTC

Tom,
Thanks for your continued assistance during your busy week.

I've created a second proc that displays the contents of v$open_cursor (as it pertains to the proc I'm trying to close) and also a script that calls both procs multiple times.

Since the count of v$open_cursor never exceeds 1, am I correct that adding the "close cursor" to all of my procs in the "exception" clause is the correct method to implement? With the test case below as evidence, it seems I will not "leak" cursors after this fix and will not encounter any more "ORA-01001"s.

Thanks.
ht

set echo on;
set serveroutput on;
set sqlprompt '>'
show parameter cursor
--
exec sample_rc_open_close_pkg1.run_refcursor1;
exec sample_rc_open_close_pkg1.check_open_cursor2;
--
exec sample_rc_open_close_pkg1.run_refcursor1;
exec sample_rc_open_close_pkg1.check_open_cursor2;
--
exec sample_rc_open_close_pkg1.run_refcursor1;
exec sample_rc_open_close_pkg1.check_open_cursor2;
--
exec sample_rc_open_close_pkg1.run_refcursor1;
exec sample_rc_open_close_pkg1.check_open_cursor2;
--
exec sample_rc_open_close_pkg1.run_refcursor1;
exec sample_rc_open_close_pkg1.check_open_cursor2;
--

exit;
~
"sample_check_cursor_close.sql" 22 lines, 589 characters
sqlplus @sample_check_cursor_close.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 24 08:02:43 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

>show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 1000
session_cached_cursors integer 100
>--
>exec sample_rc_open_close_pkg1.run_refcursor1;
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>exec sample_rc_open_close_pkg1.check_open_cursor2;
cursor created by run_refcursor1:
BEGIN sample_rc_open_close_pkg1.run_refcursor1; END;

PL/SQL procedure successfully completed.

>--
>exec sample_rc_open_close_pkg1.run_refcursor1;
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>exec sample_rc_open_close_pkg1.check_open_cursor2;
cursor created by run_refcursor1:
BEGIN sample_rc_open_close_pkg1.run_refcursor1; END;

PL/SQL procedure successfully completed.

>--
>exec sample_rc_open_close_pkg1.run_refcursor1;
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>exec sample_rc_open_close_pkg1.check_open_cursor2;
cursor created by run_refcursor1:
BEGIN sample_rc_open_close_pkg1.run_refcursor1; END;

PL/SQL procedure successfully completed.

>--
>exec sample_rc_open_close_pkg1.run_refcursor1;
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>exec sample_rc_open_close_pkg1.check_open_cursor2;
cursor created by run_refcursor1:
BEGIN sample_rc_open_close_pkg1.run_refcursor1; END;

PL/SQL procedure successfully completed.

>--
>exec sample_rc_open_close_pkg1.run_refcursor1;
ORA-02019: connection description for remote database not found

PL/SQL procedure successfully completed.

>exec sample_rc_open_close_pkg1.check_open_cursor2;
cursor created by run_refcursor1:
BEGIN sample_rc_open_close_pkg1.run_refcursor1; END;

PL/SQL procedure successfully completed.

>--
>
>exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options



Tom Kyte
October 24, 2006 - 2:06 pm UTC

if you close them, you are not leaking them - correct.

Thanks.

A reader, October 24, 2006 - 2:14 pm UTC


closing ref cursor which uses cursor as a out variable

Manoj M, April 01, 2008 - 10:45 am UTC

Hi Tom,

We have the following procedure,P_GetErmSis, which is called from a VB external application. It uses a cursor,pResultSet, as a OUT variable.

My question is : How do we close this cursor pResultSet or do we need to close this explicitly ?

procedure P_GetErmSis(pQtrID in integer,
pResultSet out curResult,
pSuccess out integer
) is
begin
open pResultSet for
select f.legal_entity_name as SiName,
f.si_filer_id as SiNbr
from fas157_entity_name f
where f.quarter_id = pQtrID;

pSuccess := SUCCESS;

exception
when others then
pSuccess := ERROR;
end P_GetErmSis;


thanks
Manoj
Tom Kyte
April 01, 2008 - 12:07 pm UTC

  exception
     when others then
     pSuccess := ERROR;
  end P_GetErmSis;




I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE
I WILL NEVER UNDERSTAND THE THOUGHT PROCESS BEHIND THIS SORT OF CODE



http://asktom.oracle.com/pls/ask/search?p_string=%22when+others%22+%22not+followed+by%22

stop doing that, immediately, now and forever. Just say "no" to when others - period.



The client would close this cursor in the same way it would close any cursor, the client would close it.


Manoj, April 01, 2008 - 12:35 pm UTC

AT least it is returning a error output so that the user knows the process errored out instead of simply ignoring it

But the answer to the original qn is still not clear

So the closing of cursor will be given from the external application which is calling this procedure which returns the cursor as OUT ?

Manoj
Tom Kyte
April 01, 2008 - 12:48 pm UTC

huh??

do you realize what you just said.

You are taking an error
You are HIDING THE ERROR from the client
you are making it easy for the client to IGNORE the error
you are CHANGING the way the database does transactions (you change how oracle works, you make statements *not atomic*)


you are doing the single worst thing you can do in plsql - coding wise. Period, nothing you say will change that - when others not followed by raise/raise_application_error is wrong in your code. period.


I do not know how else to say:

"The client would close this cursor in the same way it would close any cursor, the client would close it. "


The client gets a cursor.
The client fetches from the cursor.
The client closes the cursor.

Manoj, April 01, 2008 - 12:53 pm UTC

What if the main procedure which is calling this procedure raises an error based on the output parameter value of SUCCESS out variable ?

What if we want to continue processing and check the outcomes of various procedure executions only in the end to save time during quarter end processing ?
Tom Kyte
April 01, 2008 - 1:09 pm UTC

...What if the main procedure which is calling this procedure raises an error
based on the output parameter value of SUCCESS out variable ?
...

one would question why you

a) catch an error
b) to hide it
c) to get a generic "I failed" (losing the error code, what CAUSED the error)
d) to re-raise it - without ANY INFORMATION AT ALL. No ora-xxxxx error, no message

so, why would you write all of this EXTRA CODE - only to LOSE the important bits of information.


... what if we want to continue processing and check the outcomes of various
procedure executions only in the end to save time during quarter end processing
? ....

cool - go ahead and do that, you do NOT need a when others in this procedure to make that happen. And one wonders of what use this downstream processing could be, would be - if you hit some heinous error that you were not expecting to encounter.


YOU DO NOT NEED, WANT OR DESIRE that exception block. It only makes you look bad. It does nothing useful. It actually works AGAINST you. YOU LOSE INFORMATION - what good is that?



how would calling other procedures (which will CONSUME TIME) save you time??? It'll make everything slower - you have other procedures you are calling - but you cannot commit this transaction or anything - you have the database in some unknown state, after suffering from some unknown error.

This is sloppy coding - when others - just forget it exists.

when others exception

A reader, April 01, 2008 - 2:09 pm UTC

In one of your functions you used in other forums :

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function my_to_number( p_num in varchar2 ) return number
as
x number;
begin
x := to_number( p_num );
return 1;
exception
when others then return 0;
end;
/

Why were you using when others here ? You could have explicitly specified "when invalid numbers" exception instead of using when others ?
Tom Kyte
April 01, 2008 - 2:34 pm UTC

I should have, I'll fix that now.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2460648511591

(and hopefully, you can see the difference between the when others used in this my_to_number example - and when others used above - and that one is safe and the other not safe).

I rail against when others now because

EVERY
SINGLE
DAY

- seriously - EVERY SINGLE DAY of my life, I see it used wrong, leading to bugs. So, I just say no now - and have been for the last two or three years - it has gotten just so bad.


Just say no to when others.


Manoj, April 01, 2008 - 2:30 pm UTC

thanks

resultset.close() is closing the Java reultset only, or it is closing actual DB cursor as well

Bharat, April 06, 2009 - 7:33 am UTC

You have mentioned that if we are calling a function from Java that return a ref cursor, then that cursor can be closed using
resultset.close();

Do this way we close the resulset of Java only? Or that closes the actual database Cursors as well.

I think both will take separate memory area.

Thanks in advance.

Tom Kyte
April 13, 2009 - 10:12 am UTC

the java result *is* the database cursor, they are one in the same.

You fetch from a cursor, when you close the 'resultset' you are closing the cursor (assuming statement caching isn't enabled, which is SHOULD be - if statement caching is enabled, you'll parse less often - which is good - and the jdbc code will cache some cursors in the open state for you in the background)

re: Java resultset and REF CURSOR

Stew Ashton, April 13, 2009 - 11:19 am UTC


Tom, how does statement caching help with ref cursors?

Unless I am mistaken, there are two "objects" on the Java side :
1) the CallableStatement that calls the stored procedure,
2) the ResultSet that corresponds to the ref cursor returned from the server.

I see how the CallableStatement can benefit from statement caching (parse once, execute many), but wouldn't the ResultSet / ref cursor have to be well and truly closed after each use?

On the database side, are there two different thingies that correspond to the CallableStatement and to the ResultSet? Are they both "cursors"?
Tom Kyte
April 13, 2009 - 5:35 pm UTC

ref cursors are parsed for each execution - yes.

both things are "cursors" yes, the plsql block has a handle to it ( a cursor to it ) as does each result set.

REF cursor error Cursor is closed.

amit, March 28, 2012 - 2:14 am UTC

HI,

I am having a procedure which will return a result set as ref_cursor and do some update and insert operation
At the beginning i need check a condition,if it False than the complete procedure will be executed.
If it is true than a hard coded value in out parameter will be send and no other codes will be executed in the procedure.

But when the condition is true its throwing an error Cursor is closed. But its working fine when the condition is false

code is


create or replace
procedure test (in_id in varchar2,
list out sys_refcursor,
out_message out varchar2,
out_err_message out varchar2)

is
temp varchar2;
begin

select value into temp from table1 where col1=1;

if temp=Y then
out_message := 'ERROR';
goto pop_msg_label;
End if;


some update clause

some insert clause

open list for
select *
from table2
where id = in_id;


<<pop_msg_label>>

commit;

exception
when others then
out_message := 'ERR02';
out_err_message := 'Error in test : ' || substr(sqlerrm,1,200);
rollback;
end test;

Tom Kyte
March 28, 2012 - 9:06 am UTC

exception
        when others then
                out_message := 'ERR02';
                out_err_message := 'Error in test : ' || substr(sqlerrm,1,200); 

                rollback;


http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i%20hate%20your%20code%22

why do you do that? why do you take the most awesome way to deal with errors, a way that *cannot* be accidentally ignored - exceptions and turn it into 1975 code with return codes? I understand why we do this in C, C doesn't have exceptions. But why would you take a good feature - and turn it into something bad?

And having that rollback there - ugh, that is the worst - you took horribly bad and made it worse!!!!!

The commit shouldn't be there either - the only one that knows if you want to commit or rollback is the client.




goto????????? seriously? really?


but think about it, if your condition is true - you GOTO a bit of code that excludes opening the cursor. The client gets back

list - non-opened cursor
out_message - 'ERROR'
out_err_message - NULL


I guess the client is IGNORING your return code!!! and they are trying to fetch from a cursor that was never opened!!

what do you expect to have happen?

cursor variables closed when going out of scope?

Fred, November 12, 2014 - 11:51 am UTC

Hi Tom,

I understand that ref cursors (well, any cursor) ought to be explicitly closed when finished with, but in the case of explicit cursors, they get automatically closed (at some point) after they've passed out of scope. However, I've come across statements saying that the same is not true for ref cursors (most notably, top of page 509: http://books.google.co.uk/books?id=Cr6nAgAAQBAJ&pg=PA508&lpg=PA508&dq=oracle+automatically+close+ref+cursor+when+it+goes+out+of+scope&source=bl&ots=_anpRhpXJP&sig=7Jt1ZT__s6x8ml1XLcvyouWFy6k&hl=en&sa=X&ei=LTBjVM-UIoG6PdDNgegL&ved=0CCgQ6AEwAQ#v=onepage&q=oracle%20automatically%20close%20ref%20cursor%20when%20it%20goes%20out%20of%20scope&f=false ).

So, in the following instance, would you expect the open cursor count to go up by 100?

alter session set cursor_sharing = exact;

begin
for i in 1..100
loop
declare
v_cur sys_refcursor;
begin
open v_cur for 'select '||i||' from dual';
end;
end loop;
end;
/


I would expect that as soon as v_cur goes out of scope that the database would recognise that the ref cursor is no longer in scope and so can be closed, but that contradicts what others (including Steven Feuerstein) have said.

Plus, when I run:

SELECT SUM(VALUE) from V$SESSTAT where STATISTIC# = (select STATISTIC# from V$STATNAME where name = 'opened cursors current' );


the figure does not jump up by 100.

*scratches head*

Of course, I can't find anything in MOS or the documentation confirming things one way or the other. Is my test flawed?

seems like cursor variables get garbage collected

Rasa, November 17, 2014 - 10:01 am UTC

Hello,

Seems to me like cursor variable (unlike regular cursor) doesn't need to be closed if all variables pointing to this cursor area goes out of scope:

I tried some tests, opening cursor variables, then closing at the end of block (or not closing) and checking opened cursors in database and didn't see any difference in database behavior - if all variables, pointing to cursor, goes out of scope, then doesn't matter if I close cursor variable or not: database closes it 2 times, and after 3rd time leaves open and keeps open, reusing same cursor if needed, until total number of cursors exceeds some limit, after which they gets garbage-collected. It looks much like Java garbage-collection behavior.

P.S. our database is 11.2 version