(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()
                COLUMNS
                 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,
       t.uniquePatchID,
       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 
              COLUMNS
               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,
       p1.PatchID, 
       p1.uniquePatchID, 
       p2.BugID, 
       p2.BugDescription
from SYS.OPATCH_XML_INV op
   , xmltable('/InventoryInstance/patches/patch'
              PASSING xmltype(op.xml_inventory)
              COLUMNS
                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
              COLUMNS
                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.

😎

HTH/M