Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    5

    Unanswered: java.sql.SQLException: ORA-01722: invalid number

    Hello,

    We just migrated from MySQL 3.23 to Oracle 9i and I am getting some problems when I try to invoke simple queries.
    I have a table which contains two fields of VARCHAR2 when I execute the following query "SELECT myFiled FROM myTable the results are as expected,
    But when I tries to execute " SELECT myFiled FROM myTable WHERE myOtherField='myValue'" I am getting :
    java.sql.SQLException: ORA-01722: invalid number,

    any ideas ?

    Thanks in advance
    /Lior.

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Data type

    WHat is the data type of the field, myOtherField.
    Could u paste the exact query you have (excluding the double quotes)

    SELECT myFiled FROM myTable WHERE myOtherField='myValue'
    TaR
    Aruneesh

  3. #3
    Join Date
    Aug 2003
    Posts
    5
    the type of myOtherField is VARCHAR2
    this is the original query which returned the exception:
    SELECT RUID FROM related WHERE UID='myUser'

    However it works fine when I execute the following query:
    SELECT RUID FROM related WHERE "UID"='myUser'

    is that the only solution ? in that case I will need to edit all the queries in the application

    thanks in advance
    /Lior.

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Application type

    Hi
    I would appreciate if you could share the information on the application you are using. Where are these queries written.
    It doesnt make sense that the field name needs to be in quotes.
    TaR
    Aruneesh

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Unless UID is a reserved word on 9i? I don't know, I'm still stuck in the dark ages on the one that works (8i).

    Hth
    Bill

  6. #6
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Good Call

    Bill
    That was an interesting observation you put in.
    It could be possible.

    But since it is giving invalid integer, and UID is more like a varchar2.
    ???
    TaR
    Aruneesh

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Good luck Lior,

    Double "" quoting in Oracle is a means to circumvent reserved words, hence after you have one query which fails and one with "" which doesn't, one doesn't need to move much further to arrive at a solution.

    *IF* the double quotes fix your problem, don't assume the problem is over. There have been cases where Oracle would let you create a table with a reserved word for a column name, but not create a trigger on the same table because of the column name with or without the double quotes. (search this forum for examples).

    So even if the reserved word *is* your problem (remember I'm guessing), change it if you can.

    Hth
    Bill

  8. #8
    Join Date
    Aug 2003
    Posts
    5
    Gents,

    you are right the problem is the UID field (which apparently is a reserved word in Oracle).
    When I changed the field name the query runs as expected.

    I would like to thank you all for your help
    /Lior.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •