Oracle7 Server Distributed Systems Volume II: Replicated Data

Contents Index Home Previous Next

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 may be executed in the privilege domain of a more privileged user at the remote site (such as the replication administrator), EXECUTE privileges on the DBMS_DEFER package should not be widely granted. To control access to these procedures, you may prefer to create a cover package, and grant EXECUTE on this cover package. For more information on executing deferred transactions, and the privilege domain used to execute the transaction, refer to [*].

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 symmetric 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:

Note: This method cannot be combined with any of the following methods.

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: The parameters for the TRANSACTION procedure are described in Table 12 - 7, and the exceptions are listed in Table 12 - 8.

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 [*].

All deferred remote procedure calls are recorded in the DefCall view. If you specify a destination for the call, it is noted in the DefCallDest view.

Additional Information: The parameters for the CALL procedure are described in Table 12 - 1, and the exceptions are listed in Table 12 - 2.

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.CHAR_ARG(arg IN CHAR);
DBMS_DEFER.ROWID_ARG(arg IN ROWID);
DBMS_DEFER.RAW_ARG(arg IN RAW);

The RAW_ARG, CHAR_ARG, and VARCHAR2_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. If you want to queue any existing deferred transactions to the new destination, you must call the COPY command for each of these transactions, as described in the next section.

Additional Information: The parameter for the ADD_DEFAULT_DEST procedure is described in Table 12 - 14, and the exception is listed in Table 12 - 15.

Copying a Deferred Transaction to a New Destination

When you add a destination to the DefDefaultDest view, you may want to have certain outstanding deferred transactions applied at that location as well. To copy a deferred transaction from an existing destination site to a newly added destination, call the COPY procedure in the DBMS_DEFER_SYS package, as shown in the following example:

new_nodes dbms_defer.node_list_t;
new_nodes(1) := 'acct_ny.ny.com';
DBMS_DEFER_SYS.COPY(
    deferred_tran_id  => '234', 
    deferred_tran_db  => 'acct_hq.hq.com', 
    destination_list  => new_nodes, 
    destination_count => 1);

In this example a copy of the transaction with ID number 234 is queued for the ACCT_NY site.

When you copy a deferred transaction, the new row in the DefTran view has different values for the DEFERRED_TRAN_ID, DEFERRED_TRAN_DB, and DELIVERY_ORDER columns than the source row. Additionally, the DESTINATION_LIST column is set to `D'. The destination list for the new transaction is determined by the final two arguments to the COPY procedure: DESTINATION_LIST and DESTINATION_COUNT. Finally, the copies of the DefCall records are assigned new CALLNO values.

Additional Information: The parameters for the COPY procedure are described in Table 12 - 16.

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, call the DELETE_TRAN procedure in the DBMS_DEFER_SYS package as described [*].

Additional Information: The parameter for the DELETE_DEFAULT_DEST procedure is described in Table 12 - 17.

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 calling DBMS_DEFER_SYS.SCHEDULE_EXECUTION, or you can force the queue to be pushed immediately by calling DBMS_DEFER_SYS.EXECUTE. These transactions are propagated in the same manner as your DML changes are propagated by the symmetric replication facility. For more information on propagating the deferred transaction queue, refer to [*].

Contents Index Home Previous Next