To get lsinventory with SQL statement in 12.1.0.2

In Oracle Database 12c there's DBMS_QOPATCH package available which is very useful to query plenty of information about patches from within the database.

The DBMS_QOPATCH package provides a PLSQL/SQL interface to view the installed database patches. The interface provides all the patch information available as part of the OPATCH LSINVENTORY -XML command. The package accesses the OUI patch inventory in real time to provide patch and meta-information.

A client-server database connection won't be able to run OPatch easily and hence the DBMS_QOPATCH API is required.

Find below scripts to get lsinventory with SQL.

1)  Query to find which patches have been applied:

      SQL> set serverout on

      SQL> exec dbms_qopatch.get_sqlpatch_status;    
2)  Query to find lsinventory from SQL:

      SQL> 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;

3)  Query to find ORACLE_HOME and inventory:

      SQL> set pagesize 0
      SQL> set long 1000000
      SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Hope these scripts will help you.

Thanks,
Chowdari