Skip to Main Content
  • Questions

  • Clarifications on closing cursor and cursor bind variables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: September 12, 2003 - 9:42 pm UTC

Last updated: September 06, 2005 - 8:46 am UTC

Version: 8.0

Viewed 1000+ times

You Asked

Hello Tom,

I have couple of basic questions. Wanted to clarify them

1) When using for cursor_name to open the cursor, Do I need to explaicilty close it once I get all the rows in a loop?

2) Have a number table type (called NUM_TABLE) defined in database.

If i've a cursor like the following.

cursor c
slect emp_name
from emp
where emp_id in (
SELECT * FROM TABLE (CAST(p_emp_ids as NUM_TABLE))
);

Every time I execute this preocedure, I need to diffrent set of
emp ids in p_emp_ids table.
Wondering if it uses bin variable for p_emp_ids insternally so that
this sql does not need to be parsed every time I execute this.

Could you please clarify if this is considered as bind variable or not?

and Tom said...


1) depends on HOW you do it


declare
cursor c as select * from emp;
begin
for x in c
loop
...
end loop;
end;
/

nope, you don't close it, it is done for you.

declare
cursor c as as select * from emp;
begin
open c;
loop
fetch c into ...
exit when c%notfound;
end loop
CLOSE C;
end;
/

yup, you have to close it. if you open it -- you close it. I almost NEVER use open/close (ref cursors and bulk collect being the ONLY two times I do).

I try to ALWAYS use

for x in ( select .... )

or

for x in CURSOR_NAME


I like for x in (select .... best of all by far.


2) it'll definitely be a bind variable.

Rating

  (2 ratings)

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

Comments

Auto closing of cursor

Vikas Sharma, September 05, 2005 - 3:03 pm UTC

Hi Tom,

In ref to the first question. If the close statement is not use to close the explicit cursor, does it get close by it self after some time or it may or may not get close Please let me know.

I have read in a book that oracle will close cursor But one should not rely on oracle and close it explicitly.

thanks

Vikas Sharma


Tom Kyte
September 05, 2005 - 6:51 pm UTC

the client is responsible for closing the ref cursors returned to it, else they will never close until you exit the session.

ref cursors must be closed by the client (java, VB, whatever)

static cursor vs dynamic cursor

Antonio Vargas, September 06, 2005 - 7:36 am UTC

Tom, doing this:

declare
cursor c is select user from dual
begin
...
end;

cursor c is static and thus auto-closed when it goes out of scope, doesn't it?

Tom Kyte
September 06, 2005 - 8:46 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:44595370835434 <code>



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.