Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: improbable ora-01722

Re: improbable ora-01722

From: joel garry <joel-garry_at_home.com>
Date: Wed, 22 Aug 2007 10:48:45 -0700
Message-ID: <1187804925.923260.195710@z24g2000prh.googlegroups.com>


On Aug 22, 8:56 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> schreef in berichtnews: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

I think the misfeature lies in sqlplus giving a single error and relating it to a single line of code. Since a stack of things are happening, it really should give an error stack that explains which errors and where the error is coming from - which constraint is violated and how, what conversion was violated and how. Shouldn't be having to dig into trace files for basic information like that. An error table, maybe.

As to being able to enter constraints that violate column definitions... I'm waffling. The concept of being able to define things that aren't used or aren't correct _yet_ isn't so far out. But not to have a warning of a current self-referential violation is strange.

jg

--
@home.com is bogus.
"The surgeon who performed Anna Nicole Smith's breast augmentation
sold the video of the operation to a memorabilia dealer in Los
Angeles."
"You mean a mammarabilia dealer?" - heard on radio
Received on Wed Aug 22 2007 - 12:48:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US