RE: which bind variable value causes ora-1722
Mike, What are the NLS_NUMERIC_CHARACTERS and NLS_TERRITORY values? Could the db be interpreting the "." in the number incorrectly? Are all of the 0 zeroes or character? Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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).
the issue solved Re: which bind variable value causes ora-1722
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
> > NameNull?Type
> > ---
> >
> > OBJ_ID0 NUMBER(38)
> > REC_ID NUMBER(38)
> > ELEMENT_ID NUMBER(38)
> > FIXED_OPERANDNUMBER
> > FLAGSNUMBER(38)
> > FREE_QUANTITYNUMBER
> > GL_IDNUMBER(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: [EM
Re: which bind variable value causes ora-1722
Mike, If there a trigger on the table that does some DML with another table? You already got the sql statement. Why not run that in sql*plus the exact same way, after declaring the bind variables and assigning them the same values you found in the trace file and see what happens? >From sql prompt variable element_id number variable... exec :element_id := 250 ... finally your query verbatim. HTH Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 03, 2002 10:04 AM > 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=807252370 > 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=807252370 > ERROR #1:err=1722 tim=807252370 > > > And here is the table: > > SQL>desc fold_bal_impacts_t > NameNull?Type > --- > OBJ_ID0 NUMBER(38) > REC_ID NUMBER(38) > ELEMENT_ID NUMBER(38) > FIXED_OPERANDNUMBER > FLAGSNUMBER(38) > FREE_QUANTITYNUMBER > GL_IDNUMBER(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: Arup Nanda 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).
Re: which bind variable value causes ora-1722
It looks like that is bind value 4(gl_id) = 0. How did you determine it is 6.55802? I certainly do not how to read this trace but looks like it is define correctly :element_id, bind 0 :fixed_operand,bind 1 :flags,bind 2 :free_quantity,bind 3 :gl_id,bind 4 value 0 :scaled_operand, bind 5 value 6.55802 :rec_id, :obj_id0 Rick tim@sagelogix. com To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED] Subject: Re: which bind variable value causes ora-1722 om 12/03/2002 02:21 PM Please respond to ORACLE-L 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 > NameNull?Type > --- > > OBJ_ID0 NUMBER(38) > REC_ID NUMBER(38) > ELEMENT_ID NUMBER(38) > FIXED_OPERANDNUMBER > FLAGSNUMBER(38) > FREE_QUANTITYNUMBER > GL_IDNUMBER(38) > SCALED_OPERAND
Re: which bind variable value causes ora-1722
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 > NameNull?Type > --- > > OBJ_ID0 NUMBER(38) > REC_ID NUMBER(38) > ELEMENT_ID NUMBER(38) > FIXED_OPERANDNUMBER > FLAGSNUMBER(38) > FREE_QUANTITYNUMBER > GL_IDNUMBER(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).
