118 DBMS_QOPATCH

Overview

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.

Security Model

The DBMS_QOPATCH package is created as part of SYS schema and SYS is the only user who can execute these subprograms.

Operational Notes

• The package will work only if the database is OPEN.

• In an Oracle Real Application Clusters (RAC) environment, if the subprogram requires to fetch data from other RAC nodes it spawns a job in the other node(s) to get the data. In this case JOB_QUEUE_PROCESSES needs to be >0 for the package to fetch the data from other RAC nodes.

• If there is a delay in the job execution, the package returns ORA-20008 error.

Error Messages

The following table lists the exceptions raised by the DBMS_QOPATCH package.

GET_OPATCH_BUGS Function

This function provides a bugs list in a patch if the patch number is given. If a patch number is not given, it lists all the bugs in the specified XML format.

DBMS_QOPATCH.GET_OPATCH_BUGS (
   patchnum IN VARCHAR2 DEFAULT NULL);
 RETURN XMLTYPE;

GET_OPATCH_COUNT Function

This function provides the total number of installed patches in XML format.

DBMS_QOPATCH.GET_OPATCH_COUNT (
   patchnum IN VARCHAR2);
 RETURN XMLTYPE;

GET_OPATCH_DATA Function

This function provides top level patch information for the patch (such as Patch ID, patch creation time) in the XML element.

DBMS_QOPATCH.GET_OPATCH_DATA (
   patchnum IN VARCHAR2);
 RETURN XMLTYPE;

GET_OPATCH_FILES Function

This function provides the list of files modified in the given patch number in XML format.

DBMS_QOPATCH.GET_OPATCH_FILES (
   patchnum IN VARCHAR2);
 RETURN XMLTYPE;

GET_OPATCH_INSTALL_INFO Function

This function returns the XML element containing the ORACLE_HOME details such as patch and inventory location.

DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO
 RETURNS XMLTYPE;

GET_OPATCH_LIST Function

This function provides list of patches installed as an XML element from the XML inventory.

DBMS_QOPATCH.GET_OPATCH_LIST
 RETURN XMLTYPE;

GET_OPATCH_LSINVENTORY

This function returns whole opatch inventory as XML instance document.

DBMS_QOPATCH.GET_OPATCH_LSINVENTORY
 RETURN XMLTYPE;

GET_OPATCH_OLAYS Function

This function provides overlay patches for a given patch as XML element.

DBMS_QOPATCH.GET_OPATCH_OLAYS (
   patchnum IN VARCHAR2);
 RETURN XMLTYPE;

GET_OPATCH_PREQS Function

This function provides prerequisite patches for a given patch as XML element.

DBMS_QOPATCH.GET_OPATCH_PREQS (
   patchnum IN VARCHAR2);
 RETURN XMLTYPE;

GET_OPATCH_XSLT

This function returns the style-sheet for the opatch XML inventory presentation. You can use the return type of this subprogram to perform XMLTRANSFORM and the transformed result has the same appearance as opatch text output.

DBMS_QOPATCH.GET_OPATCH_XSLT
 RETURN XMLTYPE;

GET_PENDING_ACTIVITY Function

This function returns the information related to SQL patches applied on a single instance by querying the binary inventory. If this is Oracle Real Application Clusters (RAC) system, it will list the node names where the SQL patch is not applied.

DBMS_QOPATCH.GET_PENDING_ACTIVITY
 RETURN XMLTYPE;

GET_SQLPATCH_STATUS Procedure

This procedure displays the SQL patch status by querying from SQL patch registry to produce complete patch level information. If the patch number is given, it displays the information specific to the given SQL patch, otherwise information for all SQL patches.

DBMS_QOPATCH.GET_SQLPATCH_STATUS (
   patchnum IN VARCHAR2 DEFAULT NULL);

IS_PATCH_INSTALLED Function

This function provides information (such as patchID, application date, and SQL patch information) on the installed patch as XML node by querying the XML inventory.

DBMS_QOPATCH.IS_PATCH_INSTALLED (
   patchnum IN VARCHAR2);
 RETURN XMLTYPE;

PATCH_CONFLICT_DETECTION Function

This function returns the conflicting patch for a given file, if it conflicts with an existing patch.

DBMS_QOPATCH.PATCH_CONFLICT_DETECTION (
   fileName  IN VARCHAR2);
 RETURN XMLTYPE;

SET_CURRENT_OPINST Procedure

This procedure sets the node name and instance to get the inventory details specific to it in an Oracle Real Application Clusters (RAC) environment.

DBMS_QOPATCH.SET_CURRENT_OPINST (
   node_name    IN VARCHAR2 DEFAULT NULL,
   inst_name    IN VARCHAR2 DEFAULT NULL);