Re: improbable ora-01722

"Charles Hooper" <hooperc2000_at_yahoo.com> schreef in bericht news:1187790909.193136.5890_at_x35g2000prf.googlegroups.com...
> On Aug 22, 7:28 am, steph <stepha..._at_yahoo.de> wrote:
>> This bugged me a bit today:
>>
>> create table steptest(a number);
>>
>> alter table steptest add (constraint steptest_check1 check (a in
>> ('J','N')));
>>
>> insert into steptest values (1);
>>
>> ==>ORA-01722
>>
>> Of course the check constraint makes no sense on this column ... but
>> Oracle (9i) does not mind. The resulting error message led me in
>> completely wrong directions. Bug or feature?
>>
>> stephan
>
> I agree that the error message is a bit misleading, but consider the
> following:
> CREATE TABLE T2 (C1 VARCHAR2(10));
>
> INSERT INTO T2 VALUES ('J');
> INSERT INTO T2 VALUES ('N');
>
> ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
>
> SELECT
> *
> FROM
> T2
> WHERE
> C1=1;
>
> ERROR at line 6:
> ORA-01722: invalid number
>
> Is the above error message misleading?
>
>>From the 10053 trace:
> Current SQL statement for this session:
> SELECT
> *
> FROM
> T2
> WHERE
> C1=1
>
> ============
> Plan Table
> ============
> -------------------------------------
> +-----------------------------------+
> | Id | Operation | Name | Rows | Bytes | Cost |
> Time |
> -------------------------------------
> +-----------------------------------+
> | 0 | SELECT STATEMENT | | | | 3
> | |
> | 1 | TABLE ACCESS FULL | T2 | 1 | 7 | 3 |
> 00:00:01 |
> -------------------------------------
> +-----------------------------------+
> Predicate Information:
> ----------------------
> 1 - filter(TO_NUMBER("C1")=1)
>
> Note the filter listed in the predicate information - Oracle did not
> create the filter predicate as filter(C1=TO_CHAR(1)), as one might
> expect.
>
> I set up the following test to replicate your set up:
> CREATE TABLE T1 (C1 NUMBER);
>
> ALTER TABLE T1 ADD (CONSTRAINT T1_CHECK1 CHECK (C1 IN ('J','N')));
>
> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
> ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
>
> INSERT INTO T1 VALUES (1);
>
> ERROR at line 1:
> ORA-01722: invalid number
>
> The trace file shows this:
> INS$1 0x11f08a78 (PARSER) [FINAL]
> Optimizer State Dump: call(in-use=1880, alloc=16344), compile(in-
> use=58368, alloc=104552)
> =====================
> PARSING IN CURSOR #5 len=25 dep=0 uid=153 oct=2 lid=153 tim=4426272663
> hv=981683409 ad='947fe9c8'
> INSERT INTO T1 VALUES (1)
> END OF STMT
> PARSE
> #5:c=15625,e=54377,p=10,cr=42,cu=0,mis=1,r=0,dep=0,og=1,tim=4426272659
> BINDS #5:
> EXEC #5:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=4426272823
> ERROR #5:err=1722 tim=185259300
> WAIT #5: nam='SQL*Net break/reset to client' ela= 5 driver
> id=1413697536 break?=1 p3=0 obj#=-1 tim=4426272903
>
> If I had to make a guess, Oracle might be trying to make the
> comparison, which generates the error:
> filter(TO_NUMBER("J")=1)
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
>

Thanks Charles, at least it explains the error message! But it IS strange one can put a 'J','N' constraint to a number column.... without an error message. One would/could expect an Invalid Number message or something like that over there too......
but ok, that was not the OP's question..... so I think your answer is correct!

Shakespeare Received on Wed Aug 22 2007 - 10:56:26 CDT