Table-cast vs cast-multiset in pl-sql

Question

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?

What performance gain or impact are you observing? I for one have no idea what the question really is. — Michael O'Neill, Jul 17 '14 at 22:17
Sorry for being so late.. In both the example some casting has been used... my basic target is to know how the operation is actually being done in db. some may consumes memory more than the other or some may be more time complex. Just want to have idea to do proper use, when to use what. — Plymouth Rock, Aug 19 '14 at 07:53
You may be on the start of a good question, I don't know. I think you (and the others upvoting your question) should make an effort to clarify/improve the question. For example in your comment above, What does "time complex" mean? — Michael O'Neill, Sep 03 '14 at 21:08

score 13 · Accepted Answer · edited Nov 12 '15 at 09:24

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

1 Answers1