oracle inheritance and xml

inheritance_and_xml.sql

DECLARE
  num_Check    NUMBER;
BEGIN
  SELECT SIGN(COUNT(*))
  INTO   num_Check
  FROM   ALL_XML_SCHEMAS
  WHERE  OWNER = 'AIS_NET'
  AND    SCHEMA_URL = 'http://localhost/xdb/latera/hydra/billing/inheritantContainerSerializer.xsd';
  
  IF num_Check > 0 THEN
    DBMS_XMLSCHEMA.DELETESCHEMA(
      schemaurl => 'http://localhost/xdb/latera/hydra/billing/inheritantContainerSerializer.xsd');
    
    EXECUTE IMMEDIATE 'drop type "inheritant_container"';
    EXECUTE IMMEDIATE 'drop type "inheritance_root"';

  END IF;

  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://localhost/xdb/latera/hydra/billing/inheritantContainerSerializer.xsd',
    SCHEMADOC => '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xs:complexType name="inheritance_root" xdb:SQLType="inheritance_root"/>
  <xs:complexType name="inheritant_container" xdb:SQLType="inheritance_container">
    <xs:sequence>
      <xs:element type="xs:decimal" name="N_OBJ_ADDRESS_ID"/>
      <xs:element type="xs:string" name="C_ACTIVE" xdb:SQLType="CHAR"/>
      <xs:element type="inheritance_root" name="inh_coll" xdb:SQLCollType="inheritance_list" minOccurs="0" maxOccurs="unbounded" xdb:maintainOrder="false" xdb:SQLInline="true"/>
    </xs:sequence>
  </xs:complexType>
  <xs:element type="inheritant_container" name="inheritance_container_serializer"/>
</xs:schema>', 
    GENTYPES  => TRUE,
    GENTABLES => FALSE,
    OWNER     => 'AIS_NET');
END;
/
desc "inheritance_container"
desc "inheritance_list"
desc "inheritance_root"


CREATE OR REPLACE TYPE "inheritance_root" FORCE AS OBJECT (
  "SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T")
NOT FINAL INSTANTIABLE;

CREATE OR REPLACE TYPE "inheritance_list" FORCE AS TABLE OF "inheritance_root" ;

CREATE OR REPLACE TYPE "inheritance_container" FORCE AS OBJECT (
  "SYS_XDBPD$"         "XDB"."XDB$RAW_LIST_T",
  "N_OBJ_ADDRESS_ID"   NUMBER,
  "C_ACTIVE"           CHAR(1),
  "inh_coll"           "inheritance_list")
NOT FINAL INSTANTIABLE;

CREATE OR REPLACE TYPE "inherited_type" UNDER "inheritance_root"(
  "user_id"                           NUMBER,
  "request_id"                        NUMBER,
  "object_id"                         NUMBER)
NOT FINAL INSTANTIABLE;


select DBMS_XMLSCHEMA.GENERATESCHEMA('AIS_NET', 'inheritance_container')
from dual


select DBMS_XMLSCHEMA.GENERATESCHEMA('AIS_NET', 'account_services')
from dual


DECLARE
  obj_Root        "inheritance_root";
  obj_Inherited   "inherited_type";
  obj_List        "inheritance_list";
  obj_Container   "inheritance_container";
  --
  clb_Message     CLOB;
  --
  obj_New         "inheritance_container";
  clb_NewMessage  CLOB;
BEGIN
  obj_Root := "inheritance_root"(NULL);
  
  -- 
  obj_Inherited :=  "inherited_type"(NULL, 12, 15, 28);
  -- 
  
  obj_List := "inheritance_list"(obj_Root, obj_Inherited);
  obj_Container := "inheritance_container"(null, 71101, 'Y', obj_List);
  
  clb_Message := XMLTYPE(
                   xmlData => obj_Container,
                   schema  => 'http://localhost/xdb/latera/hydra/billing/inheritantContainerSerializer.xsd',
                   element => 'inheritance_container_serializer').getClobVal();
  
  dbms_output.put_line(clb_Message);
  
  XMLTYPE(clb_Message).toObject(
                         object  => obj_New,
                         schema  => 'http://localhost/xdb/latera/hydra/billing/inheritantContainerSerializer.xsd',
                         element => 'inheritance_container_serializer');
  
  clb_NewMessage := XMLTYPE(
                      xmlData => obj_New,
                      schema  => 'http://localhost/xdb/latera/hydra/billing/inheritantContainerSerializer.xsd',
                      element => 'inheritance_container_serializer').getClobVal();
  
  IF clb_NewMessage = clb_Message THEN
    RAISE_APPLICATION_ERROR(-20101, 'same');
  ELSE
    dbms_output.put_line('message:');
    dbms_output.put_line(clb_Message);
    dbms_output.put_line('new message:');
    dbms_output.put_line(clb_NewMessage);
    
    RAISE_APPLICATION_ERROR(-20101, 'not same');
  END IF;
END;