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.
rema10Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.