2 Replies Latest reply: Dec 19, 2007 12:54 AM by 614201 RSS

    ORA-01722: invalid number

    500634
      Hi!

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

      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!


      TIA,
      Mike
        • 1. Re: ORA-01722: invalid number
          dvohra21
          Please refer
          http://rubyforge.org/forum/message.php?msg_id=16032
          • 2. Re: ORA-01722: invalid number
            614201
            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.