(X)Query OPatch Information From The Database

There are cool posts out there regarding querying the OPatch info in Oracle 12 which can be accessed by DBMS_QOPATCH like…

So while checking up the result of my JSON Patch Bundle actions, I remembered the posts regarding this Oracle 12.x package. Fiddling a bit around with it, most have XMLType as return value (no undiscovered country for me), and going from easy stuff like to check the XMLType content (in pretty print format)

select xmlserialize(content dbms_qopatch.get_opatch_install_info() as CLOB indent size=1) OracleHome 
from dual;
select xmlserialize(content dbms_qopatch.get_opatch_bugs() as CLOB indent size=1) PatchInfo
from dual;
select xmlserialize(content dbms_qopatch.get_opatch_lsinventory()as CLOB indent size=1) LsInventory
from dual;

to fiddling with output and using this as input for other stuff (aka “wondering about Pythian/why use recursive sql…why use not dbms_qopatch.get_opatch_bugs() etc. directly?! )

select t.bugid
     , t.description
  from xmltable('/bugInfo/bugs/bug'
                PASSING dbms_qopatch.get_opatch_bugs()
                 rno for ordinality,
                 bugid varchar2(38) path '@id',
                 description varchar2(60) path 'description'
                ) t;

Procedures like dbms_qopatch.get_opatch_bugs() return XMLTYPE directly so…

or even use the output of the XMLTABLE statement as input again for DBMS_QOPATCH like…

select t.PatchID,
       xmlserialize(content dbms_qopatch.GET_OPATCH_DATA(t.PatchID) as CLOB indent size=1) 
              as PatchIdInfo
 from xmltable('/patches/patch'
              PASSING dbms_qopatch.GET_OPATCH_LIST 
               rno for ordinality,
               uniquePatchID varchar2(38) path 'uniquePatchID',
               PatchID varchar2(38) path 'patchID'
             ) t;  

But in all, this is a bit stupid, I wondered… Would Oracle always guarantee the identical XMLType return structure. In principal the package is based on the external table SYS.OPATCH_XML_INV which shows the output of the statement “OPATCH LSINVENTORY -XML”. If I would make a create XQuery statements based on XMLTYPE return values of the DBMS_QOPATCH or directly based on the XML_INVENTORY (CLOB datatype) column; If the structure changes, it changes and my statements would “break” whatever…so why not do the stuff I do and remove some of the PL/SQL layers, which bt.w. executes XMLQUERY statements anyway to produce XMLType content in return…

The following (as and advantage) creates the info and (relational) format, I need, for example the bug ID’s belonging to a patch ID:

select /*+ NO_XML_QUERY_REWRITE */ 
       p1.p_rno||'.'||p2.b_rno as rno,
   , xmltable('/InventoryInstance/patches/patch'
              PASSING xmltype(op.xml_inventory)
                p_rno for ordinality,
                uniquePatchID varchar2(50) path 'uniquePatchID',
                PatchID varchar2(50) path 'patchID',
                patchXML XMLTYPE path 'bugs' 
             ) p1
   , xmltable('bugs/bug'
              PASSING p1.patchXML
                b_rno for ordinality,
                BugID varchar2(50) path '@id',
                BugDescription varchar2(4000) path 'description'
             ) p2 ;

Okay, I admit…

My stuff will NOT be Real Application Server savvy, and it doesn’t refresh the content (REFRESH_OPATCH_DATA) but hey, I did remove some unneeded layers (that is SQL/XQuery/PLSQL engine context switches).

Okay, I admit…I might be just stubborn.



Marco Gralike Written by: