Handle Table Type in PLSQL

This post is all about how to populate and retrieve data from Table type in PLSQL.

Sample Object Type:

CREATE OR REPLACE

TYPE type_employee AS OBJECT

(

   EMPLOYEE_ID NUMBER (6),

   FIRST_NAME VARCHAR2 (20),

   LAST_NAME VARCHAR2 (25),

   SALARY NUMBER (8),

   EMAIL VARCHAR2 (25)

);

Sample Table Array Type:

CREATE OR REPLACE

TYPE employee_array AS TABLE OF type_employee;

PLSQL Anonymous Block:

DECLARE

   emp_array employee_array;

BEGIN

-- Use Object Type to Cast the data from table and use Bulk Collect to fetch large volumes of data at once

   SELECT type_employee(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, EMAIL)

   BULK COLLECT INTO emp_array

   FROM  EMPLOYEES;

   

-- Method 1 --retrieve data from Table Array

   FOR c1 IN (

      SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,EMAIL

      FROM TABLE( CAST( emp_array AS employee_array)) )

   LOOP

      DBMS_OUTPUT.PUT_LINE('Employee ID : ' || c1.EMPLOYEE_ID);

      DBMS_OUTPUT.PUT_LINE('Employee Name : ' || c1.FIRST_NAME || ' ' || c1.LAST_NAME);

      DBMS_OUTPUT.PUT_LINE('Employee Salary : '|| c1.SALARY);

      DBMS_OUTPUT.PUT_LINE('Employee Email : '|| c1.Email);

   END LOOP;

-- Method 2 --retrieve data from Table Array

   FOR i IN 1..emp_array. COUNT LOOP

      DBMS_OUTPUT.PUT_LINE('Employee ID : ' || emp_array(i).EMPLOYEE_ID);

      DBMS_OUTPUT.PUT_LINE('Employee Name : ' || emp_array(i).FIRST_NAME || ' ' || emp_array(i).LAST_NAME);

      DBMS_OUTPUT.PUT_LINE('Employee Salary : '|| emp_array(i).SALARY);

      DBMS_OUTPUT.PUT_LINE('Employee Email : '|| emp_array(i).Email);   

   END LOOP;    

END;