PLS-00306: wrong number or types of arguments in call to ‘my_procedure’

Programming

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

Contents

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,

string s;
CallMyProc(s);

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.

Back to Top