Using Objects & Nested tables.

1. Create an Object at database level 

    -- Can be Imagined as a record ( composite fields with datatypes )

2. Create a Nested table on above Created Object.

3. Use the Nested table type within the Pl/SQL Code.

This helps to avoid to iterate through the Collections and those can be directly used within the sql statements as table.

Examples:  ( @ SQL Level )

Demo 1: 

CREATE or replace TYPE phone AS TABLE OF NUMBER;   

/

CREATE or replace TYPE phone_list AS TABLE OF phone;

/

SELECT t.COLUMN_VALUE

  FROM TABLE(phone(1,2,3)) t;

  

  

Demo 2:

create or replace type emp_obj as object

( empno number,

   deptno number,

   sal number);

   

create or replace type emp_tab is table of emp_obj;

SELECT empno, deptno, sal

  FROM TABLE (emp_tab (emp_obj (1, 10, 1000), emp_obj (2, 20, 2000)));

  

  

Examples : ( @ PL/SQL Level )

CREATE OR REPLACE TYPE emp_temp AS OBJECT

   (EMPNO NUMBER,

    ENAME VARCHAR2 (10),

    SAL NUMBER,

    DEPTNO NUMBER);

CREATE OR REPLACE TYPE emp_temp_tab IS TABLE OF emp_temp;

DECLARE

   emp_table   emp_temp_tab;

BEGIN

   SELECT emp_temp (EMPNO,

                    ename,

                    sal,

                    deptno)

     BULK COLLECT

     INTO emp_table

     FROM emp;

   DBMS_OUTPUT.put_line (emp_table.COUNT);

   -- Access the collection within the sql.  ( No looping )

   INSERT INTO emp_out

          SELECT * -- column names can be specified 

           FROM TABLE (emp_table);

   COMMIT;

   

END;