BULK COLLECT into a table of objects

Question

When attempting to use a BULK COLLECT statement I got error ORA-00947: not enough values.

An example script:

CREATE OR REPLACE 
TYPE company_t AS OBJECT ( 
   Company          VARCHAR2(30),
   ClientCnt            INTEGER   );
/

CREATE OR REPLACE 
TYPE company_set AS TABLE OF company_t;    
/

CREATE OR REPLACE 
FUNCTION piped_set (
  v_DateBegin IN DATE,
  v_DateEnd IN DATE
)
return NUMBER /*company_set pipelined*/ as
  v_buf company_t := company_t( NULL, NULL);
  atReport company_set;
  sql_stmt VARCHAR2(500) := '';
begin

select * BULK COLLECT INTO atReport
from (
   SELECT 'Descr1', 1 from dual
   UNION
   SELECT 'Descr2', 2 from dual ) ;

  return 1;
end;

The error occurs at the line select * BULK COLLECT INTO atReport.

Straight PL/SQL works fine by the way (so no need to mention it as a solution). Usage of BULK COLLECT into a user table type is the question.

Solution

Your company_set is a table of objects, and you're selecting values, not objects comprised of those values. This will compile:

select * BULK COLLECT INTO atReport
from (
   SELECT company_t('Descr1', 1) from dual
   UNION
   SELECT company_t('Descr2', 2) from dual ) ;

... but when run will throw ORA-22950: cannot ORDER objects without MAP or ORDER method because the union does implicit ordering to identify and remove duplicates, so use union all instead:

select * BULK COLLECT INTO atReport
from (
   SELECT company_t('Descr1', 1) from dual
   UNION ALL
   SELECT company_t('Descr2', 2) from dual ) ;

Comments

[xacinay]

Thanks, @Alex, code really helped! As a continuation of the issue, is it possible to pass the result of bulk insert as pipelined function result? Currently, I get the result into atReport ant then execute "FOR .. LOOP ( pipe_row (atReportRow) ) END LOOP; ". Maybe this part can simplified too?

[Alex Poole]

@xacinay - not with bulk collect; you could do it as a cursor loop instead (for rec in (select company_t() as comp ...) loop pipe row rec.comp; end loop; or similar. I imagine the performance would be similar. You should probably ask a new question if you try that and cant make it work.

[xacinay]

That's exactly the way it works: for x in (select ..) pipe row ( company_t( x.Company, x.ClientCnt) ) ). I just wondered, it it can be even simplier. Anyway, current result is pretty fine, thanks!