Advice needed: ORA-01722 invalid number. Set up trace??
I'm a newby to Oracle. Here's the issue that I am having.
OS: Win 2000
Oracle version: 9i
Driver version: 9.02.00.02
Table: SQL: create table myTable (fld_my number(12,7));
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,
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,
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.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
You can set up 10046 trace with level 12 to trace the sql and the bind values for further digging into the problem.
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
Click Here to Expand Forum to Full Width