This discussion is archived
2 Replies Latest reply: Dec 10, 2012 7:44 AM by be*447301*ll RSS

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

be*447301*ll Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 });
        }
    }

Legend

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