CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqlprocedure
Alexandru Antochi
Alexandru Antochi

Reputation: 1465

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;

Upvotes: 4

Views: 342

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

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;

Upvotes: 4

Related Questions