Pls 00306 ошибочно число или типы аргументов при обращении к

I know I’ve asked this online before, but is Tourette Syndrome an occupational hazard of being a programmer?  I swear, it’s things like this that are going to cause me to develop it.

I’ve just been through one of the more frustrating exercises, debugging database calls that look just fine.

Problem

When calling Oracle PL/SQL stored procedures from C#, it appears many people get PLS-00306 responses with the message “wrong number or types of arguments  in call to ‘my_procedure’” (with the obvious substitution on procedure name).  I recently spent a few days trying to track down why this was happening in my program… intermittently.  Some reports worked, some didn’t.  I finally tracked the cause of the problem down, and it’s a subtle one unless you know about it already.

Oracle treats null strings and empty strings more or less interchangeably in most contexts.  You can’t compare the empty string to itself (” does not equal ”, but is null — in many RDBMS, the opposite is true, the empty string and the null string are distinctly different).  This is itself a gotcha that people usually learn the hard way.

The way this one bit me was pretty subtle, in that it’s really not obvious looking at the code how it fails.  I compared the parameter binding carefully, and all arguments were present with the correct types.

Let’s use a really simple example:

CREATE TABLE t (
  s  VARCHAR2(10)
);
 
CREATE OR REPLACE PROCEDURE myproc(
  in_s IN VARCHAR2
)
IS
BEGIN
  INSERT INTO t (s) VALUES (in_s);
END;

Nice and simple — a table with a single varchar2 field, and a procedure that inserts a new value into the table. Believe it or not, it’s really easy for this to break when called from C#.

Pretty much all documentation I’ve seen for calling Oracle stored procedures from C# involve setting up the connection (not shown here), creating an OracleCommand, binding the parameters, and executing the command.  Straightforward, but there’s a nonobvious gotcha in how it actually works.

A typical example might look something like this:

OracleConnection conn = new OracleConnection(connectString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "myproc";
OracleParameter op = new OracleParameter("in_s", OracleType.VarChar);
op.Value = "my value";
op.Direction = ParameterDirection.Input;
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
cmd.Dispose();

This can be expected to work.  It would be really tedious to do every time (and more importantly, every place) you want to call myproc(), so it is likely to get wrapped in a method:

void CallMyProc(string s)
{
  OracleConnection conn = new OracleConnection(connectString);
  conn.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = conn;
  cmd.CommandText = "myproc";
  OracleParameter op = new OracleParameter("in_s", OracleType.VarChar);
  op.Value = s;
  op.Direction = ParameterDirection.Input;
  cmd.Parameters.Add(op);
  cmd.ExecuteNonQuery();
  cmd.Dispose();
}

This is easy to call and works… as long as ‘s’ is not null. That is,

string s = "foo";
CallMyProc(s);

is just fine. No problem. For that matter,

string s = "";
CallMyProc(s);

also works.

However, and this is what has been chewing on me,

does not work, producing said ‘PLS-00306’ error. Compare the stored procedure to the C# invocation — the stored procedure takes a single argument, a varchar2 (‘string’), the single C# parameter is defined as expecting a varchar and something declared as string is bound. This should work, right?

Wrong. By the time the driver sees it, a not-empty-but-null string is not a string any more. Regardless of what C# says.

Solution

I’ll skip ahead to the solution, then come back for why this is.

void CallMyProc(string s)
{
  OracleConnection conn = new OracleConnection(connectString);
  conn.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = conn;
  cmd.CommandText = "myproc";
  OracleParameter op = new OracleParameter("in_s", OracleType.VarChar);
  op.Value = String.IsNullOrEmpty(s) ? "" : s;
  op.Direction = ParameterDirection.Input;
  cmd.Parameters.Add(op);
  cmd.ExecuteNonQuery();
  cmd.Dispose();
}

It’s a simple change on line 9 and appears to have resolved the problem.

Why This Is

Figuring this out required a little bit of knowledge about how parameter binding and the execute immediate instruction work in Oracle.

Execute immediate with a bound parameter can look something like this:

EXECUTE IMMEDIATE 'begin myproc(:1); end;' using s;

This is just fine. However,

EXECUTE IMMEDIATE 'begin myproc(:1); end;' using NULL;

fails because null is typeless. Oracle cannot determine what type ‘:1’ is supposed to be and chokes (PLS-00457).

I’m not entirely sure how PLS-00457 becomes PLS-00306 here, but it seems related. When invoking an Oracle command from C#, it appears the runtime library is doing something close to

string dbcommand = "execute immediate 'begin " + command + "(" +
    /* build parameter list */ + "); end;' using " +
    /* build argument list */ + ";";

When one of the parameters has a null string value, this fails with a PLS-00306 error message. Using an empty string value instead (non-null C# string — has an address but no content) works just fine.

So, null and empty string are treated as the same thing (but not equal, oh no). Except when they aren’t.

How about that Tourette Syndrome? I’m sure I’m coming down with it.

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:

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

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:

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: