2 Replies Latest reply: Jul 24, 2014 10:39 PM by rp0428 RSS

    Nested Tables: PLS-00306: wrong number or types of arguments

    vpolasa

      Hi,

       

         I created a package to update salary for the list of empnos passed as parameter to the proc. Below is the package code:

       

      CREATE OR REPLACE PACKAGE emp_info

      AS

        type emp_list IS TABLE OF emp.empno%type;

        PROCEDURE add_sal(empnos emp_list);

      END emp_info;

      /

       

      CREATE OR REPLACE PACKAGE body emp_info

      AS

      PROCEDURE add_sal(empnos emp_list)

      AS

      BEGIN

        forall i IN 1 .. empnos.count

        UPDATE emp SET sal = sal + 100 WHERE empno = empnos(i);

      END add_sal;

      END emp_info;

      /

       

      When I try to call the procedure using below block it works fine:

      DECLARE

        empnos emp_info.emp_list := emp_info.emp_list(1111,1112);

      BEGIN

        emp_info.add_sal(empnos);

      END;

      /

       

      But, when I try to create the type same as the one I created in the Package and try to pass it to the procedure, the block fails with error:

      DECLARE

      type emp_list IS TABLE OF emp.empno%type;

        empnos emp_list;

      BEGIN

        empnos := emp_list(1111,1112);

        emp_info.add_sal(empnos);

      END;

      /

       

      Error report -

      ORA-06550: line 8, column 3:

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

       

      Can someone please help me understand why we are seeing this error?

       

      Using: Windows 8.1

      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

      PL/SQL Release 12.1.0.1.0 - Production

      "CORE 12.1.0.1.0 Production"

      TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

      NLSRTL Version 12.1.0.1.0 - Production

        • 1. Re: Nested Tables: PLS-00306: wrong number or types of arguments
          JustinCave

          You don't want to redefine the type in your anonymous block.  Use the type that you declared in your package

           

          DECLARE
            empnos emp_info.emp_list;
          BEGIN
            empnos := emp_info.emp_list(1111,1112);
            emp_info.add_sal(empnos);
          END;
          /
          

          What you have done here is created a new local type that happened to have the same name as a packaged type.  Your function was expecting a parameter of your package type, it actually got a parameter of your locally defined type, so you got the error.  It might be useful for PL/SQL to look to the definition of the two types to see that they are defined identically and allow you to substitute one for the other.  But that would also open a lot of doors for figuring out what constitutes "defined identically".

           

          Justin

          • 2. Re: Nested Tables: PLS-00306: wrong number or types of arguments
            rp0428

            But, when I try to create the type same as the one I created in the Package and try to pass it to the procedure, the block fails with error:

            No - that is NOT the same type. One is the package type and this new one is NOT the package type.

             

            The fact that they have the same projection or set of attributes is irrevelant.

             

            See the Oracle docs

            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#BEIEADAA

            You can assign values to all fields at once only if you assign a record to another record with the same data type. Having fields that match exactly is not enough, as shown in Example 5-48.

            Example 5-48 Assigning All the Fields of a Record in One Statement

            DECLARE
            -- Two identical type declarations.
               TYPE DeptRec1 IS RECORD
                 (dept_num  NUMBER(2), dept_name VARCHAR2(14));
               TYPE DeptRec2 IS RECORD
                (dept_num  NUMBER(2), dept_name VARCHAR2(14));
               dept1_info DeptRec1;
               dept2_info DeptRec2;
               dept3_info DeptRec2;
            BEGIN
            -- Not allowed; different data types,
            -- even though fields are the same.
            --      dept1_info := dept2_info;
            -- This assignment is OK because the records have the same type.
               dept2_info := dept3_info;
            END;
            /