After spending hours debugging, we finally found the solution: NLS_LANG. There is no problem if nls_lang is american_america.utf8; while ora-1722 occurs when nls_lang is set to american_canada.utf8. The culprit is 6.55802. And when nls_lang is set to american_canada.utf8, the default decimal separator is a comma! So Oracle failed to do implicit conversion to_number('6.55208'), could only do to_number('6,55208').
Thanks. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 03, 2002 2:21 PM > Mike, > > It is the value in the ":gl_id" string, which is "6.55802". > The column GL_ID is defined as an integer of 38 digits (i.e. > "NUMBER(38)" instead of "NUMBER"), so there is no room for > digits to the right of the decimal... > > Hope this helps... > > -Tim > > > Hi, all, > > > > My developer continually got ora-1722 when he was > > installing a new billing system. I used 10046 event to get > > trace file, but i could not figure out which bind variable > > value causes the error, here is the part of trace: > > PARSING IN CURSOR #1 len=229 dep=0 uid=62 oct=2 lid=62 > > tim=807252370 hv=4084410285 ad='9f31e880' > > insert into fold_bal_impacts_t ( element_id, > > fixed_operand, flags, free_quantity, gl_id, > > scaled_operand, rec_id, obj_id0 ) values ( :element_id, > > :fixed_operand, :flags, :free_quantity, :gl_id, > > :scaled_operand, :rec_id, :obj_id0 ) END OF STMT > > PARSE > > #1:c=0,e=1,p=0,cr=1,cu=0,mis=1,r=0,dep=0,og=0,tim=80725237 > > 0 BINDS #1: > > bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=0 size=224 offset=0 > > bfp=018bacd0 bln=22 avl=03 flg=05 > > value=250 > > bind 1: dty=1 mxl=32(01) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=10 size=0 offset=24 > > bfp=018bace8 bln=32 avl=01 flg=01 > > value="0" > > bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=0 size=0 offset=56 > > bfp=018bad08 bln=22 avl=01 flg=01 > > value=0 > > bind 3: dty=1 mxl=32(01) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=10 size=0 offset=80 > > bfp=018bad20 bln=32 avl=01 flg=01 > > value="0" > > bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=0 size=0 offset=112 > > bfp=018bad40 bln=22 avl=01 flg=01 > > value=0 > > bind 5: dty=1 mxl=32(07) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=10 size=0 offset=136 > > bfp=018bad58 bln=32 avl=07 flg=01 > > value="6.55802" > > bind 6: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=0 size=0 offset=168 > > bfp=018bad78 bln=22 avl=01 flg=01 > > value=0 > > bind 7: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=01 > > oacfl2=10 size=0 offset=192 > > bfp=018bad90 bln=32 avl=05 flg=01 > > value="10382" > > EXEC > > #1:c=0,e=0,p=0,cr=0,cu=2,mis=0,r=0,dep=0,og=3,tim=80725237 > > 0 ERROR #1:err=1722 tim=807252370 > > > > > > And here is the table: > > > > SQL>desc fold_bal_impacts_t > > Name Null? Type > > ----------------------- -------- > > ---------------------------- > > OBJ_ID0 NUMBER(38) > > REC_ID NUMBER(38) > > ELEMENT_ID NUMBER(38) > > FIXED_OPERAND NUMBER > > FLAGS NUMBER(38) > > FREE_QUANTITY NUMBER > > GL_ID NUMBER(38) > > SCALED_OPERAND NUMBER > > > > And there is no any constraints on this table. > > > > Please help, thanks. > > > > Mike > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com -- > > Author: mike ding > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com San Diego, California -- > > Mailing list and web hosting services > > ---------------------------------------------------------- > > ----------- To REMOVE yourself from this mailing list, > > send an E-Mail message to: [EMAIL PROTECTED] (note > > EXACT spelling of 'ListGuru') and in the message BODY, > > include a line containing: UNSUB ORACLE-L (or the name of > > mailing list you want to be removed from). You may also > > send the HELP command for other information (like > > subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mike ding INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).