DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance

Occasionally, you may want to see details about deferred RPCs in the queue, such as what procedure and parameters are used. The DBMS_DEFER_QUERY package contains procedures to display this data.

Getting Started with DBMS_DEFER_QUERY

The DBMS_REPCAT_QUERY package is created when the Oracle database is installed. The dbmsdefr.sql script (found in the built-in packages source directory, as described in Chapter 1) contains the source code for this package’s specification. This script is called by catrep.sql , which must be run to install the advanced replication packages. The wrapped sql script prvtrctf.sql creates the public synonym DBMS_REPCAT_QUERY. No EXECUTE privileges are granted on DBMS_REPCAT_QUERY; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.


Table 17.14 lists the programs available in the DBMS_DEFER_QUERY package.

Table 17-14. DBMS_DEFER_QUERY Programs



Use in SQL?


Returns the type of a parameter in a deferred call



Returns information about parameters in text form



Returns the value of a parameter whose type is <datatype>; values can be CHAR, DATE, NUMBER, RAW, ROWID, or VARCHAR2


There are no exceptions defined for this package.

DBMS_DEFER_QUERY nonprogram elements

Table 17.15 lists the nonprogram elements defined for the DBMS_DEFER_QUERY package.

Table 17-15. DBMS_DEFER_QUERY ...

Get Oracle Built-in Packages now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.