Table-casting versus casting-multisets in pl-sql

Question

What are Table-CAST and CAST-Multiset ?

Table-CAST example

---

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

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 is the performance or performance impact on the code?

Answer 1

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

A CAST function call (MULTISET ()) converts a relational result set into a collection type. This is primarily used when pasting into a table with a column defined as a nested table.

Several sites use object-relational functions in their persistent data structures, so the second use is quite rare. But the ability to use collections in embedded SQL operations is a very cool technique and is widely used in PL / SQL.