Can you pass a parameter to a cursor?

Can you pass a parameter to a cursor? Explain with an explain.

A cursor can have a parameter in the IN mode.

Example:
The cursor below accepts a parameter of data type varchar2.
cursor sample (v_key varchar2) is
        select initcap(book_title) bk_title,
                 sum(quantity) sales, author_key 
        from
            book join sales using (book_key)
                 join book_author using (book_key)
        where author_key = v_key
        group by initcap(book_title), author_key;

Parameterized cursor:

/*Create a table*/
create table Employee(
ID VARCHAR2(4 BYTE)NOT NULL,
First_Name VARCHAR2(10 BYTE)
);

/*Insert some data*/
Insert into Employee (ID, First_Name) values (‘01’,’Harry’);

/*create cursor*/
declare
cursor c_emp(cin_No NUMBER)is select count(*) from employee where id=cin_No;
v_deptNo employee.id%type:=10;
v_countEmp NUMBER;
begin
open c_emp (v_deptNo);
fetch c_emp into v_countEmp;
close c_emp;
end;

/*Using cursor*/
Open c_emp (10);

Oracle - Can you pass a parameter to a cursor? Explain with an explain - April 10, 2009 at 11:00 AM

Can you pass a parameter to a cursor? Explain with an explain  

Yes, explicit cursors can take parameters.

Also read

Oracle cursors interview questions

What a SELECT FOR UPDATE cursor represent?, What WHERE CURRENT OF clause does in a cursor?, Can you pass a parameter to a cursor?, Explain the functioning of CURSOR FOR LOOP with example., Define Simple/Explicit , Parametric and Internal/Implicit cursor.............

What is the difference between REF Cursor & Normal Cursor in oracle?

Normal cursors fall under the category of static cursors while REF cursors are dynamic.........

What is use of a cursor variable in oracle?

A cursor variable works like pointer in C. It is used to hold address of an item rather than the item itself. Cursor variables can be used to hold different values at run time............

Oracle - use of parameterized cursor

A parameter makes the cursor more reusable, A parameter avoids scoping problems..............

Use of Roles in oracle

Roles in Oracle can be used to grant privileges to a specific group of users for security. The database manager is usually responsible for granting privileges to a Role........... 

<<Previous  Next>>

Write your comment - Share Knowledge and Experience Discussion Board Oracle and C# I want to pass a parameter from C# code behind to an oracle explicit cursor storedprocedure,how can i do it? Please show me with example. Thanks in advance! zelalem 02-4-2012 07:43 AM