ORA-06550 when using stored procedure

[John Colaizzi]

I'm getting this error message when I run the code snipped below:
{"ORA-06550: line 1, column 18:\nPLS-00306: wrong number or types of
arguments in call to 'TEST'\nORA-06550: line 1, column 7:\nPL/SQL: Statement
ignored\n" }

The stored procedure contains a function:
FUNCTION test
( pPeriod varchar2,
pNumPeriods NUMBER
) RETURN NUMBER:

Here's the C# code:
sQuery = "PRODUCTION.TEST";
cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sQuery;
oPeriod = new OracleParameter("pPeriod",OracleType.VarChar,10);
oPeriod.Value = "20050531";
oNumPeriod = new OracleParameter("pNumPeriods",OracleType.Number );
oNumPeriod.Value = 5;
oRetVal = new OracleParameter("RetVal", OracleType.Number);
oRetVal.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(oPeriod);
cmd.Parameters.Add(oNumPeriod);
cmd.Parameters.Add(oRetVal);
cmd.ExecuteScalar();

I know it's the pNumPeriods that's causing the problem because if I change
it to varchar2 in the function and VarChar in the C# code it works. I've
tried every OracleType that even remotely looks like a numeric type. Any
suggestions?

Thanks
John

 

[Frans Bouma [C# MVP]]

I'm getting this error message when I run the code snipped below:
{"ORA-06550: line 1, column 18:\nPLS-00306: wrong number or types of
arguments in call to 'TEST'\nORA-06550: line 1, column 7:\nPL/SQL:
Statement ignored\n" }

The stored procedure contains a function:
FUNCTION test
( pPeriod varchar2,
pNumPeriods NUMBER
) RETURN NUMBER:

Here's the C# code:
sQuery = "PRODUCTION.TEST";
cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sQuery;
oPeriod = new OracleParameter("pPeriod",OracleType.VarChar,10);
oPeriod.Value = "20050531";
oNumPeriod = new OracleParameter("pNumPeriods",OracleType.Number );
oNumPeriod.Value = 5;
oRetVal = new OracleParameter("RetVal", OracleType.Number);
oRetVal.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(oPeriod);
cmd.Parameters.Add(oNumPeriod);
cmd.Parameters.Add(oRetVal);
cmd.ExecuteScalar();

I know it's the pNumPeriods that's causing the problem because if I
change it to varchar2 in the function and VarChar in the C# code it
works. I've tried every OracleType that even remotely looks like a
numeric type. Any suggestions?

as you can't define a precision in the number definition of the PL/SQL
function, it defaults to NUMBER(38,0). Number(38,0) is a decimal, could
you try passing a decimal value (5M instead of 5) to the parameter?

Also, what does 'the procedure contains a function' mean exactly? Your
call looks like the function is in a package ('PRODUCTION'), is there
something wrong with the package setup perhaps?

FB

--