• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

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.
0
rema10
Asked:
rema10
  • 3
2 Solutions
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
>> 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.
0
 
rema10Author Commented:
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'.

0
 
k_murli_krishnaCommented:
LONG:      Character data of variable length (A bigger version the VARCHAR2 datatype)       - Max size 2 Gigabytes - but now deprecated (provided for backward compatibility only).      Max Size PL/SQL: 32760 bytes - Note this is smalller than the maximum width of a LONG column.

Also refer:
LONG Datatype and its restriction in Oracle
http://arjudba.blogspot.com/2008/06/long-datatype-in-oracle.html
LONG Datatype
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#i3056

Also:
.NET Framework Developer's Guide
Oracle Data Type Mappings
http://msdn.microsoft.com/en-us/library/yk72thhd.aspx
A Data Type Conversion
http://download.oracle.com/docs/cd/B19306_01/win.102/b14306/appendixa.htm

And, when I pass it from ASP.NET, they are sent as Oracletype 'Int32'. You will find out from these references, that you need to change LONG or Int32 i.e. at least on one end.
0
 
rema10Author Commented:
Thanks much k_murli_krishna for sending me the detailed documentation.  I am reading through the documents now and working on fixing the issue.  
0
 
rema10Author Commented:
I couldn't get my issue resolved yet.  The documentation did help me to understand everything I needed to.   As I am short of time, I have used other means to get the same data out and I will have to check this  out further later.

Thanks both of you experts for your help.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now