Skip to main content

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

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel