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
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