Home » SQL & PL/SQL » SQL & PL/SQL » ref cursor
ref cursor [message #578205] Mon, 25 February 2013 10:32 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,

i have a ref cursor and i have used 'open cursor for' statement:


CREATE OR REPLACE  PACKAGE aepuser.pkg_test
AS
   TYPE cur1 IS REF CURSOR;

   PROCEDURE get_empdetails (p_empno NUMBER, io_cur OUT cur1);
END;

CREATE OR REPLACE PACKAGE BODY aepuser.pkg_test
AS
   PROCEDURE get_empdetails (p_empno NUMBER, io_cur OUT cur1)
   IS
      v_cur   cur1;
   BEGIN
      OPEN v_cur FOR
         SELECT ename, deptno, sal, comm
           FROM scott.emp;

      io_cur := v_cur;
      COMMIT;
   END get_empdetails;
END;


then i want to know that- will oracle automatically deallocate the memory occupied by records in cursor area?

if yes, then when it will be free , in case of 'open cursor for' ?

thank in advance........
Re: ref cursor [message #578206 is a reply to message #578205] Mon, 25 February 2013 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ REF CURSOR is a reference to a cursor, the cursor lives until you explicitly close it or end the session.
2/ A package variable has the life of a session (unless you recompile the package and then invalidate the variable).

Do NOT commit after opening the cursor and before closing it.

Regards
Michel
Re: ref cursor [message #578233 is a reply to message #578206] Tue, 26 February 2013 00:38 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks for your kind advise and make me aware with other important things about the cursor.

Quote:

A package variable has the life of a session


i have some doubts:

1) is ref cursor also occupy space in memory , if we did not use it only declared , if yes then how much?

2) here we are not closing ref cursor then it consume memory over the session?


thanks again....

Re: ref cursor [message #578239 is a reply to message #578233] Tue, 26 February 2013 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) A few bytes
2) Some more bytes

The problem is not the memory, it is the number of open cursors which is limited and some security issues.

Regards
Michel

[Updated on: Tue, 26 February 2013 00:49]

Report message to a moderator

Re: ref cursor [message #578265 is a reply to message #578239] Tue, 26 February 2013 04:07 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,

so
explicitly i have to say 'close cursor' to deallocate the occupied space by cursor area in above case,

because i have said only 'open cursor' not closed it explicitly or i have to used 'cursor for loop' here,right?


thanks again.....



Re: ref cursor [message #578270 is a reply to message #578265] Tue, 26 February 2013 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want to say.
If you use "cursor for loop", you don't need to open, fetch and close the cursor, Oracle does it for you.
If you use explicit cursor, you have to explicitly open, fetch and close it.
You cannot use a REF CURSOR in a "cursor for loop".

Regards
Michel
Re: ref cursor [message #578280 is a reply to message #578270] Tue, 26 February 2013 08:47 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
sorry sir, to make you in a fix.

i have understand your points.


thanks alot......
Re: ref cursor [message #578325 is a reply to message #578265] Tue, 26 February 2013 23:55 Go to previous message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

If you close the cursor then nothing will return in the out parameter.So You need to close the cursor after processing it.Look below url for more details.
http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php
Previous Topic: Sequence Trigger
Next Topic: Auto generation Number
Goto Forum:
  


Current Time: Mon Oct 20 16:46:21 CDT 2014

Total time taken to generate the page: 0.25163 seconds