1 2 3 Previous Next 33 Replies Latest reply: Nov 29, 2010 4:12 PM by mdrake-Oracle Go to original post RSS
      • 15. Re: XMLIndex Value Index on repository
        Marco Gralike
        forgot. sorry :-(
        • 16. Re: XMLIndex Value Index on repository
          811001
          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
            Marco Gralike
            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
              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-Oracle
                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-Oracle
                  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-Oracle
                     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
                      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
                        Marco Gralike
                        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
                          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
                            Marco Gralike
                            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
                              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
                                Marco Gralike
                                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
                                  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-Oracle
                                    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...