6 Replies Latest reply: Sep 2, 2003 12:03 PM by 82532 RSS

    Microsoft Access shows "ORA-01722: Invalid number"

    268871
      Hi,

      I have seen a previous post on this problem, but none of the tips applied to my situation, so I'm asking again.

      I'm accesing Oracle 8i Personal (German Version) with Access 2002 (latest SP, on a computer with Windows 2000). Please note that I installed the latest ODBC driver from Oracle (8.1.7.6.0). When I create a table which has, say, a NUMBER(10,3) field in it, and I link it into Access using the ODBC driver, I cannot enter numbers like "1,23" into the table. The error "ORA-01722: Invalid number" is shown and the INSERT fails. If I enter the numbers like "1.23", the resulting field value is "123". The
      problem occurs, whether I use the table view, a form or VBA via ADO. Pass-Through queries (over ADO's QueryDef facility) work, however. The decimal separator is "," in both Windows and Oracle. With normal NUMBER fields everything works fine.

      When looking at the SQL log it turns out, that Access hands out a string to the ODBC driver and relies on the driver to convert it to a number. Regardless of the locale set in Windows, Access uses the dot ('1.23') as decimal separator for this string. The German version of oracle wants a comma as decimal separator as in "INSERT INTO TEST VALUES ('1,23')". From a previous post I know that ODBC specifies that all numbers must use the dot, regardless of the locale setting. So it turns out, the Oracle ODBC driver is causing the problem, not Access. But, anyway, this problem could easily be fixed by adding a "Workaround option" to the ODBC driver, that always converts dots in numbers to commas before handing it out to the backend. Another option would be, that the ODBC driver does this decision automatically by asking the backend, which separator it wants.

      Any comments greatly appreciated.

      Best regards


      Markus
        • 1. re:Microsoft Access shows
          82532
          What is your NLS_LANG setting on the machine where PO is installed?
          • 2. re:Microsoft Access shows
            268871
            What is your NLS_LANG setting on the machine where PO is installed?
            Previously, NLS_LANG was set to "NA". For testing, I changed it to "german_germany.we8iso8859p1", but it doesn't make any difference.


            Markus
            • 3. re:Microsoft Access shows
              3004
              In that case I would expect the driver to have picked it up from the regional settings (under the control panel). Obviously that wasn't happening so it must simply be a bug. Searched MetaLink and I found bug# 2106166 on this.
              • 4. re:Microsoft Access shows
                268871
                In that case I would expect the driver to have picked it up from the regional settings
                (under the control panel). Obviously that wasn't happening so it must simply be a bug.
                Searched MetaLink and I found bug# 2106166 on this.
                I do not have a MetaLink account, since I am a third-party developer, and not using Oracle myself. What does this bug mean? Do you have any suggestion?


                Markus
                • 5. Re: Microsoft Access shows "ORA-01722: Invalid number"
                  401835
                  Hi,

                  Were you finally able to make it work ?
                  If yes, could you please tell me how, I am experiencing the exact same problem.

                  thx in advance,
                  regards
                  B./
                  • 6. Re: re:Microsoft Access shows
                    82532
                    This is actually a duplicate of bug# 1938991 which says:
                    "A 'Numeric settings' option has been added to the Application Options page of the ODBC Configuration screen so that the user can choose what numeric settings will be used in receiving and returning numeric data that is bound as strings. This option allows the user to choose Oracle NLS settings (the default setting and the behavior in former releases), Microsoft default regional settings (to provide a way to mirror the Oracle OLE DB driver's behavior for greater interoperability) and US numeric settings, which are necessary when using MS Access or DAO in non-US environments. In this case, setting the Numeric settings to US settings should resolve the issue, since DAO is expecting the data in US format."

                    Apparently the fixed release is 9.2.0.2, 9.0.1.4 or 8.1.7.7 depending on your client version.