By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table sbceybudget_financial_year(
fin_yr int,
SCHOOL_YEAR_WEEKS int
)
create or replace package pkg_spFIN as
type spFIN_RowType is record(
FIN_YR sbceybudget_financial_year.FIN_YR%type,
SCHOOL_YEAR_WEEKS sbceybudget_financial_year.SCHOOL_YEAR_WEEKS%type
);
type spFIN_CurType IS REF CURSOR RETURN spFIN_RowType;
type spFIN_tab is table of spFIN_RowType;
procedure spFINTEST (s1 OUT SYS_REFCURSOR);
procedure spFinTestInsert;
end pkg_spFIN;
/
create or replace package body pkg_spFIN as
function get_cursor(n int) return spFIN_CurType is
c spFIN_CurType;
begin
open c for
SELECT
t.FIN_YR + 1 AS FIN_YR,
t.SCHOOL_YEAR_WEEKS
FROM
sbceybudget_financial_year t
WHERE
t.fin_yr = n;
return c;
end;
procedure spFINTEST (s1 OUT SYS_REFCURSOR)
is
begin
s1:=get_cursor(2021);
end spFINTEST;
procedure spFinTestInsert
is
cur spFIN_CurType;
tab spFIN_tab;
begin
pkg_spFIN.spFINTEST(cur);
loop
fetch cur bulk collect into tab limit 100;
exit when tab.count()=0;
for i in 1..tab.count loop
dbms_output.put_line(tab(i).FIN_YR);
dbms_output.put_line(tab(i).SCHOOL_YEAR_WEEKS);
-- or insert:
-- insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS)
begin
insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2019,19);
insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2020,20);
insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2021,21);
commit;
end;
/
1 rows affected
begin
pkg_spFIN.spFinTestInsert();
end;
/
1 rows affected