Live ORACLE

DECLARE

TYPE empcurtyp IS REF CURSOR;

 TYPE namelist IS TABLE OF employees.last_name%TYPE;

 TYPE sallist IS TABLE OF employees.salary%TYPE;

 emp_cv  EMPCURTYP;

 names   NAMELIST;

 sals    SALLIST;

BEGIN

OPEN emp_cv FOR

  SELECT last_name,

         salary

  FROM   employees

  WHERE  job_id = 'SA_REP';



FETCH emp_cv BULK COLLECT INTO names,sals;



CLOSE emp_cv;



-- loop through the names and sals collections

FOR i IN names.FIRST.. names.LAST LOOP

  dbms_output.Put_line('Name = '

                       ||Names(i)

                       ||', salary = '

                       ||Sals(i));

END LOOP;

END;

/

Name = Tucker, salary = 10000
Name = Bernstein, salary = 9500
Name = Hall, salary = 9000
Name = Olsen, salary = 8000
Name = Cambrault, salary = 7500
Name = Tuvault, salary = 7000
Name = King, salary = 10000
Name = Sully, salary = 9500
Name = McEwen, salary = 9000
Name = Smith, salary = 8000
Name = Doran, salary = 7500
Name = Sewall, salary = 7000
Name = Vishney, salary = 10500
Name = Greene, salary = 9500
Name = Marvins, salary = 7200
Name = Lee, salary = 6800
Name = Ande, salary = 6400
Name = Banda, salary = 6200
Name = Ozer, salary = 11500
Name = Bloom, salary = 10000
Name = Fox, salary = 9600
Name = Smith, salary = 7400
Name = Bates, salary = 7300
Name = Kumar, salary = 6100
Name = Abel, salary = 11000
Name = Hutton, salary = 8800
Name = Taylor, salary = 8600
Name = Livingston, salary = 8400
Name = Grant, salary = 7000
Name = Johnson, salary = 6200

PL/SQL procedure successfully completed.

Fetching from a Cursor Variable into Collections