#

| Chapter 17 Deferred Transactions and Remote Procedure Calls |

## 17.3 DBMS_DEFER: Building Deferred Calls

The DBMS_DEFER package builds deferred remote procedure calls.

> WARNING: DBMS_DEFER can execute procedures at remote sites under a highly privileged account, such as the replication administrator account. Therefore, EXECUTE privileges on DBMS_DEFER should not be widely granted. As a general rule, you should restrict it to DBA accounts. If you want to provide end users with the ability to create their own deferred calls, you should create a cover package and grant EXECUTE on it to end users or end user roles.

### 17.3.1 Getting Started with DBMS_DEFER

The DBMS_DEFER 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 script creates the public synonym DBMS_DEFER. EXECUTE privileges on DBMS_DEFER are not granted. The following procedures grant EXECUTE privilege on DBMS_DEFER to the specified grantees:

DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPSCHEMA
DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP
DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPSCHEMA
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP

#### 17.3.1.1 DBMS_DEFER programs

Table 17.11 lists the programs available in the DBMS_DEFER package.

Table 17.11: DBMS_DEFER Programs
Name | Description | Use in SQL?
--- | --- | ---
CALL | Defines a remote procedure call | No
COMMIT_WORK | Commits deferred RPC transaction | No
<datatype>_ARG | Adds parameter of specified datatype to a deferred call; <datatype> may be CHAR, DATE, NUMBER, RAW, ROWID, or VARCHAR2 | No
TRANSACTION | Marks a transaction as deferred | No

#### 17.3.1.2 DBMS_DEFER exceptions

The DBMS_DEFER package may raise any of the exceptions listed in Table 17.12.

Table 17.12: DBMS_DEFER Exceptions
Name | Number | Description
--- | --- | ---
bad_param_type | -23325 | Parameter type does not match actual type
deferred_rpc_quiesce | -23326 | Database is quiescing
dbms_defererror | -23305 | Generic internal errors
malformedcall | -23304 | Argument count mismatches, etc.
updateconflict | -23303 | Remote update failed due to conflict
commfailure | -23302 | Remote update failed due to communication failure
mixeddest | -23301 | Destinations for transaction not specified consistently
parameterlength | -23323 | Parameter length exceeds limits (2000 for CHAR/VARCHAR, 255 for RAW)
executiondisabled | -23354 | Deferred RPC execution is disabled

#### 17.3.1.3 DBMS_DEFER nonprogram elements

Table 17.13 lists the constants and other nonprogram elements defined in the DBMS_DEFER package. The DBMS_DEFER.NODE_LIST_T element is a PL/SQL table whose first entry is always placed in row 1. It is filled sequentially, with each subsequent entry placed in row DBMS_DEFER.NODE_LIST_T.LAST + 1.

Table 17.13: DBMS_DEFER Other Elements
Type/Name | Description
--- | ---
CONSTANT arg_csetid_none (Oracle8) | Internal Character Set ID. Value = 0. Includes types DATE, NUMBER, ROWID, RAW, and BLOB.
CONSTANT arg_form_none (Oracle8) | Internal Character Set ID. Value = 0. Includes types DATE, NUMBER, ROWID, RAW, and BLOB.
CONSTANT arg_form_implicit (Oracle8) | Internal Character Set ID. Value = 1. Includes types CHAR, VARCHAR2, and CLOB.
CONSTANT arg_form_nchar (Oracle8) | Internal Character Set ID. Value = 2. Includes types NCHAR, NVARCHAR2, and NCLOB.
CONSTANT arg_form_any (Oracle8) | Internal Character Set ID. Value = 4.
CONSTANT arg_type_num | Used in arg_type column of def$_args table. Value = 2.
CONSTANT arg_type_char | Used in arg_type column of def$_args table. Value = 96.
CONSTANT arg_type_varchar2 | Used in arg_type column of def$_args table. Value = 1.
CONSTANT arg_type_date | Used in arg_type column of def$_args table. Value = 12.
CONSTANT arg_type_rowid | Used in arg_type column of def$_args table. Value = 11.
CONSTANT arg_type_raw | Used in arg_type column of def$_args table. Value = 23.
CONSTANT arg_type_blob (Oracle8) | Used in arg_type column of def$_args table. Value = 113.
CONSTANT arg_type_blob (Oracle8) | Used in arg_type column of def$_args table. Value = 112.
CONSTANT arg_type_blob (Oracle8) | Used in arg_type column of def$_args table. Value = 114.
CONSTANT arg_type_blob (Oracle8) | Used in arg_type column of def$_args table. Value = 115.
CONSTANT repcat_status_normal | Signals normal successful completion. Value = 0.0.
TYPE node_list_t | Table of VARCHAR2(128).

### 17.3.2 Basic RPCs

The simplest RPC calls use default destinations and take no parameters. The basic procedure for building a parameterless deferred transaction or a deferred remote procedure call is to follow these steps:

1. Call DBMS_DEFER.TRANSACTION (optional).

2. Make one or more calls to DBMS_DEFER.CALL.

3. Issue a COMMIT with DBMS_DEFER.COMMIT_WORK.

The following sections describe in some detail how these procedures work. Later sections describe more complex RPC calls.

#### 17.3.2.1 The DBMS_DEFER.TRANSACTION procedure

The TRANSACTION procedure allows you to specify destination sites for the ensuing call(s) to the DBMS_DEFER.CALL procedure. There are two main reasons why you might wish to identify destinations this way:

- You might wish to override the destinations in the DBA_REPSITES data dictionary view.

- You might be making several calls to DBMS_DEFER.CALL and not wish to specify the destinations in the nodes parameter individually each time.

The TRANSACTION procedure is overloaded such that the nodes parameter is optional. You can specify either,

>     PROCEDURE DBMS_DEFER.TRANSACTION;

or:

>     PROCEDURE DBMS_DEFER.TRANSACTION
>        (nodes IN node_list_t);

If specified, nodes is a PL/SQL table containing the list of nodes that should receive the RPC call. If you do not specify the nodes parameter, the ensuing call(s) to DBMS_DEFER.CALL will queue the calls to destinations in DEFDEFAULTDEST. If you do specify the nodes parameter, you must populate it with the global name of target destinations.

#### 17.3.2.1.1 Exceptions

DBMS_DEFER.TRANSACTION may raise the following exceptions:

Name | Number | Description
--- | --- | ---
malformedcall | -23304 | Transaction is not properly formed, or transaction terminated
ORA-23319 | -23319 | Parameter value is not appropriate
ORA-23352 | -23352 | node_list_t contains duplicates

#### 17.3.2.1.2 Restrictions

You can call the TRANSACTION procedure only in conjunction with DBMS_DEFER.CALL.

#### 17.3.2.1.3 Example

At the end of the DBMS_DEFER section (in the COMMIT_ WORK subsection) is an example that incorporates the TRANSACTION procedure and the other DBMS_DEFER procedures.

#### 17.3.2.2 The DBMS_DEFER.CALL procedure

The CALL procedure queues a call to the destination specified in the DEFDEFAULTDEST data dictionary view. Here is the specification:

>     PROCEDURE DBMS_DEFER.CALL
>       (schema_name IN VARCHAR2,
>        package_name IN VARCHAR2,
>        proc_name IN VARCHAR2,
>        arg_count IN NATURAL,
>        {group_name IN VARCHAR2 := ''|
>        nodes IN node_list_t});

Parameters are summarized in the following table.

Name | Description
--- | ---
package_name | Name of the package containing the procedure that is being queued.
proc_name | Name of the procedure being queued.
arg_count | Number of parameters being passed to the procedure. You must have one call to DBMS_DEFER.<datatype>_ARG for each parameter.
group_name | Optional. Reserved for internal use.
nodes | Optional. List of destination nodes (global_names) where the procedure is to be executed. If nodes is not specified, destinations are determined by the list passed to DBMS_DEFER.TRANSACTION.

#### 17.3.2.2.1 Exceptions

The CALL procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
malformedcall | -23304 | Number of arguments in call does not match value of arg_count
ORA-23319 | -23319 | The parameter is either NULL, misspelled, or not allowed
ORA-23352 | -23352 | The nodes list contains a duplicate

#### 17.3.2.2.2 Restrictions

The procedures used in deferred RPC calls must be part of a package; it is not possible to queue standalone procedures.

#### 17.3.2.2.3 Example

At the end of the DBMS_DEFER section (in the COMMIT_WORK subsection) is an example that incorporates the CALL procedure and the other DBMS_DEFER procedures.

For an additional example, see the defcall.sql file on the companion disk. The example lists all entries in the deferred call queue (DEFCALL), including the originating database and the package name.

#### 17.3.2.3 The DBMS_DEFER.COMMIT_WORK procedure

The COMMIT_WORK procedure issues a COMMIT command to commit the transaction constructed by the preceding TRANSACTION and CALL procedures. The specification is,

>     PROCEDURE DBMS_DEFER.COMMIT_WORK
>        (commit_work_comment IN VARCHAR2);

where commit_work_comment is a description of the transaction. The comment may be up to 50 characters.

There are no restrictions on calling COMMIT_WORK.

#### 17.3.2.3.1 Exceptions

The COMMIT_WORK procedure may raise the following exception:

Name | Number | Description
--- | --- | ---
malformedcall | -23304 | Number of arguments in the CALL procedure does not match value arg_count; or missing calls to the <datatype>_ARG procedure, or the TRANSACTION procedure was not called for this transaction

#### 17.3.2.3.2 Example:

The DBMS_DEFER.TRANSACTION, CALL, <datatype>_ARG, and COMMIT_WORK procedures work together to construct a deferred transaction or deferred RPC call, as described in the following examples.

#### 17.3.2.3.3 Using DBMS_DEFER.TRANSACTION

To illustrate the way that you might use the TRANSACTION procedure, consider the following example. The schema SPROCKET has a package called PriceMaint, which contains procedure TenPctIncrease. This package exists at all sites in the DEFDEFAULTDEST data dictionary view. The TenPctIncrease procedure increases the wholesale and retail prices of products in our PRICES table by 10%.

>     CREATE OR REPLACE PACKAGE PriceMaint IS
>             PROCEDURE TenPctIncrease;
>     END PriceMaint;
>     /

>     CREATE OR REPLACE PACKAGE BODY PriceMaint IS
>     	PROCEDURE TenPctIncrease IS
>     	BEGIN
>     	        UPDATE  prices
>     	        SET     price_wholesale	= price_wholesale * 1.10,
>     	                price_retail	  = price_retail * 1.10;
>     	END TenPctIncrease;
>     END PriceMaint;

>     	/

Now, suppose that we wish to make a 10% price increase at all of our locations (i.e., all locations in the DEFDEFAULTDEST data dictionary view). We could create a procedure that queues a call to PriceMaint.TenPctIncrease to all of these sites. In this case, we issue the TRANSACTION call without parameters:

>     CREATE OR REPLACE PROCEDURE TenPctPriceHike IS
>     BEGIN
>     	DBMS_DEFER.TRANSACTION;
>     	DBMS_DEFER.CALL(	schema_name	=> 'SPROCKET',
>     				package_name	=> 'PRICEMAINT',
>     				proc_name	=> 'TENPCTINCREASE'
>     				arg_count	=> 0 );
>     	DBMS_DEFER.COMMIT_WORK(commit_work_comment=>'No nodes or args needed');
>     END;

Because the nodes parameter isn't specified in either the call to TRANSACTION or the call to CALL, Oracle resolves the destinations by using all sites in the DEFDEFAULTDEST data dictionary view.

Here is how you might use the TenPctPriceHike Procedure.

Confirm the default destinations:

>     SQL> SELECT * FROM defdefaultdest;
>     DBLINK
>     --------------------
>     D7NY.BIGWHEEL.COM
>     D7OH.BIGWHEEL.COM
>     D7OR.BIGWHEEL.COM
>     D7WA.BIGWHEEL.COM
>     D7TX.BIGWHEEL.COM

>     5 rows selected.

Now use TenPctPriceHike to queue the RPC to all five destinations:

>     SQL> EXECUTE TenPctPriceHike

>     PL/SQL procedure successfully completed.

Figure 17-1 graphically illustrates how a deferred call is queued.

#### Figure 17.1: Queueing up a deferred call to TenPctIncrease

Now check the entries in DEFTRAN (this call was made from D7CA. BIGWHEEL.COM):

>     SQL> select * from deftrandest;

>     DEFERRED_TRAN_ID	DEFERRED_TRAN_DB	 DBLINK
>     ----------------------	-----------------	 --------------------
>     2.44.13			D7CA.BIGWHEEL.COM	 D7NY.BIGWHEEL.COM
>     2.44.13			D7CA.BIGWHEEL.COM	 D7OH.BIGWHEEL.COM
>     2.44.13			D7CA.BIGWHEEL.COM	 D7OR.BIGWHEEL.COM
>     2.44.13			D7CA.BIGWHEEL.COM	 D7WA.BIGWHEEL.COM
>     2.44.13			D7CA.BIGWHEEL.COM	 D7TX.BIGWHEEL.COM

>     5 rows selected.

For an additional example, see the deftdest.sql file on the companion disk. The example queries the DEFTRANDEST data dictionary view and lists destination databases for deferred RPC calls.

> NOTE: Procedure TenPctPriceHike queues the deferred RPC only if the owner of the procedure has EXECUTE privileges on DBMS_DEFER.

#### 17.3.2.3.4 Specifying nondefault destinations with TRANSACTION

What if we wanted to apply the 10% price hike only to our West Coast sites (i.e., D7CA.BIGWHEEL.COM, D7OR.BIGWHEEL.COM, and D7WA.BIGWHEEL.COM)? The following example does just that by specifying the nodes parameter in the TRANSACTION procedure:

>     CREATE OR REPLACE PROCEDURE TenPctPriceHikeWest IS
>     vNodes DBMS_DEFER.NODE_LIST_T;
>     BEGIN
>     	vNodes(1) := 'D7CA.BIGWHEEL.COM';
>     	vNodes(2) := 'D7OR.BIGWHEEL.COM';
>     	vNodes(3) := 'D7WA.BIGWHEEL.COM';

>     	DBMS_DEFER.TRANSACTION( vNodes );
>     	DBMS_DEFER.CALL(	schema_name	=> 'SPROCKET',
>     				package_name	=> 'PRICEMAINT',
>     				proc_name	=> 'TENPCTINCREASE'
>     				arg_count	=> 0 );
>     	DBMS_DEFER.COMMIT_WORK(commit_work_comment=>'West Coast Price Hike');
>     END;

#### 17.3.2.3.5 Committing deferred RPC calls with COMMIT_WORK

Notice that the last two examples include a call to DBMS_DEFER.COMMIT_WORK. All deferred RPCs queued with the CALL procedure must be followed by a call to COMMIT_WORK; an explicit COMMIT or COMMIT WORK is not sufficient. The reason for this restriction is that COMMIT_WORK not only commits the transaction, but also updates the commit_comment and delivery_order field in the DEFTRANS data dictionary view. The commit_comment is updated with the optional string passed to COMMIT_WORK, and the delivery_order field is updated with the transaction's SCN.

Remember that the TRANSACTION procedure is not required to queue deferred calls. It is used only to specify destinations. The real power and flexibility of deferred transactions is in the CALL procedure.

For an additional example, see the defcdest.sql file on the companion disk. The example queries the DEFCALLDEST data dictionary view and lists the destination databases of all calls in the deferred call queue.

### 17.3.3 Parameterized RPCs

The preceding sections describe the simple version of building deferred RPCs with the DBMS_DEFER package. We saw in those sections that the DBMS_DEFER.CALL procedure is the program that actually queues deferred RPCs. Most of the examples we have seen so far use it in its simplest incarnation, without the nodes parameter and with an arg_count parameter of 0. This is fine when making deferred calls to procedures that take no parameters, and when the default destinations are acceptable, but sooner or later you will want to defer calls to procedures that require parameters, and you will want to specify the destinations for each call individually. The steps to accomplish these more complex operations follow:

1. Specify the destination nodes, either with DBMS_CALL.TRANSACTION or by supplying the nodes parameter to DBMS_DEFER.CALL.

2. Execute DBMS_DEFER.CALL, supplying the schema name, package name, procedure name, number of arguments to the procedure, and (if you do not use DBMS_CALL.TRANSACTION) the nodes parameter.

3. Call DBMS_DEFER.<datatype>_arg arg_count times, where arg_count is the value passed to DBMS_DEFER.CALL. The order in which you call DBMS_DEFER.<datatype>_arg must be the same order as the parameters are listed in the procedure definition.

4. Call DBMS_DEFER.COMMIT_WORK with an optional comment.

#### 17.3.3.1 The DBMS_DEFER.<datatype>_ARG procedure

This procedure specifies an argument for a deferred call. The argument is of the datatype specified in <datatype>. Here is the specification:

>     PROCEDURE DBMS_DEFER.<datatype>ARG (arg IN <datatype>.

specifications differ for different datatypes, depending on whether you are using Oracle7 or Oracle8. <datatype> can be one of the following:

NUMBER
DATE
VARCHAR2
CHAR
ROWID
RAW
NVARCHAR2 (Oracle8 only)
ANY_VARCHAR2 (Oracle8 only)
NCHAR (Oracle8 only)
ANY_CHAR (Oracle8 only)
BLOB (Oracle8 only)
CLOB (Oracle8 only)
ANY_CLOB (Oracle8 only)
NCLOB (Oracle8 only)

The arg parameter is the value to pass to the parameter of the same datatype in the procedure previously queued via DBMS_DEFER.CALL.

The various alternatives are listed here.

The following specifications apply to both Oracle7 and Oracle8:

>     PROCEDURE NUMBER_ARG (arg IN NUMBER);
>     PROCEDURE DATE_ARG (arg IN DATE);
>     PROCEDURE VARCHAR2_ARG (arg IN VARCHAR2);
>     PROCEDURE CHAR_ARG (arg IN CHAR);
>     PROCEDURE ROWID_ARG (arg IN ROWID);
>     PROCEDURE RAW_ARG (arg IN raw);

These specifications apply only to Oracle8:

>     PROCEDURE NVARCHAR2_ARG (arg IN NVARCHAR2);
>     PROCEDURE ANY_VARCHAR2_ARG (arg  IN VARCHAR2 CHARACTER SET ANY_CS);
>     PROCEDURE NCHAR_ARG (arg IN NCHAR);
>     PROCEDURE ANY_CHAR_ARG (arg IN CHAR CHARACTER SET ANY_CS);
>     PROCEDURE BLOB_ARG (arg IN BLOB);
>     PROCEDURE CLOB_ARG (arg IN CLOB);
>     PROCEDURE ANY_CLOB_ARG (arg IN CLOB CHARACTER SET ANY_CS);
>     PROCEDURE NCLOB_ARG (arg IN NCLOB);

#### 17.3.3.1.1 Exceptions

This procedure may raise the following exception:

Name | Number | Description
--- | --- | ---
paramlen_num | -23323 | Parameter is too long

#### 17.3.3.1.2 Example

The following scenario describes how to perform the steps required to construct a deferred RPC that takes parameters.

Suppose that we have a PRODUCTS table and a procedure that adds new products to it, as follows:

>     SQL> desc products
>      Name			Null?	  		Type
>      -------------------	-------- 		----
>      PRODUCT_ID		NOT NULL 		NUMBER(9)
>      PRODUCT_TYPE		NOT NULL 		NUMBER(6)
>      CATALOG_ID	 	NOT NULL 		VARCHAR2(15)
>      DESCRIPTION	 	NOT NULL 		VARCHAR2(30)
>      REV_LEVEL 		NOT NULL 		VARCHAR2(15)
>      PRODUCTION_DATE 	NOT NULL 		DATE
>      PRODUCTION_STATUS 	NOT NULL 		VARCHAR2(10)
>      AUDIT_DATE 		NOT NULL 		DATE
>      AUDIT_USER 		NOT NULL 		VARCHAR2(30)
>      GLOBAL_NAME 		NOT NULL 		VARCHAR2(20)

Procedure ProductMaint.AddProduct populates this table. We will queue deferred calls to the this procedure.

>     CREATE OR REPLACE PACKAGE ProductMaint IS
>     	PROCEDURE AddProduct(product_type_ININ NUMBER,
>     			catalog_id_IN	IN VARCHAR2,
>     			description_IN	IN VARCHAR2,
>     			rev_level_IN	IN VARCHAR2,
>     			production_date_ININ DATE,
>     			product_status_ININ VARCHAR);
>     END ProductMaint;
>     /
>     CREATE OR REPLACE PACKAGE BODY ProductMaint IS

>     PROCEDURE AddProduct(product_type_ININ NUMBER,
>     			catalog_id_IN		IN VARCHAR2,
>     			description_IN		IN VARCHAR2,
>     			rev_level_IN		IN VARCHAR2,
>     			production_date_IN	IN DATE,
>     			product_status_IN	IN VARCHAR) IS
>     BEGIN

>     	INSERT INTO products (product_id,
>     		    product_type,
>     		    catalog_id,
>     		    description,
>     		    rev_level,
>     		    production_date,
>     		    production_status,
>     		    audit_date,
>     		    audit_user,
>     		    global_name )
>     	VALUES (seq_products.nextval,
>     		product_type_IN,
>     		catalog_id_IN,
>     		description_IN,
>     		rev_level_IN,
>     		production_date_IN,
>     		product_status_IN,
>     		SYSDATE,
>     		USER,
>     		DBMS_REPUTIL.GLOBAL_NAME);
>     END AddProduct;

>     END ProductMaint;

Since the procedure ProductMaint.AddProduct accepts parameters, we must supply values for these parameters when building a deferred call. The following procedure does just that:

>     CREATE OR REPLACE PROCEDURE qAddProduct IS
>     vNodes	DBMS_DEFER.NODE_LIST_T;
>     BEGIN
>     ---------------------------------------------------------------------------
>     -- 1. Specify the nodes to which the deferred RPC call is to be queued.
>     ---------------------------------------------------------------------------
>     	vNodes(1) := 'D7NY.BIGWHEEL.COM';
>     	vNodes(2) := 'D7OH.BIGWHEEL.COM';
>     	vNodes(3) := 'D7OR.BIGWHEEL.COM';
>     	vNodes(4) := 'D7WA.BIGWHEEL.COM';
>     	vNodes(5) := 'D7TX.BIGWHEEL.COM';

>     ---------------------------------------------------------------------------
>     -- 2. Execute DBMS_DEFER.CALL, supplying the schema name, package name,
>     --    procedure name, number of arguments to the procedure, and (if you do
>     --    not use DBMS_CALL.TRANSACTION) the nodes parameter.
>     ---------------------------------------------------------------------------

>     	DBMS_DEFER.CALL(schema_name	=> 'SPROCKET',
>     			package_name	=> 'PRODUCTMAINT',
>     			proc_name	=> 'AddProduct',
>     			arg_count	=> 6,
>     			nodes		=> vNodes );

>     ---------------------------------------------------------------------------
>     -- 3. Call DBMS_DEFER.<datatype>_arg arg_count times, where arg_count is
>     --     the value passed to DBMS_DEFER.CALL.  The order in which you call
>     --     DBMS_DEFER.<datatype>_arg must be the same order as the parameters
>     --     are listed in the procedure definition.
>     ---------------------------------------------------------------------------

>     	DBMS_DEFER.NUMBER_ARG( 10 );				     -- product_type
>     	  DBMS_DEFER.VARCHAR2_ARG( 'BIKE-0018' );	             -- catalog_id
>     	  DBMS_DEFER.VARCHAR2_ARG( 'Mens 18 Speed Racer');           -- Description
>     	  DBMS_DEFER.VARCHAR2_ARG( '19971031-01' );	             -- Rev Level
>     	  DBMS_DEFER.DATE_ARG(to_date('31-OCT-1997','DD-MON-YYYY')); --Date
>     	  DBMS_DEFER.VARCHAR2_ARG( 'PLANNED' );	                     -- status

>     ---------------------------------------------------------------------------
>     -- 4. Call DBMS_DEFER.COMMIT_WORK with an optional comment.
>     ---------------------------------------------------------------------------
>     	DBMS_DEFER.COMMIT_WORK(commit_work_comment=>'5 Nodes, 6 args');
>     END;

Let's see what happens when we execute AddProduct:

>     SQL> execute qAddProduct

>     PL/SQL procedure successfully completed.

Note that even though this deferred RPC call is destined for five different databases, there is only one entry in DEFCALL:

>     SQL> SELECT		callno,
>     2  deferred_tran_db,
>     3  deferred_tran_id,
>     4  schemaname,
>     5  packagename,
>     6  procname,
>     7  argcount
>     8  FROM			defcall
>     9 /

>                    Deferred           Deferred
>                    Tran               Tran       Schema     Package     Procedure Arg
>     Call No        DB                 ID         Name       Name        Name      Count
>     ------------   ------------------ --------------------- ----------- -------------
>     6631429922096  D7CA.BIGWHEEL.COM  2.125.13   SPROCKET   PRODUCTMAINTAddProduct6

The DEFTRANDEST data dictionary view, on the other hand, includes all of the destinations for this call:

>      SQL>  SELECT	deferred_tran_id,
>       2  	deferred_tran_db,
>       3  	dblink
>       4  FROM		deftrandest
>       5  /
>     Deferred Deferred
>     Tran	 Tran
>     ID	 DB		       DB Link
>     -------- ------------------- --------------------
>     2.125.13 D7CA.BIGWHEEL.COM   D7NY.BIGWHEEL.COM
>     2.125.13 D7CA.BIGWHEEL.COM   D7OH.BIGWHEEL.COM
>     2.125.13 D7CA.BIGWHEEL.COM   D7OR.BIGWHEEL.COM
>     2.125.13 D7CA.BIGWHEEL.COM   D7WA.BIGWHEEL.COM
>     2.125.13 D7CA.BIGWHEEL.COM   D7TX.BIGWHEEL.COM

>     5 rows selected.

17.2 DBMS_DEFER_SYS: Managing Deferred Transactions |  | 17.4 DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance

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