Search code examples
oracleplsqlplsqldeveloperplsql-package

Creating a PL/SQL Package containing a function which returns a table not working

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: enter image description here

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.

Solution

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

    • An 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).
    • When you declare a function in a package, the function signature in the package specification MUST be identical to the function signature in the package body.
    • You do not need to use a CURSOR and can just SELECT ... BULK COLLECT INTO ....
    • If you were going to use a PL/SQL type (which isn't appropriate here) and you want it to be available outside the package then declare it in the package specification so it is available publicly (rather than in the package body when it is private to the package).

    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

    fiddle