2 Replies Latest reply: Dec 10, 2012 9:44 AM by be*447301*ll RSS

    ORA-01722 with ODP.NET but not in SQL Developer

    be*447301*ll
      Hi there,
      I'm using version 2.11.2.3.0 of Oracle.DataAccess.dll on Windows 7 64 bit against an Oracle Database 10g Release 10.2.0.4.0 - 64bit Production.
      Following sql gives me ORA-01722: invalid number:
      SELECT 
        c, 
        wert, 
        NULLIF(TO_NUMBER(COALESCE(wert, '-9999')), -9999) AS conv 
      FROM 
        (
          SELECT 
            1 AS c, 
            '12.34' AS wert 
          FROM 
            dual 
          UNION 
          SELECT 
            2, 
            NULL 
          FROM 
            dual 
          UNION 
          SELECT 
            3, 
            '-12.34' 
          FROM 
            dual 
          UNION 
          SELECT 
            4, 
            '-9999' 
          FROM 
            dual 
        ) 
      ORDER BY 
        c 
      SQL Developer returns what I expected:
      1     12.34     12.34
      2     (null)          (null)
      3     -12.34     -12.34
      4     -9999     (null)

      Can anyone tell me what's wrong with the sql?

      Thanks, Bernd
        • 1. Re: ORA-01722 with ODP.NET but not in SQL Developer
          be*447301*ll
          Got it: ODP.NET here expects me to provide a real number with a comma as the digit separator.
          That leads to the next question: Is there any property to set something like an invariant number format?
          • 2. Re: ORA-01722 with ODP.NET but not in SQL Developer
            be*447301*ll
            Keep replying to myself...
            Here's how I set the "invariant" format using reflection, as I code against a ProviderFactory:
            ...
            if (connection.GetType().FullName == "Oracle.DataAccess.Client.OracleConnection")
                SetSessionInfo(connection);
            ...
            
            private void SetSessionInfo(DbConnection connection)
            {
                var getSessionInfo = connection.GetType().GetMethod("GetSessionInfo", new Type[0]);
                var oracleGlobalization = getSessionInfo.Invoke(connection, null);
            
                var propertyInfo = oracleGlobalization.GetType().GetProperty("NumericCharacters");
                var wert = propertyInfo.GetValue(oracleGlobalization, null);
                if (wert.ToString() == ",.")
                {
                    propertyInfo.SetValue(oracleGlobalization, ".,", null);
            
                    var setSessionInfo = connection.GetType().GetMethod("SetSessionInfo");
                    setSessionInfo.Invoke(connection, new object[] { oracleGlobalization });
                }
            }