drop database if exists dump_package_db;
NOTICE:  database "dump_package_db" does not exist, skipping
drop database if exists restore_package_db;
NOTICE:  database "restore_package_db" does not exist, skipping
create database dump_package_db;
create database restore_package_db;
\c dump_package_db
drop package if exists pck1;
NOTICE:  package pck1() does not exist, skipping
drop table if exists tb1;
NOTICE:  table "tb1" does not exist, skipping
create table tb1(c1 int,c2 varchar2);
create or replace package pck1 as
type t1 is record(c1 int,c2 varchar2);
type t2 is varray(10) of int;
type t3 is table of varchar2;
type t4 is ref cursor;
v1 t1 :=(1,'1');
v2 t2 :=t2(1,1,1);
v3 t3 :=t3('1','1','1');
v4 tb1%rowtype :=(1,'1');
procedure proc1(var1 out v1.c2%type, var2 v2%type, var3 v3%type, var4 tb1%rowtype,var5 t4);
end pck1;
/
create or replace package body pck1 as
type t5 is record(c1 int,c2 varchar2);
type t6 is varray(10) of int;
type t7 is table of varchar2;
type t8 is ref cursor;
v5 t5 :=(2,'2');
v6 t6 :=t2(2,2,2);
v7 t7 :=t3('2','2','2');
v8 tb1%rowtype :=(2,'2');
procedure proc1(var1 out v1.c2%type, var2 v2%type, var3 v3%type, var4 tb1%rowtype, var5 t4) as
begin
  var1 := v5.c2;
  v6(1) := var2(1);
  v7(1) := var3(1);
  v5 := var4;
  open var5 for select * from tb1;
  raise info 'var1 is %',var1;
  raise info 'v6(1) is %',v6(1);
  raise info 'v7(1) is %',v7(1);
  raise info 'v5 is %',v5;
end;
end pck1;
/
drop table if exists rowtype_tb;
NOTICE:  table "rowtype_tb" does not exist, skipping
create table rowtype_tb(c1 int,c2 varchar2);
insert into rowtype_tb values(1,'a'),(2,'b');
create or replace package rowtype_pckg as
function rowtype_func() return setof rowtype_tb;
end rowtype_pckg;
/
create or replace package body rowtype_pckg as
function rowtype_func() return setof rowtype_tb as
r rowtype_tb%ROWTYPE;
begin
return query select * from rowtype_tb;
end;
end rowtype_pckg;
/
call rowtype_pckg.rowtype_func();
 c1 | c2 
----+----
  1 | a
  2 | b
(2 rows)

-- ROOT package
create or replace package pck9 is
type r1 is record (a int, b int);
type r2 is varray(10) of int;
type r3 is table of int;
type r4 is record (a r2);
va r1;
vb r2;
vc r3;
vd int;
vf r4;
end pck9;
/
create or replace package body pck9 is
end pck9;
/
-- reference package var in spec
create or replace package pck8 is
ve int := pck9.vd;
procedure p8;
end pck8;
/
-- reference package var type
create or replace package pck7 is
ve pck9.vd%type;
end pck7;
/
-- reference package type
create or replace package pck6 is
va pck9.r1;
end pck6;
/
-- test parallel_enable
CREATE TYPE my_outrec_typ AS (
    employee_id numeric(6,0),
    department_id numeric,
    first_name character varying(30),
    last_name character varying(30),
    email character varying(30),
    phone_number character varying(30)
);
create or replace package my_pkg as
    FUNCTION pkg_f_1 (p SYS_REFCURSOR) RETURN setof my_outrec_typ parallel_enable (partition p by any);
    FUNCTION pkg_f_2 (p1 SYS_REFCURSOR, p2 SYS_REFCURSOR) RETURN setof my_outrec_typ parallel_enable (partition p1 by hash(a,b));
end my_pkg;
/
NOTICE:  immutable would be set if parallel_enable specified
NOTICE:  immutable would be set if parallel_enable specified
create or replace package body my_pkg as
    FUNCTION pkg_f_1 (p SYS_REFCURSOR) RETURN setof my_outrec_typ parallel_enable (partition p by any) IS
    out_rec my_outrec_typ := my_outrec_typ(NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
    LOOP
        FETCH p INTO out_rec.employee_id, out_rec.department_id, out_rec.first_name, out_rec.last_name, out_rec.email, out_rec.phone_number;  -- input row
        EXIT WHEN p%NOTFOUND;
        return next out_rec;
    END LOOP;
    RETURN;
END pkg_f_1;
    FUNCTION pkg_f_2 (p1 SYS_REFCURSOR, p2 SYS_REFCURSOR) RETURN setof my_outrec_typ parallel_enable (partition p1 by hash(a,b)) IS
    out_rec my_outrec_typ := my_outrec_typ(NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
    LOOP
        FETCH p1 INTO out_rec.employee_id, out_rec.department_id, out_rec.first_name, out_rec.last_name, out_rec.email, out_rec.phone_number;  -- input row
        EXIT WHEN p1%NOTFOUND;
        return next out_rec;
    END LOOP;
    RETURN;
END pkg_f_2;
end my_pkg;
/
NOTICE:  immutable would be set if parallel_enable specified
NOTICE:  immutable would be set if parallel_enable specified
NOTICE:  immutable would be set if parallel_enable specified
NOTICE:  immutable would be set if parallel_enable specified
\! @abs_bindir@/gs_dump dump_package_db -p @portstring@ -f @abs_bindir@/dump_package.tar -F t >/dev/null 2>&1; echo $?
0
\! @abs_bindir@/gs_restore -d restore_package_db -p @portstring@ @abs_bindir@/dump_package.tar >/dev/null 2>&1; echo $?
0
\c restore_package_db
call rowtype_pckg.rowtype_func();
 c1 | c2 
----+----
  1 | a
  2 | b
(2 rows)

\sf my_pkg.pkg_f_1
CREATE OR REPLACE FUNCTION public.my_pkg.pkg_f_1(p SYS_REFCURSOR)
 RETURN SETOF my_outrec_typ IMMUTABLE NOT FENCED NOT SHIPPABLE PARALLEL_ENABLE (PARTITION p BY ANY) PACKAGE
AS  DECLARE out_rec my_outrec_typ := my_outrec_typ(NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
    LOOP
        FETCH p INTO out_rec.employee_id, out_rec.department_id, out_rec.first_name, out_rec.last_name, out_rec.email, out_rec.phone_number;  -- input row
        EXIT WHEN p%NOTFOUND;
        return next out_rec;
    END LOOP;
    RETURN;
END ;
/
\sf my_pkg.pkg_f_2
CREATE OR REPLACE FUNCTION public.my_pkg.pkg_f_2(p1 SYS_REFCURSOR, p2 SYS_REFCURSOR)
 RETURN SETOF my_outrec_typ IMMUTABLE NOT FENCED NOT SHIPPABLE PARALLEL_ENABLE (PARTITION p1 BY HASH(a,b)) PACKAGE
AS  DECLARE out_rec my_outrec_typ := my_outrec_typ(NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
    LOOP
        FETCH p1 INTO out_rec.employee_id, out_rec.department_id, out_rec.first_name, out_rec.last_name, out_rec.email, out_rec.phone_number;  -- input row
        EXIT WHEN p1%NOTFOUND;
        return next out_rec;
    END LOOP;
    RETURN;
END ;
/
\c regression
drop database if exists restore_subpartition_db;
NOTICE:  database "restore_subpartition_db" does not exist, skipping
drop database if exists dump_subpartition_db;
NOTICE:  database "dump_subpartition_db" does not exist, skipping