2 Replies Latest reply on Dec 19, 2007 6:54 AM by 614201

    ORA-01722: invalid number


      I have a problem with my JRuby on Rails Applications which uses ActiveRecord-JDBC and Oracle

      During startup of the Application I get a "ORA-01722: invalid number" error. In the beginning I thought it is a NLS issue but this is not the problem.

      I have activated the debug log in the OJDBC Driver and found that the following SQL query produces the error ...

      select [...]
      union select
      'DATE' as type_name, 91as data_type, 7 as precision,
      NULL as literal_prefix, NULL as literal_suffix, NULL as create_params,
      1 as nullable, 0 as case_sensitive, 3 as searchable,
      0 as unsigned_attribute, 0 as fixed_prec_scale, 0 as auto_increment,
      'DATE' as local_type_name, 0 as minimum_scale, 0 as maximum_scale,
      NULL as sql_data_type, NULL as sql_datetime_sub, 10 as num_prec_radix
      from dual

      As you can see there is "91as" instead of "91 as".

      The whole query works in Oracle 9.2 although there is the space missing.
      In Oracle 9.0 the missing space leads to the "ORA-01722: invalid number" error.

      It seems that the query is not produced by Rails but from the JDBC driver during getMetaData.

      Is this a known incompatibility with JDBC and Oracle 9.0?

      Any help is appreciated!

        • 1. Re: ORA-01722: invalid number
          Please refer
          • 2. Re: ORA-01722: invalid number
            I've found the same problem, but for me it's 93as not 91as. However, if you read the bytecode for the oracle.jdbc.driver.OracleDatabaseMetaData class you can see the problem in the method "public ResultSet getTypeInfo()":

            An excerpt:

            .append(((PhysicalConnection)connection).mapDateToTimestamp ? "93" : "91").append("as data_type, 7 as precision,\n")

            There should be a space before the word as in that string... ie:

            .append(((PhysicalConnection)connection).mapDateToTimestamp ? "93" : "91").append(" as data_type, 7 as precision,\n")

            I just called Oracle support to notify them of this issue, hopefully they can fix this soon.