SET linesize 100
COL directory_name FORMAT a30
COL directory_path FORMAT a50

SELECT directory_name, directory_path
  FROM dba_directories
 WHERE directory_name like 'OPATCH%';

COL owner FORMAT a5
COL object_name FORMAT a25
COL object_type FORMAT a15
COL status FORMAT a6

SELECT owner, object_name, object_type, status
  FROM dba_objects
 WHERE object_name like '%OPATCH%';

SET pagesize 1000
SET long 50000

SELECT
   xmltransform(dbms_qopatch.GET_OPATCH_INSTALL_INFO,
                dbms_qopatch.GET_OPATCH_XSLT)
   as "ORACLE HOME DETAILS"
  FROM dual;

SET long 50000
SET pagesize 10000

SELECT
   xmltransform(dbms_qopatch.GET_OPATCH_BUGS, dbms_qopatch.GET_OPATCH_XSLT)
   as "BUGS FIXED"
  FROM dual;

SELECT
   xmltransform(dbms_qopatch.get_opatch_list(), dbms_qopatch.GET_OPATCH_XSLT())
   as "OPATCH LIST"
  FROM dual; 

SELECT
   xmltransform(dbms_qopatch.is_patch_installed(18031528),
                dbms_qopatch.GET_OPATCH_XSLT())
   as "PATCH INSTALLED"
  FROM dual;

SET linesize 60

SELECT dbms_qopatch.is_patch_installed(18031528) 
  FROM dual;


SELECT xmltransform(dbms_qopatch.is_patch_installed(18031528),
   '<xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <xsl:template match="patchInfo">
    <xsl:text>Patch Id= </xsl:text><xsl:value-of SELECT="patchID"/>
    <xsl:text> Applied: </xsl:text> <xsl:value-of SELECT="sqlPatch"/>
    </xsl:template>
    </xsl:stylesheet>') as "CUSTOM XLST SHEET"
  FROM dual;