5 Replies Latest reply: May 1, 2013 12:15 PM by odie_63 RSS

    Dynamically adding attribute based on PL/SQL function

    paul zip
      I've been tasked with dynamically applying security to an XMLType input sample as a post XML generation process (on an 11g R2 DB) based on several parameters. The best way to describe it is with an example block of code, I need to write ApplySecurity....
      declare
        vXML XMLType;
        BONUS_SEC constant integer := 24; -- Security identifier for Bonus
        -------
        function ApplySecurity(pXML        XMLType, 
                               pSecurityID integer,    -- A Security identifier
                               pDataIDPath varchar2,   -- Absolute XPath to the data id which is passed to value
                               pAttribPath varchar2    -- Relative XPath to pDataIDPath where we want the security result attribute stored 
                               ) return XMLType is
        begin
          /* ....
            for all DataIDs found in pDataIDPath
              Call an PL/SQL function GetSecurityDesc(pSecurityID, pDataID)
                Store the result of this back in the XML in the pAttribPath as SECURITY attribute
          */
        end;
        -------  
      begin
        vXML := ApplySecurity(XMLType('<ROWSET>
                                         <ROW>                                 
                                           <BONUS_ID>8</BONUS_ID>
                                           <DESCRIPTION>Test Bonus</DESCRIPTION>                                                                            
                                         </ROW>
                                         <ROW>                                 
                                           <BONUS_ID>9</BONUS_ID>
                                           <DESCRIPTION>Another Bonus</DESCRIPTION>                                                                            
                                         </ROW>                          
                                         <ROW>                                 
                                           <BONUS_ID>10</BONUS_ID>
                                           <DESCRIPTION>April Bonus</DESCRIPTION>                                                                            
                                         </ROW>                          
                                       </ROWSET>'),
                               BONUS_SEC,        
                               '/ROWSET/ROW/BONUS_ID',
                               '.');
      end;
      /
      /* Example of the Desired Output =
       
      <ROWSET>
        <ROW>                                 
          <BONUS_ID SECURITY="HIDDEN">8</BONUS_ID>
          <DESCRIPTION>Test Bonus</DESCRIPTION>                                                                            
        </ROW>
        <ROW>                                 
          <BONUS_ID SECURITY="READONLY">9</BONUS_ID>
          <DESCRIPTION>Another Bonus</DESCRIPTION>                                                                            
        </ROW>                          
        <ROW>                                 
          <BONUS_ID SECURITY="NONE">10</BONUS_ID>
          <DESCRIPTION>April Bonus</DESCRIPTION>                                                                            
        </ROW>                          
      </ROWSET>
      
      */
      I have tried several approaches like iterating using a dynamically created XMLTable and calling InsertChildXML for each iteration, but that seems messy and inefficient as the call to InsertChildXML will have to locate the correct node each time. Ideally I think the best solution would be to do it with XQuery, but online examples are few and far between and I don't know how I'd call a PL/SQL function as part of an XQuery iteration as well as passing in the path information.

      Anyone have any ideas? Thanks!
        • 1. Re: Dynamically adding attribute based on PL/SQL function
          odie_63
          There are multiple solutions to this requirement.
          Finding the best one would need you to give a little more details :

          - Exact db version (SELECT * FROM v$version)
          - What does GetSecurityDesc() function do internally? Does it access a database table? Does it access a different table based on pSecurityID ?
          - How much data are we talking about here ? In the XML ? In the lookup table (assuming there's one) ?
          - What's the storage option of the XMLType column ? Binary XML, CLOB, Object-Relational ?
          Ideally I think the best solution would be to do it with XQuery, but online examples are few and far between and I don't know how I'd call a PL/SQL function as part of an XQuery iteration as well as passing in the path information.
          XQuery cannot call user-defined PL/SQL code, but it can access database tables or views and expose it as XML.
          That's why I asked about the code behind GetSecurityDesc().

          Depending on the answers to the above questions, I see a solution using XSLT or XQuery.
          • 2. Re: Dynamically adding attribute based on PL/SQL function
            paul zip
            Thanks for replying. In reply to your questions....

            1. Exact DB = Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

            2. In regards to GetSecurityDesc() - Our system has a security module whereby we publish security elements pertaining to certain logical groupings of functionality in the system. This is in a hierarchical structure and allow app administrators to define what each user can have access to. For example, if you work in accounts you'd be able to read an edit bonus information. If you work in customer support you shouldn't (unless you are a customer support manager). So each security element can be either Hidden, Read Only or Read Write. A kind of inheritance occurs so that hiding a parent security element also hides children unless administrators override it.

            e.g.
            For a customer support manager they may have the following set.
            
            Accounting, ID = 101 (Hidden)
              Pricing, ID = 222
                Bonuses, ID = 326 
                  Sales target, ID = 326, Data ID = 23 (Read Only)
                  Big deal reward, ID = 326, Data ID = 25
                Pay rises, ID = 405 (Hidden)
            Assuming a bonus called "Sales target" BONUS_ID = 23. If I wanted to know if a user could see "Sales target" I'd call
            GetSecurityDesc(326 , /* Bonuses security ID */, 
                            23    /* "Sales Target" data id */) => 'READONLY'
            In this respect, GetSecurityDesc is not a simple view (it is actually a package function with quite a lot of associated business logic).

            3. I kept the example sample down to a minimum, but we have XML to process that can be say from a few hundred bytes to 30 kB or more. The security lookup has several tables involved : published security elements, which apps have access to which security elements and settings per user. All are indexed / caching occurs and access is quick though.

            4. The XMLType data is not stored, it is generated by a package function as part of a XML over HTTP / Web Service, i.e. Input XML (request), Output XML (response).
            function P_Bonus.GetBonusInfo(pRequestXML XMLType) return XMLType is
              vResponseXML XMLType;
            begin
             ...
              return vResponseXML;
            end;
            I hope this makes things clearer.
            • 3. Re: Dynamically adding attribute based on PL/SQL function
              odie_63
              In this respect, GetSecurityDesc is not a simple view (it is actually a package function with quite a lot of associated business logic).
              OK, then that closes some of the doors to the bulk-processing solutions I was thinking of.

              In this situation, a pure DOM approach may be fine enough :
              SQL> set serveroutput on
              SQL> 
              SQL> declare
                2  
                3    BONUS_SEC  constant integer := 24;
                4  
                5    vXML XMLType := XMLType(
                6  '<ROWSET>
                7    <ROW>
                8      <BONUS_ID>8</BONUS_ID>
                9      <DESCRIPTION>Test Bonus</DESCRIPTION>
               10    </ROW>
               11    <ROW>
               12      <BONUS_ID>9</BONUS_ID>
               13      <DESCRIPTION>Another Bonus</DESCRIPTION>
               14    </ROW>
               15    <ROW>
               16      <BONUS_ID>10</BONUS_ID>
               17      <DESCRIPTION>April Bonus</DESCRIPTION>
               18    </ROW>
               19  </ROWSET>');
               20  
               21    doc       dbms_xmldom.DOMDocument;
               22    docNode   dbms_xmldom.DOMNode;
               23    anyNode   dbms_xmldom.DOMNode;
               24    nodeList  dbms_xmldom.DOMNodeList;
               25    eltNode   dbms_xmldom.DOMElement;
               26  
               27    secAttr   varchar2(30);
               28  
               29    pSecurityID integer      := BONUS_SEC;
               30    pDataIDPath varchar2(80) := '/ROWSET/ROW/BONUS_ID';
               31    pAttribPath varchar2(80) := '.';
               32  
               33  begin
               34  
               35    doc := dbms_xmldom.newDOMDocument(vXML);
               36    docNode := dbms_xmldom.makeNode(doc);
               37    nodeList := dbms_xslprocessor.selectNodes(docNode, pDataIDPath);
               38  
               39    for i in 0 .. dbms_xmldom.getLength(nodeList) loop
               40  
               41      anyNode := dbms_xmldom.item(nodeList, i);
               42      eltNode := dbms_xmldom.makeElement(anyNode);
               43      secAttr := GetSecurityDesc(pSecurityID, dbms_xslprocessor.valueOf(anyNode, pAttribPath));
               44      dbms_xmldom.setAttribute(eltNode, 'SECURITY', secAttr);
               45  
               46    end loop;
               47  
               48    dbms_xmldom.freeDocument(doc);
               49    dbms_output.put_line(vXML.getclobval);
               50  
               51  end;
               52  /
               
              <ROWSET>
                <ROW>
                  <BONUS_ID SECURITY="HIDDEN">8</BONUS_ID>
                  <DESCRIPTION>Test Bonus</DESCRIPTION>
                </ROW>
                <ROW>
                  <BONUS_ID SECURITY="READ-ONLY">9</BONUS_ID>
                  <DESCRIPTION>Another Bonus</DESCRIPTION>
                </ROW>
                <ROW>
                  <BONUS_ID SECURITY="NONE">10</BONUS_ID>
                  <DESCRIPTION>April Bonus</DESCRIPTION>
                </ROW>
              </ROWSET>
              
               
              PL/SQL procedure successfully completed
               
              (tested with a dummy GetSecurityDesc() function based on your input/output samples)
              • 4. Re: Dynamically adding attribute based on PL/SQL function
                paul zip
                That's a very elegant solution to quite an awkward problem. I wasn't really familiar with the XML DOM and XSLT packages in Oracle. Having tested on one of the biggest files we have, the performance was good too. Many thanks!
                • 5. Re: Dynamically adding attribute based on PL/SQL function
                  odie_63
                  A funnier option, adapted from http://odieweblog.wordpress.com/2012/06/19/how-to-update-xml-nodes-with-values-from-same-doc/ :
                  declare
                  
                    BONUS_SEC  constant integer := 24;
                  
                    vXML XMLType := XMLType(
                  '<ROWSET>
                    <ROW>                                 
                      <BONUS_ID>8</BONUS_ID>
                      <DESCRIPTION>Test Bonus</DESCRIPTION>                                                                            
                    </ROW>
                    <ROW>                                 
                      <BONUS_ID>9</BONUS_ID>
                      <DESCRIPTION>Another Bonus</DESCRIPTION>                                                                            
                    </ROW>                          
                    <ROW>                                 
                      <BONUS_ID>10</BONUS_ID>
                      <DESCRIPTION>April Bonus</DESCRIPTION>                                                                            
                    </ROW>                          
                  </ROWSET>');
                    
                    pSecurityID integer      := BONUS_SEC;
                    pDataIDPath varchar2(80) := '/ROWSET/ROW/BONUS_ID';
                    pAttribPath varchar2(80) := '.';
                    
                    queryString varchar2(4000);
                    
                  begin
                  
                    queryString := 
                  q'~select xmlpatch(
                           :b1
                         , xmlelement("xd:xdiff"
                           , xmlattributes(
                               'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                             , 'http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd' as "xsi:schemaLocation"
                             , 'http://xmlns.oracle.com/xdb/xdiff.xsd' as "xmlns:xd"
                             )
                           , xmlpi("oracle-xmldiff", 'operations-in-docorder="true" output-model="current"')
                           , xmlagg(
                               xmlelement("xd:append-node"
                               , xmlattributes(
                                   'attribute' as "xd:node-type"
                                 , '(#DATA-ID-PATH#)[' || x.NodeOrder || ']' as "xd:parent-xpath"
                                 , 'SECURITY' as "xd:attr-local"
                                 )
                               , xmlelement("xd:content", GetSecurityDesc(:b2, x.DataID))
                               )
                             )
                           )
                         )
                  from xmltable('#DATA-ID-PATH#' 
                         passing :b3
                         columns DataID    integer path '#ATTRIB-PATH#'
                               , NodeOrder for ordinality ) x~';
                  
                    queryString := replace(queryString, '#DATA-ID-PATH#', pDataIDPath);
                    queryString := replace(queryString, '#ATTRIB-PATH#', pAttribPath);
                    
                    execute immediate queryString into vXML using vXML, BONUS_SEC, vXML ;
                  
                    dbms_output.put_line(vXML.getclobval);
                  
                  end;
                  /
                  probably slower though.