9
Using Deferred Transactions

This chapter describes the following topics:

Listing Information about Deferred Transactions

Oracle provides several tables and views for you to use in administering deferred transactions. These views provide information about each deferred transaction, such as the transaction destinations, the deferred calls that make up the transaction, and any errors encountered during attempted execution of the transaction.

Attention: You should not modify these tables directly; use the procedures provided in the DBMS_DEFER and DBMS_DEFER_SYS packages.

These views are briefly described below. For more information, see "Data Dictionary Views" in the Oracle8i Replication API Reference book.

Data Dictionary View Description

DEFCALL Records all deferred remote procedure calls (RPCs).

DEFCALLDEST Lists the destinations for each deferred remote procedure call.

DEFDEFAULT DEST Lists the default destination for deferred remote procedure calls.

DEFERROR Provides information about transactions that could not be applied.

DEFLOB Storage for LOB parameters to deferred RPCs.

DEFSCHEDULE Displays information about when a job is next scheduled to be executed.

DEFTRAN Records all deferred transactions.

DEFTRANDEST Lists the destinations for a deferred transaction.

Data Dictionary View	Description
DEFCALL	Records all deferred remote procedure calls (RPCs).
DEFCALLDEST	Lists the destinations for each deferred remote procedure call.
DEFDEFAULT DEST	Lists the default destination for deferred remote procedure calls.
DEFERROR	Provides information about transactions that could not be applied.
DEFLOB	Storage for LOB parameters to deferred RPCs.
DEFSCHEDULE	Displays information about when a job is next scheduled to be executed.
DEFTRAN	Records all deferred transactions.
DEFTRANDEST	Lists the destinations for a deferred transaction.

Creating a Deferred Transaction

Every well formed deferred transaction must consist of zero or one DBMS_DEFER.TRANSACTION calls followed by zero or more well formed deferred remote procedure calls, followed by a SQL COMMIT statement.

Attention: The procedures for which you are building deferred calls must be part of a package. Deferred calls to standalone procedures are not supported.

Every well formed deferred remote procedure call must consist of one DBMS_DEFER.CALL call, followed by zero or more DBMS_DEFER.datatype_ARG calls. The number of calls to the appropriate datatype_ARG procedures is determined by the value of the ARG_COUNT parameter passed to the CALL procedure.

If you do not call DBMS_DEFER.TRANSACTION to indicate the start of a transaction, Oracle considers your first call to DBMS_DEFER.CALL to be the start of a new transaction.

Security

To create your own deferred transactions, you must have the EXECUTE privilege on the DBMS_DEFER package. This package is owned by SYS. Because deferred transactions are executed in the privilege domain of the replication propagator, EXECUTE privileges on the DBMS_DEFER package should not be widely granted.

Suggestion: To control access to these procedures, you should create a cover package in the replication propagator's schema, and grant EXECUTE on this cover package.

Specifying a Destination

In addition to building the calls that make up a deferred transaction, you must also specify the destination for this transaction. Transactions placed into the deferred transaction queue by the advanced replication facility are queued to all of the asynchronous locations (dblinks) for the replicated object, as listed in the DBA_REPPROP view. When you use the procedures in the DBMS_DEFER package to add a deferred transaction to the queue, you must specify a destination using one of the following methods. These methods are listed in order of precedence:

If you meet the following conditions, the DBA_REPPROP view is used to determine destinations for the deferred transaction:
- You do not use the NODES parameter to specify a destination in the call to DBMS_DEFER.TRANSACTION.
- You do not use the NODES parameter to specify a destination for any calls to DBMS_DEFER.CALL.
- Every call corresponds to a procedure in a package generated for an object in DBA_REPOBJECT.
  
  Note:
  This method cannot be combined with any of the following methods.
You specify one or more fully qualified database names as the NODES parameter to the DBMS_DEFER.CALL procedure. This value applies to the current deferred remote procedure call only.
You specify one or more fully qualified database names as the NODES parameter to the DBMS_DEFER.TRANSACTION procedure. This value applies to all deferred calls that make up the transaction.
If you do not use one of the previous mechanisms to specify a destination, Oracle uses the contents of the "DEFDEFAULTDEST View" to determine the destination for the calls.

Initiating a Deferred Transaction

Indicate the start of a new deferred transaction by calling the TRANSACTION procedure in the DBMS_DEFER package, as shown in the following example:

nodes dbms_defer.node_list_t;

node(1) := 'acct_hq.hq.com';

node(2) := 'acct_ny.ny.com';

DBMS_DEFER.TRANSACTION(nodes);

In this example, any calls that make up the deferred transaction for which you do not specify a destination when you call DBMS_DEFER.CALL, will be queued for the ACCT_HQ and ACCT_NY databases.

The call to TRANSACTION is optional. If you do not call TRANSACTION, Oracle considers your first call to DBMS_DEFER.CALL to be the start of a new transaction. Calling TRANSACTION is useful if you want to specify a list of nodes to which to forward the deferred calls, and the list is the same for all calls in the deferred transaction.

All deferred transactions are recorded in the DEFTRAN view. Each destination of the transaction is noted in the DEFTRANDEST view.

Additional Information: See the "TRANSACTION" procedure in the Oracle8i Replication API Reference book for details.

Deferring a Remote Procedure Call

To build a deferred call to a remote procedure, call the CALL procedure in the DBMS_DEFER package, as shown in the following example:

DBMS_DEFER.CALL(
     schema_name       =>   'accts_rec', 
     package_name      =>   'hr', 
     proc_name         =>   'hire_emp', 
     arg_count         =>   3);

This example builds a deferred call to the HR.HIRE_EMP procedure in the ACCTS_REC schema. This HIRE_EMP procedure takes three arguments. No destination is specified for the deferred call, so the destination must have been specified using one of the other methods outlined on page .

All deferred remote procedure calls are recorded in the DEFCALL view. Each destination for the call is noted in the DEFCALLDEST view.

Additional Information: See the "CALL" procedure in the Oracle8i Replication API Reference book for details.

Queuing a Parameter Value for a Deferred Call

After deferring a call to a remote procedure, you must provide the data that is passed to this procedure (only IN parameters are supported). There must be one call for each of the arguments that is passed to the remote procedure, and these calls must be made in the order that the arguments must be passed. The type of the data determines which procedure in the DBMS_DEFER package you must call. For example, suppose you deferred a call to the HIRE_EMP procedure, and it took three arguments, as shown below:

HIRE_EMP(ename IN VARCHAR2, empno IN NUMBER, salary IN NUMBER)

After building the deferred call to HIRE_EMP, you could pass the necessary data to this procedure by making the following three calls:

DBMS_DEFER.VARCHAR2_ARG('scott');
DBMS_DEFER.NUMBER_ARG(12345);
DBMS_DEFER.NUMBER_ARG(30000);

Depending upon the type of the data that you need to pass to the procedure, you need to call one of the following procedures in the DBMS_DEFER package for each argument to the procedure:

DBMS_DEFER.NUMBER_ARG(arg IN NUMBER);
DBMS_DEFER.DATE_ARG(arg IN DATE);
DBMS_DEFER.VARCHAR2_ARG(arg IN VARCHAR2);
DBMS_DEFER.NVARCHAR2_ARG(arg IN NVARCHAR2);
DBMS_DEFER.CHAR_ARG(arg IN CHAR);
DBMS_DEFER.NCHAR_ARG(arg IN NCHAR);
DBMS_DEFER.ROWID_ARG(arg IN ROWID);
DBMS_DEFER.RAW_ARG(arg IN RAW);
DBMS_DEFER.BLOB_ARG(arg IN BLOB);
DBMS_DEFER.CLOB_ARG(arg IN CLOB);
DBMS_DEFER.NCLOB_ARG(arg IN NCLOB);






Note:



The RAW_ARG, CHAR_ARG, NCHAR_ARG, VARCHAR2_ARG, and NVARCHAR2_ARG procedures can raise an ORA-23323 exception if the argument that you pass to the procedure is too long.

Adding a Destination to the DEFDEFAULTDEST View

If you use the DBMS_DEFER package to build a deferred transaction, and you do not supply a destination for a deferred transaction or the calls within that transaction, Oracle uses the DEFDEFAULTDEST view to determine the destination databases to which you want to defer a remote procedure call.

To add a destination database to this view call the ADD_DEFAULT_DEST procedure in the DBMS_DEFER_SYS package as shown in the following example:

DBMS_DEFER_SYS.ADD_DEFAULT_DEST( dblink => 'acct_ny.ny.com');

In this example, any future deferred transactions for which no destination has been specified will be queued for the ACCT_NY database.

Additional Information: See the "ADD_DEFAULT_DEST" procedure in the Oracle8i Replication API Reference book for details.

Removing a Destination from the DEFDEFAULTDEST View

To remove a destination database from the DEFDEFAULTDEST view, call the DELETE_DEFAULT_DEST procedure in the DBMS_DEFER_SYS package, as shown in the following example:

DBMS_DEFER_SYS.DELETE_DEFAULT_DEST( dblink => 'acct_ny.ny.com');

In this example, any future deferred transactions that you create will no longer be queued for the ACCT_NY database as the default.

To delete a transaction from the deferred transaction queue, you can use Replication Manager. For more information, see "Purging a Site's Deferred Transaction Queue".

Additional Information: See the "DELETE_DEFAULT_DEST" procedure in the Oracle8i Replication API Reference book for details.

Executing a Deferred Transaction

When you build a deferred transaction, the transaction is added to the deferred transaction queue at your local site. The remote procedures are not executed until this queue is pushed. You can either schedule this queue to be pushed at a periodic interval by creating a scheduled link or by calling DBMS_DEFER_SYS.SCHEDULE_PUSH, or you can force the queue to be pushed immediately with Replication Manager or by calling DBMS_DEFER_SYS.PUSH. These transactions are propagated in the same manner as your DML changes are propagated by the advanced replication facility.

LOB Storage

Oracle supports large internal objects (LOBs): binary LOBs (BLOBs); character LOBs (CLOBs); and national character LOBs (NCLOBs).

Note:
For data manipulation language (DML) or for piecewise update, the larger the size of the LOB (update), the more the propagation time will increase.

Note:
All sites must be Oracle8 or greater sites to support deferred RPCs of above named objects.

For security, note that a LOB parameter to a (deferred) RPC is visible in the transaction only while the RPC is being executed.

DEFLOB View of Storage for RPC

Oracle stores internal LOB parameters to deferred RPCs in a side table that is referenced only by way of a synonym. This gives the you flexibility for storage parameters and the containing schema. The following shows the default storage table for LOB parameters.

CREATE TABLE system.def$_lob( 
 id RAW(16) CONSTRAINT def$_lob_primary PRIMARY KEY, 
 deferred_tran_db VARCHAR2(128), -- origin db 
 deferred_tran_id VARCHAR2(22), -- transaction id 
 blob_col BLOB, 
 clob_col CLOB
 nclob_col NCLOB) 
/ 
-- make deletes fast 
CREATE INDEX system.def$_lob_n1 ON system.def$_lob( 
 deferred_tran_db, 
 deferred_tran_id) 
/ 
-- use a synonym in case underlying table is moved 
CREATE SYNONYM sys.def$_lob FOR system.def$_lob 
/ 
CREATE OR REPLACE VIEW DefLOB AS SELECT * FROM sys.def$_lob 
/ 
CREATE PUBLIC SYNONYM DefLOB FOR DefLOB 
/

9Using Deferred Transactions