 | Chapter 17 Deferred Transactions and Remote Procedure Calls |  |
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.
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.
Name | Description | Use in SQL? |
|---|---|---|
Returns the type of a parameter in a deferred call | No | |
Returns information about parameters in text form | No | |
Returns the value of a parameter whose type is <datatype>; values can be CHAR, DATE, NUMBER, RAW, ROWID, or VARCHAR2 | No |
Table 17.15 lists the nonprogram elements defined for the DBMS_DEFER_QUERY package.
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.
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.
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.
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 |
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."
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.
GET_CALL_ARGS may raise the following exception:
Name | Number | Description |
|---|---|---|
NO_DATA_FOUND | -100 | Specified argument does not exist for specified RPC call |
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.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.
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> |
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.