NOT NULL constraint on XMLTYPE Column not working
Hi all,
I run into a problem that must have been noticed by other but i can't find any comments on it in this forum or any other web resource. It seems that under some conditions NOT NULL Check constraints are not checked for XMLTYPE columns. consider the following scenario:
CREATE TABLE TEST ( ID INTEGER, XML XMLTYPE NOT NULL)
/
INSERT INTO TEST (ID, XML) VALUES (1, '<qwerty/>')
/
UPDATE TEST SET XML = NULL WHERE ID = 1
/
-- this generates (correctly) ORA-01400
ALTER TABLE TEST MODIFY (XML NULL)
/
ALTER TABLE TEST MODIFY (XML NOT NULL)
/
UPDATE TEST SET XML = NULL WHERE ID = 1
/
-- this incorrectly updates the column to NULL
SELECT * FROM TEST
/
gives:
ID: 1
XMLTYPE: (null)
It seems that NOT NULL constraints that are added after the table was created are not enforced. Looking at the constraint i see another strange thing:
SELECT CONSTRAINT_TYPE, SEARCH_CONDITION, STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TEST'
/
CONSTRAINT_TYPE: ?
SEARCH_CONDITION: "XML" IS NOT NULL
STATUS: ENABLED
The constraint type is ? instead of C. But when i check this directly after creating the table the ? shows up as well, so this is not the explanation.
Does anyone have an idea of whet is going on and how this can be fixed?
My version banner is:
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
Thanks for any help,
Gertjan