Table-cast vs cast-multiset in pl-sql

oracle plsql casting multiset

Plymouth Rock · May 20, 2014 · Viewed 23.1k times · Source

What is the use of Table-CAST and CAST-Multiset?

Example of Table-Cast

SELECT count(1)
INTO   v_Temp
FROM   TABLE(CAST(Pi_Save_Data_List AS Property_data_list))
WHERE  Column_Value LIKE '%Contact';

Example of Cast-Multiset

SELECT e.last_name,
   CAST(MULTISET(SELECT p.project_name
   FROM projects p 
   WHERE p.employee_id = e.employee_id
   ORDER BY p.project_name)
   AS project_table_typ)
FROM emps_short e;

What isthe performance gain or impact on the code?

Answer

The TABLE() function casts a nested table type to a relational result set. This is allows us to query a previously populated collection in SQL.

The CAST(MULTISET()) function call converts a relational result set into a collection type. This is primarily of use when inserting into a table with column defined as a nested table.

Few sites employ object-relational features in their permanent data structures so the second usage is pretty rare. But being able to use collections in embedded SQL statements is a very cool technique, and widely used in PL/SQL.

Table-cast vs cast-multiset in pl-sql

Answer

Related questions