This discussion is archived
6 Replies Latest reply: Apr 30, 2013 7:55 AM by 1005328 RSS

Out parameter cast issue - C#

1005328 Newbie
Currently Being Moderated
Hi all,

Having an issue with an older application I am supporting. The data access is CSLA .Net and there is a line that updates a newly created entity id with the value from an out parameter. This is defined in the database as NUMERIC(10,0). The code looks like this:

OracleParameter opID = new OracleParameter("P_ID", OracleDbType.Int64);
//... do some stuff and save the new entity to the db
_id = (long)opID.Value;

This used to be fine on windows XP with version 9.x of ODP .Net. On a Windows 7 box with the latest version the cast fails and the return data type Decimal. This looks to me like a bug/weird behavior in the new version. Is there a workaround or something I can do to fix the issue without, preferably without changing my code?

Thanks!
Matei

Edited by: Matei on Apr 29, 2013 4:05 PM
  • 1. Re: Cast issue
    Tridus Journeyer
    Currently Being Moderated
    Can you post the stored procedure code or whatever function you're calling in the database that has how the OUT parameter is defined?
  • 2. Re: Cast issue
    1005328 Newbie
    Currently Being Moderated
    Yes for sure, thanks for the reply. Here are more details on the table schema and the proc that's being called. It's part of a package that does user related CRUD operations.

    ID     NUMBER(10,0)     No
    USERNAME     VARCHAR2(20 BYTE)     No
    PASSWORD     VARCHAR2(20 BYTE)
    FIRST_NAME     VARCHAR2(40 BYTE)
    LAST_NAME     VARCHAR2(40 BYTE)


    PROCEDURE insert_by_pk (
    p_id OUT fact.users.ID%TYPE,
    p_first_name IN fact.users.first_name%TYPE,
    p_last_name IN fact.users.last_name%TYPE,
    p_home_phone IN fact.users.home_phone%TYPE,
    p_business_phone IN fact.users.business_phone%TYPE,
    p_mobile_phone IN fact.users.mobile_phone%TYPE,
    p_fax IN fact.users.fax%TYPE,
    p_email IN fact.users.email%TYPE,
    p_internal IN fact.users.INTERNAL%TYPE,
    p_username IN fact.users.username%TYPE,
    p_password IN fact.users.password%TYPE,
    p_home_ext IN fact.users.home_ext%TYPE,
    p_business_ext IN fact.users.business_ext%TYPE,
    p_active          IN     fact.users.active%TYPE
    )
    AS
    BEGIN
    INSERT INTO fact.users
    (ID, first_name,
    last_name, home_phone, business_phone,
    mobile_phone, fax, email, internal,
    username, password, home_ext, business_ext, active,
                        CREATE_USER,CREATE_DATE
    )
    VALUES (FACT.users_seq.NEXTVAL, p_first_name,
    p_last_name, p_home_phone, p_business_phone,
    p_mobile_phone, p_fax, p_email, p_internal,
    LOWER(p_username), p_password, p_home_ext, p_business_ext, p_active,
                        (select sys_context('userenv','os_user') FROM DUAL),SYSDATE
    );
    SELECT FACT.users_seq.CURRVAL
    INTO p_id
    FROM DUAL;
    END;

    And for completeness the C# code. The line that fails is the second to last one where the cast happens: _id = (long)opID.Value;

    // we're not being deleted, so insert or update
                                  OracleParameter opID = new OracleParameter("P_ID", OracleDbType.Int64);

                                  if(this.IsNew)
                                  {
                                       // we're new so insert
                                       cm.CommandText = "FACT.users_pkg.insert_by_pk";
                                       opID.Direction = ParameterDirection.Output;
                                       cm.Parameters.Add(opID);
                                  }
                                  else
                                  {
                                       // we're not new, so update
                                       cm.CommandText = "FACT.users_pkg.update_by_pk";
                                       opID.Direction = ParameterDirection.Input;
                                       cm.Parameters.Add("p_id", _id);
                                  }

                                  cm.Parameters.Add("p_first_name", _firstName);
                                  cm.Parameters.Add("p_last_name", _lastName);
                                  cm.Parameters.Add("p_home_phone", _homePhone);
                                  cm.Parameters.Add("p_bus_phone", _busPhone);
                                  cm.Parameters.Add("p_mobile_phone", _mobilePhone);
                                  cm.Parameters.Add("p_fax", _fax);
                                  cm.Parameters.Add("p_email", _email);
                                  cm.Parameters.Add("p_internal", _internal ? "Y" : "N");
                                  cm.Parameters.Add("p_username", _username.ToLower());
                                  cm.Parameters.Add("p_password", "password");
                                  cm.Parameters.Add("p_home_ext", _homePhoneExt);
                                  cm.Parameters.Add("p_business_ext", _busPhoneExt);
                                  cm.Parameters.Add("p_active", _active ? "Y" : "N");

                                  cm.ExecuteNonQuery();

                                  if(this.IsNew)
                                  {
                                       // update ID with the oracle generated sequence
                                       _id = (long)opID.Value;
                                  }

    Edited by: 1002325 on Apr 25, 2013 7:56 AM

    Edited by: 1002325 on Apr 25, 2013 7:57 AM
  • 3. Re: Cast issue
    Tridus Journeyer
    Currently Being Moderated
    That looks right to me, I'm not sure why that wouldn't bring back a long.

    Hopefully one of the Oracle guys can help you.
  • 4. Re: Cast issue
    1005328 Newbie
    Currently Being Moderated
    Thanks for checking it out! It looks good to me as well, which is why I am a little puzzled by what I'm seeing.
  • 5. Re: Cast issue
    1005328 Newbie
    Currently Being Moderated
    Bump.
    Has anyone seen anything like this? I've tried Convert.ToInt64() but that fails because the decimal value that comes back doesn't implement IConvertible. My last resort here is to convert to a string and then to an Int64 but that's pretty lame and I really shouldn't have to do this.

    Thanks!
  • 6. Re: Out parameter cast issue - C#
    1005328 Newbie
    Currently Being Moderated
    See this thread here: ODP.NET 10.2.0.2.20 changes to parameter types
    Further details what the issue is and there are a couple of quick fixes in there too.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points