Collection Types Defined in Package Specifications

A collection type defined in a package specification is incompatible with an identically defined local or standalone collection type.

The package specification and the anonymous block define the collection type NumList identically. The package defines a procedure, print_numlist, which has a NumList parameter. The anonymous block declares the variable n1 of the type pkg.NumList (defined in the package) and the variable n2 of the type NumList (defined in the block). The anonymous block can pass n1 toprint_numlist, but it cannot pass n2 to print_numlist.

Example 1 Identically Defined Package and Local Collection Types

CREATE OR REPLACE PACKAGE pkg AS

  TYPE NumList IS TABLE OF NUMBER;

  PROCEDURE print_numlist (nums NumList);

END pkg;

/

CREATE OR REPLACE PACKAGE BODY pkg AS

  PROCEDURE print_numlist (nums NumList) IS

  BEGIN

    FOR i IN nums.FIRST..nums.LAST LOOP

      DBMS_OUTPUT.PUT_LINE(nums(i));

    END LOOP;

  END;

END pkg;

/

DECLARE

  TYPE NumList IS TABLE OF NUMBER;  -- local type identical to package type

  n1 pkg.NumList := pkg.NumList(2,4);  -- package type

  n2     NumList :=     NumList(6,8);  -- local type

BEGIN

  pkg.print_numlist(n1);  -- succeeds

  pkg.print_numlist(n2);  -- fails

END;

/

Result:

  pkg.print_numlist(n2);  -- fails

  *

ERROR at line 7:

ORA-06550: line 7, column 3:

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

ORA-06550: line 7, column 3:

PL/SQL: Statement ignored

Defines a standalone collection type NumList that is identical to the collection type NumList defined in the package specification above. The anonymous block declares the variable n1 of the type pkg.NumList (defined in the package) and the variable n2 of the standalone type NumList. The anonymous block can pass n1 to print_numlist, but it cannot pass n2 toprint_numlist.

Example 2 Identically Defined Package and Standalone Collection Types

CREATE OR REPLACE TYPE NumList IS TABLE OF NUMBER;

  -- standalone collection type identical to package type

/

DECLARE

  n1 pkg.NumList := pkg.NumList(2,4);  -- package type

  n2     NumList :=     NumList(6,8);  -- standalone type

BEGIN

  pkg.print_numlist(n1);  -- succeeds

  pkg.print_numlist(n2);  -- fails

END;

/

Result:

  pkg.print_numlist(n2);  -- fails

  *

ERROR at line 7:

ORA-06550: line 7, column 3:

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

ORA-06550: line 7, column 3:

PL/SQL: Statement ignored

I hope you all have enjoyed reading this article. Comments are welcome....