CODEWITHSUNDEEP
c#oracle-databaseplsqlodp.net
Nick
Nick

Reputation: 3965

PLS-00306: wrong number or types of arguments error in C#

We are using an Oracle stored procedure in a C# program, which was working fine until we added the 'v_nameurn' variable.

Here's the Oracle procedure:

create or replace procedure stats_By_Date (
   v_SDate   IN varchar2, 
   v_EDate   IN varchar2, 
   v_user    IN number, 
   v_nameurn IN number,
   p_cursor  OUT SYS_REFCURSOR
) as 
BEGIN
   OPEN p_cursor FOR
     select T1.Staff_No, 
            T3.Title, 
            T2.Verno, 
            To_char(T1.Response_dt, 'DD/MM/YYYY'), 
            T5.Response, 
            T6.Forenames||' '||T6.Surname
       from TOU.Users_version_link T1, 
            TOU.Version T2, 
            TOU.name T3, 
            TOU.Admin_Access T4, 
            Tou.CV_Response T5, 
            corp.wforce_employee@remote_DWLive T6
      where T1.Version_urn=T2.Urn
        and T1.Name_urn= T3.urn
        and T3.urn = T4.name_urn
        and T1.Response_urn=T5.urn
        and T1.staff_no=T6.Staff_Number
        and Trunc(T1.Response_dt) >= To_date(v_Sdate, 'DD/MM/YYYY')
        and Trunc(T1.Response_dt) <= To_date(v_EDate, 'DD/MM/YYYY')
        and T4.Staff_No = v_user
        and T3.urn = v_nameurn;
End;

And here is the C# code:

this.ConnectToDb();
var cmd = new OracleCommand
{
   Connection = cn,
   CommandText = "TOU.Stats_By_Date",
   CommandType = CommandType.StoredProcedure
 };
 var id = HttpContext.Current.User.Identity.Name.Split('\\')[1].ToString();
 cmd.Parameters.Add("v_SDate", OracleDbType.Varchar2).Value = 
          startdate.ToString("dd-MM-yyyy");
 cmd.Parameters.Add("v_EDate", OracleDbType.Varchar2).Value = 
          enddate.ToString("dd-MM-yyyy");
 cmd.Parameters.Add("v_user", OracleDbType.Decimal).Value = int.Parse(id);
 cmd.Parameters.Add("v_nameurn", OracleDbType.Decimal).Value = 3;
 var rc = cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor);
 rc.Direction = ParameterDirection.Output;
 cmd.ExecuteNonQuery();
 this.DisconnectFromDb();

Any suggestions as to what could be wrong please? There are 5 total variables in both pieces of code (4 in, 1 out), which is fine and all of the data types seem to match. I've also tried different variations on the actual v_nameurn variable, so I've tried 3, "3", and defining it as an Int variable beforehand.

Upvotes: 5

Views: 3028

Answers (4)

Jeremy Thompson
Jeremy Thompson

Reputation: 65594

I've encountered the same error under a different set of circumstances.

With OleDB and earlier versions of Oracle you don't set the return parameter when calling a stored procedure:

PROCEDURE getThingByID
(thingID NUMBER,
results IN OUT tSelectCursor) IS
selectCursor tSelectCursor;
BEGIN
OPEN selectCursor FOR
 SELECT Name, Description, IsDeleted
 FROM thing WHERE thingID = getThingByID.thingID;
results := selectCursor;
END;

C#/psuedo:

cn = New OleDbConnection("Provider=msdaora.1;Data Source=myTNSEntry;User ID=fakeuser;Password=abc123");
cn.Open();
oleDBCmd = cn.CreateCommand();
oleDBCmd.CommandType = CommandType.StoredProcedure;
oleDBCmd.CommandText = "ThingPackage.GetThingByID";
oleDBCmd.Parameters.Add(new OleDbParameter("thingID", "1"));

We execute it without the tSelectCursor input output parameter:

dataReader = oleDBCmd.ExecuteReader();

As per: C# and Oracle 10g database to call stored procedure

However, it is important to note this works using Oracle 9.2 drivers on a 10g database if you execute this with an Oracle 12c database you get the error:

"ORA-06550: PLS-00306: wrong number or types of arguments in call to 'GETTHINGBYID'"

I've found if you do specify the output parameter it is expecting: 

oleDBCmd.Parameters.Add(parm);

You don't get the PLS-00306: wrong number or types of arguments in call to {proc name} error, but when added you get:

size property has an invalid size of 0

This leaves us little choice (I don't know how to overcome this) to resort to the Oracle Managed Data ODP.Net driver.

With ODP.Net you have to set the return parameter when calling the stored procedure, unlike OleDB the Oracle driver provides REFCURSOR output parameters:

PROCEDURE getThingByID2
(thingID NUMBER,
results OUT SYS_REFCURSOR) IS
BEGIN
OPEN refCursor FOR
 SELECT Name, Description, IsDeleted
 FROM thing WHERE thingID = getThingByID2.thingID;
END;

Now we can easily call this:

ocn = New OracleConnection("Data Source=myTNSEntry;User ID=fakeuser;Password=abc123");
ocn.Open();
ocmd = ocn.CreateCommand();
ocmd.CommandType = CommandType.StoredProcedure();
ocmd.CommandText = "ThingPackage.GetThingByID2";
ocmd.Parameters.Add(New OracleParameter("thingID", "1"));
ocmd.Parameters.Add(New OracleParameter("results", OracleDbType.RefCursor, ParameterDirection.InputOutput));

dataReader = ocmd.ExecuteReader();

For using SSL you may encounter the .Net Oracle SSL Wallet problem 'Network Transport: SSL failure in parsing wallet location': Oracle.ManagedDataAccess to AWS RDS DB - TCPS: Invalid SSL Wallet (Magic)

Upvotes: 0

b_levitt
b_levitt

Reputation: 7415

This is one of those errors where oracle proves to not be a developer friendly RDBMS. Why they can't tell you which of those two problems is the issue I have no idea. I've got a long-winded list of things to check that comes down to the following:

  • Missing comma
  • Wrong number of parameters
  • Wrong order of parameters and cmd.BindByName is false
  • Using a parameter twice and cmd.BindByName is false (in the case if inline sql)
  • The data types on the parameters match. To avoid issues with this use the .Add("paramName",value) overload for all input parameters and let the type be inferred.

However, from the looks of this, I think i'm going to add one more to my list: Make sure you're calling the procedure you think you are calling. I see the "TOU" qualifier on the procedure. I have to assume this is a schema and not a package based on the sql script. Are you altering the proc in this schema? If you remove the new param in your .net code does it work or do you get the same error? You should get the same error, but if you don't you might be loading the new proc to the wrong schema.

The last thing you can do is just start with a shell of a proc and add one proc at a time until you can figure out what's going on.

I will say that your int.Parse(id) is a bit odd - are your authenticated user IDs just ints? But in any case i'd expect a .net error there and not a oracle error.

Upvotes: 1

nvoigt
nvoigt

Reputation: 77304

cmd.Parameters.Add("v_user", OracleDbType.Decimal).Value = decimal.Parse(id);
cmd.Parameters.Add("v_nameurn", OracleDbType.Decimal).Value = 3m;

You must use a decimal when you are dealing with decimals. For Oracle, decimals don't translate well to ints and the other way round.

Upvotes: 2

Nagaraj S
Nagaraj S

Reputation: 13484

Decimal is Oracle NUMBER type.. So Change your datatype for v_user and 'v_nameurn'

cmd.Parameters.Add("v_user", OracleDbType.Decimal).Value = int.Parse(id);
cmd.Parameters.Add("v_nameurn", OracleDbType.Decimal).Value = 3;

OracleDbType Enumeration

Upvotes: 2

Related Questions