[Chapter 17] 17.4 DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance

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

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

17.4.1.1 DBMS_DEFER_QUERY programs

Table 17.14 lists the programs available in the DBMS_DEFER_QUERY package.

Table 17.14: DBMS_DEFER_QUERY Programs
Name	Description	Use in SQL?
GET_ARG_TYPE	Returns the type of a parameter in a deferred call	No
GET_CALL_ARGS	Returns information about parameters in text form	No
GET_<datatype>_ARG	Returns the value of a parameter whose type is <datatype>; values can be CHAR, DATE, NUMBER, RAW, ROWID, or VARCHAR2	No

There are no exceptions defined for this package.

17.4.1.2 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 Other Elements
Name/Type	Description
TYPE type_ary	Table of NUMBER
TYPE val_ary	Table of VARCHAR2(2000)

The PL/SQL tables type_ary and val_ary are both used in parameters to the procedure GET_CALL_ARGS; type_ary is an output array for RPC parameter datatypes and val_ary is an output array of the parameter values. Table 17.16 shows the mapping of numbers to datatypes in type_ary.

Table 17.16: Mapping Numbers to Datatypes
Datatype	Numeric Value in type_ary
BFILE (Oracle8 only)	114
BLOB (Oracle8 only)	113
CFIL (Oracle8 only)	115
CHAR	96
CFIL (Oracle8 only)	112
DATE	12
NUMBER	2
RAW	23
ROWID	11
VARCHAR2	1

Before examining the details of the individual procedures, let's look at a basic example of how they are used. Suppose that we have queued a call to the ProductMaint.AddProduct procedure, as described in an earlier example.

 SQL> EXECUTE qAddProduct
PL/SQL procedure successfully completed.

We now have an entry in the DEFCALL data dictionary view:

  1  SELECT	callno,
  2  		deferred_tran_db,
  3  		deferred_tran_id,
  4  		schemaname,
  5  		packagename,
  6  		procname,
  7  		argcount
  8* FROM	defcall
SYSTEM@D7CA SQL> /

	     Deferred          Deferred
	     Tran              Tran         Schema    Package	  Procedure Arg
Call No      DB                ID           Name      Name	  Name	    Count
------------ ----------------- ------------ --------- ----------- ---------------
9929966326029D7CA.BIGWHEEL.COM 3.58.14      SPROCKET  PRODUCTMAINTAddProduct6

1 row selected.

Here we see that the procedure ProductMaint.AddProduct is queued, and that it has six parameters. To determine what these parameters are, first determine their data types with the GET_ARG_TYPE procedure, and then determine their values with GET_<datatype>_ARG. Alternatively, you can use GET_CALL_ARGS, which returns all the information in a single call. The examples in the following sections illustrate the use of each technique.

17.4.1.3 The DBMS_DEFER_QUERY. GET_ARG_TYPE function

You can use this function in conjunction with the GET_<datatype>_ARG or GET_CALL_ARGS functions to determine information about the deferred RPCs in the queue. GET_ARG_TYPE returns a number corresponding to the argument's datatype. Here is the specification for GET_ARG_TYPE:

FUNCTION DBMS_DEFER_QUEUE.GET_ARG_TYPE
   (callno IN NUMBER,
    deferred_tran_db IN VARCHAR2,
    arg_no IN  NUMBER,
    deferred_tran_id IN VARCHAR2)
RETURN NUMBER;

The following table shows the mapping of datatypes to return values.

Argument Datatype	GET_ARG_TYPE Return Code
BFIL (Oracle8 only)	114
BLOB (Oracle8 only)	113
CFIL (Oracle8 only)	115
CHAR	96
CFIL (Oracle8 only)	112
DATE	12
NUMBER	2
RAW	23
ROWID	11
VARCHAR2	1

Notice that the datatypes here are limited to the Oracle-supplied datatypes; you cannot, for example, defer a call to a procedure that accepts a PL/SQL table as a parameter.

Parameters are summarized in the following table.

Name	Description
callno	The CALLNO of the RPC, as stored in the DEFCALL data dictionary view
deferred_tran_db	Global name of the database deferring the call (also stored in DEFCALL)
arg_no	The position of the argument in the RPC
deferred_tran_id	The deferred_tran_id for the call (also stored in DEFCALL)

There are no restrictions on calling GET_ARG_TYPE.

17.4.1.3.1 Exceptions

The GET_ARG_TYPE function may raise the following exception:

Name	Number	Description
NO_DATA_FOUND	-100	Specified argument does not exist for specified RPC call

17.4.1.3.2 Example

This example shows how you use the GET_ARG_TYPE function to determine the datatypes of a queued call:

  1  DECLARE
  2	     vDataType NUMBER;
  3  BEGIN
  4	     vDataType := DBMS_DEFER_QUERY.GET_ARG_TYPE(
  5			 callno => 9929966326029,
  6			 deferred_tran_db => 'D7CA.BIGWHEEL.COM',
  7			 arg_no => 3,
  8			 deferred_tran_id => '3.58.14');
  9	     dbms_output.put_line('Datatype for arg 1 is '|| vDataType);
 10* END;
SYSTEM@D7CA SQL> /
Datatype for arg 3 is 1

PL/SQL procedure successfully completed.

Here we see that the third argument passed to ProductMaint.AddProduct is of type VARCHAR2. Now you can use the GET_VARCHAR2_ARG function (described in the next section) to determine the value passed.

  1  DECLARE
  2	     vArgValue VARCHAR2(80);
  3  BEGIN
  4	     vArgValue := DBMS_DEFER_QUERY.GET_VARCHAR2_ARG(
  5			 callno => 9929966326029,
  6			 deferred_tran_db => 'D7CA.BIGWHEEL.COM',
  7			 arg_no => 3,
  8			 deferred_tran_id => '3.58.14');
  9	     dbms_output.put_line('Argument 3 is '|| vArgValue);
 10* END;
SYSTEM@D7CA SQL> /
Argument 3 is Mens 18 Speed Racer

PL/SQL procedure successfully completed.

Here we see that the actual value passed was "Mens 18 Speed Racer."

17.4.1.4 The DBMS_DEFER_QUERY.GET_CALL_ARGS procedure

The GET_CALL_ARGS procedure allows you to obtain the datatypes and values for all arguments passed to a procedure in a single call. This is the easiest way to obtain information about the datatypes and values of all passed parameters. Here is the specification:

PROCEDURE DBMS_DEFER_QUERY.GET_CALL_ARGS
   (callno IN NUMBER,              
    startarg IN NUMBER := 1,        
    argcnt IN NUMBER,              
    argsize IN NUMBER,           
    tran_db IN VARCHAR2,          
    tran_id IN VARCHAR2,    
    date_fmt IN VARCHAR2,      
    types OUT TYPE_ARY,          
    vals OUT VAL_ARY);

Parameters are summarized in the following table.

Name	Description
callno	The CALLNO of the RPC as stored in the DEFCALL data dictionary view
start_arg	First argument to fetch
argcnt	Number of arguments to fetch
argsize	Largest size of a returned argument
tran_db	Global name of database deferring the call (also stored in DEFCALL)
tran_id	The deferred_tran_id parameter for the call (also stored in DEFCALL)
date_fmt	Date format mask
types	Output array for argument types
vals	Output array for argument values

There are no restrictions on calling the GET_CALL_ARGS procedure.

17.4.1.4.1 Exceptions

GET_CALL_ARGS may raise the following exception:

Name	Number	Description
NO_DATA_FOUND	-100	Specified argument does not exist for specified RPC call

17.4.1.4.2 Example

The following example illustrates the use of the GET_CALL_ARGS procedure:

  1  DECLARE
  2	     vTypes DBMS_DEFER_QUERY.TYPE_ARY;
  3	     vVals  DBMS_DEFER_QUERY.VAL_ARY;
  4	     indx NUMBER;
  5  BEGIN
  6	     DBMS_DEFER_QUERY.GET_CALL_ARGS(
  7			 callno => 9929966326029,
  8			 startarg => 1,
  9			 argcnt => 6,
 10			 argsize => 128,
 11			 tran_db => 'D7CA.BIGWHEEL.COM',
 12			 tran_id => '3.58.14',
 13			 date_fmt => 'DD-Mon-YYYY hh24:MI:SS',
 14			 types => vTypes,
 15			 vals => vVals );
 16	     FOR indx IN 1..6 LOOP
 17		     dbms_output.put_line('Arg '|| indx || ': Datatype '||
 18			     vTypes(indx) || ' Value: '|| vVals(indx) );
 19	     END LOOP;
 20* END;
SYSTEM@D7CA SQL> /
Arg 1: Datatype 2 Value: 10
Arg 2: Datatype 1 Value: BIKE-0018
Arg 3: Datatype 1 Value: Mens 18 Speed Racer
Arg 4: Datatype 1 Value: 19971031-01
Arg 5: Datatype 12 Value: 31-Oct-1997 00:00:00
Arg 6: Datatype 1 Value: PLANNED

PL/SQL procedure successfully 



completed.

17.4.1.5 The DBMS_DEFER_QUERY.GET_<datatype>_ARG function

The GET_<datatype>_ARG function returns a value of a certain type (specified by <datatype>). The type of the returned value corresponds to the value of the argument specified by arg_no in the deferred RPC corresponding to callno.

There is one variant of the GET_<datatype>_ARG function for each of the Oracle-supplied datatypes. Here is the specification:

FUNCTION DBMS_DEFER_QUERY.GET_<datatype>_ARG

   
(callno IN NUMBER,                   
   deferred_tran_db IN VARCHAR2
   arg_no IN NUMBER,
   deferred_tran_id IN VARCHAR2 DEFAULT NULL)
RETURN arg;

<datatype> can be one of the following:

CHAR

DATE

NUMBER

RAW

ROWID

VARCHAR2

NCHAR (Oracle8 only)

NVARCHAR2 (Oracle8 only)

BLOB (Oracle8 only)

CLOB (Oracle8 only)

NCLOB (Oracle8 only)

Therefore, any of the following are valid:

FUNCTION DBMS_DEFER_QUERY.

GET_CHAR_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_DATE_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_NUMBER_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_RAW_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_ROWID_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_VARCHAR2_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_NCHAR_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_NVARCHAR2_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_BLOB_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_CLOB_ARG...
FUNCTION DBMS_DEFER_QUERY.

GET_NCLOB_ARG...

Parameters have the same meanings described for the GET_ARG_TYPE procedure.

17.4.1.5.1 Exceptions

The GET_<datatype>_ARG function may raise the following exceptions:

Name	Number	Description
NO_DATA_FOUND	-100	Specified argument does not exist for specified RPC call
WRONG_TYPE	-26564	Specified argument is not of type <datatype>

17.4.1.5.2 Example

Assuming that argument number 3 in the deferred call has CALLNO = 8 and DEFERRED_TRAN_ID = 45.12.3 in the DEFCALL data dictionary view is of type CHAR, follow these steps to determine the argument's value:

VARIABLE vChar	 CHAR;

BEGIN
	vChar := DBMS_QUERY.GET_CHAR_ARG(callno => 8,
					



deferred_tran_db	=> 'D8CA.BIGWHEEL.COM',
					arg_no => 3,
					

deferred_tran_id => 45.12.3);




END;
/