I am creating PL/SQL package for the first time. It looks simple but I am not able to wrap my function which returns a table in a package.
My error screenshot is given below:
Below is my package specification:
CREATE OR REPLACE PACKAGE P_trdelclvr01 AS
FUNCTION fn_trdelclvr_QADJ return table_trdelclvr_QADJ;
END P_trdelclvr01;
/
Below is the package body:
CREATE OR REPLACE PACKAGE BODY P_trdelclvr01 AS
type type_trdelclvr_QADJ as object(
TOTAL_CLAIMS INTEGER,
calculated_year INTEGER,
MTH varchar2(500),
CLAIM INTEGER,
CIW INTEGER,
PAPER INTEGER,
MBRECLAIM INTEGER,
PRVRDIALUP INTEGER,
PRVRIP INTEGER,
ELECTRONIC INTEGER
);
type table_trdelclvr_QADJ is table of type_trdelclvr_QADJ;
function fn_trdelclvr_QADJ
(P_CLAIM varchar2,
P_CIW varchar2,
P_PP varchar2,
P_PAPER varchar2,
P_MBR varchar2,
P_EDI varchar2,
P_MBR_NatApp varchar2,
P_CDAnet_DialUp varchar2,
P_CDAnet_IP varchar2,
P_CDAnet_ICA varchar2,
P_START_DATE date,
P_END_DATE date,
P_DENTAL varchar2,
P_IVR varchar2
);
--select * from table(fn_trdelclvr_QADJ(1,2,3,1,2,2,6,3,4,7,to_date('01/01/1990','mm/dd/yyyy'),to_date('12/12/2024','mm/dd/yyyy'),80,3));
return table_trdelclvr_QADJ
as
CURSOR CURSEUR_ETAPE
IS
select type_trdelclvr_QADJ(tbl.total_claims, tbl.calculated_year, tbl.mth, tbl.claim, tbl.ciw, tbl.paper, tbl.MbrEclaim, tbl.PrvrDialUp, tbl.PrvrIP, tbl.Electronic)
from
(
select
count(1) total_claims,
to_char(system_date,'YYYY') calculated_year,
to_char(system_date,'YYYY-MM') mth,
sum(decode(document_category, P_CLAIM ,1,0)) claim,
sum(decode(document_category, P_CIW ,1, P_PP ,1,0)) ciw,
sum(decode(document_source,P_PAPER,1,0)) paper,
sum(decode(document_source, P_MBR,decode(edi_app_source,P_MBR,1,0),0)
+ decode(document_source, P_EDI, decode(edi_app_source, P_MBR_NatApp, 1,0),0)) MbrEclaim,
sum(decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_DialUp, 1,0),0)) PrvrDialUp,
sum(decode(document_source, P_EDI,decode(edi_app_source, P_CDAnet_IP, 1,0),0)
+ decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_ICA, 1,0),0)) PrvrIP,
sum(decode(document_source, P_EDI, 1,0)) Electronic
from blu_adj_statistics adj
where system_date >= P_START_DATE
and system_date < P_END_DATE + 1
and claim_type = P_DENTAL
and claim_iteration = 1
and document_source <> P_IVR
and ( hsa_indicator is null )
group by to_char ( system_date , 'YYYY' ) ,
to_char ( system_date , 'YYYY-MM' )
ORDER BY 2 DESC,3 DESC
) tbl;
test_type table_trdelclvr_QADJ:=table_trdelclvr_QADJ();
BEGIN
OPEN CURSEUR_ETAPE;
LOOP
FETCH CURSEUR_ETAPE
BULK COLLECT INTO test_type;
EXIT WHEN CURSEUR_ETAPE%NOTFOUND;
END LOOP;
CLOSE CURSEUR_ETAPE;
RETURN test_type;
END;
END P_trdelclvr01;
/
Thanks in advance.
The Oracle database contains two different "engines":
The SQL engine processes SQL statements (i.e. SELECT
, INSERT
, UPDATE
, CREATE TABLE
, etc.) and can use types declared in the SQL scope (i.e. with CREATE TYPE
) statements.
The PL/SQL engine processes PL/SQL statements and can use types declared in both the PL/SQL and SQL scopes (i.e. declared locally in a PL/SQL block, declared globally in a PL/SQL package, or in the SQL scope with a CREATE TYPE
statement).
If you want to use a type in an SQL statement then declare it in the SQL scope (and not in a PL/SQL scope).
Additionally:
OBJECT
type is an SQL type and MUST be declared in the SQL scope. (If you want a semi-equivalent PL/SQL type then use a RECORD
).CURSOR
and can just SELECT ... BULK COLLECT INTO ...
.Like this:
CREATE TYPE type_trdelclvr_QADJ as object(
TOTAL_CLAIMS INTEGER,
calculated_year INTEGER,
MTH varchar2(500),
CLAIM INTEGER,
CIW INTEGER,
PAPER INTEGER,
MBRECLAIM INTEGER,
PRVRDIALUP INTEGER,
PRVRIP INTEGER,
ELECTRONIC INTEGER
);
CREATE TYPE table_trdelclvr_QADJ is table of type_trdelclvr_QADJ;
Then you can declare the package signature:
CREATE OR REPLACE PACKAGE P_trdelclvr01 AS
FUNCTION fn_trdelclvr_QADJ(
P_CLAIM varchar2,
P_CIW varchar2,
P_PP varchar2,
P_PAPER varchar2,
P_MBR varchar2,
P_EDI varchar2,
P_MBR_NatApp varchar2,
P_CDAnet_DialUp varchar2,
P_CDAnet_IP varchar2,
P_CDAnet_ICA varchar2,
P_START_DATE date,
P_END_DATE date,
P_DENTAL varchar2,
P_IVR varchar2
) return table_trdelclvr_QADJ;
END P_trdelclvr01;
/
and the package body:
CREATE OR REPLACE PACKAGE BODY P_trdelclvr01 AS
FUNCTION fn_trdelclvr_QADJ(
P_CLAIM varchar2,
P_CIW varchar2,
P_PP varchar2,
P_PAPER varchar2,
P_MBR varchar2,
P_EDI varchar2,
P_MBR_NatApp varchar2,
P_CDAnet_DialUp varchar2,
P_CDAnet_IP varchar2,
P_CDAnet_ICA varchar2,
P_START_DATE date,
P_END_DATE date,
P_DENTAL varchar2,
P_IVR varchar2
) RETURN table_trdelclvr_QADJ
AS
test_type table_trdelclvr_QADJ;
BEGIN
SELECT type_trdelclvr_QADJ(
count(1),
TO_CHAR(TRUNC(system_date, 'MM'),'YYYY'),
TO_CHAR(TRUNC(system_date, 'MM'),'YYYY-MM'),
sum(decode(document_category, P_CLAIM ,1,0)),
sum(decode(document_category, P_CIW ,1, P_PP ,1,0)),
sum(decode(document_source,P_PAPER,1,0)),
sum(decode(document_source, P_MBR,decode(edi_app_source,P_MBR,1,0),0)
+ decode(document_source, P_EDI, decode(edi_app_source, P_MBR_NatApp, 1,0),0)),
sum(decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_DialUp, 1,0),0)),
sum(decode(document_source, P_EDI,decode(edi_app_source, P_CDAnet_IP, 1,0),0)
+ decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_ICA, 1,0),0)),
sum(decode(document_source, P_EDI, 1,0))
)
BULK COLLECT INTO test_type
FROM blu_adj_statistics adj
WHERE system_date >= P_START_DATE
AND system_date < P_END_DATE + 1
AND claim_type = P_DENTAL
AND claim_iteration = 1
AND document_source <> P_IVR
AND hsa_indicator is null
GROUP BY TRUNC(system_date, 'MM')
ORDER BY TRUNC(system_date, 'MM');
RETURN test_type;
END;
END P_trdelclvr01;
/
Given the sample data:
CREATE TABLE blu_adj_statistics (
system_date DATE,
document_category VARCHAR2(50),
document_source VARCHAR2(50),
claim_type VARCHAR2(50),
claim_iteration NUMBER,
hsa_indicator NUMBER,
edi_app_source VARCHAR2(50)
)
INSERT INTO blu_adj_statistics VALUES (SYSDATE, 'A', 'B', 'C', 1, NULL, 'D');
Then:
DECLARE
v_data table_trdelclvr_QADJ;
BEGIN
v_data := P_trdelclvr01.fn_trdelclvr_QADJ(
P_CLAIM => 'A',
P_CIW => 'A',
P_PP => 'X',
P_PAPER => 'B',
P_MBR => 'B',
P_EDI => 'D',
P_MBR_NatApp => 'D',
P_CDAnet_DialUp => 'D',
P_CDAnet_IP => 'D',
P_CDAnet_ICA => 'D',
P_START_DATE => TRUNC(SYSDATE),
P_END_DATE => TRUNC(SYSDATE),
P_DENTAL => 'C',
P_IVR => 'A'
);
FOR i IN 1 .. v_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
v_data(i).TOTAL_CLAIMS
||', '||v_data(i).calculated_year
||', '||v_data(i).MTH
||', '||v_data(i).CLAIM
||', '||v_data(i).CIW
||', '||v_data(i).PAPER
||', '||v_data(i).MBRECLAIM
||', '||v_data(i).PRVRDIALUP
||', '||v_data(i).PRVRIP
||', '||v_data(i).ELECTRONIC
);
END LOOP;
END;
/
Outputs:
1, 2024, 2024-08, 1, 1, 1, 0, 0, 0, 0