How to make use of parameterized cursor in Oracle


Oracle - use of parameterized cursor

Next>>         Oracle Tutorial
Oracle - use of parameterized cursor - April 30, 2009 at 20:00 PM by Amit Satpute

Explain how to make use of parameterized cursor in Oracle.

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


Cursor without parameters:

cursor find_id is
select id, name from emp;

Parameterized cursor

cursor find_id is
select id, name, dept from emp
where dept =”IT”

Generalizing a cursor:

     CURSOR cursor_emp (dept_in VARCHAR2)
           SELECT name, dept, last_used_date
           FROM emp
           WHERE dept = dept_in;

emp_rec cursor_emp%ROWTYPE;
         OPEN cursor_emp (:emp.dept);
         FETCH cursor_emp INTO emp_rec;

In the previous example, we passed a hard-coded value (IT) as a parameter to the where.
In this example, we generalized it. So whenever we open a cursor with an argument, the select is parsed and the output is limited to the conditions that apply.


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 cursors

Oracle form

Oracle security

Oracle system privilege

Oracle object privileges

Write your comment - Share Knowledge and Experience


Interview questions
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring