Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)

E17602-14
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

116 DBMS_QOPATCH

The DBMS_QOPATCH package provides an interface to view the installed database patches.

This chapter contains the following topics:


Using 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.


Exceptions

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

Table 116-1 Exceptions Raised by DBMS_QOPATCH

Exception Error Code Description

INVENTORY_NOT_LOADED

ORA-20001

Latest XML inventory is not loaded into table.

CREATE_DIR_FAILED

ORA-20002

Directory creation failed.

OTHERS

ORA-20003

Configuration of a job on a node failed.

INCORRECT_NODE_NAME

ORA-20004

Job configuration failed as node is inactive.

NO_DATA_FOUND

ORA-20005

Job is not configured with given node, instance name.

NO_MATCH_CJOBS

ORA-20006

Number of RAC active instances and opatch jobs configured are not same.

INCORRECT_NODE_NAME

ORA-20007

Job configuration failed as node or instance is not active.

JOB_RUN_TIMEOUT

ORA-20008

Timed out - job execution time is more than 120Secs.

JOB_RUN_FAILED

ORA-20009

Job execution failed.

NODE_INACTIVE

ORA-20010

Node is inactive and job cannot be executed.

NULL_JOBNAME

ORA-20011

Job name is NULL and inventory cannot be loaded.

JQP_SET_ZERO

ORA-20012

JOB_QUEUE_PROCESSES is set to zero and the inventory can not be loaded.



Summary of DBMS_QOPATCH Subprograms

Table 116-2 DBMS_QOPATCH Package Subprograms

Subprogram Description

GET_OPATCH_BUGS Function

Provides a bugs list for a patch in XML format if the patch number is given. If patch is not given then it lists all the bugs installed in all the patches in XML format.

GET_OPATCH_COUNT Function

Provides the total number of installed patches in XML format

GET_OPATCH_DATA Function

Provides top level patch information for the patch (such as Patch ID, patch creation time) in the XML element

GET_OPATCH_FILES Function

Provides the list of files modified in the given patch number in XML format

GET_OPATCH_INSTALL_INFO Function

Returns the XML element containing the ORACLE_HOME details such as patch and inventory location

GET_OPATCH_LIST Function

Provides list of patches installed as an XML element from the XML inventory

GET_OPATCH_LSINVENTORY

Returns whole opatch inventory as XML instance document.

GET_OPATCH_OLAYS Function

Provides overlay patches for a given patch as XML element

GET_OPATCH_PREQS Function

Provides prerequisite patches for a given patch as XML element

GET_OPATCH_XSLT

Returns the style-sheet for the opatch XML inventory presentation

GET_PENDING_ACTIVITY Function

Returns the information related to SQL patches applied on a single instance by querying the binary inventory

GET_SQLPATCH_STATUS Procedure

Displays the SQL patch status by querying from SQL patch registry to produce complete patch level information

IS_PATCH_INSTALLED Function

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

PATCH_CONFLICT_DETECTION Function

Returns the conflicting patch for a given file, if it conflicts with an existing patch

SET_CURRENT_OPINST Procedure

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



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.

Syntax

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

Parameters

Table 116-3 GET_OPATCH_BUGS Function Parameters

Parameter Description

patchnum

Patch number



GET_OPATCH_COUNT Function

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

Syntax

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

Parameters

Table 116-4 GET_OPATCH_COUNT Function Parameters

Parameter Description

patchnum

Patch number



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.

Syntax

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

Parameters

Table 116-5 GET_OPATCH_DATA Function Parameters

Parameter Description

patchnum

Patch number



GET_OPATCH_FILES Function

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

Syntax

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

Parameters

Table 116-6 GET_OPATCH_FILES Function Parameters

Parameter Description

patchnum

Patch number



GET_OPATCH_INSTALL_INFO Function

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

Syntax

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.

Syntax

DBMS_QOPATCH.GET_OPATCH_LIST
 RETURN XMLYPE;

GET_OPATCH_LSINVENTORY

This function returns whole opatch inventory as XML instance document.

Syntax

DBMS_QOPATCH.GET_OPATCH_LSINVENTORY
 RETURN XMLYPE;

GET_OPATCH_OLAYS Function

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

Syntax

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

Parameters

Table 116-7 GET_OPATCH_OLAYS Function Parameters

Parameter Description

patchnum

Patch number



GET_OPATCH_PREQS Function

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

Syntax

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

Parameters

Table 116-8 GET_OPATCH_PREQS Function Parameters

Parameter Description

patchnum

Patch number



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.

Syntax

DBMS_QOPATCH.GET_OPATCH_XSLT
 RETURN XMLYPE;

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.

Syntax

DBMS_QOPATCH.GET_PENDING_ACTIVITY
 RETURN XMLYPE;

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.

Syntax

DBMS_QOPATCH.GET_SQLPATCH_STATUS (
   patchnum IN VARCHAR2 DEFAULT NULL);

Parameters

Table 116-9 GET_SQLPATCH_STATUS Procedure Parameters

Parameter Description

patchnum

Patch number



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.

Syntax

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

Parameters

Table 116-10 IS_PATCH_INSTALLED Function Parameters

Parameter Description

patchnum

Patch number



PATCH_CONFLICT_DETECTION Function

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

Syntax

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

Parameters

Table 116-11 PATCH_CONFLICT_DETECTION Function Parameters

Parameter Description

fileName

File name



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.

Syntax

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

Parameters

Table 116-12 PATCH_CONFLICT_DETECTION Function Parameters

Parameter Description

node_name

Name of node

inst_name

Name of instance