Dealing with "PLS-306: Wrong number or types of arguments" across schemas

The PLS-306 compile error can be a real pain in the you know what. There are many possible causes, including typo in name with named notation, wrong datatype, wrong number of arguments....you know: just like the error message says. :-)

But one of the most puzzling situations occurs when you need to execute a subprogram that is defined in another schema of your instance, or in another instance entirely, and the type of at least one parameter in the subprogram is "user-defined" (not a built-in datatype).

So let's first state the rule that should guide you in this scenario, and then I will offer up  some code so that you can verify it for yourself.

The rule:

Even if a user-defined type in one schema has the same name and same structure as a type in another schema, they are different types.

And now some code to drive the point home. Assuming you have two schemas defined, schema1 and schema2:

CONNECT schema1/schema1

CREATE OR REPLACE TYPE number_nt IS TABLE OF NUMBER

/

CREATE OR REPLACE PROCEDURE show_nt_count (n IN number_nt)

IS

BEGIN

   DBMS_OUTPUT.put_line (n.COUNT);

END;

/

GRANT EXECUTE ON show_nt_count TO schema2

/

GRANT EXECUTE ON number_nt TO schema2

/

And now I can successfully invoke show_nt_count from schema2 as follows:

CONNECT schema2/schema2

DECLARE

   n   schema1.number_nt := schema1.number_nt (1);

BEGIN

   schema1.show_nt_count (n);

END;

/

But that's because I am defining a local variable based on the schema1 type.

If I try to use the "same" type that is defined in schema2, I get the PLS-00306 eorr:

CREATE OR REPLACE TYPE number_nt IS TABLE OF NUMBER

/

DECLARE

   n   number_nt := number_nt (1);

BEGIN

   schema1.show_nt_count (n);

END;

/

ORA-06550: line 4, column 1:

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

ORA-06550: line 4, column 1:

PL/SQL: Statement ignored

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

This problem does not always occur with datatypes defined in built-in packages. For example, DBMS_SQL comes with several pre-defined collection types. If I use one of those with my two schemas, I will not get the PLS-00306 error:

CONNECT schema1/schema1

CREATE OR REPLACE PROCEDURE show_nt_count (

   n IN DBMS_SQL.number_table)

IS

BEGIN

   DBMS_OUTPUT.put_line (n.COUNT);

END;

/

GRANT EXECUTE ON show_nt_count TO schema2

/

CONNECT schema2/schema2

DECLARE

   n   DBMS_SQL.number_table;

BEGIN

   n(1) := 1;

   schema1.show_nt_count (n);

END;

/

Ah, but why don't I get the error? As Kim Berg Hansen points out in the comments, it is not because there is any secret and mysterious action taken by the PL/SQL compiler ("Built-in packages are special."). Instead, it is because these are two schemas in the same instance, and each instance of the Oracle RDBMS has a single copy of DBMS_SQL defined in SYS.

In other words, both schema1 and schema2 are referencing the same type in the same package. So, of course, the compiler has nothing to complain about.

If, however, you try to do the same thing via a database link, the story changes (even if the database link ends up pointing to the same instance) . If I execute that last block, with the only change being to execute the schema1.show_nt_count as a remote stored procedure:

DECLARE
   n   DBMS_SQL.number_table;
BEGIN
   n(1) := 1;
   schema1.show_nt_count@my_db_link (n);
END;
/

Then I will get a PLS-00306 error, since the DBMS_SQL package referenced in my block can no longer be resolved by the compiler to be the same DBMS_SQL package in the now-remote schema1.

To get rid of this problem, I can force the resolution of DBMS_SQL in the above block to be the same as that used by the remote procedure call:

DECLARE
   n   DBMS_SQL.number_table@my_db_link;
BEGIN
   n(1) := 1;
   schema1.show_nt_count@my_db_link (n);
END;
/

And then no more PLS-00306 error!