Thanks for the question, Andrew.

Asked: January 11, 2002 - 8:58 pm UTC

Last updated: July 06, 2010 - 12:50 pm UTC

Version: 8i

Viewed 10K+ times! This question is

You Asked

Tom

Please could you provide guidelines on the use of cursor parameters. I have read that using them allows better resource sharing.
e.g.
cursor c1 (v_hire_date in emp.hire_date%type) is
select * from emp where hire_date = v_hire_date;

is better than

cursor c1 is
select * from emp where hire_date = v_hire_date;

How can the same same benefit be achieved with ref cursors and implicit cursors like:

for rec in (select * from emp where hire_date = v_hire_date) loop
.. whatever...
end loop;

Thanks for a brilliant site!

Regards
Andrew

and Tom said...

They are identical, those two cursor definitions are the same.

The first one is useful if you put the cursors into a package:

create package my_cursors
as
cursor c1 ( v_hire_date in date ) is
select * from emp where hire_date = v_hire_date;

....
end;
/

since v_hire_date isn't in scope, you need to parameterize it. In a local block, you need not do this:

create procedure p
as
v_hire_date date;

cursor c1 is select * from emp where hire_date = v_hire_date;
begin

here, since v_hire_date is in scope, you need not parameterize it. In theory -- the cursor c1 in the package could be reused more often because procedure p1, p2, p3, ... pn could all use it where as the cursor in procedure p can ONLY be used by P (obviously, it is only known to p).

I personally am not a huge fan of the package approach. If you use this you have to remember that (for example) P1, P3, P5 and P7 use it. If you are working on the code for P1, you have to be careful not to call P3, P5, P7 or any piece of code that might call them as YOU are using that cursor and they are GOING TO WANT TO use that cursor. It becomes a shared resource and you have these implicit dependencies between these bits of code. I would rather let each have their own cursor and avoid such situations.

I use implicit cursors -- the last one you have there, the cursor for loop, whenever I can. Meaning the only time I have an explicit cursor is when I am forced to use a ref cursor.

Rating

(4 ratings)

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

Comments

use of cursor parameters

Jean S, October 18, 2006 - 12:36 pm UTC

Thanks for clarifying cursor parameters.

I haven't seen in for loop implicit cursor before. May I guess another reason for explicit cursors would be if you need more than one %rowtype variable.

October 18, 2006 - 1:12 pm UTC

You can do it this way too:

declare
cursor c is select ....

<things based on c%rowtype here>
begin
for x in C
loop

cursor parameters and ref cursors

sriram, December 07, 2006 - 11:51 am UTC

Hi tom thanks for the brilliant answers
We may use a parameterized cursor for accepting different categories and return the appropriate rows

say for example
DECLARE
CURSOR app_cur (category_in VARCHAR2)
IS
SELECT name, category, last_used_date
FROM app
WHERE category = UPPER (category_in);

app_rec app_cur%ROWTYPE;

BEGIN

OPEN app_cur (:app.category);
FETCH app_cur INTO app_rec;

I can pass values like
OPEN app_cur (:app.category);
OPEN app_cur ('database');
OPEN app_cur ('appserver');
etc....

ie the where clause changes ...
I have read in one of your forums where you mention about using an appliaction context along with a ref cursor for these kind of situations .... correct me if my understanding is wrong....

The question is if i can make use of ref cursor for these type of scenarios ...which is better a ref cursor or a parametrized cursor?

Thanks in advance
Sriram

December 07, 2006 - 1:21 pm UTC

the where clause it NOT changing at all - why do you say that?

it is ALWAYS:

where category = f( :VARIABLE )

cursor parameter and ref cursor

sriram, December 08, 2006 - 8:53 am UTC

Hi tom
Thanks for the information ....
I was wrong in saying that the where clause changes ..i should have updated saying the where predicate changes ..appologies ....
I am still not clear whether a ref cursor can be used in these kind of scenarios ?
If yes what are the pros and cons ...

Thanks in advance
Sriram

December 09, 2006 - 12:16 pm UTC

what is the difference between

a where clause
the predicate

?? a predicate is phrased using a where clause, the where clause is the predicate.

you have a single predicate in your where clause, it is never changing.

Parametrized cursors

Suhail Dev, June 29, 2010 - 7:48 am UTC

I have a query where I am joining two table say A and B.
Select * from A, var_B
where rownum < var_no;
Here table name A is fixed but value of var_B is different depending upon different situations. The results are later filtered according to var_no specified in cursor.

Using var_no is ok in cursor, but when I try to use var_B
as cursor parameter, I am getting malformed cursor error.

Can I use Parametrized cursors for substituting values in where clause only? If yes, please tell h ow can I create a join query where value of one of the table is decided at runtime.

thanks you

July 06, 2010 - 12:50 pm UTC

you will have to use dynamic sql.

You cannot parameterize identifiers, you need an entirely DIFFERENT, NEW query with a new plan and security checks and all.

You'll not be using bind variables for "var_b", you'll be using dynamic sql.

beware of sql injection!
read about the dbms_assert package and use it when constructing your query!

Breadcrumb

Question and Answer