rema10
asked on
Error when passing arguments from ASP.NET to Oracle stored procedure.
Hello,
I am getting an oracle error when I run my oracle stored procedure from ASP.NET.
But, I do not get this error when I run it from sqlplus and the results come out correctly.
The error message is: 'wrong number or types of arguments in call to 'test_procedure'
ORA-06550: line 1, col 7
PLS-00306: wrong number or types of args in call to 'test_procedure':
Background Info:
** I also know that the error is in the argument 'senditem'.
** I tried to set it to 'varchar' in the oracle stored procedure - but the error still keeps coming
** Initially I got the 'no size set for string' for the 'senditem' argument and I fixed that by
specifying the size of the string when sending the parameters.
My 'test_procedure' in my oracle package is as follows:
PROCEDURE test_procedure(compnr IN long, pdno IN long, sitm IN string, senditem OUT string ) IS
tblname varchar2(30):='atable';
qrystr varchar2(2000):='';
BEGIN
tblname:=tblname || to_char(compnr);
qrystr := 'select t$sitm from ' || tblname ||
' where t$pdno = :pdno_v and t$sitm =:sitm_v ' ;
EXECUTE IMMEDIATE qrystr INTO senditem USING pdno, sitm;
I am passing the parameters when calling the above procedure from asp.net as follows:
comp.Direction = ParameterDirection.Input;
pdno.Direction = ParameterDirection.Input;
sitm.Direction = ParameterDirection.Input;
senditem.Direction = ParameterDirection.Output;
oracmd.Parameters.Add(comp ,OracleTyp e.Int32,0) ;
oracmd.Parameters.Add(pdno ,OracleTyp e.Int32,0) ;
oracmd.Parameters.Add(sitm ,OracleTyp e.VarChar, 24);
oracmd.Parameters.Add(send item,Oracl eType.VarC har,24);
Please let me know how I can resolve this issue. Thanks.
I am getting an oracle error when I run my oracle stored procedure from ASP.NET.
But, I do not get this error when I run it from sqlplus and the results come out correctly.
The error message is: 'wrong number or types of arguments in call to 'test_procedure'
ORA-06550: line 1, col 7
PLS-00306: wrong number or types of args in call to 'test_procedure':
Background Info:
** I also know that the error is in the argument 'senditem'.
** I tried to set it to 'varchar' in the oracle stored procedure - but the error still keeps coming
** Initially I got the 'no size set for string' for the 'senditem' argument and I fixed that by
specifying the size of the string when sending the parameters.
My 'test_procedure' in my oracle package is as follows:
PROCEDURE test_procedure(compnr IN long, pdno IN long, sitm IN string, senditem OUT string ) IS
tblname varchar2(30):='atable';
qrystr varchar2(2000):='';
BEGIN
tblname:=tblname || to_char(compnr);
qrystr := 'select t$sitm from ' || tblname ||
' where t$pdno = :pdno_v and t$sitm =:sitm_v ' ;
EXECUTE IMMEDIATE qrystr INTO senditem USING pdno, sitm;
I am passing the parameters when calling the above procedure from asp.net as follows:
comp.Direction = ParameterDirection.Input;
pdno.Direction = ParameterDirection.Input;
sitm.Direction = ParameterDirection.Input;
senditem.Direction = ParameterDirection.Output;
oracmd.Parameters.Add(comp
oracmd.Parameters.Add(pdno
oracmd.Parameters.Add(sitm
oracmd.Parameters.Add(send
Please let me know how I can resolve this issue. Thanks.
ASKER
Quote: "Replace long with varchar in procedure defination."
You mean "compnr" and "pdno". They are of type long though. And, when I pass it from ASP.NET, they are sent as Oracletype 'Int32'.
You mean "compnr" and "pdno". They are of type long though. And, when I pass it from ASP.NET, they are sent as Oracletype 'Int32'.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks much k_murli_krishna for sending me the detailed documentation. I am reading through the documents now and working on fixing the issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> oracmd.Parameters.Add(sitm
>> oracmd.Parameters.Add(send
Replace long with varchar in procedure defination.