Obtaining Database PSU history from SQL

With Oracle 12c it is now possible to query patch information directly from SQL.

This is done using the DBMS_QOPATCH package to fetch the output from OPatch as an XML document. We then process this document using xmltabl() to map the attributes to columns and join to DBA_REGISTRY_SQLPATCH. Here's an example of the output:

  PATCH_ID BUNDLE_SERIES                  DESCRIPTION                                                  PATCH_DATE
---------- ------------------------------ ------------------------------------------------------------ ------------
  24732082 PSU                            DATABASE PATCH SET UPDATE 12.1.0.2.170117                    17-JAN-2017

The query to produce this is shown below. There are additional columns available in DBA_REGISTRY_SQLPATCH that you might also want to display.

 with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.patch_id
     , bundle_series
     , s.description
     , to_char(to_date(regexp_substr(s.description,'\.[0-9]{6}'),'.YYMMDD'),'DD-MON-YYYY') patch_date
  from a,
       xmltable('InventoryInstance/patches/*'
          passing a.patch_output
          columns
             patch_id number path 'patchID',
             patch_uid number path 'uniquePatchID',
             description varchar2(80) path 'patchDescription',
             rollbackable varchar2(8) path 'rollbackable'
       ) x
           , dba_registry_sqlpatch s
 where x.patch_id = s.patch_id
   and x.patch_uid = s.patch_uid
  order by patch_date