none
ORA-06550 (PLS-00306: wrong number or types of arguments) Error

    Question

  • I'm using Microsoft's Oracle Data Access library (http://www.microsoft.com/downloads/details.aspx?familyid=4f55d429-17dc-45ea-bfb3-076d1c052524&displaylang=en) and I keep getting this error when trying to invoke a function on the oracle server:

    System.Data.OracleClient.OracleException: ORA-06550: line 1, column 24:
    PLS-00306: wrong number or types of arguments in call to 'INSERT_SUBSCRIBER'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    I check the parameter spelling and types over 5, times, I have no clue what is going on.

    I tried using the following to manually add the parameters:

    Code Snippet

    command.CommandText = "INSERT_SUBSCRIBER";
    command.CommandType = CommandType.StoredProcedure;
    command.Connection = connection;

    command.Parameters.Add("RETURN_VALUE", OracleType.Number).Direction = ParameterDirection.ReturnValue;
    command.Parameters.Add("P_SUB_CAR_ID", OracleType.Number).Value = 0;
    command.Parameters.Add("P_SUB_PHONE_MODEL_ID", OracleType.Number).Value = 0;
    command.Parameters.Add("P_SUB_PHONE_NUMBER", OracleType.Number).Value = 0;
    command.Parameters.Add("P_SUB_FIRST_NAME", OracleType.VarChar, 20).Value = null;
    command.Parameters.Add("P_SUB_LAST_NAME", OracleType.VarChar, 20).Value = null;
    command.Parameters.Add("P_SUB_USERNAME", OracleType.VarChar, 32).Value = userName;
    command.Parameters.Add("P_SUB_PASSWD", OracleType.VarChar, 32).Value = passwd;
    command.Parameters.Add("P_SUB_ENC_PWD", OracleType.VarChar, 100).Value = encPwd;
    command.Parameters.Add("P_SUB_DOB", OracleType.DateTime).Value = DateTime.Now;
    command.Parameters.Add("P_SUB_SEX", OracleType.Char, 1).Value = null;
    command.Parameters.Add("P_SUB_LOC_ID", OracleType.Number).Value = 0;
    command.Parameters.Add("P_SUB_ZIPCODE", OracleType.VarChar, 10).Value = null;
    command.Parameters.Add("P_SUB_AREACODE", OracleType.Number).Value = 0;
    command.Parameters.Add("P_SUB_EMAIL", OracleType.VarChar, 50).Value = null;
    command.Parameters.Add("P_SUB_NEWS", OracleType.Number).Value = 0;
    command.Parameters.Add("P_SUB_DIST_ID", OracleType.Number).Value = 0;

    connection.Open();
    command.ExecuteNonQuery(); // ERROR HERE



    I even try letting asp tell me the parameters using DeriveParamters:

    Code Snippet

    command.CommandText = "INSERT_SUBSCRIBER";
    command.CommandType = CommandType.StoredProcedure;
    command.Connection = connection;

    connection.Open();

    OracleCommandBuilder.DeriveParameters(command);

    command.Parameters["P_SUB_USERNAME"].Value = userName;
    command.Parameters["P_SUB_PASSWD"].Value = ComputeMD5Hex(password);
    command.Parameters["P_SUB_ENC_PWD"].Value = password;
    command.Parameters["P_SUB_CAR_ID"].Value = 0;
    command.Parameters["P_SUB_PHONE_MODEL_ID"].Value = 0;
    command.Parameters["P_SUB_PHONE_NUMBER"].Value = 0;
    command.Parameters["P_SUB_LOC_ID"].Value = 0;
    command.Parameters["P_SUB_AREACODE"].Value = 0;
    command.Parameters["P_SUB_NEWS"].Value = 0;
    command.Parameters["P_SUB_DIST_ID"].Value = 0;
    command.Parameters["P_SUB_DOB"].Value = DateTime.Now;

    command.ExecuteNonQuery(); // ERROR THROW HERE AGAIN!



    The following function is on the server, and it looks like this:
    Code Snippet

    SQL> describe insert_subscriber;
    FUNCTION insert_subscriber RETURNS NUMBER
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     P_SUB_CAR_ID                   NUMBER(4)               IN
     P_SUB_PHONE_MODEL_ID           NUMBER(4)               IN
     P_SUB_PHONE_NUMBER             NUMBER(15)              IN
     P_SUB_FIRST_NAME               VARCHAR2(20)            IN
     P_SUB_LAST_NAME                VARCHAR2(20)            IN
     P_SUB_USERNAME                 VARCHAR2(32)            IN
     P_SUB_PASSWD                   VARCHAR2(32)            IN
     P_SUB_ENC_PWD                  VARCHAR2(100)           IN
     P_SUB_DOB                      DATE                    IN
     P_SUB_SEX                      CHAR(1)                 IN
     P_SUB_LOC_ID                   NUMBER(10)              IN
     P_SUB_ZIPCODE                  VARCHAR2(10)            IN
     P_SUB_AREACODE                 NUMBER(10)              IN
     P_SUB_EMAIL                    VARCHAR2(50)            IN
     P_SUB_NEWS                     NUMBER(1)               IN
     P_SUB_DIST_ID                  NUMBER(32)              IN


    The funny thing is, if I right click on the function in Visual Studio's Database Explorer, and select "Execute", fill in the parameters, and it works!  WHY?  What am I doing wrong?

    Sunday, February 03, 2008 11:22 PM

Answers

  • Thanks for the reply, one of the args is a return value, that is why there are 17 parameters.

    I figured out the issue actually.  To submit null values to the database, you have to set the OracleParameter.Value property to DbNull.Value instead of regular .net nulls.  Are you trying to give me white hairs microsoft?  Oh well. 


    Monday, February 04, 2008 8:10 PM

All replies

  • What does the signature of the call to the function in Oracle look like? You've added 17 parameters but I only see 16 in the list above.

     

    Monday, February 04, 2008 3:54 PM
  • Thanks for the reply, one of the args is a return value, that is why there are 17 parameters.

    I figured out the issue actually.  To submit null values to the database, you have to set the OracleParameter.Value property to DbNull.Value instead of regular .net nulls.  Are you trying to give me white hairs microsoft?  Oh well. 


    Monday, February 04, 2008 8:10 PM
  • PeLee,

     

    Your fix worked like a charm thanks so much here is the code I modified:

    .

    .

    .

    //If status = null send DBNull.Value instead so Oracle will recognize it

    if (i_vchStatus == null)

    {

    paramArray[2] = new OracleParameter("i_vchStatus", OracleType.VarChar, 10);

    paramArray[2].Direction = ParameterDirection.Input;

    paramArray[2].Value = DBNull.Value;

    }

    else

    {

    paramArray[2] = new OracleParameter("i_vchStatus", OracleType.VarChar, 10);

    paramArray[2].Direction = ParameterDirection.Input;

    paramArray[2].Value = i_vchStatus;

    }

     

    Glad I finally found this solution, I have been looking for a couple of days off and on.

     

     

    Monday, April 07, 2008 7:28 PM
  • Thank you so so ... much.

    It worked for me setting the null value to DBNull.value

    Relieved me from lots of pressue having pressed with time constraint

    Thanks

    Lizy

    Sunday, July 31, 2011 6:13 PM