DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Advice needed: ORA-01722 invalid number. Set up trace??

  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Advice needed: ORA-01722 invalid number. Set up trace??

    Hi all,
    I'm a newby to Oracle. Here's the issue that I am having.

    Box: French
    OS: Win 2000
    Oracle version: 9i
    Driver version: 9.02.00.02
    Table: SQL: create table myTable (fld_my number(12,7));

    Issue:
    I have a third party application that is attempting to perform an insert of decimal value (12,5) into an Oracle db file (see file above).

    I get the following error,
    SQL 1722
    After the error nothing is inserted into the file.


    I have traced the third party app and have not got anything from that. I traced ODBC. Specifically I was looking to see if the driver was trying to insert the "," (french decimal) character into the table rather than "." as should be. Unfortunatly, the value is hidden in the ODBC trace.

    Im not sure how to trace Oracle so as to expose the values or insert attempts.

    Does anyone have any ideas, comments?

    Thanks in advance,
    Zippo

    By the way this is what the error means.

    ORA-01722 invalid number
    Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
    Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can set up 10046 trace with level 12 to trace the sql and the bind values for further digging into the problem.

    Tamil

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Question

    Also you can create an AFTER LOGON trigger to issue an:

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.';

    and see if that fixes the problem.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

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


Click Here to Expand Forum to Full Width