Re: Oracle 10g - Putting a where clause on a column with a check constraint.

On 22 Mar 2006 11:10:45 -0800, "Jon" <jonathan.p.crawford_at_gmail.com> wrote:

>
>Has anyone experienced this? Below is an example.
>
>If I don't have the check constraint on the column it works fine. Also
>this worked fine in 9i and we are trying to migrate our application
>onto 10g as new clients are requesting compatability with 10g.
>
>Thanks for all the help.
>
>
>******************************************************************
>create table jon5
>(
>AAA NVARCHAR2(1),
>BBB NVARCHAR2(10),
>CONSTRAINT CC_AAA CHECK(AAA IN('N','Y')),
>CONSTRAINT CC_BBB CHECK(BBB IN('Happy','Sad','Angry'))
>);
>
>insert into jon5 values('Y','Happy');
>insert into jon5 values('Y','Sad');
>insert into jon5 values('Y','Angry');
>insert into jon5 values('N','Sad');
>
>select * from jon5
>A BBB
>- ----------
>Y Happy
>Y Sad
>Y Angry
>N Sad
>
>4 rows selected.
>
>
>select * from jon5 where

Not using NVARCHAR2 myself I seem to recall you needed to prefix hardcoded literals with N everywhere, like N'Happy' If this is true, you are relying on implicit conversion everywhere. Oracle could have become more strict. Also only the variation of National Charactersets has been limited to UTF... starting in 9i.

--
Sybrand Bakker, Senior Oracle DBA