OSDM: Not There Yet…(Reverse Engineering XMLTypes)

Driven by a post from Lewis about “OSDM: Rerverse Engineer A Schema“, I dared my luck to play with Oracle SQL Developer Data Modeler (OSDM), trying to see if it understands the XML database realm off doing things. It doesn’t understands it…at least, yet.† I used the early adopter release 1.5.1 (build 518).

I wonder if it is reasonable that I am disappointed; In the long run the “XDB” schema that I used to reverse engineer isn’t relational but (at least) object-relational. Most of the object relational issues, OSDM understands, but not the theory behind XMLType tables. I tried it multiple times, in the end, even the “secondary tables” and “spatial properties” option you can check during the reverse enginering option.

Although in it now also reverse enginered the PATH_TABLE in the XDB schema, it still didn’t “know” what to do with the XMLType tables which where XML schema based and ended up with DDL that was far from complete. An XMLIndex, a domain index with its foundation a PATH_TABLE, is especially designed for the XMLDB realm and apparently is only picked up if you use the “spatial properties” option.

A simple binary XML table, using BASICFILE storage, shows the following generated DDL

CREATE TABLE XM_TAB
    OF SYS.XMLTYPE
    SUBSTITUTABLE AT ALL LEVELS
    OBJECT IDENTIFIER IS SYSTEM GENERATED
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE USERS
        LOGGING
        STORAGE (
        INITIAL 65536
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    MONITORING;
.

It should have been something like the following

SQL> SELECT dbms_metadata.get_ddl('TABLE','XM_TAB')
  2* FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','XM_TAB')
-----------------------------------------------------------
  CREATE TABLE "XM_TAB" OF "SYS"."XMLTYPE"
 OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 
  2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
  MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
 DEFAULT)
  TABLESPACE "USERS"
 XMLTYPE COLUMN "SYS_NC_ROWINFO$" 
 STORE AS BASICFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
 DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA
 
1 ROW selected.

One of the issues here is that the OSDM generator doesn’t use double quotes, so objects are not created case-sensitive, but maybe I missed a setting that allows to generate double quotes in the DDL scripting anyway.

I did once a test, “Oracle 11g – Reverse Engineering Attempt of the XDB Schema“,† with Toad Data Modeler while trying to see what would happen if I used it on the XDB Schema. Although nog yet 11gR compliant, it did a very nice attempt and most of it correct. OSDM doesn’t even come near to this achievement.

Don’t get me wrong, I just hoped for the better, because it is only an early adopter release, who knows and this will be improved. I would really hope so.

If you would use it for a relational schema, as I did trying to reverse engineer the SYSMAN schema that is used for the local DB Console, then it does a remarkable job. With ease it handled hundreds off tables and other objects. At least for that I am very grateful, because we have finally again a worthy successor of the Designer Suite.

Rectifications needed…

Can’t read… I got mail from Oracle that OSDM doesn’t support 11g new features yet. Apparently I also overlooked the following in the “readme” that goes with the current OSDM build.

Support for Oracle 11g
Full support for Oracle 11g needs to be added. The current Oracle 11g implementation is Oracle 10g compatible.

I don’t have a Oracle 10gRx database at hand, so I tried some simple 9i2, 10g syntax to see how OSDM handles this.

I created an XMLType based on CLOB storage and a XMLType table (Object Relational Storage) based on a XML Schema to test old syntax handling. Both of these “XMLType”‘s are available in Oracle 9.2.0.3 and upwards.

SQL> CREATE TABLE xmltab
  2  (xdata xmltype)
  3  xmltype COLUMN "XDATA" store AS CLOB
  4  nomonitoring
  5  ;
 
TABLE created.
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','XMLTAB','TEST') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','XMLTAB')
---------------------------------------------------------------
  CREATE TABLE "TEST"."XMLTAB"
   (    "XDATA" "SYS"."XMLTYPE"
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 XMLTYPE COLUMN "XDATA" STORE 
     AS BASICFILE CLOB "NOMONITORING"(
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 
  PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT))
 
1 ROW selected.

OSDM DDL output shows the following:

CREATE TABLE TEST.XMLTAB 
    ( 
     XDATA XMLTYPE 
    ) 
        PCTFREE 10 
        PCTUSED 40 
        MAXTRANS 255 
        TABLESPACE USERS 
        LOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    ) 
    MONITORING 
;

I am missing out on some tablespace details but the monitoring part is picked up correctly, in the light that OSDM isn’t 11g new features compatible. NOMONITORING and MONITORING keywords are ignored in 11g (See: Automatically Collecting Statistics on Tables), although dbms_metadata isn’t aware of this yet.

SQL> SELECT TABLE_NAME, monitoring FROM user_tables;
 
TABLE_NAME                     MON
------------------------------ ---
XMLTAB                         YES
 
1 ROW selected.

OK, what if I create an Object Relational table based on an XML Schema, in this case “root.xsd

SQL> BEGIN
  2    DBMS_XMLSCHEMA.deleteSchema(
  3    'http://localhost/public/myschema.xsd', 4);
  4  END;
  5  /
 
PL/SQL PROCEDURE successfully completed.
 
SQL> purge recyclebin;
 
Recyclebin purged.
 
SQL>  BEGIN
  2     DBMS_XMLSCHEMA.registerSchema(
  3       SCHEMAURL  => 'http://localhost/public/myschema.xsd',
  4       SCHEMADOC  => xdbURIType('/public/root.xsd').getClob(),
  5       LOCAL      => FALSE,  -- local
  6       GENTYPES   => TRUE,  -- generate object types
  7       GENBEAN    => FALSE,  -- no java beans
  8       GENTABLES  => TRUE,  -- generate object tables
  9       OWNER      => USER);
 10    END;
 11  /
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
INFO182_TAB                    TABLE
ROOT184_TAB                    TABLE
 
2 ROWS selected.
 
SQL> SELECT dbms_metadata.get_ddl('TABLE','ROOT184_TAB',USER) 
     FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','ROOT184_TAB',USER)
------------------------------------------------------------
  CREATE TABLE "TEST"."ROOT184_TAB" OF "SYS"."XMLTYPE"
  XMLSCHEMA "http://localhost/public/myschema.xsd" 
  ELEMENT "ROOT" ID 3468 PCTFREE 10 
  PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 VARRAY "XMLEXTRA"."NAMESPACES" 
  STORE AS BASICFILE LOB "NAMESPACES188_L"
  (ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT))
 VARRAY "XMLEXTRA"."EXTRADATA" 
  STORE AS BASICFILE LOB "EXTRADATA187_L"
  (ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT))
 VARRAY "XMLDATA"."SYS_XDBPD$" 
  STORE AS BASICFILE LOB "SYS_XDBPD$185_L"
  (ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT))
 VARRAY "XMLDATA"."INFO"."SYS_XDBPD$" 
  STORE AS BASICFILE LOB "SYS_XDBPD$186_L"
  (ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT))
 
1 ROW selected.

The OSDM DDL output after reverse engineering:

CREATE TABLE TEST.ROOT184_TAB 
    OF SYS.XMLTYPE 
    SUBSTITUTABLE AT ALL LEVELS 
    OBJECT IDENTIFIER IS SYSTEM GENERATED 
        PCTFREE 10 
        PCTUSED 40 
        MAXTRANS 255 
        TABLESPACE USERS 
        LOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    )    
    MONITORING 
;

The full OSDM table and type listing was

CREATE OR REPLACE TYPE TEST.INFO181_T 
;
/
 
CREATE OR REPLACE TYPE TEST.ROOT183_T 
;
/
 
CREATE OR REPLACE TYPE XDB.XDB$RAW_LIST_T 
    IS VARRAY ( 2147483647 ) OF RAW (2000) 
;
 
CREATE OR REPLACE TYPE TEST.INFO181_T 
    AS OBJECT 
    ( 
        SYS_XDBPD$ XDB$RAW_LIST_T , 
        INFO_ID NUMBER (38) , 
        INFO_CONTENT VARCHAR2 (4000) 
    ) FINAL 
;
/
 
CREATE OR REPLACE TYPE TEST.ROOT183_T 
    AS OBJECT 
    ( 
        SYS_XDBPD$ XDB$RAW_LIST_T , 
        ID NUMBER (38) , 
        INFO INFO181_T 
    ) FINAL 
;
/
 
CREATE TABLE TEST.INFO182_TAB 
    OF SYS.XMLTYPE 
    SUBSTITUTABLE AT ALL LEVELS 
    OBJECT IDENTIFIER IS SYSTEM GENERATED 
        PCTFREE 10 
        PCTUSED 40 
        MAXTRANS 255 
        TABLESPACE USERS 
        LOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    )     
    MONITORING ;
 
-- Error - Unique Constraint INFO182_TAB.SYS_C006570 doesn't have columns 
 
CREATE TABLE TEST.ROOT184_TAB 
    OF SYS.XMLTYPE 
    SUBSTITUTABLE AT ALL LEVELS 
    OBJECT IDENTIFIER IS SYSTEM GENERATED 
        PCTFREE 10 
        PCTUSED 40 
        MAXTRANS 255 
        TABLESPACE USERS 
        LOGGING 
        STORAGE ( 
        INITIAL 65536 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        FREELISTS 1 
        FREELIST GROUPS 1 
        BUFFER_POOL DEFAULT 
    )     
    MONITORING ;
 
-- Error - Unique Constraint ROOT184_TAB.SYS_C006569 doesn't have columns 
.

Alternative ways

An alternative way to see what is created is setting a debugging event, this will create a trace file in the udump directory.

 
SQL> ALTER SESSION SET events = '31098 trace name context forever';
 
SESSION altered.
 
-- 
-- The content of the trace file gives the following output
-- 
 ------------ QMTS Executing SQL ------------ 
CREATE OR REPLACE TYPE "TEST"."INFO192_T" AS OBJECT 
("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",
"INFO_ID" NUMBER(38),
"INFO_CONTENT" VARCHAR2(4000 CHAR))
FINAL INSTANTIABLE 
/
 -------------------------------------------- 
SELECT * FROM user_errors WHERE name = 'INFO192_T'; 
 
 ------------ QMTS Executing SQL ------------ 
CREATE OR REPLACE TYPE "TEST"."ROOT194_T" AS OBJECT 
("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",
"ID" NUMBER(38),
"INFO" "INFO192_T")
FINAL INSTANTIABLE 
/
 -------------------------------------------- 
SELECT * FROM user_errors WHERE name = 'ROOT194_T'; 
 ------------ QMTS Executing SQL ------------ 
GRANT EXECUTE ON "TEST"."INFO192_T" TO PUBLIC WITH GRANT OPTION
/
 -------------------------------------------- 
 ------------ QMTS Executing SQL ------------ 
GRANT EXECUTE ON "TEST"."ROOT194_T" TO PUBLIC WITH GRANT OPTION
/
 -------------------------------------------- 
 
 ------------ QMTS Executing SQL ------------ 
CREATE TABLE "TEST"."ROOT195_TAB" OF SYS.XMLTYPE  
XMLSCHEMA "http://localhost/public/myschema.xsd" 
ID 'E6F21F2A25794FEC9ED1B08EE1DD272E' 
ELEMENT "ROOT" ID 3474  
TYPE "TEST"."ROOT194_T"  
 VARRAY XMLDATA."SYS_XDBPD$" STORE AS LOB SYS_XDBPD$196_L 
 VARRAY XMLDATA."INFO"."SYS_XDBPD$" STORE AS LOB SYS_XDBPD$197_L 
 VARRAY XMLEXTRA.EXTRADATA STORE AS LOB EXTRADATA198_L 
 VARRAY XMLEXTRA.NAMESPACES STORE AS LOB NAMESPACES199_L
/
 -------------------------------------------- 
 
*** 2008-10-18 21:51:20.453
 ------------ QMTS Executing SQL ------------ 
BEGIN
  xdb.dbms_xdbz.enable_hierarchy('"TEST"','"ROOT195_TAB"',DBMS_XDBZ.ENABLE_CONTENTS,TRUE); END;
/
 -------------------------------------------- 
 
 ------------ QMTS Executing SQL ------------ 
CREATE TABLE "TEST"."INFO193_TAB" OF SYS.XMLTYPE  
XMLSCHEMA "http://localhost/public/myschema.xsd" ID 'E6F21F2A25794FEC9ED1B08EE1DD272E' 
ELEMENT "INFO" ID 3477  
TYPE "TEST"."INFO192_T"  
 VARRAY XMLDATA."SYS_XDBPD$" STORE AS LOB SYS_XDBPD$200_L 
 VARRAY XMLEXTRA.EXTRADATA STORE AS LOB EXTRADATA201_L 
 VARRAY XMLEXTRA.NAMESPACES STORE AS LOB NAMESPACES202_L
/
 -------------------------------------------- 
 ------------ QMTS Executing SQL ------------ 
BEGIN 
  xdb.dbms_xdbz.enable_hierarchy('"TEST"','"INFO193_TAB"',DBMS_XDBZ.ENABLE_CONTENTS,TRUE); END;
/
 --------------------------------------------

This is what actually is happening under the cover, to create the object relational XMLType table.

Although OSDM shouldn’t understand stuff like “BASICFILE” keywords, it should have noticed the VARRAY’s involved. It got the create types correct in the overall DDL scripting it generates. In Oracle 11g syntax has progressed, but not that much.

I will double check against an 10gR2 database next week, but I guess it still doesn’t give me the correct DDL syntax.

Marco Gralike Written by:

One Comment

  1. Halsey
    June 12

    Give me a lever long enough, and I shall move the world.

Comments are closed.