Bdtjtk

I am calling a function with an associative array as parameter in a package. When this associative array is defined using table rowtype, the package compiles properly, whereas, if I use user created record to declare associative array, then the package compiles with error PLS 00306.

Working block of associative array declaration:

CREATE OR REPLACE PACKAGE IST.ACCOUNT_UNBILLED_USAGE AS

type row_tab is table of Account_Unbilled_Usage_GTT%rowtype index by binary_integer;



PROCEDURE queryUnbilledSummaryTotals (

e2eData                 IN OUT        VARCHAR2,

                                    p_Account_num             IN account.account_num%type,

                                    p_Account_Summary_Boo     IN VARCHAR2,

                                    p_Product_Summary_Boo     IN VARCHAR2,

                                    p_Event_Source            IN VARCHAR2,

                                    rws                       out nocopy row_tab

                                     );



END;

/

Issue causing associative array declaration:

/* Formatted on 28/11/2018 14:39:24 (QP5 v5.267.14150.38573) */



CREATE OR REPLACE PACKAGE IST.ACCOUNT_UNBILLED_USAGE

AS

   TYPE accountUnbilledUsageRecTyp IS RECORD

   (

      COL1       VARCHAR2 (50 BYTE),

      COL2       VARCHAR2 (50 BYTE),

      COL3       VARCHAR2 (50 BYTE),

      COL4       VARCHAR2 (50 BYTE),

      COL5       VARCHAR2 (50 BYTE),

      COL6       VARCHAR2 (50 BYTE),

      COL7       VARCHAR2 (50 BYTE),

      COL8       VARCHAR2 (50 BYTE),

      COL9       VARCHAR2 (50 BYTE),

      COL10      VARCHAR2 (50 BYTE),

      COL11      VARCHAR2 (50 BYTE),

      COL12      VARCHAR2 (50 BYTE),

      COL13      VARCHAR2 (50 BYTE),

      COL14      VARCHAR2 (50 BYTE),

      COL15      VARCHAR2 (50 BYTE),

      COL16      VARCHAR2 (50 BYTE),

      COL17      VARCHAR2 (50 BYTE),

      COL18      VARCHAR2 (50 BYTE),

      COL19      VARCHAR2 (50 BYTE),

      COL20      VARCHAR2 (50 BYTE),

      COL21      VARCHAR2 (50 BYTE),

      COL22      VARCHAR2 (50 BYTE),

      COL23      VARCHAR2 (50 BYTE),

      COL24      VARCHAR2 (50 BYTE),

      COL25      VARCHAR2 (50 BYTE),

      COL26      VARCHAR2 (50 BYTE),

      COL27      VARCHAR2 (50 BYTE),

      COL28      VARCHAR2 (50 BYTE),

      COL29      VARCHAR2 (50 BYTE),

      COL30      VARCHAR2 (50 BYTE),

      COL31      VARCHAR2 (50 BYTE),

      COL32      VARCHAR2 (50 BYTE),

      COL33      VARCHAR2 (50 BYTE),

      ERROR_NO   NUMBER (3),

      COL34      VARCHAR2 (50 BYTE)

   );



   TYPE row_tab IS TABLE OF accountUnbilledUsageRecTyp

      INDEX BY PLS_INTEGER;





   PROCEDURE queryUnbilledSummaryTotals (

      e2eData                 IN OUT        VARCHAR2,

      p_Account_num           IN            account.account_num%TYPE,

      p_Account_Summary_Boo   IN            VARCHAR2,

      p_Product_Summary_Boo   IN            VARCHAR2,

      p_Event_Source          IN            VARCHAR2,

      rws                        OUT NOCOPY row_tab);



END;

/

Other package which uses this associative array:

FUNCTION PL_TO_SQL0(aPlsqlItem IST.ACCOUNT_UNBILLED_USAGE.ROW_TAB)

 RETURN ACCOUNT_UNBILLED_X528946X1X6 IS 

aSqlItem ACCOUNT_UNBILLED_X528946X1X6; 

BEGIN 

    -- initialize the table 

    aSqlItem := ACCOUNT_UNBILLED_X528946X1X6();

    IF aPlsqlItem IS NOT NULL THEN

    aSqlItem.EXTEND(aPlsqlItem.COUNT);

    IF aPlsqlItem.COUNT>0 THEN

    FOR I IN aPlsqlItem.FIRST..aPlsqlItem.LAST LOOP

        aSqlItem(I + 1 - aPlsqlItem.FIRST) := PL_TO_SQL1(aPlsqlItem(I));

    END LOOP; 

    END IF; 

    END IF; 

    RETURN aSqlItem;

END PL_TO_SQL0;

ACCOUNT_UNBILLED_X528946X1X6 - this is a nested table of same structure.

For below line , I am getting the error:

PLS-00306: wrong number or types of arguments in call to 'PL_TO_SQL1'

aSqlItem(I + 1 - aPlsqlItem.FIRST) := PL_TO_SQL1(aPlsqlItem(I));

Could anyone help me out to resolve this problem?

Just remove %TYPE in second declaration and everything works, like in this example:

create table test_tbl(col1, error_no) as (select 'PQR', 7 from dual);



declare

  type rec is record(col1 varchar2(50), error_no number(3));

  type row_tab is table of rec index by binary_integer;  -- <-- here don't use rec%type

  v_rws row_tab;  



  procedure p1(rws out row_tab) is 

  begin 

    select col1, error_no bulk collect into rws from test_tbl;

  end p1;

begin

  p1(v_rws);

  for i in 1..v_rws.count loop

    dbms_output.put_line(v_rws(i).col1);

  end loop;

end;