Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursor (Oracle, 10g, Windows 2003 Server)
Ref Cursor [message #434677] Thu, 10 December 2009 04:10 Go to next message
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 #434678 is a reply to message #434677] Thu, 10 December 2009 04:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
First of all, does your code compile successfully?

And why don't you try, whatever you have asked here?
And please reply whatever output you get.

regards,
Delna
Re: Ref Cursor [message #434680 is a reply to message #434678] Thu, 10 December 2009 04:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 #434692 is a reply to message #434689] Thu, 10 December 2009 04:44 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

thanks for reply


Frontend of my application is microsoft sharepoint and backend is Oracle 10g.

So what if records in ref cursor are fetched in microsoft sharepoint?

[Updated on: Thu, 10 December 2009 04:49]

Report message to a moderator

Re: Ref Cursor [message #434696 is a reply to message #434692] Thu, 10 December 2009 04:51 Go to previous message
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.
Previous Topic: error in using the utl_file package
Next Topic: Bulk Collect
Goto Forum:
  


Current Time: Fri Dec 02 12:23:29 CST 2016

Total time taken to generate the page: 0.12961 seconds