Live ORACLE

DECLARE

 TYPE empcurtyp IS REF CURSOR;

 emp_cv    EMPCURTYP;

 emp_rec   employees%ROWTYPE;

 sql_stmt  VARCHAR2(200);

 v_job     VARCHAR2(10) := 'ST_CLERK';

BEGIN

 sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';



 OPEN emp_cv FOR sql_stmt USING v_job;



 LOOP

   FETCH emp_cv INTO emp_rec;

 

   EXIT WHEN emp_cv%NOTFOUND;

 

   dbms_output.Put_line('Name: '

                        ||emp_rec.last_name

                        ||' Job Id: '

                        ||emp_rec.job_id);

 END LOOP;



 CLOSE emp_cv;

END;

/

Name: Nayer Job Id: ST_CLERK
Name: Mikkilineni Job Id: ST_CLERK
Name: Landry Job Id: ST_CLERK
Name: Markle Job Id: ST_CLERK
Name: Bissot Job Id: ST_CLERK
Name: Atkinson Job Id: ST_CLERK
Name: Marlow Job Id: ST_CLERK
Name: Olson Job Id: ST_CLERK
Name: Mallin Job Id: ST_CLERK
Name: Rogers Job Id: ST_CLERK
Name: Gee Job Id: ST_CLERK
Name: Philtanker Job Id: ST_CLERK
Name: Ladwig Job Id: ST_CLERK
Name: Stiles Job Id: ST_CLERK
Name: Seo Job Id: ST_CLERK
Name: Patel Job Id: ST_CLERK
Name: Rajs Job Id: ST_CLERK
Name: Davies Job Id: ST_CLERK
Name: Matos Job Id: ST_CLERK
Name: Vargas Job Id: ST_CLERK

PL/SQL procedure successfully completed.

Dynamic SQL Fetching into a Record