Collections: Nestes Tables - Use of CAST , MULTISET , COLLECT and BULK_EXCEPTIONS

CAST
Maps a collection of on type to a collection of another type. It can be used in SQL statements to convert from one built in datatype or collection type to another built in datatype or collection type.

Thus instead of TO_CHAR you can also use CAST.

SELECT TO_CHAR(12345) FROM DUAL;

can also be written as

SELECT CAST(12345 AS VARCHAR2(100)) FROM DUAL;

The same usage can be extented to other datatype like nested tables etc to select a multiset of data from another collection type or table and cast it to a Nested Table type

MULTISET

Maps a database table to a collection.

With Multiset and Cast we can select a set of data from the database and collect it into a Nested table type have the same set of columns as in the select clause.

Below is an example of the same to collect a list of employees and display their date of joining.

Create the object type and table types for employee id and date of joining.

DROP TYPE typ_employee_doj
/
CREATE OR REPLACE TYPE obj_employee_doj AS OBJECT
(
employee_id    NUMBER,
hire_date      DATE
)
/
DROP TYPE tab_employee_doj
/
CREATE OR REPLACE TYPE tab_employee_doj AS TABLE OF obj_employee_doj
/

PLSQL block to select list of employees and their date of joining, belonging to ACCOUNTS department

DECLARE
vtab_employee_doj   tab_employee_doj := tab_employee_doj();
v_return  NUMBER;
BEGIN

  SELECT CAST(MULTISET(SELECT EMPLOYEE_ID, HIRE_DATE
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID IN (
  SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'FI_ACCOUNT')
  )
  AS tab_employee_doj )
  INTO vtab_employee_doj
  FROM DUAL;
  
  FOR i IN vtab_employee_doj.FIRST .. vtab_employee_doj.LAST
    LOOP --{
      dbms_output.put_line('Employee Id : ' || vtab_employee_doj(i).employee_id || ' JOINING DATE : ' || vtab_employee_doj(i).hire_date);
  END LOOP; --}

END;

Output of the code

Employee Id : 110 JOINING DATE : 28-SEP-05
Employee Id : 111 JOINING DATE : 30-SEP-05
Employee Id : 112 JOINING DATE : 07-MAR-06
Employee Id : 113 JOINING DATE : 07-DEC-07
Employee Id : 109 JOINING DATE : 16-AUG-02

COLLECT 

The COLLECT pseudofunction enables aggregation of data from an Oracle table into a collection. You can also specify an ORDER BY and DISTINCT clause to get a distinct set of ordered data. Below is an example of using the COLLECT pseudofunction to get a list of distinct employee names ordered by the hire date.

CREATE OR REPLACE TYPE tab_empname AS TABLE OF VARCHAR2(100)
/

SELECT DEPARTMENT_ID, CAST(COLLECT(DISTINCT LAST_NAME ORDER BY HIRE_DATE) AS tab_empname) AS EMP_HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10,20,30)
GROUP BY DEPARTMENT_ID;

You will get an output as below

DEPARTMENT_ID	EMP_BY_HIRE_DATE
10	HR.TAB_EMPNAME('Whalen')
20	HR.TAB_EMPNAME('Hartstein','Fay')
30	HR.TAB_EMPNAME('Raphaely','Khoo','Tobias','Baida','Himuro','Colmenares')

How to Handle BULK EXCEPTIONS

When handling BULK EXCEPTIONS the SAVE EXCEPTIONS clause is important else the code will exit after the first exception has occured.

DROP TYPE typ_emp_id;
/

CREATE OR REPLACE TYPE typ_emp_id IS TABLE OF NUMBER(6,0);
/

DROP TYPE typ_emp_name;
/

CREATE OR REPLACE TYPE typ_emp_name IS TABLE OF VARCHAR2(25);
/

DECLARE

vtyp_emp_name  typ_emp_name := typ_emp_name(null);
vtyp_emp_id    typ_emp_id := typ_emp_id(null);

l_error_count  NUMBER;

BEGIN

  BEGIN

      SELECT
      EMPLOYEE_ID,
      FIRST_NAME || ' ' || LAST_NAME
      BULK COLLECT INTO
      vtyp_emp_id,
      vtyp_emp_name
      FROM EMPLOYEES
      WHERE DEPARTMENT_ID = 50;
      
--      FOR i IN vtyp_emp_name.FIRST ..vtyp_emp_name.LAST
--      LOOP
--        dbms_output.put_line('name ' || i || ' ' || vtyp_emp_name(i));
--      END LOOP;
      FORALL i IN vtyp_emp_name.FIRST ..vtyp_emp_name.LAST SAVE EXCEPTIONS
      INSERT INTO EMP_FULL_NAME (EMPLOYEE_ID, EMP_FULL_NAME)
      VALUES (vtyp_emp_id(i), vtyp_emp_name(i));
      
      
    EXCEPTION
    WHEN OTHERS THEN
    FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT  
      LOOP
        DBMS_OUTPUT.put_line ('Rows inserted : ' || SQL%ROWCOUNT);
        l_error_count := SQL%BULK_EXCEPTIONS.count;
        DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
  
           DBMS_OUTPUT.put_line (
                 'Error '
              || indx
              || ' occurred on index '
              || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
              || ' selecting emp name from employees for employee id "'
              || vtyp_emp_id (
                    SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
              || '" : "'
              || vtyp_emp_name (
                    SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
              || '"');
           DBMS_OUTPUT.put_line (
                 'Oracle error is : '
              || SQLERRM(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
                 
        END LOOP;
    END;

EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.put_line ('OTHERS ERROR ' || SQLCODE);
END; 

Bulk exceptions are printed on console as below

Rows inserted : 42
Number of failures: 3
Error 1 occurred on index 7 selecting emp name from employees for employee id "126" : "Irene Mikkilineni"
Oracle error is : ORA-12899: value too large for column  (actual: , maximum: )
Rows inserted : 42
Number of failures: 3
Error 2 occurred on index 17 selecting emp name from employees for employee id "136" : "Hazel Philtanker"
Oracle error is : ORA-12899: value too large for column  (actual: , maximum: )
Rows inserted : 42
Number of failures: 3
Error 3 occurred on index 30 selecting emp name from employees for employee id "184" : "Nandita Sarchand"
Oracle error is : ORA-12899: value too large for column  (actual: , maximum: )

For CAST multiset we have to use nested tables. Else we get error as below.

DECLARE

TYPE typ_emp_name IS TABLE OF VARCHAR2(10);

vtyp_emp_name  typ_emp_name;

BEGIN

SELECT 
    CAST(MULTISET(SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES) AS typ_emp_name)
    INTO vtyp_emp_name
  FROM DUAL;
  
END;

Below is error

Error report -
ORA-06550: line 11, column 76:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Therefore create the collection type as a nested table and run the above PLSQL block

CREATE OR REPLACE TYPE typ_emp_name IS TABLE OF VARCHAR2(15);

Thought for the day

The purpose of our lives is to be happy.

--Dalai Lama