Is there a better way to debug "wrong number or types of arguments in call" errors?

Question

So I've recently been vexed by problems similar to this one: Oracle .NET error - Wrong number or type of arguments. I know oracle is famous for its terrible error reporting, but this is a giant pain to debug -- if you have ten parameters, that's twenty things to check by hand (types and names,) not to mention the actual number of parameters, and if they're actually legitimate values.

Is there some way to get better diagnostics on stored procedure calls to oracle?

Answer 1

Overloading and DEFAULT values for missing parameters may help. You create a dummy procedure similar to the following :

procedure xxx (p_1 IN VARCHAR2 DEFAULT NULL, p_2 IN VARCHAR2 DEFAULT NULL....)
begin
  IF     p_1 IS NULL THEN RAISE_APPLICATION_ERROR(-20001,'P_1 is null);
  ELSIF  p_2 IS NULL THEN RAISE_APPLICATION_ERROR(-20001,'P_2 is null);
...
  END IF;
end xxx;

The default values means you can build up the .NET call bit by bit. Overloading means you can have one procedure that forces the correct datatypes (eg date) and a second that is lax as assumes everything is a VARCHAR2. If the call 'falls back' to the lax procedure (with implict conversions), you include some debugs to determine whether the correct values are being passed in.

An alternative route is using a SELECT on ALL_ARGUMENTS to create a stub of the call.