#

| Chapter 17 Deferred Transactions and Remote Procedure Calls |

## 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;
>     /

17.3 DBMS_DEFER: Building Deferred Calls |  | A. What's on the Companion Disk?

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.