This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Nov 29, 2010 2:12 PM by mdrake Go to original post RSS
  • 15. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    forgot. sorry :-(
  • 16. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    No problem. I postponed the whole topic a bit here in our company because we just cannot use the repository as planned until this is solved anyway. We wanted to save completely unstructed data and query it at will. We would have to create hundreds of individual indexes if we cannot use the value index. So, just let me know if you find anything on this, or if this works on 11.2
  • 17. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    But isn't better to go for the DBMS_XDBT method then...? If it is completely unstructured, an unstructured XMLIndex will only be a partial solution...
  • 18. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    No, because we don't want to search for words but also do exact comparisons (spaces and special characters included) and furthermore numeric comparisons, date comparisons etc. AFAIK this can't be done with Oracle Text. In fact, what we want to do is offer clients the possibility to store data on our server the structure of which we don't know beforehand. So we don't know for sure what data they want to query and how. We just cannot create any possible index and a fulltext index is not sufficient. This works great without a repository because there is a value index. The repository OTOH has numerous other advantages like foldering which would be extremely useful for us. For some reason the value index is never used. Though I can see one is created. Unfortunately the path table cannot be queried directly because Oracle is blocking this for some unknown reason. I'm considering recreating an XML index feature from scratch using triggers and PL/SQL which would be a lot of work though for a feature which is basically there :-(
  • 19. Re: XMLIndex Value Index on repository
    mdrake Expert
    Currently Being Moderated
    Development are looking at this ...

    Can you try the following in the mean time as a work around
      SELECT rowid 
        FROM resource_view
       where XMLCAST
                 (
                    XMLQUERY
                    (
                         'declare namespace r="http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                          /r:Resource/r:Contents/xml/properties/username'
                          passing res returning content 
                   ) as VARCHAR2(200) 
                )  = username
         AND UNDER_PATH(res,'/public/users')=1
    Another solution, based on the idea of putting all the XML in a single table would be to use a repository event to store the content in your own table, and leave the metadata in the repostiory. You could then write the query as a join between resource view and the content..

    A simple example of this can be seen here..

    set echo on
    spool outOfLineContent.log
    
    --
    connect / as sysdba
    --
    def USERNAME=EVENTS
    --
    def PASSWORD=&USERNAME
    --
    VAR UPLOAD_FOLDER  VARCHAR2(700);
    VAR TARGET_FOLDER  VARCHAR2(700);
    VAR RESCONFIG_PATH VARCHAR2(700);
    VAR LOGFILE_PATH   VARCHAR2(700);
    --
    begin
      :TARGET_FOLDER  := '/public/resConfigTest1';
      :RESCONFIG_PATH := :TARGET_FOLDER || '/ResConfig.xml';
      :LOGFILE_PATH   := :TARGET_FOLDER || '/logFile.txt';
      :UPLOAD_FOLDER  := :TARGET_FOLDER || '/uploadFolder';
    end;
    /
    select path, dbms_RESConfig.getResConfigPaths(path)
      from path_view
     where under_path(res,:TARGET_FOLDER) = 1
    /
    --
    ALTER SESSION SET XML_DB_EVENTS = DISABLE
    /
    --
    begin
      if (DBMS_XDB.EXISTSRESOURCE(:UPLOAD_FOLDER)) then
        DBMS_XDB.DELETERESOURCE(:UPLOAD_FOLDER,DBMS_XDB.DELETE_RECURSIVE);
      end if;
      commit;
    
      if (DBMS_XDB.EXISTSRESOURCE(:RESCONFIG_PATH)) then
        DBMS_XDB.DELETERESOURCE(:RESCONFIG_PATH);
      end if;
    
      if (DBMS_XDB.EXISTSRESOURCE(:TARGET_FOLDER)) then
        DBMS_XDB.DELETERESOURCE(:TARGET_FOLDER,DBMS_XDB.DELETE_RECURSIVE);
      end if;
    end;
    /
    --
    ALTER SESSION SET XML_DB_EVENTS = ENABLE
    /
    --
    select path, dbms_RESConfig.getResConfigPaths(path)
      from path_view
     where under_path(res,:TARGET_FOLDER) = 1
    /
    DROP USER &USERNAME CASCADE
    /
    grant connect, resource to &USERNAME identified by &PASSWORD
    /
    grant XDB_SET_INVOKER, ALTER SESSION to &USERNAME
    /
    connect &USERNAME/&PASSWORD
    --
    DEF LOGFILE_PATH = ""
    --
    column LOGFILE_PATH NEW_VALUE LOGFILE_PATH
    --
    select :LOGFILE_PATH LOGFILE_PATH 
      from dual
    /
    def RESCONFIG_PATH = ""
    --
    column RESCONFIG_PATH NEW_VALUE RESCONFIG_PATH
    --
    select :RESCONFIG_PATH RESCONFIG_PATH
      from DUAL
    /
    VAR RESCONFIG CLOB
    --
    begin
      :RESCONFIG :=
    '<ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd http://xmlns.oracle.com/xdb/XDBResConfig.xsd">
      <event-listeners set-invoker="true">
        <listener>
          <description>CUSTOM CONTENT STORE</description>
          <schema>&USERNAME</schema>
          <source>CUSTOM_CONTENT_EVENTS</source>
          <language>PL/SQL</language>
          <events>
            <Pre-Create/>
            <Pre-Delete/>
            <Render/>
          </events>
        </listener>
      </event-listeners>
      <defaultChildConfig>
        <configuration>
          <path>' || :RESCONFIG_PATH || '</path>
        </configuration>
      </defaultChildConfig>
    </ResConfig>';
    
    end;
    /
    set pages 0 lines 256 long 1000000
    --
    column RESCONFIG format A256
    --
    select :RESCONFIG RESCONFIG
      from DUAL
    /
    --
    create or replace package CUSTOM_CONTENT_MANAGER
    as
      function storeXML(P_XML_CONTENT XMLTYPE) return XMLType;
      function storeBinary(P_BINARY_CONTENT BLOB) return XMLType;
      function storeText(P_TEXT_CONTENT CLOB) return XMLType;
      function getXML(P_LOCATOR XMLTYPE) return XMLType;
      function getBinary(P_LOCATOR XMLTYPE) return BLOB;
      function getText(P_LOCATOR XMLTYPE) return CLOB;
      procedure deleteContent(P_LOCATOR XMLTYPE);
    end;
    /
    show errors
    --
    grant execute on CUSTOM_CONTENT_MANAGER to public
    /
    create or replace package CUSTOM_CONTENT_EVENTS
    as
      BINARY_CONTENT_SCHEMA    constant varchar2(700)   := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary';
      TEXT_CONTENT_SCHEMA     constant varchar2(700)   := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#text';
      procedure handlePreCreate(P_EVENT dbms_xevent.XDBRepositoryEvent);
      procedure handlePreDelete(P_EVENT dbms_xevent.XDBRepositoryEvent);
      procedure handleRender(P_EVENT dbms_xevent.XDBRepositoryEvent);
    end;
    /
    --
    show errors
    --
    grant execute on CUSTOM_CONTENT_EVENTS to public
    /
    create or replace package body CUSTOM_CONTENT_EVENTS
    as
    --
    procedure updateEventLog(P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      pragma autonomous_transaction;
        
      V_BINARY_CONTENT   BLOB;
      V_EXISTING_CONTENT BLOB;
    
      V_SOURCE_OFFSET    integer := 1;
      V_TARGET_OFFSET    integer := 1;
      V_WARNING          integer;
      V_LANG_CONTEXT     integer := 0;
    
    begin
         
         update RESOURCE_VIEW
         set RES = updateXML(RES,'/Resource/DisplayName/text()',extractValue(RES,'/Resource/DisplayName/text()'))
       where equals_path(RES,'&LOGFILE_PATH') = 1;
       
      select extractValue(res,'/Resource/XMLLob') 
        into V_EXISTING_CONTENT
        from RESOURCE_VIEW 
       where equals_path(res,'&LOGFILE_PATH') = 1
         for update;
      
      dbms_lob.createTemporary(V_BINARY_CONTENT,true);
      dbms_lob.convertToBlob(V_BINARY_CONTENT,P_EVENT_LOG_ENTRY,dbms_lob.getLength(P_EVENT_LOG_ENTRY),V_SOURCE_OFFSET,V_TARGET_OFFSET,nls_charset_id('AL32UTF8'),V_LANG_CONTEXT,V_WARNING);
      dbms_lob.open(V_EXISTING_CONTENT,dbms_lob.lob_readwrite);
      dbms_lob.append(V_EXISTING_CONTENT,V_BINARY_CONTENT);
      dbms_lob.close(V_EXISTING_CONTENT);
      dbms_lob.freeTemporary(V_BINARY_CONTENT);
      commit;
    
      dbms_lob.freeTemporary(P_EVENT_LOG_ENTRY);
      dbms_lob.createTemporary(P_EVENT_LOG_ENTRY,TRUE);
    
    end;
    --
    procedure logException(V_RESOURCE_PATH VARCHAR2, P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      V_STACK_TRACE XMLType;
      V_BUFFER      VARCHAR2(32000);
    begin
    
      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Exception caught while processing "' || V_RESOURCE_PATH || '".';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      select xmlElement
             (
               "Error",
               xmlElement
               (
                 "Stack",
                 xmlCDATA(DBMS_UTILITY.FORMAT_ERROR_STACK())
               ),
               xmlElement
               (
                 "BackTrace",
                 xmlCDATA(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE())
               )
             )
        into V_STACK_TRACE
        from DUAL;
          
      V_BUFFER := CHR(13) || CHR(10);
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
        
      select XMLSERIALIZE(DOCUMENT V_STACK_TRACE AS CLOB INDENT SIZE = 2)
        into V_BUFFER 
        from DUAL;
               
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      updateEventLog(P_EVENT_LOG_ENTRY);  
      DBMS_LOB.FREETEMPORARY(P_EVENT_LOG_ENTRY);
    end;
    --
    function makeBinary(P_TEXT_CONTENT CLOB, P_CSID BINARY_INTEGER) 
    return BLOB
    as
      V_BINARY_CONTENT    BLOB;
      V_SOURCE_OFFSET     integer := 1;
      V_TARGET_OFFSET     integer := 1;
      V_WARNING           integer;
      V_LANG_CONTEXT      integer := 0;
    begin
      dbms_lob.createTemporary(V_BINARY_CONTENT,true);
      dbms_lob.convertToBlob(V_BINARY_CONTENT,P_TEXT_CONTENT,dbms_lob.getLength(P_TEXT_CONTENT),V_SOURCE_OFFSET,V_TARGET_OFFSET,P_CSID,V_LANG_CONTEXT,V_WARNING);
      return V_BINARY_CONTENT;
    end;
    --
    function makeText(P_XML_CONTENT XMLTYPE)
    return CLOB
    as
      V_TEXT_CONTENT CLOB;
    begin
      select XMLSERIALIZE(DOCUMENT P_XML_CONTENT AS CLOB) 
        into V_TEXT_CONTENT
        from dual;
       
      return V_TEXT_CONTENT;
    end;
    --
    function makeBinary(P_XML_CONTENT XMLTYPE, P_CSID BINARY_INTEGER)
    return BLOB
    as
    begin
      return makeBinary(makeText(P_XML_CONTENT),P_CSID);
    end;
    --
    procedure storeXMLContent(P_RESOURCE IN OUT DBMS_XDBRESOURCE.xdbResource, P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      V_BUFFER            VARCHAR2(32000);    
      V_XML_CONTENT       XMLType;
      V_LOCATOR           XMLType;
    begin
      V_BUFFER := ' Processing XML Content.';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      V_XML_CONTENT :=  DBMS_XDBRESOURCE.getContentXML(P_RESOURCE);
    
      if (V_XML_CONTENT is null) then
        V_BUFFER := ' XML Content is null.';
        DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      else
        DBMS_XDBRESOURCE.SETCONTENT(P_RESOURCE,CUSTOM_CONTENT_MANAGER.storeXML(V_XML_CONTENT));
      end if;
    end;
    --
    procedure storeBinaryContent(P_RESOURCE IN OUT DBMS_XDBRESOURCE.xdbResource, P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      V_BUFFER            VARCHAR2(32000);    
      V_BINARY_CONTENT    BLOB;
      V_CSID              BINARY_INTEGER;
    begin
      V_BUFFER := ' Processing Binary Content.';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      V_BINARY_CONTENT :=  DBMS_XDBRESOURCE.getContentBLOB(P_RESOURCE,V_CSID);
    
      V_BUFFER := ' CSID = "' || V_CSID || '".';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      if (V_BINARY_CONTENT is null) then
        V_BUFFER := ' BLOB Content is null.';
        DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      else
        V_BINARY_CONTENT := makeBinary(CUSTOM_CONTENT_MANAGER.storeBinary(V_BINARY_CONTENT),V_CSID);
        DBMS_XDBRESOURCE.SETCONTENT(P_RESOURCE,V_BINARY_CONTENT,V_CSID);
      end if;
    end;
    --
    procedure storeTextContent(P_RESOURCE IN OUT DBMS_XDBRESOURCE.xdbResource, P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      V_BUFFER            VARCHAR2(32000);    
      V_TEXT_CONTENT      CLOB;
      V_ROWID             ROWID;
    begin
      V_BUFFER := ' Processing Text Content.';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      V_TEXT_CONTENT :=  DBMS_XDBRESOURCE.getContentCLOB(P_RESOURCE);
      if (V_TEXT_CONTENT is null) then
        V_BUFFER := ' CLOB Content is null.';
        DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      else
        V_TEXT_CONTENT := makeText(CUSTOM_CONTENT_MANAGER.storeText(V_TEXT_CONTENT));
        DBMS_XDBRESOURCE.SETCONTENT(P_RESOURCE,V_TEXT_CONTENT);
      end if;
    end;
    --
    procedure handlePreCreate(P_EVENT dbms_xevent.XDBRepositoryEvent)
    as
      V_RESOURCE_PATH     VARCHAR2(700);
      V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
      V_RESOURCE_DOCUMENT DBMS_XMLDOM.DOMDocument;
    
      V_BUFFER            VARCHAR2(32000);    
      V_EVENT_LOG_ENTRY   CLOB;
      
      V_NODE_LIST         DBMS_XMLDOM.DOMNodeList;
      V_SCHEMA_ELEMENT    VARCHAR2(1024);
      
      V_BLOB_CONTENT      BLOB;
       
    begin
    
      V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));  
    
         DBMS_LOB.CREATETEMPORARY(V_EVENT_LOG_ENTRY,TRUE);
      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Fired Pre Create Event for "' || V_RESOURCE_PATH || '".';
      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
      V_RESOURCE_DOCUMENT := DBMS_XDBRESOURCE.makeDocument(V_RESOURCE);
    
      V_NODE_LIST         := DBMS_XSLPROCESSOR.selectNodes(DBMS_XMLDOM.makeNode(V_RESOURCE_DOCUMENT),'/r:Resource/r:SchemaElement','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"');
    
      if (DBMS_XMLDOM.getLength(V_NODE_LIST) > 0) then 
        -- Non XML Conent
        V_SCHEMA_ELEMENT    := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.item(V_NODE_LIST,0)));
        V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : SchemaElement = "' || V_SCHEMA_ELEMENT || '".';
        DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
        
        if (V_SCHEMA_ELEMENT = BINARY_CONTENT_SCHEMA) then
          storeBinaryContent(V_RESOURCE, V_EVENT_LOG_ENTRY);
        end if;
        
        if (V_SCHEMA_ELEMENT = TEXT_CONTENT_SCHEMA) then
          storeTextContent(V_RESOURCE, V_EVENT_LOG_ENTRY);
        end if;
      else
        -- Non Schema Based XML
        storeXMLContent(V_RESOURCE, V_EVENT_LOG_ENTRY);
      end if;
    
      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Completed Pre Create Event for "' || V_RESOURCE_PATH || '".';
      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      updateEventLog(V_EVENT_LOG_ENTRY);  
      DBMS_LOB.FREETEMPORARY(V_EVENT_LOG_ENTRY);
    
    exception
      when others then
        logException(V_RESOURCE_PATH, V_EVENT_LOG_ENTRY);
        raise;
    end;   
    --
    procedure renderXMLContent(P_EVENT dbms_xevent.XDBRepositoryEvent, P_LOCATOR XMLTYPE, P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      V_BUFFER            VARCHAR2(32000);    
      V_XML_CONTENT       XMLType;
    begin
      V_BUFFER := ' Processing XML Content';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      V_XML_CONTENT := CUSTOM_CONTENT_MANAGER.getXML(P_LOCATOR);     
         -- DBMS_XEVENT.setRenderStream(P_EVENT,V_XML_CONTENT);
    end;
    --
    procedure renderBinaryContent(P_EVENT dbms_xevent.XDBRepositoryEvent, P_LOCATOR XMLTYPE, P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      V_BUFFER            VARCHAR2(32000);    
      V_BINARY_CONTENT    BLOB;
    begin
      V_BUFFER := ' Processing Binary Content';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      V_BINARY_CONTENT := CUSTOM_CONTENT_MANAGER.getBinary(P_LOCATOR);
         DBMS_XEVENT.setRenderStream(P_EVENT,V_BINARY_CONTENT);
    end;
    --
    procedure renderTextContent(P_EVENT dbms_xevent.XDBRepositoryEvent, P_LOCATOR XMLTYPE, P_EVENT_LOG_ENTRY IN OUT CLOB)
    as
      V_BUFFER            VARCHAR2(32000);    
      V_TEXT_CONTENT      CLOB;
      V_ROWID             ROWID;
    begin
      V_BUFFER := ' Processing Text Content';
      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      V_TEXT_CONTENT := CUSTOM_CONTENT_MANAGER.getText(P_LOCATOR);     
      -- DBMS_XEVENT.setRenderStream(P_EVENT,V_TEXT_CONTENT);
    end;
    --
    procedure handleRender(P_EVENT dbms_xevent.XDBRepositoryEvent)
    as
      V_RESOURCE_PATH     VARCHAR2(700);
      V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
      V_RESOURCE_DOCUMENT DBMS_XMLDOM.DOMDocument;
    
      V_BUFFER            VARCHAR2(32000);    
      V_EVENT_LOG_ENTRY   CLOB;
      
      V_NODE_LIST         DBMS_XMLDOM.DOMNodeList;
      V_SCHEMA_ELEMENT    VARCHAR2(1024);
      
      V_CSID              BINARY_INTEGER;
       
    begin
    
      V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));  
    
         DBMS_LOB.CREATETEMPORARY(V_EVENT_LOG_ENTRY,TRUE);
      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Fired Render Event for "' || V_RESOURCE_PATH || '".';
      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
      V_RESOURCE_DOCUMENT := DBMS_XDBRESOURCE.makeDocument(V_RESOURCE);
    
      V_NODE_LIST         := DBMS_XSLPROCESSOR.selectNodes(DBMS_XMLDOM.makeNode(V_RESOURCE_DOCUMENT),'/r:Resource/r:SchemaElement','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"');
    
      if (DBMS_XMLDOM.getLength(V_NODE_LIST) > 0) then 
        -- Non XML Conent
        V_SCHEMA_ELEMENT    := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.item(V_NODE_LIST,0)));
        V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : SchemaElement = "' || V_SCHEMA_ELEMENT || '".';
        DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
        
        if (V_SCHEMA_ELEMENT = BINARY_CONTENT_SCHEMA) then
          renderBinaryContent(P_EVENT, XMLType(DBMS_XDBRESOURCE.getContentBLOB(V_RESOURCE,V_CSID),V_CSID),V_EVENT_LOG_ENTRY);
        end if;
        
        if (V_SCHEMA_ELEMENT = TEXT_CONTENT_SCHEMA) then
          -- renderTextContent(P_EVENT, XMLType(DBMS_XDBRESOURCE.getContentCLOB(V_RESOURCE)),V_EVENT_LOG_ENTRY);
          renderBinaryContent(P_EVENT, XMLType(DBMS_XDBRESOURCE.getContentBLOB(V_RESOURCE,V_CSID),V_CSID),V_EVENT_LOG_ENTRY);
        end if;
      else
        -- Non Schema Based XML
        renderXMLContent(P_EVENT, DBMS_XDBRESOURCE.getContentXML(V_RESOURCE), V_EVENT_LOG_ENTRY);
      end if;
    
      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Completed Render Event for "' || V_RESOURCE_PATH || '".';
      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      updateEventLog(V_EVENT_LOG_ENTRY);  
      DBMS_LOB.FREETEMPORARY(V_EVENT_LOG_ENTRY);
    
    exception
      when others then
        logException(V_RESOURCE_PATH, V_EVENT_LOG_ENTRY);
        raise;
    end;   
    --
    procedure handlePreDelete(P_EVENT dbms_xevent.XDBRepositoryEvent)
    as
      V_RESOURCE_PATH     VARCHAR2(700);
      V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
      V_RESOURCE_DOCUMENT DBMS_XMLDOM.DOMDocument;
    
      V_BUFFER            VARCHAR2(32000);    
      V_EVENT_LOG_ENTRY   CLOB;
      
      V_NODE_LIST         DBMS_XMLDOM.DOMNodeList;
      V_SCHEMA_ELEMENT    VARCHAR2(1024);
      
      V_CSID              BINARY_INTEGER;
      V_LOCATOR           XMLTYPE := NULL;
      V_ROWID             ROWID;
      
    begin
    
      V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));  
    
         DBMS_LOB.CREATETEMPORARY(V_EVENT_LOG_ENTRY,TRUE);
      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Fired PreDelete Event for "' || V_RESOURCE_PATH || '".';
      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    
      V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
      V_RESOURCE_DOCUMENT := DBMS_XDBRESOURCE.makeDocument(V_RESOURCE);
    
      V_NODE_LIST         := DBMS_XSLPROCESSOR.selectNodes(DBMS_XMLDOM.makeNode(V_RESOURCE_DOCUMENT),'/r:Resource/r:SchemaElement','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"');
    
      if (DBMS_XMLDOM.getLength(V_NODE_LIST) > 0) then 
        -- Non XML Conent
        V_SCHEMA_ELEMENT    := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.item(V_NODE_LIST,0)));
        V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : SchemaElement = "' || V_SCHEMA_ELEMENT || '".';
        DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
        
        if (V_SCHEMA_ELEMENT = BINARY_CONTENT_SCHEMA) then
          V_LOCATOR := xmlType(DBMS_XDBRESOURCE.getContentBLOB(V_RESOURCE,V_CSID),V_CSID);
        end if;
        
        if (V_SCHEMA_ELEMENT = TEXT_CONTENT_SCHEMA) then
          V_LOCATOR :=  xmlType(DBMS_XDBRESOURCE.getContentCLOB(V_RESOURCE));
        end if;
      else
        -- Non Schema Based XML
        V_LOCATOR := DBMS_XDBRESOURCE.getContentXML(V_RESOURCE);
      end if;
      
      if (V_LOCATOR is not null) then
        CUSTOM_CONTENT_MANAGER.deleteContent(V_LOCATOR);
      end if;
      
      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Completed PreDelete Event for "' || V_RESOURCE_PATH || '".';
      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
      updateEventLog(V_EVENT_LOG_ENTRY);  
      DBMS_LOB.FREETEMPORARY(V_EVENT_LOG_ENTRY);
    
    exception
      when others then
        logException(V_RESOURCE_PATH, V_EVENT_LOG_ENTRY);
        raise;
    end;   
    --
    end;
    /
    show errors
    --
    create table CUSTOM_CONTENT_TABLE
    (
       XML_CONTENT    XMLTYPE,
       TEXT_CONTENT   CLOB,
       BINARY_CONTENT BLOB
    )
    LOB (TEXT_CONTENT) STORE AS SECUREFILE,
    LOB (BINARY_CONTENT) STORE AS SECUREFILE,
    XMLTYPE COLUMN XML_CONTENT STORE AS SECUREFILE BINARY XML
    /
    create or replace package body CUSTOM_CONTENT_MANAGER
    as
    --
    function newLocator(P_ROWID ROWID, P_COLUMN_NAME VARCHAR2)
    return xmltype
    as
      V_LOCATOR XMLTYPE;
    begin
      select xmlElement
             (
               "locator",
                xmlAttributes('http:/xmlns.example.com/xdb/pm/contentLocator' as "xmlns"),
                xmlElement("SCHEMA",'&USERNAME'),
                xmlElement("TABLE",'CUSTOM_CONTENT_TABLE'),
                xmlElement("COLUMN",P_COLUMN_NAME),
                xmlElement("ROWID",P_ROWID)
               )
          into V_LOCATOR
        from dual;
    
         return V_LOCATOR;
    end;
    --
    function getROWID(P_LOCATOR XMLTYPE)
    return ROWID
    as
      V_ROWID ROWID;
    begin
      select XMLCAST 
                (
                   XMLQUERY
                   (
                     'declare default element namespace "http:/xmlns.example.com/xdb/pm/contentLocator"; (: :)
                     $L/locator/ROWID'
                     passing P_LOCATOR as "L" returning CONTENT
                   )
                   as VARCHAR2(32)
                )
           into V_ROWID
           from DUAL;
          
         return V_ROWID;
    end;
    --
    function storeXML(P_XML_CONTENT XMLType)
    return XMLTYPE
    as
      V_ROWID ROWID;
    begin
      insert into CUSTOM_CONTENT_TABLE (XML_CONTENT) values (P_XML_CONTENT) returning ROWID into V_ROWID;
      return newLocator(V_ROWID,'XML_CONTENT');
    end;
    --
    function storeBinary(P_BINARY_CONTENT BLOB)
    return XMLTYPE
    as
      V_ROWID ROWID;
    begin
      insert into CUSTOM_CONTENT_TABLE (BINARY_CONTENT) values (P_BINARY_CONTENT) returning ROWID into V_ROWID;
      return newLocator(V_ROWID,'BINARY_CONTENT');
    end;
    --
    function storeText(P_TEXT_CONTENT CLOB)
    return XMLTYPE
    as
      V_ROWID ROWID;
    begin
      insert into CUSTOM_CONTENT_TABLE (TEXT_CONTENT) values (P_TEXT_CONTENT) returning ROWID into V_ROWID;
      return newLocator(V_ROWID,'TEXT_CONTENT');
    end;
    --
    function getXML(P_LOCATOR XMLTYPE) 
    return XMLType
    as
      V_XML_CONTENT XMLTYPE;
      V_ROWID ROWID;
    begin
         V_ROWID := getROWID(P_LOCATOR);
         select XML_CONTENT
           into V_XML_CONTENT
           from CUSTOM_CONTENT_TABLE
          where ROWID = V_ROWID;
         return V_XML_CONTENT;
    end;
    --
    function getBinary(P_LOCATOR XMLTYPE) 
    return BLOB
    as
      V_BINARY_CONTENT BLOB;
      V_ROWID ROWID;
    begin
         V_ROWID := getROWID(P_LOCATOR);
         select BINARY_CONTENT
           into V_BINARY_CONTENT
           from CUSTOM_CONTENT_TABLE
          where ROWID = V_ROWID;
         return V_BINARY_CONTENT;
    end;
    --
    function getText(P_LOCATOR XMLTYPE) 
    return CLOB
    as
      V_TEXT_CONTENT CLOB;
      V_ROWID ROWID;
    begin
         V_ROWID := getROWID(P_LOCATOR);
         select TEXT_CONTENT
           into V_TEXT_CONTENT
           from CUSTOM_CONTENT_TABLE
          where ROWID = V_ROWID;
      return V_TEXT_CONTENT;
    end;
    --
    procedure deleteContent(P_LOCATOR XMLType)
    as
      V_ROWID ROWID;
    begin
         V_ROWID := getROWID(P_LOCATOR);
      delete 
        from CUSTOM_CONTENT_TABLE
          where ROWID = V_ROWID;
    end;
    --
    end;
    /
    show errors
    --
    grant execute on CUSTOM_CONTENT_EVENTS to public
    /
    declare
      V_RESULT BOOLEAN;
    begin
      V_RESULT := DBMS_XDB.CREATEFOLDER(:TARGET_FOLDER);
      V_RESULT := DBMS_XDB.CREATEFOLDER(:UPLOAD_FOLDER);
      -- V_RESULT := DBMS_XDB.CREATERESOURCE(:RESCONFIG_PATH,XMLTYPE(:RESCONFIG));
      V_RESULT := DBMS_XDB.CREATERESOURCE(:RESCONFIG_PATH,:RESCONFIG);
      V_RESULT := DBMS_XDB.CREATERESOURCE(:LOGFILE_PATH, to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF')  || ' : Log File Created');
      COMMIT;
      
      dbms_resconfig.addResConfig(:UPLOAD_FOLDER,:RESCONFIG_PATH,null);
    end;
    /
    select XMLSERIALIZE(DOCUMENT XDBURITYPE(:RESCONFIG_PATH).getXML() as CLOB INDENT SIZE = 2)  RESCONFIG
      from DUAL
    /
    select path, dbms_RESConfig.getResConfigPaths(path)
      from path_view
     where under_path(res,:TARGET_FOLDER) = 1
    /
    set long 100000 pages 0 lines 256 
    column LOG format A250
    --
    select xdburitype(:LOGFILE_PATH).getClob() LOG
      from dual
    /
    declare
      V_RESULT BOOLEAN;
      V_XMLDOC_PATH VARCHAR2(700) := :UPLOAD_FOLDER || '/test2.xml';
    begin
      V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,XMLTYPE('<Hello>World</Hello>'));
      COMMIT;
    end;
    /
    declare
      V_RESULT BOOLEAN;
      V_XMLDOC_PATH VARCHAR2(700) := :UPLOAD_FOLDER || '/test2.txt';
    begin
      V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,'Hello World');
      COMMIT;
    end;
    /
    declare
      V_RESULT      BOOLEAN;
      V_XMLDOC_PATH VARCHAR2(700) := :UPLOAD_FOLDER || '/test2.bin';
    begin
      V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,HEXTORAW('AABBCCDDEEFF'));
      COMMIT;
    end;
    /
    select xdburitype(:LOGFILE_PATH).getClob() LOG
      from dual
    /
    select xmlserialize(DOCUMENT RES as CLOB INDENT SIZE = 2)
      from RESOURCE_VIEW
     where equals_path(RES,:UPLOAD_FOLDER || '/test2.xml') = 1
    /
    select xmlserialize(DOCUMENT RES as CLOB INDENT SIZE = 2)
      from RESOURCE_VIEW
     where equals_path(RES,:UPLOAD_FOLDER || '/test2.txt') = 1
    /
    select xmlserialize(DOCUMENT RES as CLOB INDENT SIZE = 2)
      from RESOURCE_VIEW
     where equals_path(RES,:UPLOAD_FOLDER || '/test2.bin') = 1
    /
    select xdburitype(:UPLOAD_FOLDER || '/test2.txt').getClob()
      from DUAL
    /
    select xdburitype(:UPLOAD_FOLDER || '/test2.bin').getBlob()
      from DUAL
    /
    select xdburitype(:LOGFILE_PATH).getClob() LOG
      from dual
    /
    select path, dbms_RESConfig.getResConfigPaths(path)
      from path_view
     where under_path(res,:TARGET_FOLDER) = 1
    /
    select count(*) 
      from CUSTOM_CONTENT_TABLE
    /
    call dbms_xdb.deleteResource(:UPLOAD_FOLDER || '/test2.txt')
    /
    commit
    /
    select path, dbms_RESConfig.getResConfigPaths(path)
      from path_view
     where under_path(res,:TARGET_FOLDER) = 1
    /
    select count(*) 
      from CUSTOM_CONTENT_TABLE
    /
    select xdburitype(:LOGFILE_PATH).getClob() LOG
      from dual
    /
    --
    quit
  • 20. Re: XMLIndex Value Index on repository
    mdrake Expert
    Currently Being Moderated
    Here it is running
    C:\xdb\examples\Events>sqlplus /nolog @outOfLineContent
    
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 25 07:48:09 2010
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    SQL> spool outOfLineContent.log
    SQL>
    SQL> --
    SQL> connect / as sysdba
    Connected.
    SQL> --
    SQL> def USERNAME=EVENTS
    SQL> --
    SQL> def PASSWORD=&USERNAME
    SQL> --
    SQL> VAR UPLOAD_FOLDER  VARCHAR2(700);
    SQL> VAR TARGET_FOLDER  VARCHAR2(700);
    SQL> VAR RESCONFIG_PATH VARCHAR2(700);
    SQL> VAR LOGFILE_PATH   VARCHAR2(700);
    SQL> --
    SQL> begin
      2    :TARGET_FOLDER  := '/public/resConfigTest1';
      3    :RESCONFIG_PATH := :TARGET_FOLDER || '/ResConfig.xml';
      4    :LOGFILE_PATH   := :TARGET_FOLDER || '/logFile.txt';
      5    :UPLOAD_FOLDER  := :TARGET_FOLDER || '/uploadFolder';
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    
    no rows selected
    
    SQL> --
    SQL> ALTER SESSION SET XML_DB_EVENTS = DISABLE
      2  /
    
    Session altered.
    
    SQL> --
    SQL> begin
      2    if (DBMS_XDB.EXISTSRESOURCE(:UPLOAD_FOLDER)) then
      3      DBMS_XDB.DELETERESOURCE(:UPLOAD_FOLDER,DBMS_XDB.DELETE_RECURSIVE);
      4    end if;
      5    commit;
      6
      7    if (DBMS_XDB.EXISTSRESOURCE(:RESCONFIG_PATH)) then
      8      DBMS_XDB.DELETERESOURCE(:RESCONFIG_PATH);
      9    end if;
     10
     11    if (DBMS_XDB.EXISTSRESOURCE(:TARGET_FOLDER)) then
     12      DBMS_XDB.DELETERESOURCE(:TARGET_FOLDER,DBMS_XDB.DELETE_RECURSIVE);
     13    end if;
     14  end;
     15  /
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> ALTER SESSION SET XML_DB_EVENTS = ENABLE
      2  /
    
    Session altered.
    
    SQL> --
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    
    no rows selected
    
    SQL> DROP USER &USERNAME CASCADE
      2  /
    old   1: DROP USER &USERNAME CASCADE
    new   1: DROP USER EVENTS CASCADE
    DROP USER EVENTS CASCADE
              *
    ERROR at line 1:
    ORA-01918: user 'EVENTS' does not exist
    
    
    SQL> grant connect, resource to &USERNAME identified by &PASSWORD
      2  /
    old   1: grant connect, resource to &USERNAME identified by &PASSWORD
    new   1: grant connect, resource to EVENTS identified by EVENTS
    
    Grant succeeded.
    
    SQL> grant XDB_SET_INVOKER, ALTER SESSION to &USERNAME
      2  /
    old   1: grant XDB_SET_INVOKER, ALTER SESSION to &USERNAME
    new   1: grant XDB_SET_INVOKER, ALTER SESSION to EVENTS
    
    Grant succeeded.
    
    SQL> connect &USERNAME/&PASSWORD
    Connected.
    SQL> --
    SQL> DEF LOGFILE_PATH = ""
    SQL> --
    SQL> column LOGFILE_PATH NEW_VALUE LOGFILE_PATH
    SQL> --
    SQL> select :LOGFILE_PATH LOGFILE_PATH
      2    from dual
      3  /
    
    LOGFILE_PATH
    --------------------------------------------------------------------------------
    /public/resConfigTest1/logFile.txt
    
    SQL> def RESCONFIG_PATH = ""
    SQL> --
    SQL> column RESCONFIG_PATH NEW_VALUE RESCONFIG_PATH
    SQL> --
    SQL> select :RESCONFIG_PATH RESCONFIG_PATH
      2    from DUAL
      3  /
    
    RESCONFIG_PATH
    --------------------------------------------------------------------------------
    /public/resConfigTest1/ResConfig.xml
    
    SQL> VAR RESCONFIG CLOB
    SQL> --
    SQL> begin
      2    :RESCONFIG :=
      3  '<ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd http://xmlns.oracle.com/xd
      4    <event-listeners set-invoker="true">
      5      <listener>
      6        <description>CUSTOM CONTENT STORE</description>
      7        <schema>&USERNAME</schema>
      8        <source>CUSTOM_CONTENT_EVENTS</source>
      9        <language>PL/SQL</language>
     10        <events>
     11          <Pre-Create/>
     12          <Pre-Delete/>
     13          <Render/>
     14        </events>
     15      </listener>
     16    </event-listeners>
     17    <defaultChildConfig>
     18      <configuration>
     19        <path>' || :RESCONFIG_PATH || '</path>
     20      </configuration>
     21    </defaultChildConfig>
     22  </ResConfig>';
     23
     24  end;
     25  /
    old   7:       <schema>&USERNAME</schema>
    new   7:       <schema>EVENTS</schema>
    
    PL/SQL procedure successfully completed.
    
    SQL> set pages 0 lines 256 long 1000000
    SQL> --
    SQL> column RESCONFIG format A256
    SQL> --
    SQL> select :RESCONFIG RESCONFIG
      2    from DUAL
      3  /
    <ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd http://xmlns.oracle.com/xdb/XDBR
      <event-listeners set-invoker="true">
        <listener>
          <description>CUSTOM CONTENT STORE</description>
          <schema>EVENTS</schema>
          <source>CUSTOM_CONTENT_EVENTS</source>
          <language>PL/SQL</language>
          <events>
            <Pre-Create/>
            <Pre-Delete/>
            <Render/>
          </events>
        </listener>
      </event-listeners>
      <defaultChildConfig>
        <configuration>
          <path>/public/resConfigTest1/ResConfig.xml</path>
        </configuration>
      </defaultChildConfig>
    </ResConfig>
    
    
    SQL> --
    SQL> create or replace package CUSTOM_CONTENT_MANAGER
      2  as
      3    function storeXML(P_XML_CONTENT XMLTYPE) return XMLType;
      4    function storeBinary(P_BINARY_CONTENT BLOB) return XMLType;
      5    function storeText(P_TEXT_CONTENT CLOB) return XMLType;
      6    function getXML(P_LOCATOR XMLTYPE) return XMLType;
      7    function getBinary(P_LOCATOR XMLTYPE) return BLOB;
      8    function getText(P_LOCATOR XMLTYPE) return CLOB;
      9    procedure deleteContent(P_LOCATOR XMLTYPE);
     10  end;
     11  /
    
    Package created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> grant execute on CUSTOM_CONTENT_MANAGER to public
      2  /
    
    Grant succeeded.
    
    SQL> create or replace package CUSTOM_CONTENT_EVENTS
      2  as
      3    BINARY_CONTENT_SCHEMA    constant varchar2(700)   := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary';
      4    TEXT_CONTENT_SCHEMA        constant varchar2(700)        := 'http://xmlns.oracle.com/xdb/XDBSchema.xsd#text';
      5    procedure handlePreCreate(P_EVENT dbms_xevent.XDBRepositoryEvent);
      6    procedure handlePreDelete(P_EVENT dbms_xevent.XDBRepositoryEvent);
      7    procedure handleRender(P_EVENT dbms_xevent.XDBRepositoryEvent);
      8  end;
      9  /
    
    Package created.
    
    SQL> --
    SQL> show errors
    No errors.
    SQL> --
    SQL> grant execute on CUSTOM_CONTENT_EVENTS to public
      2  /
    
    Grant succeeded.
    
    SQL> create or replace package body CUSTOM_CONTENT_EVENTS
      2  as
      3  --
      4  procedure updateEventLog(P_EVENT_LOG_ENTRY IN OUT CLOB)
      5  as
      6    pragma autonomous_transaction;
      7
      8    V_BINARY_CONTENT   BLOB;
      9    V_EXISTING_CONTENT BLOB;
     10
     11    V_SOURCE_OFFSET    integer := 1;
     12    V_TARGET_OFFSET    integer := 1;
     13    V_WARNING          integer;
     14    V_LANG_CONTEXT     integer := 0;
     15
     16  begin
     17
     18          update RESOURCE_VIEW
     19       set RES = updateXML(RES,'/Resource/DisplayName/text()',extractValue(RES,'/Resource/DisplayName/text()'))
     20     where equals_path(RES,'&LOGFILE_PATH') = 1;
     21
     22    select extractValue(res,'/Resource/XMLLob')
     23      into V_EXISTING_CONTENT
     24      from RESOURCE_VIEW
     25     where equals_path(res,'&LOGFILE_PATH') = 1
     26       for update;
     27
     28    dbms_lob.createTemporary(V_BINARY_CONTENT,true);
     29    dbms_lob.convertToBlob(V_BINARY_CONTENT,P_EVENT_LOG_ENTRY,dbms_lob.getLength(P_EVENT_LOG_ENTRY),V_SOURCE_OFFSET,V_TARGET_OFFSET,nls_charset_id('AL32UTF8'),V_LANG_CONTEXT,V_WARNING);
     30    dbms_lob.open(V_EXISTING_CONTENT,dbms_lob.lob_readwrite);
     31    dbms_lob.append(V_EXISTING_CONTENT,V_BINARY_CONTENT);
     32    dbms_lob.close(V_EXISTING_CONTENT);
     33    dbms_lob.freeTemporary(V_BINARY_CONTENT);
     34    commit;
     35
     36    dbms_lob.freeTemporary(P_EVENT_LOG_ENTRY);
     37    dbms_lob.createTemporary(P_EVENT_LOG_ENTRY,TRUE);
     38
     39  end;
     40  --
     41  procedure logException(V_RESOURCE_PATH VARCHAR2, P_EVENT_LOG_ENTRY IN OUT CLOB)
     42  as
     43    V_STACK_TRACE XMLType;
     44    V_BUFFER VARCHAR2(32000);
     45  begin
     46
     47    V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Exception caught while processing "' || V_RESOURCE_PATH || '".';
     48    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
     49
     50    select xmlElement
     51           (
     52             "Error",
     53             xmlElement
     54             (
     55               "Stack",
     56               xmlCDATA(DBMS_UTILITY.FORMAT_ERROR_STACK())
     57             ),
     58             xmlElement
     59             (
     60               "BackTrace",
     61               xmlCDATA(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE())
     62             )
     63           )
     64      into V_STACK_TRACE
     65      from DUAL;
     66
     67    V_BUFFER := CHR(13) || CHR(10);
     68    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
     69
     70    select XMLSERIALIZE(DOCUMENT V_STACK_TRACE AS CLOB INDENT SIZE = 2)
     71      into V_BUFFER
     72      from DUAL;
     73
     74    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
     75    updateEventLog(P_EVENT_LOG_ENTRY);
     76    DBMS_LOB.FREETEMPORARY(P_EVENT_LOG_ENTRY);
     77  end;
     78  --
     79  function makeBinary(P_TEXT_CONTENT CLOB, P_CSID BINARY_INTEGER)
     80  return BLOB
     81  as
     82    V_BINARY_CONTENT    BLOB;
     83    V_SOURCE_OFFSET     integer := 1;
     84    V_TARGET_OFFSET     integer := 1;
     85    V_WARNING           integer;
     86    V_LANG_CONTEXT      integer := 0;
     87  begin
     88    dbms_lob.createTemporary(V_BINARY_CONTENT,true);
     89    dbms_lob.convertToBlob(V_BINARY_CONTENT,P_TEXT_CONTENT,dbms_lob.getLength(P_TEXT_CONTENT),V_SOURCE_OFFSET,V_TARGET_OFFSET,P_CSID,V_LANG_CONTEXT,V_WARNING);
     90    return V_BINARY_CONTENT;
     91  end;
     92  --
     93  function makeText(P_XML_CONTENT XMLTYPE)
     94  return CLOB
     95  as
     96    V_TEXT_CONTENT CLOB;
     97  begin
     98    select XMLSERIALIZE(DOCUMENT P_XML_CONTENT AS CLOB)
     99      into V_TEXT_CONTENT
    100      from dual;
    101
    102    return V_TEXT_CONTENT;
    103  end;
    104  --
    105  function makeBinary(P_XML_CONTENT XMLTYPE, P_CSID BINARY_INTEGER)
    106  return BLOB
    107  as
    108  begin
    109    return makeBinary(makeText(P_XML_CONTENT),P_CSID);
    110  end;
    111  --
    112  procedure storeXMLContent(P_RESOURCE IN OUT DBMS_XDBRESOURCE.xdbResource, P_EVENT_LOG_ENTRY IN OUT CLOB)
    113  as
    114    V_BUFFER            VARCHAR2(32000);
    115    V_XML_CONTENT       XMLType;
    116    V_LOCATOR           XMLType;
    117  begin
    118    V_BUFFER := ' Processing XML Content.';
    119    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    120
    121    V_XML_CONTENT :=  DBMS_XDBRESOURCE.getContentXML(P_RESOURCE);
    122
    123    if (V_XML_CONTENT is null) then
    124      V_BUFFER := ' XML Content is null.';
    125      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    126    else
    127      DBMS_XDBRESOURCE.SETCONTENT(P_RESOURCE,CUSTOM_CONTENT_MANAGER.storeXML(V_XML_CONTENT));
    128    end if;
    129  end;
    130  --
    131  procedure storeBinaryContent(P_RESOURCE IN OUT DBMS_XDBRESOURCE.xdbResource, P_EVENT_LOG_ENTRY IN OUT CLOB)
    132  as
    133    V_BUFFER            VARCHAR2(32000);
    134    V_BINARY_CONTENT    BLOB;
    135    V_CSID              BINARY_INTEGER;
    136  begin
    137    V_BUFFER := ' Processing Binary Content.';
    138    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    139
    140    V_BINARY_CONTENT :=  DBMS_XDBRESOURCE.getContentBLOB(P_RESOURCE,V_CSID);
    141
    142    V_BUFFER := ' CSID = "' || V_CSID || '".';
    143    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    144
    145    if (V_BINARY_CONTENT is null) then
    146      V_BUFFER := ' BLOB Content is null.';
    147      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    148    else
    149      V_BINARY_CONTENT := makeBinary(CUSTOM_CONTENT_MANAGER.storeBinary(V_BINARY_CONTENT),V_CSID);
    150      DBMS_XDBRESOURCE.SETCONTENT(P_RESOURCE,V_BINARY_CONTENT,V_CSID);
    151    end if;
    152  end;
    153  --
    154  procedure storeTextContent(P_RESOURCE IN OUT DBMS_XDBRESOURCE.xdbResource, P_EVENT_LOG_ENTRY IN OUT CLOB)
    155  as
    156    V_BUFFER            VARCHAR2(32000);
    157    V_TEXT_CONTENT      CLOB;
    158    V_ROWID             ROWID;
    159  begin
    160    V_BUFFER := ' Processing Text Content.';
    161    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    162
    163    V_TEXT_CONTENT :=  DBMS_XDBRESOURCE.getContentCLOB(P_RESOURCE);
    164    if (V_TEXT_CONTENT is null) then
    165      V_BUFFER := ' CLOB Content is null.';
    166      DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    167    else
    168      V_TEXT_CONTENT := makeText(CUSTOM_CONTENT_MANAGER.storeText(V_TEXT_CONTENT));
    169      DBMS_XDBRESOURCE.SETCONTENT(P_RESOURCE,V_TEXT_CONTENT);
    170    end if;
    171  end;
    172  --
    173  procedure handlePreCreate(P_EVENT dbms_xevent.XDBRepositoryEvent)
    174  as
    175    V_RESOURCE_PATH     VARCHAR2(700);
    176    V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
    177    V_RESOURCE_DOCUMENT DBMS_XMLDOM.DOMDocument;
    178
    179    V_BUFFER            VARCHAR2(32000);
    180    V_EVENT_LOG_ENTRY   CLOB;
    181
    182    V_NODE_LIST         DBMS_XMLDOM.DOMNodeList;
    183    V_SCHEMA_ELEMENT    VARCHAR2(1024);
    184
    185    V_BLOB_CONTENT      BLOB;
    186
    187  begin
    188
    189    V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));
    190
    191          DBMS_LOB.CREATETEMPORARY(V_EVENT_LOG_ENTRY,TRUE);
    192    V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Fired Pre Create Event for "' || V_RESOURCE_PATH || '".';
    193    DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    194
    195    V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
    196    V_RESOURCE_DOCUMENT := DBMS_XDBRESOURCE.makeDocument(V_RESOURCE);
    197
    198    V_NODE_LIST         := DBMS_XSLPROCESSOR.selectNodes(DBMS_XMLDOM.makeNode(V_RESOURCE_DOCUMENT),'/r:Resource/r:SchemaElement','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"');
    199
    200    if (DBMS_XMLDOM.getLength(V_NODE_LIST) > 0) then
    201      -- Non XML Conent
    202      V_SCHEMA_ELEMENT    := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.item(V_NODE_LIST,0)));
    203      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : SchemaElement = "' || V_SCHEMA_ELEMENT || '".';
    204      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    205
    206      if (V_SCHEMA_ELEMENT = BINARY_CONTENT_SCHEMA) then
    207        storeBinaryContent(V_RESOURCE, V_EVENT_LOG_ENTRY);
    208      end if;
    209
    210      if (V_SCHEMA_ELEMENT = TEXT_CONTENT_SCHEMA) then
    211        storeTextContent(V_RESOURCE, V_EVENT_LOG_ENTRY);
    212      end if;
    213    else
    214      -- Non Schema Based XML
    215      storeXMLContent(V_RESOURCE, V_EVENT_LOG_ENTRY);
    216    end if;
    217
    218    V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Completed Pre Create Event for "' || V_RESOURCE_PATH || '".';
    219    DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    220
    221    updateEventLog(V_EVENT_LOG_ENTRY);
    222    DBMS_LOB.FREETEMPORARY(V_EVENT_LOG_ENTRY);
    223
    224  exception
    225    when others then
    226      logException(V_RESOURCE_PATH, V_EVENT_LOG_ENTRY);
    227      raise;
    228  end;
    229  --
    230  procedure renderXMLContent(P_EVENT dbms_xevent.XDBRepositoryEvent, P_LOCATOR XMLTYPE, P_EVENT_LOG_ENTRY IN OUT CLOB)
    231  as
    232    V_BUFFER            VARCHAR2(32000);
    233    V_XML_CONTENT       XMLType;
    234  begin
    235    V_BUFFER := ' Processing XML Content';
    236    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    237    V_XML_CONTENT := CUSTOM_CONTENT_MANAGER.getXML(P_LOCATOR);
    238          -- DBMS_XEVENT.setRenderStream(P_EVENT,V_XML_CONTENT);
    239  end;
    240  --
    241  procedure renderBinaryContent(P_EVENT dbms_xevent.XDBRepositoryEvent, P_LOCATOR XMLTYPE, P_EVENT_LOG_ENTRY IN OUT CLOB)
    242  as
    243    V_BUFFER            VARCHAR2(32000);
    244    V_BINARY_CONTENT    BLOB;
    245  begin
    246    V_BUFFER := ' Processing Binary Content';
    247    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    248    V_BINARY_CONTENT := CUSTOM_CONTENT_MANAGER.getBinary(P_LOCATOR);
    249          DBMS_XEVENT.setRenderStream(P_EVENT,V_BINARY_CONTENT);
    250  end;
    251  --
    252  procedure renderTextContent(P_EVENT dbms_xevent.XDBRepositoryEvent, P_LOCATOR XMLTYPE, P_EVENT_LOG_ENTRY IN OUT CLOB)
    253  as
    254    V_BUFFER            VARCHAR2(32000);
    255    V_TEXT_CONTENT      CLOB;
    256    V_ROWID             ROWID;
    257  begin
    258    V_BUFFER := ' Processing Text Content';
    259    DBMS_LOB.WRITEAPPEND(P_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    260    V_TEXT_CONTENT := CUSTOM_CONTENT_MANAGER.getText(P_LOCATOR);
    261    -- DBMS_XEVENT.setRenderStream(P_EVENT,V_TEXT_CONTENT);
    262  end;
    263  --
    264  procedure handleRender(P_EVENT dbms_xevent.XDBRepositoryEvent)
    265  as
    266    V_RESOURCE_PATH     VARCHAR2(700);
    267    V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
    268    V_RESOURCE_DOCUMENT DBMS_XMLDOM.DOMDocument;
    269
    270    V_BUFFER            VARCHAR2(32000);
    271    V_EVENT_LOG_ENTRY   CLOB;
    272
    273    V_NODE_LIST         DBMS_XMLDOM.DOMNodeList;
    274    V_SCHEMA_ELEMENT    VARCHAR2(1024);
    275
    276    V_CSID              BINARY_INTEGER;
    277
    278  begin
    279
    280    V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));
    281
    282          DBMS_LOB.CREATETEMPORARY(V_EVENT_LOG_ENTRY,TRUE);
    283    V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Fired Render Event for "' || V_RESOURCE_PATH || '".';
    284    DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    285
    286    V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
    287    V_RESOURCE_DOCUMENT := DBMS_XDBRESOURCE.makeDocument(V_RESOURCE);
    288
    289    V_NODE_LIST         := DBMS_XSLPROCESSOR.selectNodes(DBMS_XMLDOM.makeNode(V_RESOURCE_DOCUMENT),'/r:Resource/r:SchemaElement','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"');
    290
    291    if (DBMS_XMLDOM.getLength(V_NODE_LIST) > 0) then
    292      -- Non XML Conent
    293      V_SCHEMA_ELEMENT    := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.item(V_NODE_LIST,0)));
    294      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : SchemaElement = "' || V_SCHEMA_ELEMENT || '".';
    295      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    296
    297      if (V_SCHEMA_ELEMENT = BINARY_CONTENT_SCHEMA) then
    298        renderBinaryContent(P_EVENT, XMLType(DBMS_XDBRESOURCE.getContentBLOB(V_RESOURCE,V_CSID),V_CSID),V_EVENT_LOG_ENTRY);
    299      end if;
    300
    301      if (V_SCHEMA_ELEMENT = TEXT_CONTENT_SCHEMA) then
    302        -- renderTextContent(P_EVENT, XMLType(DBMS_XDBRESOURCE.getContentCLOB(V_RESOURCE)),V_EVENT_LOG_ENTRY);
    303        renderBinaryContent(P_EVENT, XMLType(DBMS_XDBRESOURCE.getContentBLOB(V_RESOURCE,V_CSID),V_CSID),V_EVENT_LOG_ENTRY);
    304      end if;
    305    else
    306      -- Non Schema Based XML
    307      renderXMLContent(P_EVENT, DBMS_XDBRESOURCE.getContentXML(V_RESOURCE), V_EVENT_LOG_ENTRY);
    308    end if;
    309
    310    V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Completed Render Event for "' || V_RESOURCE_PATH || '".';
    311    DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    312    updateEventLog(V_EVENT_LOG_ENTRY);
    313    DBMS_LOB.FREETEMPORARY(V_EVENT_LOG_ENTRY);
    314
    315  exception
    316    when others then
    317      logException(V_RESOURCE_PATH, V_EVENT_LOG_ENTRY);
    318      raise;
    319  end;
    320  --
    321  procedure handlePreDelete(P_EVENT dbms_xevent.XDBRepositoryEvent)
    322  as
    323    V_RESOURCE_PATH     VARCHAR2(700);
    324    V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
    325    V_RESOURCE_DOCUMENT DBMS_XMLDOM.DOMDocument;
    326
    327    V_BUFFER            VARCHAR2(32000);
    328    V_EVENT_LOG_ENTRY   CLOB;
    329
    330    V_NODE_LIST         DBMS_XMLDOM.DOMNodeList;
    331    V_SCHEMA_ELEMENT    VARCHAR2(1024);
    332
    333    V_CSID              BINARY_INTEGER;
    334    V_LOCATOR           XMLTYPE := NULL;
    335    V_ROWID             ROWID;
    336
    337  begin
    338
    339    V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));
    340
    341          DBMS_LOB.CREATETEMPORARY(V_EVENT_LOG_ENTRY,TRUE);
    342    V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Fired PreDelete Event for "' || V_RESOURCE_PATH || '".';
    343    DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    344
    345    V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
    346    V_RESOURCE_DOCUMENT := DBMS_XDBRESOURCE.makeDocument(V_RESOURCE);
    347
    348    V_NODE_LIST         := DBMS_XSLPROCESSOR.selectNodes(DBMS_XMLDOM.makeNode(V_RESOURCE_DOCUMENT),'/r:Resource/r:SchemaElement','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"');
    349
    350    if (DBMS_XMLDOM.getLength(V_NODE_LIST) > 0) then
    351      -- Non XML Conent
    352      V_SCHEMA_ELEMENT    := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.item(V_NODE_LIST,0)));
    353      V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : SchemaElement = "' || V_SCHEMA_ELEMENT || '".';
    354      DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    355
    356      if (V_SCHEMA_ELEMENT = BINARY_CONTENT_SCHEMA) then
    357        V_LOCATOR := xmlType(DBMS_XDBRESOURCE.getContentBLOB(V_RESOURCE,V_CSID),V_CSID);
    358      end if;
    359
    360      if (V_SCHEMA_ELEMENT = TEXT_CONTENT_SCHEMA) then
    361        V_LOCATOR :=  xmlType(DBMS_XDBRESOURCE.getContentCLOB(V_RESOURCE));
    362      end if;
    363    else
    364      -- Non Schema Based XML
    365      V_LOCATOR := DBMS_XDBRESOURCE.getContentXML(V_RESOURCE);
    366    end if;
    367
    368    if (V_LOCATOR is not null) then
    369      CUSTOM_CONTENT_MANAGER.deleteContent(V_LOCATOR);
    370    end if;
    371
    372    V_BUFFER := chr(13) || chr(10) || to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF') || ' : Completed PreDelete Event for "' || V_RESOURCE_PATH || '".';
    373    DBMS_LOB.WRITEAPPEND(V_EVENT_LOG_ENTRY,LENGTH(V_BUFFER),V_BUFFER);
    374    updateEventLog(V_EVENT_LOG_ENTRY);
    375    DBMS_LOB.FREETEMPORARY(V_EVENT_LOG_ENTRY);
    376
    377  exception
    378    when others then
    379      logException(V_RESOURCE_PATH, V_EVENT_LOG_ENTRY);
    380      raise;
    381  end;
    382  --
    383  end;
    384  /
    old  20:    where equals_path(RES,'&LOGFILE_PATH') = 1;
    new  20:    where equals_path(RES,'/public/resConfigTest1/logFile.txt') = 1;
    old  25:    where equals_path(res,'&LOGFILE_PATH') = 1
    new  25:    where equals_path(res,'/public/resConfigTest1/logFile.txt') = 1
    
    Package body created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> create table CUSTOM_CONTENT_TABLE
      2  (
      3     XML_CONTENT    XMLTYPE,
      4     TEXT_CONTENT   CLOB,
      5     BINARY_CONTENT BLOB
      6  )
      7  LOB (TEXT_CONTENT) STORE AS SECUREFILE,
      8  LOB (BINARY_CONTENT) STORE AS SECUREFILE,
      9  XMLTYPE COLUMN XML_CONTENT STORE AS SECUREFILE BINARY XML
     10  /
    
    Table created.
    
    SQL> create or replace package body CUSTOM_CONTENT_MANAGER
      2  as
      3  --
      4  function newLocator(P_ROWID ROWID, P_COLUMN_NAME VARCHAR2)
      5  return xmltype
      6  as
      7    V_LOCATOR XMLTYPE;
      8  begin
      9    select xmlElement
     10           (
     11             "locator",
     12              xmlAttributes('http:/xmlns.example.com/xdb/pm/contentLocator' as "xmlns"),
     13              xmlElement("SCHEMA",'&USERNAME'),
     14              xmlElement("TABLE",'CUSTOM_CONTENT_TABLE'),
     15              xmlElement("COLUMN",P_COLUMN_NAME),
     16              xmlElement("ROWID",P_ROWID)
     17             )
     18        into V_LOCATOR
     19      from dual;
     20
     21          return V_LOCATOR;
     22  end;
     23  --
     24  function getROWID(P_LOCATOR XMLTYPE)
     25  return ROWID
     26  as
     27    V_ROWID ROWID;
     28  begin
     29    select XMLCAST
     30                 (
     31                    XMLQUERY
     32                    (
     33                      'declare default element namespace "http:/xmlns.example.com/xdb/pm/contentLocator"; (: :)
     34                      $L/locator/ROWID'
     35                      passing P_LOCATOR as "L" returning CONTENT
     36                    )
     37                    as VARCHAR2(32)
     38                 )
     39            into V_ROWID
     40            from DUAL;
     41
     42          return V_ROWID;
     43  end;
     44  --
     45  function storeXML(P_XML_CONTENT XMLType)
     46  return XMLTYPE
     47  as
     48    V_ROWID ROWID;
     49  begin
     50    insert into CUSTOM_CONTENT_TABLE (XML_CONTENT) values (P_XML_CONTENT) returning ROWID into V_ROWID;
     51    return newLocator(V_ROWID,'XML_CONTENT');
     52  end;
     53  --
     54  function storeBinary(P_BINARY_CONTENT BLOB)
     55  return XMLTYPE
     56  as
     57    V_ROWID ROWID;
     58  begin
     59    insert into CUSTOM_CONTENT_TABLE (BINARY_CONTENT) values (P_BINARY_CONTENT) returning ROWID into V_ROWID;
     60    return newLocator(V_ROWID,'BINARY_CONTENT');
     61  end;
     62  --
     63  function storeText(P_TEXT_CONTENT CLOB)
     64  return XMLTYPE
     65  as
     66    V_ROWID ROWID;
     67  begin
     68    insert into CUSTOM_CONTENT_TABLE (TEXT_CONTENT) values (P_TEXT_CONTENT) returning ROWID into V_ROWID;
     69    return newLocator(V_ROWID,'TEXT_CONTENT');
     70  end;
     71  --
     72  function getXML(P_LOCATOR XMLTYPE)
     73  return XMLType
     74  as
     75    V_XML_CONTENT XMLTYPE;
     76    V_ROWID ROWID;
     77  begin
     78          V_ROWID := getROWID(P_LOCATOR);
     79          select XML_CONTENT
     80            into V_XML_CONTENT
     81            from CUSTOM_CONTENT_TABLE
     82           where ROWID = V_ROWID;
     83          return V_XML_CONTENT;
     84  end;
     85  --
     86  function getBinary(P_LOCATOR XMLTYPE)
     87  return BLOB
     88  as
     89    V_BINARY_CONTENT BLOB;
     90    V_ROWID ROWID;
     91  begin
     92          V_ROWID := getROWID(P_LOCATOR);
     93          select BINARY_CONTENT
     94            into V_BINARY_CONTENT
     95            from CUSTOM_CONTENT_TABLE
     96           where ROWID = V_ROWID;
     97          return V_BINARY_CONTENT;
     98  end;
     99  --
    100  function getText(P_LOCATOR XMLTYPE)
    101  return CLOB
    102  as
    103    V_TEXT_CONTENT CLOB;
    104    V_ROWID ROWID;
    105  begin
    106          V_ROWID := getROWID(P_LOCATOR);
    107          select TEXT_CONTENT
    108            into V_TEXT_CONTENT
    109            from CUSTOM_CONTENT_TABLE
    110           where ROWID = V_ROWID;
    111    return V_TEXT_CONTENT;
    112  end;
    113  --
    114  procedure deleteContent(P_LOCATOR XMLType)
    115  as
    116    V_ROWID ROWID;
    117  begin
    118          V_ROWID := getROWID(P_LOCATOR);
    119    delete
    120      from CUSTOM_CONTENT_TABLE
    121           where ROWID = V_ROWID;
    122  end;
    123  --
    124  end;
    125  /
    old  13:             xmlElement("SCHEMA",'&USERNAME'),
    new  13:             xmlElement("SCHEMA",'EVENTS'),
    
    Package body created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> grant execute on CUSTOM_CONTENT_EVENTS to public
      2  /
    
    Grant succeeded.
  • 21. Re: XMLIndex Value Index on repository
    mdrake Expert
    Currently Being Moderated
     SQL> declare
      2    V_RESULT BOOLEAN;
      3  begin
      4    V_RESULT := DBMS_XDB.CREATEFOLDER(:TARGET_FOLDER);
      5    V_RESULT := DBMS_XDB.CREATEFOLDER(:UPLOAD_FOLDER);
      6    -- V_RESULT := DBMS_XDB.CREATERESOURCE(:RESCONFIG_PATH,XMLTYPE(:RESCONFIG));
      7    V_RESULT := DBMS_XDB.CREATERESOURCE(:RESCONFIG_PATH,:RESCONFIG);
      8    V_RESULT := DBMS_XDB.CREATERESOURCE(:LOGFILE_PATH, to_char(systimestamp,'YYYY-MM-DD"T"HH24:MI:SS.FF')  || ' : Log File Created');
      9    COMMIT;
     10
     11    dbms_resconfig.addResConfig(:UPLOAD_FOLDER,:RESCONFIG_PATH,null);
     12  end;
     13  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select XMLSERIALIZE(DOCUMENT XDBURITYPE(:RESCONFIG_PATH).getXML() as CLOB INDENT SIZE = 2)  RESCONFIG
      2    from DUAL
      3  /
    <ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd http://xmlns.oracle.com/xdb/XDBR
      <event-listeners set-invoker="true">
        <listener>
          <description>CUSTOM CONTENT STORE</description>
          <schema>EVENTS</schema>
          <source>CUSTOM_CONTENT_EVENTS</source>
          <language>PL/SQL</language>
          <events>
            <Pre-Create/>
            <Pre-Delete/>
            <Render/>
          </events>
        </listener>
      </event-listeners>
      <defaultChildConfig>
        <configuration/>
      </defaultChildConfig>
    </ResConfig>
    
    
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    /public/resConfigTest1/ResConfig.xml
    XDB$STRING_LIST_T()
    
    /public/resConfigTest1/logFile.txt
    XDB$STRING_LIST_T()
    
    /public/resConfigTest1/uploadFolder
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    
    SQL> set long 100000 pages 0 lines 256
    SQL> column LOG format A250
    SQL> --
    SQL> select xdburitype(:LOGFILE_PATH).getClob() LOG
      2    from dual
      3  /
    2010-11-25T07:48:12.329000000 : Log File Created
    
    SQL> declare
      2    V_RESULT BOOLEAN;
      3    V_XMLDOC_PATH VARCHAR2(700) := :UPLOAD_FOLDER || '/test2.xml';
      4  begin
      5    V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,XMLTYPE('<Hello>World</Hello>'));
      6    COMMIT;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL> declare
      2    V_RESULT BOOLEAN;
      3    V_XMLDOC_PATH VARCHAR2(700) := :UPLOAD_FOLDER || '/test2.txt';
      4  begin
      5    V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,'Hello World');
      6    COMMIT;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL> declare
      2    V_RESULT BOOLEAN;
      3    V_XMLDOC_PATH VARCHAR2(700) := :UPLOAD_FOLDER || '/test2.bin';
      4  begin
      5    V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,HEXTORAW('AABBCCDDEEFF'));
      6    COMMIT;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select xdburitype(:LOGFILE_PATH).getClob() LOG
      2    from dual
      3  /
    2010-11-25T07:48:12.329000000 : Log File Created
    2010-11-25T07:48:17.201000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.xml". Processing XML Content.
    2010-11-25T07:48:17.423000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.xml".
    2010-11-25T07:48:17.859000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:17.859000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content. CSID = "178".
    2010-11-25T07:48:17.861000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:18.014000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:18.015000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content. CSID = "178".
    2010-11-25T07:48:18.017000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    
    
    SQL> select xmlserialize(DOCUMENT RES as CLOB INDENT SIZE = 2)
      2    from RESOURCE_VIEW
      3   where equals_path(RES,:UPLOAD_FOLDER || '/test2.xml') = 1
      4  /
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
    alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
    ef="true">
      <CreationDate>2010-11-25T15:48:17.782000</CreationDate>
      <ModificationDate>2010-11-25T15:48:17.782000</ModificationDate>
      <DisplayName>test2.xml</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>WINDOWS-1252</CharacterSet>
      <ContentType>text/xml</ContentType>
      <RefCount>1</RefCount>
      <ACL>
        <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracl
    e.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
    emaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http:
    //xmlns.oracle.com/xdb/acl.xsd" shared="true">
          <ace>
            <grant>true</grant>
            <principal>PUBLIC</principal>
            <privilege>
              <all/>
            </privilege>
          </ace>
        </acl>
      </ACL>
      <Owner>EVENTS</Owner>
      <Creator>EVENTS</Creator>
      <LastModifier>EVENTS</LastModifier>
      <Contents>
        <locator xmlns="http:/xmlns.example.com/xdb/pm/contentLocator">
          <SCHEMA>EVENTS</SCHEMA>
          <TABLE>CUSTOM_CONTENT_TABLE</TABLE>
          <COLUMN>XML_CONTENT</COLUMN>
          <ROWID>AAETNqAAEAACiZfAAA</ROWID>
        </locator>
      </Contents>
    </Resource>
    
    
    SQL> select xmlserialize(DOCUMENT RES as CLOB INDENT SIZE = 2)
      2    from RESOURCE_VIEW
      3   where equals_path(RES,:UPLOAD_FOLDER || '/test2.txt') = 1
      4  /
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
    alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
    ef="true">
      <CreationDate>2010-11-25T15:48:17.962000</CreationDate>
      <ModificationDate>2010-11-25T15:48:17.962000</ModificationDate>
      <DisplayName>test2.txt</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>WINDOWS-1252</CharacterSet>
      <ContentType>text/plain</ContentType>
      <RefCount>1</RefCount>
      <ACL>
        <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracl
    e.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
    emaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http:
    //xmlns.oracle.com/xdb/acl.xsd" shared="true">
          <ace>
            <grant>true</grant>
            <principal>PUBLIC</principal>
            <privilege>
              <all/>
            </privilege>
          </ace>
        </acl>
      </ACL>
      <Owner>EVENTS</Owner>
      <Creator>EVENTS</Creator>
      <LastModifier>EVENTS</LastModifier>
      <SchemaElement>http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary</SchemaElement
    >
      <Contents>
        <text>&lt;locator xmlns=&quot;http:/xmlns.example.com/xdb/pm/contentLocator&
    quot;&gt;&lt;SCHEMA&gt;EVENTS&lt;/SCHEMA&gt;&lt;TABLE&gt;CUSTOM_CONTENT_TABLE&lt
    ;/TABLE&gt;&lt;COLUMN&gt;BINARY_CONTENT&lt;/COLUMN&gt;&lt;ROWID&gt;AAETNqAAEAACi
    ZfAAB&lt;/ROWID&gt;&lt;/locator&gt;</text>
      </Contents>
    </Resource>
    
    
    SQL> select xmlserialize(DOCUMENT RES as CLOB INDENT SIZE = 2)
      2    from RESOURCE_VIEW
      3   where equals_path(RES,:UPLOAD_FOLDER || '/test2.bin') = 1
      4  /
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
    alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
    ef="true">
      <CreationDate>2010-11-25T15:48:18.022000</CreationDate>
      <ModificationDate>2010-11-25T15:48:18.022000</ModificationDate>
      <DisplayName>test2.bin</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>WINDOWS-1252</CharacterSet>
      <ContentType>application/octet-stream</ContentType>
      <RefCount>1</RefCount>
      <ACL>
        <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracl
    e.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
    emaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http:
    //xmlns.oracle.com/xdb/acl.xsd" shared="true">
          <ace>
            <grant>true</grant>
            <principal>PUBLIC</principal>
            <privilege>
              <all/>
            </privilege>
          </ace>
        </acl>
      </ACL>
      <Owner>EVENTS</Owner>
      <Creator>EVENTS</Creator>
      <LastModifier>EVENTS</LastModifier>
      <SchemaElement>http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary</SchemaElement
    >
      <Contents>
        <binary>3C6C6F6361746F7220786D6C6E733D22687474703A2F786D6C6E732E6578616D706C
    652E636F6D2F7864622F706D2F636F6E74656E744C6F6361746F72223E3C534348454D413E455645
    4E54533C2F534348454D413E3C5441424C453E435553544F4D5F434F4E54454E545F5441424C453C
    2F5441424C453E3C434F4C554D4E3E42494E4152595F434F4E54454E543C2F434F4C554D4E3E3C52
    4F5749443E414145544E71414145414143695A664141433C2F524F5749443E3C2F6C6F6361746F72
    3E</binary>
      </Contents>
    </Resource>
    
    
    SQL> select xdburitype(:UPLOAD_FOLDER || '/test2.txt').getClob()
      2    from DUAL
      3  /
    Hello World
    
    SQL> select xdburitype(:UPLOAD_FOLDER || '/test2.bin').getBlob()
      2    from DUAL
      3  /
    414142424343444445454646
    
    SQL> select xdburitype(:LOGFILE_PATH).getClob() LOG
      2    from dual
      3  /
    2010-11-25T07:48:12.329000000 : Log File Created
    2010-11-25T07:48:17.201000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.xml". Processing XML Content.
    2010-11-25T07:48:17.423000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.xml".
    2010-11-25T07:48:17.859000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:17.859000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content. CSID = "178".
    2010-11-25T07:48:17.861000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:18.014000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:18.015000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content. CSID = "178".
    2010-11-25T07:48:18.017000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:18.436000000 : Fired Render Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:18.437000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content
    2010-11-25T07:48:18.442000000 : Completed Render Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:18.454000000 : Fired Render Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:18.454000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content
    2010-11-25T07:48:18.456000000 : Completed Render Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    
    
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    /public/resConfigTest1/ResConfig.xml
    XDB$STRING_LIST_T()
    
    /public/resConfigTest1/logFile.txt
    XDB$STRING_LIST_T()
    
    /public/resConfigTest1/uploadFolder
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    /public/resConfigTest1/uploadFolder/test2.bin
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    /public/resConfigTest1/uploadFolder/test2.txt
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    /public/resConfigTest1/uploadFolder/test2.xml
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    
    6 rows selected.
    
    SQL> select count(*)
      2    from CUSTOM_CONTENT_TABLE
      3  /
             3
    
    SQL> call dbms_xdb.deleteResource(:UPLOAD_FOLDER || '/test2.txt')
      2  /
    
    Call completed.
    
    SQL> commit
      2  /
    
    Commit complete.
    
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    /public/resConfigTest1/ResConfig.xml
    XDB$STRING_LIST_T()
    
    /public/resConfigTest1/logFile.txt
    XDB$STRING_LIST_T()
    
    /public/resConfigTest1/uploadFolder
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    /public/resConfigTest1/uploadFolder/test2.bin
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    /public/resConfigTest1/uploadFolder/test2.xml
    XDB$STRING_LIST_T('/public/resConfigTest1/ResConfig.xml')
    
    
    SQL> select count(*)
      2    from CUSTOM_CONTENT_TABLE
      3  /
             2
    
    SQL> select xdburitype(:LOGFILE_PATH).getClob() LOG
      2    from dual
      3  /
    2010-11-25T07:48:12.329000000 : Log File Created
    2010-11-25T07:48:17.201000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.xml". Processing XML Content.
    2010-11-25T07:48:17.423000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.xml".
    2010-11-25T07:48:17.859000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:17.859000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content. CSID = "178".
    2010-11-25T07:48:17.861000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:18.014000000 : Fired Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:18.015000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content. CSID = "178".
    2010-11-25T07:48:18.017000000 : Completed Pre Create Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:18.436000000 : Fired Render Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:18.437000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content
    2010-11-25T07:48:18.442000000 : Completed Render Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:18.454000000 : Fired Render Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:18.454000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary". Processing Binary Content
    2010-11-25T07:48:18.456000000 : Completed Render Event for "/public/resConfigTest1/uploadFolder/test2.bin".
    2010-11-25T07:48:23.449000000 : Fired PreDelete Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    2010-11-25T07:48:23.449000000 : SchemaElement = "http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary".
    2010-11-25T07:48:23.452000000 : Completed PreDelete Event for "/public/resConfigTest1/uploadFolder/test2.txt".
    
    
    SQL> --
    SQL> quit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    C:\xdb\examples\Events>
    As you can see, in this case any XML loaded in "/public/resConfigTest1/uploadFolder is stored in the XML_CONTENT column of the table CUSTOM_CONTENT_TABLE.
  • 22. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    Thank you for the example using repository events. If all else fails, I might consider implementing this somewhat complex solution. We are also thinking about saving the XML data completely outside the database on disk or using some simple schema-less database like CouchDB as a temporrary solution for the time being until this is solved. Of course, we'll miss out on Oracle's far superior performance no matter which solution we use, unfortunately.
    The workaround you mention (using a literal as username):

    SELECT rowid
    FROM resource_view
    where XMLCAST
    (
    XMLQUERY
    (
    'declare namespace r="http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
    /r:Resource/r:Contents/xml/properties/username'
    passing res returning content
    ) as VARCHAR2(200)
    ) = 'admin'
    AND UNDER_PATH(res,'/public/users')=1

    doesnt use any index except XDBHI_IDX if I run an EXPLAIN plan on it.
    Interestingly, if I try the "old-fashioned" method:

    SELECT rowid FROM resource_view
    WHERE
    EXTRACTVALUE(res,'/r:Resource/r:Contents/xml/properties/username','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='admin'
    AND UNDER_PATH(res,'/public')=1

    at least XDB_REPOS_PATHID_IDX is used.
    So, the second method finishes in a few seconds. Which is not really sufficient for the performance we need, but the best method I could find so far.
    Your first example with XMLCAST/XMLQUERY takes more than 20 seconds here, probably because it doesn't use any index according to EXPLAIN.
    We have millions of rows in the repository, though.
  • 23. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    For me it becomes even more fuzzy why you have an explain plan report / output stating that it uses the XMLIndex. Used multiple variations on your statement but result keeps the same (usage of the hierarchical index)

    Test on the OTN Development VirtualBox environment upgraded to 11.2.0.2.0
    -- 1) created via FTP and user XDB resource /public/users
    
    -- 2) created the test resources via the following statement (same as the ones from Mark) while using SQL*Plus and user XDB
    
    
    declare
      V_RESULT BOOLEAN;
      V_XMLDOC_PATH VARCHAR2(700) := '/public/users' || '/test.xml';
    begin
      V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,XMLTYPE('<Hello>World</Hello>'));
      COMMIT;
    end;
    / 
    
    
    declare
      V_RESULT BOOLEAN;
      V_XMLDOC_PATH VARCHAR2(700) := '/public/users'  || '/test2.txt';
    begin
      V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,'Hello World');
      COMMIT;
    end;
    / 
     
    declare
      V_RESULT BOOLEAN;
      V_XMLDOC_PATH VARCHAR2(700) := '/public/users'  || '/test2.bin';
    begin
      V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,HEXTORAW('AABBCCDDEEFF'));
      COMMIT;
    end;
    / 
    
    -- 3) Created repository XMLIndex and added resource path and created all stats for and while being XDB
    
    SQL> CALL DBMS_XDB_ADMIN.DropRepositoryXMLIndex();
    
    Call completed.
    
    SQL> CALL DBMS_XDB_ADMIN.CreateRepositoryXMLIndex();
    
    Call completed.
    
    SQL> call DBMS_XDB_ADMIN.XMLIndexAddPath('/public/users');
    
    Call completed.
    
    
    SQL> exec dbms_stats.gather_schema_stats(user);
    
    PL/SQL procedure successfully completed.
    
    -- 4) Enabled explain plan output and run both statements
    
    SQL> SELECT rowid
      2      FROM resource_view
      3     where XMLCAST
      4               (
      5                  XMLQUERY
      6                  (
      7                       'declare namespace r="http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
      8                        /r:Resource/r:Contents/Hello'
      9                        passing res returning content
     10                 ) as VARCHAR2(200)
     11              )  = 'World'
     12      AND UNDER_PATH(res,'/public/users')=1;
    
    ROWID
    ------------------
    AAANynAACAAANLPAAV
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3007404872
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |     1 |   612 |    28   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| XDB$RESOURCE |     1 |   612 |    28   (0)| 00:00:01 |
    |*  2 |   DOMAIN INDEX              | XDBHI_IDX    |       |       |            |          |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(CAST(EXTRACTVALUE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"
                  P"."XMLEXTRA","P"."XMLDATA"),'/r:Resource/r:Contents/Hello','
                  xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"',0,0,536872961,1086325248) AS
                  VARCHAR2(200)             )='World')
       2 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,
                  "XMLEXTRA","XMLDATA"),'/public/users')=1)
    
    Note
    -----
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
    
    SQL> SELECT  rowid,
      2          EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"'),
      3          EXTRACT(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')
      4    FROM  resource_view
      5   WHERE  EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='World'
      6     AND  UNDER_PATH(res,'/public')=1;
    
    
    ROWID
    ------------------
    EXTRACTVALUE(RES,'/R:RESOURCE/R:CONTENTS/HELLO','XMLNS:R="HTTP://XMLNS.ORACLE.COM/XDB/XDBRESOURCE.XSD"')
    --------------------------------------------------------------------------------------------------------
    EXTRACT(RES,'/R:RESOURCE/R:CONTENTS/HELLO','XMLNS:R="HTTP://XMLNS.ORACLE.COM/XDB/XDBRESOURCE.XSD"')
    --------------------------------------------------------------------------------------------------------
    AAANynAACAAANLPAAV
    World
    <Hello>World</Hello>
    
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3007404872
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |     1 |   612 |    28   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| XDB$RESOURCE |     1 |   612 |    28   (0)| 00:00:01 |
    |*  2 |   DOMAIN INDEX              | XDBHI_IDX    |       |       |            |          |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(EXTRACTVALUE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."X
                  MLEXTRA","P"."XMLDATA"),'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle
                  .com/xdb/XDBResource.xsd"')='World')
       2 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,
                  "XMLEXTRA","XMLDATA"),'/public')=1)
    
    Note
    -----
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
    Edited by: Marco Gralike on Nov 25, 2010 8:48 PM
  • 24. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    Interesting. With the second query I get the plan below which shows use of XMLIndex but not of the VALUE INDEX. With all other variants (XQuery, XMLEXISTS, EXISTSNODE) I get the same plan as your first example - meaning no index use at all except for the hierarchical index, of course, which is probably caused by UNDER_PATH. Could ALTER SESSION SET NLS_COMP or sth. like that be responsible for the fact that your plan is different than mine?
    SELECT  rowid,                                                                                                                                              
                EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"'),                                            
                EXTRACT(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')                                                  
          FROM  resource_view
         WHERE  EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='World'
           AND  UNDER_PATH(res,'/public')=1;
    
    ----------------------------------------------------------
    Plan hash value: 2013106002
    
    --------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                               |   316 | 64464 |   336   (0)| 00:00:05 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID | XDB_REPOS_PATH_TAB            |   287 |   846K|     4   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN           | SYS100948_XDB_REPOS_PATHID_IX | 28660 |       |     1   (0)| 00:00:01 |
    |*  3 |  FILTER                      |                               |       |       |            |          |
    |   4 |   TABLE ACCESS BY INDEX ROWID| XDB$RESOURCE                  |   316 | 64464 |   332   (0)| 00:00:04 |
    |*  5 |    DOMAIN INDEX              | XDBHI_IDX                     |       |       |            |          |
    |*  6 |   TABLE ACCESS BY INDEX ROWID| XDB_REPOS_PATH_TAB            |   287 |   846K|     4   (0)| 00:00:01 |
    |*  7 |    INDEX RANGE SCAN          | SYS100948_XDB_REPOS_PATHID_IX | 28660 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
       2 - access("SYS_P0"."PATHID"=HEXTORAW('6409')  AND "SYS_P0"."RID"=:B1)
       3 - filter( (SELECT "SYS_P2"."VALUE" FROM "XDB"."XDB_REPOS_PATH_TAB" "SYS_P2" WHERE
                  "SYS_P2"."RID"=:B1 AND "SYS_P2"."PATHID"=HEXTORAW('6409')  AND
                  SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)='World')
       5 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"XMLEXTRA","XMLDAT
                  A"),'/public')=1)
       6 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
       7 - access("SYS_P2"."PATHID"=HEXTORAW('6409')  AND "SYS_P2"."RID"=:B1)
    
    Note
    -----
       - dynamic sampling used for this statement
  • 25. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Could you create the first resource so you get a value returned so we can see if it still uses the xmlindex...? For nls_comp see output
    SQL> conn / as sysdba
    
    Connected.
    
    SQL> show parameter NLS_COMP
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    nls_comp                             string      BINARY
    
    SQL> show parameter compat
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    compatible                           string      11.2.0.2.0
    plsql_v2_compatibility               boolean     FALSE
    
    
    SQL> conn xdb/xdb
    Connected.
    
    SQL> SQL> set autotrace on expl
    
    SQL> r
      1  SELECT  rowid,
      2              EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"'),
      3              EXTRACT(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')
      4        FROM  resource_view
      5       WHERE  EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='World'
      6*        AND  UNDER_PATH(res,'/public')=1
    
    ROWID
    ------------------
    EXTRACTVALUE(RES,'/R:RESOURCE/R:CONTENTS/HELLO','XMLNS:R="HTTP://XMLNS.ORACLE.COM/XDB/XDBRESOURCE.XSD"')
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    EXTRACT(RES,'/R:RESOURCE/R:CONTENTS/HELLO','XMLNS:R="HTTP://XMLNS.ORACLE.COM/XDB/XDBRESOURCE.XSD"')
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    AAANynAACAAANLPAAV
    World
    <Hello>World</Hello>
    
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3007404872
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |     1 |   612 |    28   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| XDB$RESOURCE |     1 |   612 |    28   (0)| 00:00:01 |
    |*  2 |   DOMAIN INDEX              | XDBHI_IDX    |       |       |            |          |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(EXTRACTVALUE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."X
                  MLEXTRA","P"."XMLDATA"),'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle
                  .com/xdb/XDBResource.xsd"')='World')
       2 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,
                  "XMLEXTRA","XMLDATA"),'/public')=1)
    
    Note
    -----
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
    
    SQL> 5
      5*      WHERE  EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='World'
    
    SQL> c/Hello/Goodbye/
      5*      WHERE  EXTRACTVALUE(res,'/r:Resource/r:Contents/Goodbye','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='World'
    
    SQL> r
      1  SELECT  rowid,
      2              EXTRACTVALUE(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"'),
      3              EXTRACT(res,'/r:Resource/r:Contents/Hello','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')
      4        FROM  resource_view
      5       WHERE  EXTRACTVALUE(res,'/r:Resource/r:Contents/Goodbye','xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='World'
      6*        AND  UNDER_PATH(res,'/public')=1
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3007404872
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              |     1 |   612 |    28   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| XDB$RESOURCE |     1 |   612 |    28   (0)| 00:00:01 |
    |*  2 |   DOMAIN INDEX              | XDBHI_IDX    |       |       |            |          |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(EXTRACTVALUE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."X
                  MLEXTRA","P"."XMLDATA"),'/r:Resource/r:Contents/Goodbye','xmlns:r="http://xmlns.orac
                  le.com/xdb/XDBResource.xsd"')='World')
       2 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,
                  "XMLEXTRA","XMLDATA"),'/public')=1)
    
    Note
    -----
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
    
    SQL> 
    Edited by: Marco Gralike on Nov 25, 2010 10:15 PM
  • 26. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    I created the resource. A value is now returned by the query. Explain plan is exactly the same.
  • 27. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    What do you have...?
    SQL> set head off long 10000
    SQL> select dbms_metadata.get_ddl('INDEX','XDB_REPOS_XIDX','XDB') from dual;
    
    
      CREATE INDEX "XDB"."XDB_REPOS_XIDX" ON "XDB"."XDB$RESOURCE" (OBJECT_VALUE)
       INDEXTYPE IS "XDB"."XMLINDEX"  PARAMETERS ('PATH TABLE XDB_REPOS_PATH_TAB')
  • 28. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    Almost the same. I assume your object value is my sys_makexml. Don't know why this is different though:
      CREATE INDEX "XDB"."XDB_REPOS_XIDX" ON "XDB"."XDB$RESOURCE" (
    SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"XMLEXTRA","XMLDATA"))
       INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS ('PATH TABLE XDB_REPOS_PATH_TAB')
    A value index is created as well. Because it isnt listed in the parameters clause above it is created with a standard name:

    SYS100948_XDB_REPOS_VALUE_IX

    I have never ever seen this index appear in any EXPLAIN plan whatsoever. I even tried lots of different hints to convince the database
    to use this - all attempts were futile. This is probably the holy grail of XML DB. So close but oh so far ;-)
  • 29. Re: XMLIndex Value Index on repository
    mdrake Expert
    Currently Being Moderated
    Sorry, I converted the example of extractValue() given to me by development into XMLCAST/XMLQuery but didn't test it. I do applogize.

    The event model should not be that difficutl to implement. I gave you a example I put together which allows binary, text and xml content all to be stored 'out-of-line', eg outside the XDB repository. If all you are interested in is XML content the code could be reduced to something much easier.

    Basically the concept is that when a document is inserted the CUSTOM_CONTENT_MANAGER package is called in the eventHandler. The package takes the content of the resource and stores it in your table and returns an XML 'Locator' document. The Locator document provides the information needed to locate the content when other operations occur. It should also be possible to performa join between the resource and the content using the locator.

    If all you are talking about is XML content this become very easy. Your custom content table will be an XMLType table and the locator would be the OID of the row that was inserted. you can remove all the code related to handling text or binary content...

    See below

    Setup
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 25 14:55:03 2010
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    SQL> spool outOfLineContent.log
    SQL>
    SQL> --
    SQL> connect / as sysdba
    Connected.
    SQL> --
    SQL> def USERNAME=EVENTS
    SQL> --
    SQL> def PASSWORD=&USERNAME
    SQL> --
    SQL> VAR UPLOAD_FOLDER  VARCHAR2(700);
    SQL> VAR TARGET_FOLDER  VARCHAR2(700);
    SQL> VAR RESCONFIG_PATH VARCHAR2(700);
    SQL> VAR LOGFILE_PATH   VARCHAR2(700);
    SQL> --
    SQL> begin
      2    :TARGET_FOLDER  := '/public/uploadExample';
      3    :RESCONFIG_PATH := :TARGET_FOLDER || '/ResConfig.xml';
      4    :UPLOAD_FOLDER  := :TARGET_FOLDER || '/uploadFolder';
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    
    PATH
    --------------------------------------------------------------------------------
    DBMS_RESCONFIG.GETRESCONFIGPATHS(PATH)
    --------------------------------------------------------------------------------
    /public/uploadExample/ResConfig.xml
    XDB$STRING_LIST_T()
    
    /public/uploadExample/uploadFolder
    XDB$STRING_LIST_T('/public/uploadExample/ResConfig.xml')
    
    
    SQL> --
    SQL> ALTER SESSION SET XML_DB_EVENTS = DISABLE
      2  /
    
    Session altered.
    
    SQL> --
    SQL> begin
      2    if (DBMS_XDB.EXISTSRESOURCE(:UPLOAD_FOLDER)) then
      3      DBMS_XDB.DELETERESOURCE(:UPLOAD_FOLDER,DBMS_XDB.DELETE_RECURSIVE);
      4    end if;
      5    commit;
      6
      7    if (DBMS_XDB.EXISTSRESOURCE(:RESCONFIG_PATH)) then
      8      DBMS_XDB.DELETERESOURCE(:RESCONFIG_PATH);
      9    end if;
     10
     11    if (DBMS_XDB.EXISTSRESOURCE(:TARGET_FOLDER)) then
     12      DBMS_XDB.DELETERESOURCE(:TARGET_FOLDER,DBMS_XDB.DELETE_RECURSIVE);
     13    end if;
     14  end;
     15  /
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> ALTER SESSION SET XML_DB_EVENTS = ENABLE
      2  /
    
    Session altered.
    
    SQL> --
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    
    no rows selected
    
    SQL> DROP USER &USERNAME CASCADE
      2  /
    old   1: DROP USER &USERNAME CASCADE
    new   1: DROP USER EVENTS CASCADE
    
    User dropped.
    
    SQL> grant connect, resource to &USERNAME identified by &PASSWORD
      2  /
    old   1: grant connect, resource to &USERNAME identified by &PASSWORD
    new   1: grant connect, resource to EVENTS identified by EVENTS
    
    Grant succeeded.
    
    SQL> grant XDB_SET_INVOKER, ALTER SESSION to &USERNAME
      2  /
    old   1: grant XDB_SET_INVOKER, ALTER SESSION to &USERNAME
    new   1: grant XDB_SET_INVOKER, ALTER SESSION to EVENTS
    
    Grant succeeded.
    
    SQL> grant all on XDB.XDB$RESOURCE to &USERNAME
      2  /
    old   1: grant all on XDB.XDB$RESOURCE to &USERNAME
    new   1: grant all on XDB.XDB$RESOURCE to EVENTS
    
    Grant succeeded.
    
    SQL> connect &USERNAME/&PASSWORD
    Connected.
    SQL> --
    SQL> DEF LOGFILE_PATH = ""
    SQL> --
    SQL> column LOGFILE_PATH NEW_VALUE LOGFILE_PATH
    SQL> --
    SQL> def RESCONFIG_PATH = ""
    SQL> --
    SQL> column RESCONFIG_PATH NEW_VALUE RESCONFIG_PATH
    SQL> --
    SQL> select :RESCONFIG_PATH RESCONFIG_PATH
      2    from DUAL
      3  /
    
    RESCONFIG_PATH
    --------------------------------------------------------------------------------
    /public/uploadExample/ResConfig.xml
    
    SQL> VAR RESCONFIG CLOB
    SQL> --
    SQL> begin
      2    :RESCONFIG :=
      3  '<ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd http://xmlns.oracle.com/xdb/XDBResConfig.xsd">
      4    <event-listeners set-invoker="true">
      5      <listener>
      6        <description>CUSTOM CONTENT STORE</description>
      7        <schema>&USERNAME</schema>
      8        <source>CUSTOM_CONTENT_EVENTS</source>
      9        <language>PL/SQL</language>
     10        <events>
     11          <Pre-Create/>
     12          <Pre-Delete/>
     13          <Render/>
     14        </events>
     15                          <pre-condition>
     16          <existsNode>
     17            <XPath>/r:Resource[r:ContentType="text/xml"]</XPath>
     18            <namespace>xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"</namespace>
     19          </existsNode>
     20        </pre-condition>
     21      </listener>
     22    </event-listeners>
     23  <defaultChildConfig>
     24      <configuration>
     25        <path>' || :RESCONFIG_PATH || '</path>
     26      </configuration>
     27    </defaultChildConfig>
     28  </ResConfig>';
     29
     30  end;
     31  /
    old   7:       <schema>&USERNAME</schema>
    new   7:       <schema>EVENTS</schema>
    
    PL/SQL procedure successfully completed.
    
    SQL> set pages 0 lines 256 long 1000000
    SQL> --
    SQL> column RESCONFIG format A256
    SQL> --
    SQL> select :RESCONFIG RESCONFIG
      2    from DUAL
      3  /
    <ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd http://xmlns.oracle.com/xdb/XDBResConfig.xsd">
      <event-listeners set-invoker="true">
        <listener>
          <description>CUSTOM CONTENT STORE</description>
          <schema>EVENTS</schema>
          <source>CUSTOM_CONTENT_EVENTS</source>
          <language>PL/SQL</language>
          <events>
            <Pre-Create/>
            <Pre-Delete/>
            <Render/>
          </events>
                            <pre-condition>
            <existsNode>
              <XPath>/r:Resource[r:ContentType="text/xml"]</XPath>
              <namespace>xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"</namespace>
            </existsNode>
          </pre-condition>
        </listener>
      </event-listeners>
    <defaultChildConfig>
        <configuration>
          <path>/public/uploadExample/ResConfig.xml</path>
        </configuration>
      </defaultChildConfig>
    </ResConfig>
    
    
    SQL> --
    SQL> create or replace package CUSTOM_CONTENT_MANAGER
      2  as
      3    function storeXML(P_XML_CONTENT XMLTYPE) return XMLType;
      4    function getXML(P_LOCATOR XMLTYPE) return XMLType;
      5    procedure deleteContent(P_LOCATOR XMLTYPE);
      6  end;
      7  /
    
    Package created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> grant execute on CUSTOM_CONTENT_MANAGER to public
      2  /
    
    Grant succeeded.
    
    SQL> create or replace package CUSTOM_CONTENT_EVENTS
      2  as
      3    procedure handlePreCreate(P_EVENT dbms_xevent.XDBRepositoryEvent);
      4    procedure handlePreDelete(P_EVENT dbms_xevent.XDBRepositoryEvent);
      5    procedure handleRender(P_EVENT dbms_xevent.XDBRepositoryEvent);
      6  end;
      7  /
    
    Package created.
    
    SQL> --
    SQL> show errors
    No errors.
    SQL> --
    SQL> grant execute on CUSTOM_CONTENT_EVENTS to public
      2  /
    
    Grant succeeded.
    
    SQL> create or replace package body CUSTOM_CONTENT_EVENTS
      2  as
      3  --
      4  procedure storeXMLContent(P_RESOURCE IN OUT DBMS_XDBRESOURCE.xdbResource)
      5  as
      6    V_XML_CONTENT       XMLType;
      7    V_LOCATOR           XMLType;
      8  begin
      9    V_XML_CONTENT :=  DBMS_XDBRESOURCE.getContentXML(P_RESOURCE);
     10    if (V_XML_CONTENT is not null) then
     11      V_LOCATOR := CUSTOM_CONTENT_MANAGER.storeXML(V_XML_CONTENT);
     12      DBMS_XDBRESOURCE.SETCONTENT(P_RESOURCE,V_LOCATOR);
     13    end if;
     14  end;
     15  --
     16  procedure handlePreCreate(P_EVENT dbms_xevent.XDBRepositoryEvent)
     17  as
     18    V_RESOURCE_PATH     VARCHAR2(700);
     19    V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
     20  begin
     21    V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));
     22    V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
     23    storeXMLContent(V_RESOURCE);
     24  end;
     25  --
     26  procedure renderXMLContent(P_EVENT dbms_xevent.XDBRepositoryEvent, P_LOCATOR XMLTYPE)
     27  as
     28    V_XML_CONTENT       XMLType;
     29    V_CSID              BINARY_INTEGER;
     30  begin
     31    select NLS_CHARSET_ID(VALUE)
     32      into V_CSID
     33      from NLS_DATABASE_PARAMETERS
     34     where PARAMETER = 'NLS_CHARACTERSET';
     35
     36    V_XML_CONTENT := CUSTOM_CONTENT_MANAGER.getXML(P_LOCATOR);
     37    DBMS_XEVENT.setRenderStream(P_EVENT,V_XML_CONTENT.getBlobVal(V_CSID));
     38  end;
     39  --
     40  procedure handleRender(P_EVENT dbms_xevent.XDBRepositoryEvent)
     41  as
     42    V_RESOURCE_PATH     VARCHAR2(700);
     43    V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
     44
     45  begin
     46
     47    V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));
     48    V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
     49    renderXMLContent(P_EVENT, DBMS_XDBRESOURCE.getContentXML(V_RESOURCE));
     50  end;
     51  --
     52  procedure handlePreDelete(P_EVENT dbms_xevent.XDBRepositoryEvent)
     53  as
     54    V_RESOURCE_PATH     VARCHAR2(700);
     55    V_RESOURCE          DBMS_XDBRESOURCE.xdbResource;
     56    V_CSID              BINARY_INTEGER;
     57    V_LOCATOR           XMLTYPE := NULL;
     58    V_ROWID             ROWID;
     59
     60  begin
     61
     62    V_RESOURCE_PATH     := DBMS_XEVENT.getName(DBMS_XEVENT.getPath(P_EVENT));
     63    V_RESOURCE          := DBMS_XEVENT.getResource(P_EVENT);
     64    V_LOCATOR           := DBMS_XDBRESOURCE.getContentXML(V_RESOURCE);
     65
     66    if (V_LOCATOR is not null) then
     67      CUSTOM_CONTENT_MANAGER.deleteContent(V_LOCATOR);
     68    end if;
     69  --
     70  end;
     71  --
     72  end;
     73  /
    
    Package body created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> create table MY_XML_CONTENT of XMLTYPE
      2  XMLTYPE STORE AS SECUREFILE BINARY XML
      3  /
    
    Table created.
    
    SQL> create index MY_XML_INDEX
      2      on MY_XML_CONTENT(OBJECT_VALUE)
      3         indextype is XDB.XMLINDEX
      4  /
    
    Index created.
    
    SQL> create or replace package body CUSTOM_CONTENT_MANAGER
      2  as
      3  --
      4  function newLocator(P_OBJECT_ID RAW)
      5  return xmltype
      6  as
      7    V_LOCATOR XMLTYPE;
      8  begin
      9    select xmlElement
     10           (
     11             "locator",
     12              xmlAttributes('http:/xmlns.example.com/xdb/pm/contentLocator' as "xmlns"),
     13              xmlElement("OBJECTID",P_OBJECT_ID)
     14             )
     15        into V_LOCATOR
     16      from dual;
     17
     18          return V_LOCATOR;
     19  end;
     20  --
     21  function getObjectId(P_LOCATOR XMLTYPE)
     22  return RAW
     23  as
     24    V_OBJECT_ID RAW(16);
     25  begin
     26    select XMLCAST
     27                 (
     28                    XMLQUERY
     29                    (
     30                      'declare default element namespace "http:/xmlns.example.com/xdb/pm/contentLocator"; (: :)
     31                      $L/locator/OBJECTID'
     32                      passing P_LOCATOR as "L" returning CONTENT
     33                    )
     34                    as RAW(16)
     35                 )
     36            into V_OBJECT_ID
     37            from DUAL;
     38
     39          return V_OBJECT_ID;
     40  end;
     41  --
     42  function storeXML(P_XML_CONTENT XMLType)
     43  return XMLTYPE
     44  as
     45    V_OBJECT_ID RAW(16);
     46  begin
     47    insert into MY_XML_CONTENT values (P_XML_CONTENT) returning OBJECT_ID into V_OBJECT_ID;
     48    return newLocator(V_OBJECT_ID);
     49  end;
     50  --
     51  function getXML(P_LOCATOR XMLTYPE)
     52  return XMLType
     53  as
     54    V_XML_CONTENT XMLTYPE;
     55    V_OBJECT_ID RAW(16) := getObjectId(P_LOCATOR);
     56  begin
     57          select OBJECT_VALUE
     58            into V_XML_CONTENT
     59            from MY_XML_CONTENT
     60           where OBJECT_ID = V_OBJECT_ID;
     61          return V_XML_CONTENT;
     62  end;
     63  --
     64  procedure deleteContent(P_LOCATOR XMLType)
     65  as
     66    V_OBJECT_ID RAW(16) := getObjectId(P_LOCATOR);
     67  begin
     68    delete
     69      from MY_XML_CONTENT
     70           where OBJECT_ID = OBJECT_ID;
     71  end;
     72  --
     73  end;
     74  /
    
    Package body created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> grant execute on CUSTOM_CONTENT_EVENTS to public
      2  /
    
    Grant succeeded.
    Usage and plans
    SQL> declare
      2    V_RESULT BOOLEAN;
      3  begin
      4    V_RESULT := DBMS_XDB.CREATEFOLDER(:TARGET_FOLDER);
      5    V_RESULT := DBMS_XDB.CREATEFOLDER(:UPLOAD_FOLDER);
      6    V_RESULT := DBMS_XDB.CREATERESOURCE(:RESCONFIG_PATH,:RESCONFIG);
      7    COMMIT;
      8
      9    dbms_resconfig.addResConfig(:UPLOAD_FOLDER,:RESCONFIG_PATH,null);
     10  end;
     11  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select XMLSERIALIZE(DOCUMENT XDBURITYPE(:RESCONFIG_PATH).getXML() as CLOB INDENT SIZE = 2)  RESCONFIG
      2    from DUAL
      3  /
    <ResConfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd http://xmlns.oracle.com/xdb/XDBResConfig.xsd" enable="true">
      <event-listeners set-invoker="true">
        <listener>
          <description>CUSTOM CONTENT STORE</description>
          <schema>EVENTS</schema>
          <source>CUSTOM_CONTENT_EVENTS</source>
          <language>PL/SQL</language>
          <events>
            <Pre-Create/>
            <Pre-Delete/>
            <Render/>
          </events>
          <pre-condition>
            <existsNode>
              <XPath>/r:Resource[r:ContentType=&quot;text/xml&quot;]</XPath>
              <namespace>xmlns:r=&quot;http://xmlns.oracle.com/xdb/XDBResource.xsd&quot;</namespace>
            </existsNode>
          </pre-condition>
        </listener>
      </event-listeners>
      <defaultChildConfig>
        <configuration/>
      </defaultChildConfig>
    </ResConfig>
    
    
    SQL> select path, dbms_RESConfig.getResConfigPaths(path)
      2    from path_view
      3   where under_path(res,:TARGET_FOLDER) = 1
      4  /
    /public/uploadExample/ResConfig.xml
    XDB$STRING_LIST_T()
    
    /public/uploadExample/uploadFolder
    XDB$STRING_LIST_T('/public/uploadExample/ResConfig.xml')
    
    
    SQL>
    SQL> set long 100000 pages 0 lines 256
    SQL> column LOG format A250
    SQL> --
    SQL> select OBJECT_VALUE
      2    from MY_XML_CONTENT
      3  /
    
    no rows selected
    
    SQL> declare
      2    V_RESULT BOOLEAN;
      3    V_XMLDOC_PATH VARCHAR2(700) := :UPLOAD_FOLDER || '/test.xml';
      4  begin
      5    V_RESULT := DBMS_XDB.CREATERESOURCE(V_XMLDOC_PATH,XMLTYPE('<Hello>World</Hello>'));
      6    COMMIT;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select OBJECT_VALUE
      2    from MY_XML_CONTENT
      3  /
    <Hello>World</Hello>
    
    SQL> select xmlserialize(DOCUMENT RES as CLOB INDENT SIZE = 2)
      2    from RESOURCE_VIEW
      3   where equals_path(RES,:UPLOAD_FOLDER || '/test.xml') = 1
      4  /
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
    alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
    ef="true">
      <CreationDate>2010-11-25T22:55:18.646000</CreationDate>
      <ModificationDate>2010-11-25T22:55:18.646000</ModificationDate>
      <DisplayName>test.xml</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>WINDOWS-1252</CharacterSet>
      <ContentType>text/xml</ContentType>
      <RefCount>1</RefCount>
      <ACL>
        <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracl
    e.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
    emaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http:
    //xmlns.oracle.com/xdb/acl.xsd" shared="true">
          <ace>
            <grant>true</grant>
            <principal>PUBLIC</principal>
            <privilege>
              <all/>
            </privilege>
          </ace>
        </acl>
      </ACL>
      <Owner>EVENTS</Owner>
      <Creator>EVENTS</Creator>
      <LastModifier>EVENTS</LastModifier>
      <Contents>
        <locator xmlns="http:/xmlns.example.com/xdb/pm/contentLocator">
          <OBJECTID>5258277CAE094B90A56E05E9D0DFADEB</OBJECTID>
        </locator>
      </Contents>
    </Resource>
    
    
    SQL> select xdburitype(:UPLOAD_FOLDER || '/test.xml').getXML()
      2    from dual
      3  /
    <?xml version="1.0" encoding="WINDOWS-1252"?>
    <Hello>World</Hello>
    
    
    SQL> set autotrace on explain lines 256 trimspool on
    SQL> --
    SQL> select object_value
      2    from MY_XML_CONTENT
      3   where XMLExists('/Hello[.="World"]' passing OBJECT_VALUE)
      4  /
    <Hello>World</Hello>
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3411889711
    
    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                                |     1 |  5036 |     3  (34)| 00:00:01 |
    |   1 |  NESTED LOOPS                 |                                |     1 |  5036 |     3  (34)| 00:00:01 |
    |   2 |   SORT UNIQUE                 |                                |     1 |  3022 |     1   (0)| 00:00:01 |
    |*  3 |    TABLE ACCESS BY INDEX ROWID| SYS1127475_MY_XML_I_PATH_TABLE |     1 |  3022 |     1   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | SYS1127475_MY_XML_I_VALUE_IX   |     1 |       |     1   (0)| 00:00:01 |
    |   5 |   TABLE ACCESS BY USER ROWID  | MY_XML_CONTENT                 |     1 |  2014 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("SYS_P0"."PATHID"=HEXTORAW('11F4')  AND SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
       4 - access("SYS_P0"."VALUE"='World')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> select *
      2    from RESOURCE_VIEW, MY_XML_CONTENT x
      3   where XMLCAST
      4         (
      5           XMLQuery
      6           (
      7             'declare namespace r = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
      8              declare namespace l = "http://xmlns.example.com/xdb/pm/contentLocator"; (: :)
      9              /r:Resource/r:Contents/l:locator/l:OBJECT_ID'
     10              passing RES returning content
     11           ) as RAW(16)
     12         ) = x.OBJECT_ID
     13  /
    ERROR:
    ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
    
    
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3679414992
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                |   214 |   463K|    92   (2)| 00:00:02 |
    |*  1 |  HASH JOIN                   |                |   214 |   463K|    92   (2)| 00:00:02 |
    |   2 |   TABLE ACCESS FULL          | MY_XML_CONTENT |     1 |  2012 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| XDB$RESOURCE   |   214 | 43870 |    88   (0)| 00:00:02 |
    |*  4 |    DOMAIN INDEX              | XDBHI_IDX      |       |       |            |          |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("X"."OBJECT_ID"=CAST(EXTRACTVALUE(SYS_MAKEXML('8758D485E6004793E034080020
                  B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/r:Resource/r:Contents/l:locator/l:OBJECT_ID
                  ',' xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
                  xmlns:l="http://xmlns.example.com/xdb/pm/contentLocator"',0,0,536872961,1086325248) AS
                  RAW(16)        ))
       4 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"XM
                  LEXTRA","XMLDATA"),'/',8888)=1)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
    
    SQL> select *
      2    from RESOURCE_VIEW, MY_XML_CONTENT x
      3   where XMLCAST
      4         (
      5           XMLQuery
      6           (
      7             'declare namespace r = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
      8              declare namespace l = "http://xmlns.example.com/xdb/pm/contentLocator"; (: :)
      9              /r:Resource/r:Contents/l:locator/l:OBJECT_ID'
     10              passing RES returning content
     11           ) as RAW(16)
     12         ) = x.OBJECT_ID
     13     and XMLExists('/Hello[.="World"]' passing OBJECT_VALUE)
     14  /
    ERROR:
    ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
    
    
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2834754709
    
    ------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                                |     1 |  5251 |    90   (0)| 00:00:02 |
    |   1 |  NESTED LOOPS                   |                                |     1 |  5251 |    90   (0)| 00:00:02 |
    |   2 |   MERGE JOIN CARTESIAN          |                                |     2 |  6454 |    88   (0)| 00:00:02 |
    |   3 |    TABLE ACCESS BY INDEX ROWID  | XDB$RESOURCE                   |   214 | 43870 |    88   (0)| 00:00:02 |
    |*  4 |     DOMAIN INDEX                | XDBHI_IDX                      |       |       |            |          |
    |   5 |    BUFFER SORT                  |                                |     1 |  3022 |     0   (0)| 00:00:01 |
    |   6 |     SORT UNIQUE                 |                                |     1 |  3022 |     0   (0)| 00:00:01 |
    |*  7 |      TABLE ACCESS BY INDEX ROWID| SYS1127475_MY_XML_I_PATH_TABLE |     1 |  3022 |     0   (0)| 00:00:01 |
    |*  8 |       INDEX RANGE SCAN          | SYS1127475_MY_XML_I_VALUE_IX   |     1 |       |     0   (0)| 00:00:01 |
    |*  9 |   TABLE ACCESS BY USER ROWID    | MY_XML_CONTENT                 |     1 |  2024 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"XMLEXTRA","XMLDATA"),
                  '/',8888)=1)
       7 - filter("SYS_P0"."PATHID"=HEXTORAW('11F4')  AND SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
       8 - access("SYS_P0"."VALUE"='World')
       9 - filter("X"."OBJECT_ID"=CAST(EXTRACTVALUE(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XM
                  LEXTRA","P"."XMLDATA"),'/r:Resource/r:Contents/l:locator/l:OBJECT_ID','
                  xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
                  xmlns:l="http://xmlns.example.com/xdb/pm/contentLocator"',0,0,536872961,1086325248) AS RAW(16)        ))
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
    
    SQL> call dbms_xdb.deleteResource(:UPLOAD_FOLDER || '/test.xml')
      2  /
    
    Call completed.
    
    SQL> commit
      2  /
    
    Commit complete.
    
    SQL> select OBJECT_VALUE
      2    from MY_XML_CONTENT
      3  /
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2174318538
    
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                |     1 |  2002 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| MY_XML_CONTENT |     1 |  2002 |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL> --
    SQL> quit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    C:\xdb\examples\Events>
    C:\xdb\examples\Events>
    I think the plan looks like it's along the lines of what you are looking for. I'm not sure where the ORA-31020 is coming from but will investigate further...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points