oracle-databaseplsqlplsqldeveloperplsql-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":

    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:

    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