2012-04-04

Miss leading ORA-44425

Error message for ORA-44425 is miss leading.

ORA-44425: XML DB needs to be installed for creating an XMLType table or column
Cause: An attempt was made to create an XMLType table or column with out XML DB installed.

The correct information in the message is that you do not have XML DB installed in your database. The fact is that you are trying to store your XMLType column as BINARY XML. You actually do not have to have XML DB installed to use XMLType column. SQL Developer generates binary option to DDL for a XMLType column by default.

CREATE TABLE TABLE_1 
    ( 
     Column_1 XMLTYPE 
    ) 
    XMLTYPE COLUMN Column_1 STORE AS BINARY XML 
;
You have the option to STORE as CLOB.

CREATE TABLE TABLE_1 
    ( 
     Column_1 XMLTYPE 
    ) 
    XMLTYPE COLUMN Column_1 STORE AS CLOB XML 
;


If you need BINARY stored XML install XML DB option to your database as the error message states. Otherwise you have the option to change the store method to database default CLOB. This may be found from Relational Models - Physical Models - Tables - TABLE_1 - Columns - Column_1 - Right click mouse - Properties - Store as - CLOB

There is also option of STORE AS OBJECT RELATIONAL. Then you need to deal with ORA-19002: Missing XMLSchema URL. The same properties page has XML Schema URL and XML Schema Elem properties to be filled. Once filled the generated DDL throws an error ORA-31159: XML DB is in an invalid state. Maybe this error should be the same as ORA-44425.

The latest 11.2.0.3 create table XMLType_column_properties documentation instructs not to use CLOB storage as it might be deprecated in a future release.

Maybe it would be wise just to install the XML DB if a XMLType column is used. There is many kinds of XML out there. Unfortunately so often no knowledge of schema used. In this case maybe CLOB store is ok. No need to install XML DB and make Oracle patching take longer.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.