Home » SQL & PL/SQL » SQL & PL/SQL » Comparing CAST (multiset) with Bulk Collect (11.0.2.10)
Comparing CAST (multiset) with Bulk Collect [message #658433] Wed, 14 December 2016 00:38 Go to next message
OraFerro
Messages: 316
Registered: July 2011
Senior Member
Dear All,

This is a general question regarding two techniques that can be used in a PLSQL select statement to fill a table of object:
1- Using:
RETURN My_TABLE As MTable MY_TABLE;

CAST(MULTISET(
      -- SELECT ... select statement here
      ) AS My_TABLE) INTO MTable FROM DUAL;

2- Using:
   SELECT OBJ_My_Object( --Object definition
                            
                                    )
      BULK COLLECT INTO My_Table
      FROM
      (  
        -- SELECT ... select statement here
      )

Please not that the code provided is just an example to ask for your preference and advice as I found little sources on the internet that actually compares between the two techniques.

Thanks,
Ferro
Re: Comparing CAST (multiset) with Bulk Collect [message #658434 is a reply to message #658433] Wed, 14 December 2016 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The correct way is the second one.
As Tom Kyte once told me: why selecting from dual when dual has nothing to do in the issue.

Re: Comparing CAST (multiset) with Bulk Collect [message #658435 is a reply to message #658434] Wed, 14 December 2016 00:49 Go to previous messageGo to next message
OraFerro
Messages: 316
Registered: July 2011
Senior Member
Thanks Michel, but can you elaborate more or give me a link to an article that describes the advantages of the 2nd way over the first one?
Re: Comparing CAST (multiset) with Bulk Collect [message #658438 is a reply to message #658435] Wed, 14 December 2016 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Search on AskTom for the answer Tom gave me, it was about a query that should return a message when there is no rows to return.

Re: Comparing CAST (multiset) with Bulk Collect [message #658449 is a reply to message #658433] Wed, 14 December 2016 06:59 Go to previous message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
I didn't check SQL function COLLECT vs PL/SQL BULK COLLECT fetch, but you can also:

SELECT  CAST(COLLECT(OBJ_My_Object(...)) AS OBJ_TBL_My_Object
  INTO  OBJ_TBL_My_Object_variable
  FROM
      (  
        -- SELECT ... select statement here
      )
/

SY.
Previous Topic: Sending a Password protected PDF using utl_smtp
Next Topic: Bulk bind a single attribute of a collection
Goto Forum:
  


Current Time: Tue Feb 20 20:28:27 CST 2018

Total time taken to generate the page: 0.20822 seconds