Skip to Main Content
  • Questions
  • Question on collection using outside of package

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Girish.

Asked: June 16, 2017 - 4:52 am UTC

Last updated: June 16, 2017 - 9:15 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi ,

I have this requirement wherein I need to collect data in a collection in a PL/SQL package and use that collection in SQL * Plus in a query as shown in the below script.I was able to do by using cast(multiset). However I do not want to use this. Is there a way to do this ?

In LiveSQL URL please see statement 53,54 and 61 for these steps

create or replace package dfr_pkg as 
l_emp NUMBERLIST_TBL; -- a nested table  
end dfr_pkg;

create or replace package body dfr_pkg as 
procedure p1 as  
begin 
select empno bulk collect into dfr_pkg.l_emp 
from emp where rownum <=10; 
end; 
begin 
p1(); 
end dfr_pkg;


SQL> select column_name from table(cast(dfr_pkg.l_emp as NUMBERLIST_TBL));


Thanks,
Girish

with LiveSQL Test Case:

and Chris said...

The short answer is you can't.

Using PL/SQL arrays in SQL only works within PL/SQL:

create table emp (
  empno      number( 4,0 ),
  ename      varchar2( 10 ),
  job        varchar2( 9 ),
  mgr        number( 4,0 ),
  hiredate   date,
  sal        number( 7,2 ),
  comm       number( 7,2 ),
  deptno     number( 2,0 ),
  constraint pk_emp primary key ( empno )
);

insert into emp values (
  7839,  'KING',  'PRESIDENT',
  null,  to_date( '17-11-1981','dd-mm-yyyy' ),
  5000,  null,  10
);

create type numberlist_tbl as
  table of number;
/

create or replace package dfr_pkg as
  l_emp numberlist_tbl; -- a nested table  
  procedure p2;
end dfr_pkg;
/

create or replace package body dfr_pkg as

  procedure p1
    as
  begin
    select empno
    bulk collect into
      dfr_pkg.l_emp
    from emp
    where rownum <= 10;

  end;

  procedure p2
    is
  begin
    p1 ();
    for rws in (
      select *
      from table ( dfr_pkg.l_emp )
    ) loop
      dbms_output.put_line( rws.column_value );
    end loop;

  end p2;

begin
  p1 ();
end dfr_pkg;
/

exec dfr_pkg.p2;

7839

select *
from table ( cast( dfr_pkg.l_emp as numberlist_tbl ) );

SQL Error: ORA-06553: PLS-221: 'L_EMP' is not a procedure or is undefined


The longer answer is you could create a pipelined table function to return the data.

create or replace package dfr_pkg as
  l_emp numberlist_tbl; -- a nested table  
  procedure p2;
  function f return numberlist_tbl;
end dfr_pkg;
/

create or replace package body dfr_pkg as

  procedure p1
    as
  begin
    select empno
    bulk collect into
      dfr_pkg.l_emp
    from emp
    where rownum <= 10;

  end;

  procedure p2
    is
  begin
    p1 ();
    for rws in (
      select *
      from table ( dfr_pkg.l_emp )
    ) loop
      dbms_output.put_line( rws.column_value );
    end loop;

  end p2;

  function f return numberlist_tbl as
    retval   numberlist_tbl := numberlist_tbl();
  begin
    for rws in (
      select *
      from table ( dfr_pkg.l_emp )
    ) loop
      retval.extend;
      retval( retval.last )   := rws.column_value;
    end loop;

    return retval;
  end;

begin
  p1 ();
end dfr_pkg;
/
select * from table ( dfr_pkg.f );

Result Sequence
---------------
           7839


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library