which bind variable value causes ora-1722
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: SQLdesc 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).
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: SQLdesc 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).
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: SQLdesc 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
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: SQLdesc 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).
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:
SQLdesc 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: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To
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).
