6 Replies Latest reply: Apr 30, 2013 9:55 AM by 1005328 RSS

    Out parameter cast issue - C#

    1005328
      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
          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
            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
              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
                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
                  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
                    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.