Search code examples
sqldatabaseoracle-databaseplsqlprocedure

Oracle database not recognizing type when using procedure


I declared my types in the package header:

file_2.sql

CREATE OR REPLACE PACKAGE MY_UTILS
IS
TYPE VECTOR IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
TYPE MATRIX IS TABLE OF VECTOR INDEX BY PLS_INTEGER;
PROCEDURE PRINT_MATRIX(p_matrix IN MATRIX);
END MY_UTILS;

My print matrix procedure

PROCEDURE PRINT_MATRIX(p_matrix IN MATRIX)  IS
BEGIN
DBMS_OUTPUT.PUT_LINE('printing matrix..');
END PRINT_MATRIX;

The PRINT_MATRIX procedure does a simple DBMS_OUTPUT.PUT_LINE to print the matrix; When I try to use my MY_UTILS.PRINT_MATRIX(v_matrix) I get the error:

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

In my other .sql file I also declare the MATRIX type the same as in the package.

file_1.sql

set serveroutput ON;
DECLARE
TYPE VECTOR IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
TYPE MATRIX IS TABLE OF VECTOR INDEX BY PLS_INTEGER;
v_matrix MATRIX;
BEGIN
--populate matrix, printing it works with 2 FOR LOOPS
MY_UTILS.PRINT_MATRIX(v_matrix); -- error
END;

Solution

  • Oracle sees your variable as a different type to the formal argument. To you they look identical, buy to Oracle they are two different types.

    From the documentation:

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

    So you have to change your variable declaration to use the type from the package:

    DECLARE
      v_matrix MY_UTILS.MATRIX;
    BEGIN
      --populate matrix, printing it works with 2 FOR LOOPS
      MY_UTILS.PRINT_MATRIX(v_matrix); -- error
    END;