Ref Cursor [message #434677] |
Thu, 10 December 2009 04:10  |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |

|
|
Hi,
Is it necessary to close a ref cursor which is OUT parameter in a procedure?
Create Or Replace Package package_dummy Is
Type ref_cur_emp Is Ref Cursor;
Procedure proc_emp(p_emp_cur OUT ref_cur_emp,
);
End package_dummy;
Create Or Replace Package Body package_cusip_dummy Is
Procedure proc_cusip(p_cusip_cur OUT ref_cur_emp
) Is
Begin
If chk = 1 Then
Open p_emp_cur For Select empno,ename,job
From EMP
Where deptno in (10,20);
Elsif chk = 2 Then
Open p_emp_cur For Select empno,ename,job
From EMP
Where deptno in (30,40);
End If;
End package_cusip_dummy;
My question is that is it necessary to close p_emp_cur before opening again.
Regards,
Ritesh
|
|
|
|
Re: Ref Cursor [message #434680 is a reply to message #434678] |
Thu, 10 December 2009 04:21   |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |

|
|
Create Or Replace Package package_dummy Is
Type ref_cur_emp Is Ref Cursor;
Procedure proc_emp(p_emp_cur OUT ref_cur_emp
);
End package_dummy;
/
Package created.
Create Or Replace Package Body package_dummy Is
Procedure proc_emp(p_emp_cur OUT ref_cur_emp
) Is
chk number;
Begin
If chk = 1 Then
Open p_emp_cur For Select empno,ename,job
From EMP
Where deptno in (10,20);
Elsif chk = 2 Then
Open p_emp_cur For Select empno,ename,job
From EMP
Where deptno in (30,40);
End If;
End proc_emp;
End package_dummy;
/
Package body created.
Now it is ok
Please clarify my doubt
[Updated on: Thu, 10 December 2009 04:42] Report message to a moderator
|
|
|
Re: Ref Cursor [message #434689 is a reply to message #434677] |
Thu, 10 December 2009 04:39   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Ref cursors should be closed after the records you want have been Fetched from them. They shouldn't be closed in the procedure that opens them.
|
|
|
|
Re: Ref Cursor [message #434696 is a reply to message #434692] |
Thu, 10 December 2009 04:51  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Then either do whatever your client does to close a cursor, or write a procedure in Oracle that you can pass the cursor to, that will close the cursor.
|
|
|