By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE PACKAGE pkg_test
is
type codes_t is table of number;
function getCodes(
acc_id in NUMBER
)
return codes_t PIPELINED;
end pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
is
function getCodes(
acc_id in NUMBER
) return codes_t PIPELINED
is
v_codes codes_t := codes_t();
begin
for r in (select acc_id as code from dual
union
select acc_id+1 from dual) loop
PIPE row(r.code);
end loop;
end getCodes;
end pkg_test;
/
select t.* from table( pkg_test.getCodes( 111 ) ) t;
| COLUMN_VALUE |
|---|
| 111 |
| 112 |