Re: improbable ora-01722

On Aug 22, 2:28 pm, 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 think oracle tries to cast 'J' and 'N' into a number type and it fails, beacuse J and N are not numeric literals. Note that it will work this way:

SCOTT_at_andrkydb> create table tte (a number);

Table created.

SCOTT_at_andrkydb> alter table tte add (constraint tte_cc check (a in ('1','2')) );

Table altered.

SCOTT_at_andrkydb> insert into tte values (1);

1 row created.

• Andrei