17.3.2.1.1 Exceptions

DBMS_DEFER.TRANSACTION may raise the following exceptions:

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.1 Exceptions

The CALL procedure may raise the following exceptions:

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.1 Exceptions

The COMMIT_WORK procedure may raise the following exception:

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.1.1 Exceptions

This procedure may raise the following exception:

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.