CODEWITHSUNDEEP
sqloracle-databaseplsql
David Ranzalo
David Ranzalo

Reputation: 13

BULK COLLECT into table of objects inside LOOP?

This is what i have done untill now. I created this object and table of objects.

CREATE TYPE my_obj as object(
   val1      varchar2(30),
   val2     varchar2(30),
   val3      varchar2(30));

create TYPE table_obj IS TABLE OF my_obj;

Inside my procedure i do this:

PROCEDURE MYPROC(
    TABLEOBJ                          OUT     table_obj, 
    MYCURSOR                          OUT     OTHERPCK.REFCURSOR,
    ...other IN param)
AS

--other code

Inside a LOOP i need to do a query and it gives three varchar2 values as output on each iteration (i.e. val1, val2, val3) and i need to store them in TABLEOBJ and after query this table to put all of the results on my ref cursor, so:

 BEGIN    
 FOR SOMETHING IN SOMETHINGELSE LOOP

 SELECT my_obj(VAL1, VAL2, VAL3) BULK COLLECT INTO TABLEOBJ
 FROM ...
 WHERE ...
 ENDLOOP;

 OPEN MYCURSOR FOR SELECT * FROM TABLE(TABLEOBJ);

The code compiles without problems but i get only one row in MYCURSOR and surely there are more than one. I have tried also:

SELECT VAL1, VAL2, VAL3 INTO TABLEOBJ

But i get:

PL/SQL: ORA-00947: not enough values

How can i put each result of the select (always one row of these three varchar2) and save it to TABLEOBJ?

Upvotes: 1

Views: 2036

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132650

The answer to your specific question is this, given that the select inside the loop always returns exactly one row:

DECLARE
  obj my_obj;
  tableobj table_obj := table_obj(); -- Initialise the collection
BEGIN    
 FOR SOMETHING IN SOMETHINGELSE LOOP

 SELECT my_obj(VAL1, VAL2, VAL3) INTO obj
 FROM ...
 WHERE ...

   tableobj.extend();
   tableobj(tableobj.COUNT) := obj;
 ENDLOOP;

 OPEN MYCURSOR FOR SELECT * FROM TABLE(TABLEOBJ);

However, I feel sure it would be possible to rewrite your query to avoid the loop altogether:

BEGIN    

 SELECT my_obj(VAL1, VAL2, VAL3) BULK COLLECT INTO TABLEOBJ
 FROM ...
 WHERE ... IN (SELECT SOMETHING ... SOMETHINGELSE)

 OPEN MYCURSOR FOR SELECT * FROM TABLE(TABLEOBJ);

But it's hard to be sure without knowing more about SOMETHING and SOMETHINGELSE!

Upvotes: 2

Related Questions