Oracle Database - Query opatch inventory using SQL interface

  

Listing alternate way to check opatch  details , using   sqlplus 

==> Sql to check output similar to  opatch lsinventory 

with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

  select x.*

    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',

               applied_date varchar2(30) path 'appliedDate',

               sql_patch varchar2(8) path 'sqlPatch',

               rollbackable varchar2(8) path 'rollbackable'

         ) x;

==> Sql  to check detailed  output . similar to opatch lsinventory detail

with a as (select dbms_qopatch.get_opatch_bugs patch_output from dual)

  select x.*

    from a,

         xmltable('bugInfo/bugs/*'

            passing a.patch_output

            columns

               bug_id number path '@id',

               description varchar2(160) path 'description'

         ) x;

==> Checking Precise output 

select patch_id, patch_uid, target_version, status, description, action_time

from dba_registry_sqlpatch

where action = 'APPLY';