Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Tuesday, 27 June 2017

PL/SQL Cursor with Parameter

To process any DML or SELECT queries, Oracle allocates an area of memory on the database server, known as context area
PL/SQL Cursor is a pointer to the context area
Context area contains information about the SQL statement and the set of data returned or affected by that statement
           PL/SQL Cursor is a mechanism by which one can name that work area and  manipulate the information within it

Parameter Cursor:


Parameters allow values to be passed to a cursor when it is opened and to be used in the query when it executes
Different active set each time based on a parameter value that is passed to a cursor

Parameter Cursor Syntax:

CURSOR cursor_name(para_name1  datatype,
    para_name2  datatype,…)                           
          IS  select_statement;

Parameter Cursors Example:


   CURSOR par_cur (p_deptno NUMBER, p_job VARCHAR2)
      SELECT empno, ename
        FROM emp
       WHERE deptno = p_deptno AND UPPER (job) = UPPER (p_job);
   v_deptno   emp.deptno%TYPE   := &dno;
   v_job      emp.job%TYPE      := '&job';
   FOR emp_record IN par_cur (v_deptno, v_job)
      DBMS_OUTPUT.put_line('Empno      :' || emp_record.empno);
      DBMS_OUTPUT.put_line('Ename       :' || emp_record.ename);

Share this article :


  1. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  2. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.