Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

[ Oracle PL/SQL ]

Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

Using dynamic SQL with OPEN FOR, FETCH and CLOSE:

set serveroutput on
declare 
 -- define a type cursor. 
 -- declare a variable of type cursor. This variable will 
 -- execute the dynamic SQL stmt later (OPEN, LOOP-FETCH, CLOSE).
 type empcurtype is ref cursor;
 v_emp_c    empcurtype;
 emp_rec    employees%rowtype;
 v_stmt_str varchar2(200);
 v_e_job    employees.job_id%type;
begin
  -- dynamic SQL stmt. Note the placeholder (:j) for 
  -- a bind variable.
  v_stmt_str := 'SELECT * FROM employees '|| 
                'WHERE job_id = :j';
  
  -- Open the cursor. List the bind variables
  -- with the USING clause. Placeholders, in this
  -- case (single SQL stmt), are associated 
  -- BY POSITION, and NOT by name.
  OPEN v_emp_c FOR v_stmt_str USING 'IT_PROG';
  LOOP
    FETCH v_emp_c INTO emp_rec;
    exit when v_emp_c%notfound;
    dbms_output.put_line('Empid: '|| emp_rec.employee_id || 
                   '--'||emp_rec.first_name || ' ' || emp_rec.last_name );
  END LOOP;
  CLOSE v_emp_c;
end;

nonymous block completed
Empid: 103--Alexander Hunold
Empid: 104--Bruce Ernst
Empid: 105--David Austin
Empid: 106--Valli Pataballa
Empid: 107--Diana Lorentz

(cont..)