Link to home
Start Free TrialLog in
Avatar of rema10
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,OracleType.Int32,0);
      oracmd.Parameters.Add(pdno,OracleType.Int32,0);
      oracmd.Parameters.Add(sitm,OracleType.VarChar,24);
      oracmd.Parameters.Add(senditem,OracleType.VarChar,24);

Please let me know how I can resolve this issue. Thanks.
Avatar of Devinder Singh Virdi
Devinder Singh Virdi
Flag of United States of America image

>> compnr IN long, pdno IN long, sitm IN string,  senditem OUT string )
>> oracmd.Parameters.Add(sitm,OracleType.VarChar,24);
>> oracmd.Parameters.Add(senditem,OracleType.VarChar,24);

Replace long with varchar in procedure defination.
Avatar of rema10
rema10

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'.

SOLUTION
Avatar of k_murli_krishna
k_murli_krishna
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rema10

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial