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!

Comments

  1. One nitpick, Steven:

    For your counterexample with DBMS_SQL.number_table you state that it is because it is a built-in datatype that this works in both schemas. Well, I'd say it is because it is a PL/SQL collection type defined in a package that both schemas have permissions to use, rather than a SQL collection type defined per schema.

    You could get the same effect as DBMS_SQL.number_table yourself. It is not just built-in datatypes that can be used to help for this.

    If schema1 created a package, defined number_nt as a PL/SQL collection type in that package, and granted permissions to schema2 (and maybe even made a public synonym), schema1 and schema2 could use the same datatype.

    Or in some cases maybe even better, have a "metadata/dictionary/utility" schema, that creates such a package with public grants and synonym. For "generic" datatypes like "table of number" that might be preferable, while application specific datatypes probably should be in API package in application schema.

    Just a couple cents from me ;-)

    ReplyDelete
    Replies
    1. I'm glad I wasn't the only one thinking.

      I do however think that Steven was documenting a more realistic use case and what the symptoms are. Most people don't have the foresight, access, or time to have a schema for all their custom data types.

      Delete
  2. [Sound of hand slapping on forehead, mouth emitting" D'oh" sound]

    Right you are, Kim. It's not an issue for DBMS_SQL types because we are all using the same package, in the same instance.

    I will update the post with a more accurate AND detailed set of examples on this.

    ReplyDelete
  3. Can you add this to your 'PL/SQL Improvements' list?

    ReplyDelete
  4. @kevan, what exactly would you want to be added?

    ReplyDelete
  5. Hi Steven,
    does your rule (in blue) also apply for user defined types in the same schema (having same name and structure - but defined twices - they are of different "types")?
    Thanks

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts