Re: improbable ora-01722

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 22 Aug 2007 06:55:09 -0700
Message-ID: <1187790909.193136.5890@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. Received on Wed Aug 22 2007 - 08:55:09 CDT