Deploying Mapviewer in Oracle 11g

Install Oracle Spatial in Oracle 11g

Install Oracle Spatial in Oracle 11g

Create the (just the basic without any bells and whistles) database first.

To install Oracle Spatial, you need to have these below said database components/products already installed in the database:-

1) Java
2) XML database
3) Oracle Multimedia (In Pre-11g versions, its called interMedia)

Let us check if its already there:-

> select comp_id,version,status from dba_registry
    where comp_id in ('JAVAVM','ORDIM','XDB');

no rows selected

It shows that these components are not installed.

1) Installing Java:-

Requirements for Java install: Check space availability for SYSTEM UNDO (minimum 100MB) and SYSTEM (minimum 70MB) tablespace, and memory settings for SHARED POOL (minimum 96MB) and JAVA POOL (minimum 50MB).

Run "initjvm.sql" to verify these requirements.

> col COMPONENT for a15
>select COMPONENT,CURRENT_SIZE/1024/1024 "CURRENT_MB",MIN_SIZE/1024/1024 "MIN_MB",
MAX_SIZE/1024/1024 "MAX_MB" from V$MEMORY_DYNAMIC_COMPONENTS where upper(COMPONENT) like '%POOL';

COMPONENT         CURRENT_MB MIN_MB     MAX_MB
---------------   ---------- ---------- ----------
shared pool       156        156        156
large pool        4          0          4
java pool         4          4          4
streams pool      0          0          0
Shared IO Pool    0          0          0

> ALTER SYSTEM SET JAVA_POOL_SIZE=50M;

System altered.

> select COMPONENT,CURRENT_SIZE/1024/1024 "CURRENT_MB",MIN_SIZE/1024/1024 "MIN_MB",
2 MAX_SIZE/1024/1024 "MAX_MB" from V$MEMORY_DYNAMIC_COMPONENTS where upper(COMPONENT) like '%POOL';

COMPONENT         CURRENT_MB MIN_MB     MAX_MB
---------------   ---------- ---------- ----------
shared pool       156        156        156
large pool        4          0          4
java pool         52         4          52
streams pool      0          0          0
Shared IO Pool    0          0          0

>SELECT TABLESPACE_NAME,BYTES/1024/1024 "BYTES_MB",MAXBYTES/1024/1024 "MAX_BYTES_MB"
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME IN ('SYSTEM','UNDOTBS1');

TABLESPACE_NAME                BYTES_MB   MAX_BYTES_MB
------------------------------ ---------- ------------
SYSTEM                         325        32767.9844
UNDOTBS1                       200        32767.9844

> spool C:\full_jvminst.log;

>set echo on
>connect sys/*** as sysdba
>startup mount
>alter system set "_system_trig_enabled" = false scope=memory;
>alter database open;
>ALTER SYSTEM SET JAVA_POOL_SIZE=50M;

>@C:\oracle\ora11\javavm\install\initjvm.sql
>@C:\oracle\ora11\xdk\admin\initxml.sql
>@C:\oracle\ora11\xdk\admin\xmlja.sql
>@C:\oracle\ora11\RDBMS\ADMIN\catjava.sql
>@C:\oracle\ora11\RDBMS\ADMIN\catexf.sql

> select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;

  COUNT(*) OBJECT_TYPE
---------- ---------------
       317 JAVA DATA
       763 JAVA RESOURCE
     19974 JAVA CLASS

>shutdown immediate
>set echo off
>spool off
>exit

2) Installing XML DB:-

Create a user and tablespace to store the XML DB components:-

> connect sys/*** as sysdba
> Shutdown Immediate;
> Startup;

> create tablespace XDB
datafile 'C:\ORACLE\ORADATA\ORA11\XDB1.DBF' size 50m autoextend on next 1m maxsize 4096m
extent management local
uniform size 1m
segment space management auto;

> create user XDB identified by XDB
default tablespace XDB
quota unlimited on XDB
temporary tablespace TEMP;

> @C:\oracle\ora11\RDBMS\ADMIN\catqm.sql XDB XDB TEMP

> @C:\Babu\4Project\GTO_PROJECT\Oracle_Spatial\Oracle_11g\xdbusagecheck.sql

> SELECT TABLESPACE_NAME,BYTES/1024/1024 "BYTES_MB",MAXBYTES/1024/1024 "MAX_BYTES_MB"
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'XDB';

TABLESPACE_NAME                BYTES_MB   MAX_BYTES_MB
------------------------------ ---------- ------------
XDB                            830        4096

@C:\oracle\ora11\RDBMS\ADMIN\utlrp.sql

> COL COMP_NAME FOR A30
> select comp_name, status, version from DBA_REGISTRY where comp_name= 'Oracle XML Database';

COMP_NAME                      STATUS      VERSION
------------------------------ ----------- ------------------------------
Oracle XML Database            VALID       11.1.0.6.0

3) Installing Oracle Multimedia:-

Create a tablespace to store the Oracle Spatial components (or SYSAUX default tablespace can be used) :-

> select version, status from dba_registry where comp_id='JAVAVM';

VERSION                        STATUS
------------------------------ -----------
11.1.0.6.0                     VALID

> create tablespace ORA_SPATIAL
datafile 'C:\ORACLE\ORADATA\ORA11\ORA_SPATIAL1.DBF' size 50m autoextend on next 1m maxsize 4096m
extent management local
uniform size 1m
segment space management auto;

> @C:\oracle\ora11\ord\admin\ordinst.sql SYSAUX ORA_SPATIAL

> COL USERNAME FOR A20
> COL ACCOUNT_STATUS FOR A20
> COL DEFAULT_TABLESPACE FOR A20
> SELECT USERNAME,TO_CHAR(CREATED,'DD-MON-YY HH24:MI:SS') CREATED,ACCOUNT_STATUS,DEFAULT_TABLESPACE
FROM DBA_USERS ORDER BY 2;

USERNAME             CREATED            ACCOUNT_STATUS       DEFAULT_TABLESPACE
-------------------- ------------------ -------------------- -------------------
SYS                  03-JAN-08 10:08:44 OPEN                 SYSTEM
SYSTEM               03-JAN-08 10:08:44 OPEN                 SYSTEM
OUTLN                03-JAN-08 10:08:46 OPEN                 SYSTEM
DIP                  03-JAN-08 10:11:02 EXPIRED & LOCKED     USERS
TSMSYS               03-JAN-08 10:11:06 EXPIRED & LOCKED     USERS
ORACLE_OCM           03-JAN-08 10:12:40 EXPIRED & LOCKED     USERS
BABU                 11-AUG-08 09:31:08 OPEN                 BABU
DBSNMP               23-JAN-08 13:30:18 EXPIRED & LOCKED     SYSAUX
EXFSYS               29-OCT-09 13:26:45 EXPIRED & LOCKED     SYSAUX
XS$NULL              29-OCT-09 13:51:16 OPEN                 USERS
ANONYMOUS            29-OCT-09 14:24:51 LOCKED               XDB
XDB                  29-OCT-09 14:40:29 OPEN                 XDB
ORDSYS               29-OCT-09 15:03:24 EXPIRED & LOCKED     SYSAUX
ORDPLUGINS           29-OCT-09 15:03:24 EXPIRED & LOCKED     SYSAUX
SI_INFORMTN_SCHEMA   29-OCT-09 15:03:24 EXPIRED & LOCKED     SYSAUX
MDSYS                29-OCT-09 15:03:24 EXPIRED & LOCKED     ORA_SPATIAL

16 rows selected.

> @C:\oracle\ora11\ord\im\admin\catim.sql

> execute validate_ordim;

> select version, status from dba_registry where comp_id='ORDIM';

VERSION STATUS
------------------------------ -----------
11.1.0.6.0 VALID

1 row selected.

4) Verify Install Oracle Spatial in Oracle 11g

> connect sys/***  as sysdba

> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB');

COMP_ID    VERSION    STATUS
---------- ---------- -----------
ORDIM      11.1.0.6.0 VALID
XDB        11.1.0.6.0 VALID
JAVAVM     11.1.0.6.0 VALID

3 rows selected.

> @C:\oracle\ora11\md\admin\mdprivs.sql
> @C:\oracle\ora11\md\admin\mdinst.sql

> set serveroutput on
> execute validate_sdo;

> col COMP_ID for a10
> col COMP_NAME for a10
> col CONTROL for a10
> col SCHEMA for a10
> col VERSION for a10
> select comp_id, control, schema, version, status, comp_name from dba_registry
where comp_id='SDO';

COMP_ID    CONTROL    SCHEMA     VERSION    STATUS      COMP_NAME
---------- ---------- ---------- ---------- ----------- ----------
SDO        SYS        MDSYS      11.1.0.6.0 VALID       Spatial

> select object_name, object_type, status from dba_objects
where owner='MDSYS' and status <> 'VALID'
order by object_name;

no rows selected

> select comp_id,version,status from dba_registry
where comp_id in ('JAVAVM','ORDIM','XDB','SDO');

COMP_ID    VERSION    STATUS
---------- ---------- -----------
SDO        11.1.0.6.0 VALID
ORDIM      11.1.0.6.0 VALID
XDB        11.1.0.6.0 VALID
JAVAVM     11.1.0.6.0 VALID

4 rows selected

Oracle Error - "ORA-01762: vopdrv: view query block not in FROM"

Came across this error "ORA-01762: vopdrv: view query block not in FROM"

Here is the version and platform details:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Here is the structure of the view:

SQL> DESC DBA_AUTOTASK_WINDOW_CLIENTS;
Name Null? Type
----------------------- -------- ----------------
WINDOW_NAME NOT NULL VARCHAR2(30)
WINDOW_NEXT_TIME TIMESTAMP(6) WITH TIME ZONE
WINDOW_ACTIVE VARCHAR2(5)
AUTOTASK_STATUS VARCHAR2(8)
OPTIMIZER_STATS VARCHAR2(8)
SEGMENT_ADVISOR VARCHAR2(8)
SQL_TUNE_ADVISOR VARCHAR2(8)
HEALTH_MONITOR VARCHAR2(8)

This query works:

SQL> SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS

WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
---------------- ---------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW 03-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
TUESDAY_WINDOW 04-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
WEDNESDAY_WINDOW 05-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
THURSDAY_WINDOW 06-AUG-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
FRIDAY_WINDOW 31-JUL-09 10.00.00.000000 PM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
SATURDAY_WINDOW 01-AUG-09 06.00.00.000000 AM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED
SUNDAY_WINDOW 02-AUG-09 06.00.00.000000 AM US/EASTERN FALSE ENABLED ENABLED ENABLED ENABLED DISABLED

7 rows selected.

But this one fails (or sucks! for the USA readers!):

SQL> SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS;
SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS
*
ERROR at line 1:
ORA-01762: vopdrv: view query block not in FROM

Any clue?

SECUREFILE Migration - Oracle 11g Feature

Step#1) Create a partition table:-

SQL> CREATE TABLE DOC_TAB (ID NUMBER, COLX CLOB )
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS LOBP1,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS LOBP2,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS LOBP3);

Table created.

Step#2) Insert data

SQL> INSERT INTO DOC_TAB VALUES (5,'XXX');

1 row created.

SQL> INSERT INTO DOC_TAB VALUES (15,'YYY');

1 row created.

SQL> INSERT INTO DOC_TAB VALUES (25,'ZZZ');

1 row created.

SQL> commit;

Commit complete.

Step#3) Create a transient table

SQL> CREATE TABLE DOC_TAB_TEMP (ID NUMBER, COLX CLOB)
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS SECUREFILE,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS SECUREFILE,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS SECUREFILE);

Table created.

Step#4) Table Redefinition

SQL> DECLARE
2 ERROR_COUNT NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.START_REDEF_TABLE(
5 UNAME=>'BABU',
6 ORIG_TABLE=>'DOC_TAB',
7 INT_TABLE=>'DOC_TAB_TEMP',
8 COL_MAPPING=>'ID ID,COLX COLX');
9 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
10 UNAME=>'BABU',
11 ORIG_TABLE=>'DOC_TAB',
12 INT_TABLE=>'DOC_TAB_TEMP',
13 COPY_INDEXES=>1,
14 COPY_TRIGGERS=>TRUE,
15 COPY_CONSTRAINTS=>TRUE,
16 COPY_PRIVILEGES=>TRUE,
17 IGNORE_ERRORS=>FALSE,
18 NUM_ERRORS=>ERROR_COUNT);
19 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
20 UNAME=>'BABU',ORIG_TABLE=>'DOC_TAB',INT_TABLE=>'DOC_TAB_TEMP');
21 DBMS_OUTPUT.PUT_LINE(ERROR_COUNT);
22 END;
23 /
DECLARE
*
ERROR at line 1:
ORA-12089: cannot online redefine table "BABU"."DOC_TAB" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1631
ORA-06512: at line 4

SQL> CREATE TABLE DOC_TAB (ID NUMBER PRIMARY KEY, COLX CLOB )
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS LOBP1,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS LOBP2,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS LOBP3);

Table created.

Step#4) Table Redefinition (again)

SQL> DECLARE
2 ERROR_COUNT NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.START_REDEF_TABLE(
5 UNAME=>'BABU',
6 ORIG_TABLE=>'DOC_TAB',
7 INT_TABLE=>'DOC_TAB_TEMP',
8 COL_MAPPING=>'ID ID,COLX COLX');
9 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
10 UNAME=>'BABU',
11 ORIG_TABLE=>'DOC_TAB',
12 INT_TABLE=>'DOC_TAB_TEMP',
13 COPY_INDEXES=>1,
14 COPY_TRIGGERS=>TRUE,
15 COPY_CONSTRAINTS=>TRUE,
16 COPY_PRIVILEGES=>TRUE,
17 IGNORE_ERRORS=>FALSE,
18 NUM_ERRORS=>ERROR_COUNT);
19 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
20 UNAME=>'BABU',ORIG_TABLE=>'DOC_TAB',INT_TABLE=>'DOC_TAB_TEMP');
21 DBMS_OUTPUT.PUT_LINE(ERROR_COUNT);
22 END;
23 /
0

PL/SQL procedure successfully completed.

New features in Oracle 11g Database - Reference Partition

Reference Partitioning is a new feature in Oracle 11g, it is meant for partitioning the parent and child tables using the foreign key constraint.

SQL> CREATE TABLE PARENT_TAB (
2 COL1 NUMBER(3) PRIMARY KEY,
3 COL2 DATE)
4 PARTITION BY RANGE (COL2) (
5 PARTITION Q1 VALUES LESS THAN (TO_DATE('1-4-2009','DD-MM-YYYY')),
6 PARTITION Q2 VALUES LESS THAN (TO_DATE('1-7-2009','DD-MM-YYYY')));

Table created.

SQL> CREATE TABLE CHILD_TAB (
2 COL1 NUMBER(3),
3 CONSTRAINT CHILD_FK FOREIGN KEY (COL1) REFERENCES PARENT_TAB(COL1))
4 PARTITION BY REFERENCE (CHILD_FK);
PARTITION BY REFERENCE (CHILD_FK)
*
ERROR at line 4:
ORA-14652: reference partitioning foreign key is not supported

SQL> CREATE TABLE CHILD_TAB (
2 COL1 NUMBER(3) NOT NULL,
3 CONSTRAINT CHILD_FK FOREIGN KEY (COL1) REFERENCES PARENT_TAB(COL1))
4 PARTITION BY REFERENCE (CHILD_FK);

Table created.

In Reference Partitioning, specifying NOT NULL for the FOREIGN KEY column in child table is mandatory. Otherwise it will throw the above error.

Oracle NUMBER Data Types

Introduction: This is my study about number data types present
in Oracle database. I have used Oracle 10g (10.2.0.3) for my study:-

Data types INT, INTEGER and SMALLINT all uses NUMBER in Oracle, let us see how:-

Example # 1:

drop table tab1 purge;

create table tab2 (
I INT,
S SMALLINT,
IG INTEGER);

describe tab1

Name Null? Type
------------------ -------- ----------
I NUMBER(38)
S NUMBER(38)
IG NUMBER(38)

Example # 2: Let's see about other number data types:-

drop table tab2 purge;

create table tab2 (
X number,
NUM1 NUMBER,
FLOAT1 FLOAT,
BFLOAT BINARY_FLOAT,
BDOUBLE BINARY_DOUBLE);

describe tab2

Name Null? Type
----------------------- -------- -------------
X NUMBER
NUM1 NUMBER
FLOAT1 FLOAT(126)
BFLOAT BINARY_FLOAT
BDOUBLE BINARY_DOUBLE

declare
y number;
begin for a in 1..10 loop
y:=1/(a*100);
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for .99999999999999999999
col FLOAT1 for .99999999999999999999
col BFLOAT for .99999999999999999999
col BDOUBLE for .99999999999999999999
set lines 120

select * from tab2;

X NUM1 FLOAT1 BFLOAT BDOUBLE
-- ---------------------- ---------------------- ---------------------- ------------
1 .01000000000000000000 .01000000000000000000 .00999999978000000000 .01000000000000000000 2 .00500000000000000000 .00500000000000000000 .00499999989000000000 .00500000000000000010 3 .00333333333333333333 .00333333333333333333 .00333333341000000000 .00333333333333333350 4 .00250000000000000000 .00250000000000000000 .00249999994000000000 .00250000000000000010 5 .00200000000000000000 .00200000000000000000 .00200000009000000000 .00200000000000000000 6 .00166666666666666667 .00166666666666666667 .00166666671000000000 .00166666666666666680 7 .00142857142857142857 .00142857142857142857 .00142857141000000000 .00142857142857142860 8 .00125000000000000000 .00125000000000000000 .00124999997000000000 .00125000000000000000 9 .00111111111111111111 .00111111111111111111 .00111111114000000000 .00111111111111111110 10 .00100000000000000000 .00100000000000000000 .00100000005000000000 .00100000000000000000
10 rows selected.

In the above, the NUM1 (which is using Number data type) and FLOAT1 (which is using FLOAT data type) preserves accuracy/scale than others.

Example # 3: Now, lets us try this

delete tab2;

declare
y number;
begin for a in 1..10 loop
y:=(a+(sqrt(a))/(a*100));
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for 99.99999999999999999999999999999999999999
col FLOAT1 for 99.99999999999999999999999999999999999999

select NUM1,FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 1.01000000000000000000000000000000000000 1.01000000000000000000000000000000000000
2.00707106781186547524400844362104849039 2.00707106781186547524400844362104849040
3.00577350269189625764509148780501957456 3.00577350269189625764509148780501957460
4.00500000000000000000000000000000000000 4.00500000000000000000000000000000000000
5.00447213595499957939281834733746255247 5.00447213595499957939281834733746255250
6.00408248290463863016366214012450981899 6.00408248290463863016366214012450981900
7.00377964473009227227214516536234180061 7.00377964473009227227214516536234180060
8.00353553390593273762200422181052424520 8.00353553390593273762200422181052424520
9.00333333333333333333333333333333333333 9.00333333333333333333333333333333333330
10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

10 rows selected.

select avg(NUM1) NUM1, avg(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 5.50502099789929266650050620329386725341 5.50502099789929266650050620329386725346

select sum(NUM1) NUM1, sum(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 55.05020997899292666500506203293867253408 55.05020997899292666500506203293867253460

select max(NUM1) NUM1, max(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

Example # 4: Now, lets us compare NUMBER and FLOAT data type and try this

drop table tab3 purge;

create table tab3 (
NUM1 NUMBER,
FLOAT1 FLOAT);

delete tab3;

insert into tab3 values (
1234567891234567891234567890123456789123.45,
1234567891234567891234567890123456789123.45);

col NUM1 for 9999999999999999999999999999999999999999.99
col FLOAT1 for 9999999999999999999999999999999999999999.99

select * from tab3;

NUM1
--------------------------------------------
FLOAT1
--------------------------------------------
1234567891234567891234567890123456789123.00
1234567891234567891234567890123456789100.00

In the above when comparing the NUM1 (NUMBER data type) and FLOAT1 (FLOAT data type), it appears that NUM1 (Number data type) preserves accuracy/scale than the other.

When default is used, NUMBER datatype can preserve values upto 40 bytes and FLOAT upto 38 bytes

Bug in Oracle 10g while using Check Constraint

Came across this bug in the check constraint in Oracle database 10g.
This has been tested in both 32 and 64 bit Oracle 10g (10.2.0.3) running on Windows 2003 Server.

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Reproducing the bug: Create a table called STORE having filed called LOCATION, this field should accept specified values or NULL values, below is the syntax:-

SQL> CREATE TABLE STORE (LOCATION VARCHAR2(15));

Table created.

SQL> ALTER TABLE STORE
2 ADD CONSTRAINT C1 CHECK
3 (LOCATION IN ('PHILLY','HARRISBURG',NULL));

Table altered.

Bug Note: By specifying a NULL, the constraint doesn't not verifies/validates the entered value.

SQL> INSERT INTO STORE VALUES ('PHILLY');
1 row created.

SQL> INSERT INTO STORE VALUES ('philly');
1 row created.

SQL> INSERT INTO STORE VALUES ('HARRISBURG');
1 row created.

SQL> INSERT INTO STORE VALUES ('harrisburg');
1 row created.

SQL> INSERT INTO STORE VALUES (null);
1 row created.

SQL> INSERT INTO STORE VALUES ('PITTSBURGH');
1 row created.

SQL> INSERT INTO STORE VALUES ('pittsburgh');
1 row created.

SQL> commit;
Commit complete.

SQL> col SEARCH_CONDITION for a50
SQL> select
2 STATUS,
3 DEFERRABLE,
4 DEFERRED,
5 VALIDATED,
6 SEARCH_CONDITION
7 FROM ALL_CONSTRAINTS A
8 WHERE table_name='STORE';

STATUS DEFERRABLE DEFERRED VALIDATED SEARCH_CONDITION
-------- -------------- --------- ------------- ----------------------------------------
ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED LOCATION IN ('PHILLY','HARRISBURG',NULL)

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
philly
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

7 rows selected.

PROBLEM: DMLs like INSERT, UPDATE and DELETE works, but Query (SELECT) statements doesn't work in Oracle 10g (Tested in 10.2.0.3 both in 32 and 64 bit) while querying for records that actually violates the constraint rule. But works fine in Oracle 9i (tested in 9.2.0.8) and 11g (tested in 11.1.0.6)

Condition that violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='philly';

COUNT(*)
----------
0

Condition that doesn't violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='PHILLY';
COUNT(*)
----------
1

SQL> select count(*) from STORE where upper(LOCATION)=upper('philly');
COUNT(*)
----------
2

Condition that violate the constraint rule, DMLs works fine:-
SQL> update STORE set LOCATION='XYX' where LOCATION='philly';
1 row updated.

SQL> commit;
Commit complete.

Condition that violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='XYX';
COUNT(*)
----------
0

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
XYX
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

7 rows selected.

Condition that violate the constraint rule, DMLs works fine:-
SQL> delete from STORE where LOCATION='XYX';

1 row deleted.

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

6 rows selected.

SQL> commit;

Commit complete.

FIX for 10g

ALTER TABLE STORE drop CONSTRAINT C1 ;

Option # 1: Create a check constraint without specifying NULL. By default check constraint will accept NULL values, hence it doesn't have to be mentioned in the constraint condition

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG'));

or

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG') or LOCATION IS NULL);

Option # 2: Specify function like UPPER or LOWER in the constraint

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(UPPER(LOCATION) IN ('PHILLY','HARRISBURG'));

Option # 3: If NOT NULL need to be enforced along with the above condition, then use the following:-

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG') and LOCATION IS NOT NULL);

Did anyone else had similar problem?