Usage of PL/SQL Collections

By | August 4, 2015
/*********************************************************
*PURPOSE: Sample Usage of PL/SQL Collections             *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
SET serveroutput ON;
DECLARE
  --------------------
  --Record type object
  --------------------
TYPE emp_type
IS
  record
  (
    empno  NUMBER(4),
    ename  VARCHAR2(10),
    job    VARCHAR2(9),
    deptno NUMBER(2));
  -------------------
  --Table type object
  -------------------
TYPE emp_table
IS
  TABLE OF emp_type;
  ---------------------
  --Table type variable
  ---------------------
  emp_var emp_table := emp_table();
  --------
  --Cursor
  --------
  CURSOR emp_cur
  IS
    SELECT * FROM emp;
  --
  --
BEGIN
  ----------------------------------------
  --Assigning value to table type variable
  ----------------------------------------
  FOR i IN emp_cur
  LOOP
    BEGIN
      --
      emp_var.extend(); --To add one cell of memory to table type variable since it is empty
      emp_var(emp_var.LAST).ename := i.ename;
      emp_var(emp_var.LAST).job   := i.job;
      --
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Error while assigning: '||SQLERRM);
    END;
  END LOOP;
  --
  --
  dbms_output.put_line('row count: '||emp_var.COUNT);
  ------------------------------------------------
  --Printing values present in table type variable
  ------------------------------------------------
  FOR i IN emp_var.FIRST .. emp_var.LAST
  LOOP
    BEGIN
      --
      dbms_output.put_line(RPAD('Ename('||i||')',10,'')||emp_var(i).ename);
      dbms_output.put_line(RPAD('Job('||i||')',10,'')||emp_var(i).job);
      dbms_output.put_line(RPAD('Empno('||i||')',10,'')||emp_var(i).empno); --will print blank since no value is assigned
      --
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Error while printing: '||SQLERRM);
    END;
  END LOOP;
  --
END;
/
WOW! Did you like this post? We'll send more interesting posts like Usage of PL/SQL Collections to you!
Enter your Email Address: