Skip Headers
Oracle® Database Advanced Replication Management API Reference
11g Release 2 (11.2)

Part Number E10707-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

12 DBMS_DEFER

DBMS_DEFER is the user interface to a replicated transactional deferred remote procedure call facility. Replicated applications use the calls in this interface to queue procedure calls for later transactional execution at remote nodes.

These procedures are typically called from either after row triggers or application specified update procedures.

This chapter contains this topic:


Summary of DBMS_DEFER Subprograms

Table 12-1 DBMS_DEFER Package Subprograms

Subprogram Description

"CALL Procedure"

Builds a deferred call to a remote procedure.

"COMMIT_WORK Procedure"

Performs a transaction commit after checking for well-formed deferred remote procedure calls.

"datatype_ARG Procedure"

Provides the data that is to be passed to a deferred remote procedure call.

"TRANSACTION Procedure"

Indicates the start of a new deferred transaction.



CALL Procedure

This procedure builds a deferred call to a remote procedure.

Syntax

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

Note:

This procedure is overloaded. The nodes and group_name parameters are mutually exclusive.

Parameters

Table 12-2 CALL Procedure Parameters

Parameter Description
schema_name

Name of the schema in which the stored procedure is located.

package_name

Name of the package containing the stored procedure. The stored procedure must be part of a package. Deferred calls to standalone procedures are not supported.

proc_name

Name of the remote procedure to which you want to defer a call.

arg_count

Number of parameters for the procedure. You must have one call to DBMS_DEFER.datatype_ARG for each of these parameters.

Note: You must include all of the parameters for the procedure, even if some parameters have defaults.

nodes

A PL/SQL associative array of fully qualified database names to which you want to propagate the deferred call. The table is indexed starting at position 1 and continuing until a NULL entry is found, or the no_data_found exception is raised. The data in the table is case insensitive. This parameter is optional.

group_name

Reserved for internal use.


Exceptions

Table 12-3 CALL Procedure Exceptions

Exception Description
ORA-23304 (malformedcall)

Previous call was not correctly formed.

ORA-23319

Parameter value is not appropriate.

ORA-23352

Destination list (specified by nodes or by a previous DBMS_DEFER.TRANSACTION call) contains duplicates.



COMMIT_WORK Procedure

This procedure performs a transaction commit after checking for well-formed deferred remote procedure calls.

Syntax

DBMS_DEFER.COMMIT_WORK (
   commit_work_comment IN VARCHAR2);

Parameters

Table 12-4 COMMIT_WORK Procedure Parameters

Parameter Description
commit_work_comment

Equivalent to the COMMIT COMMENT statement in SQL.


Exceptions

Table 12-5 COMMIT_WORK Procedure Exceptions

Exception Description
ORA-23304 (malformedcall)

Transaction was not correctly formed or terminated.



datatype_ARG Procedure

This procedure provides the data that is to be passed to a deferred remote procedure call. Depending upon the type of the data that you must pass to a procedure, you must call one of the following procedures for each argument to the procedure.

You must specify each parameter in your procedure using the datatype_ARG procedure after you execute DBMS_DEFER.CALL. That is, you cannot use the default parameters for the deferred remote procedure call. For example, suppose you have the following procedure:

CREATE OR REPLACE PACKAGE my_pack AS
   PROCEDURE my_proc(a VARCHAR2, b VARCHAR2 DEFAULT 'SALES');
END;
/

When you run the DBMS_DEFER.CALL procedure, you must include a separate procedure call for each parameter in the my_proc procedure:

CREATE OR REPLACE PROCEDURE load_def_tx IS
   node DBMS_DEFER.NODE_LIST_T;
BEGIN  
   node(1) := 'MYCOMPUTER.EXAMPLE.COM';
   node(2) := NULL;
   DBMS_DEFER.TRANSACTION(node);
   DBMS_DEFER.CALL('PR', 'MY_PACK', 'MY_PROC', 2);
   DBMS_DEFER.VARCHAR2_ARG('TEST');
   DBMS_DEFER.VARCHAR2_ARG('SALES'); -- required, cannot omit to use default
END;
/

Note:

Syntax

DBMS_DEFER.ANYDATA_ARG      (arg  IN ANYDATA);
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);
DBMS_DEFER.BLOB_ARG         (arg  IN BLOB);
DBMS_DEFER.CLOB_ARG         (arg  IN CLOB);
DBMS_DEFER.NCLOB_ARG        (arg  IN NCLOB);
DBMS_DEFER.NCHAR_ARG        (arg  IN NCHAR);
DBMS_DEFER.NVARCHAR2_ARG    (arg  IN NVARCHAR2);
DBMS_DEFER.ANY_CLOB_ARG     (arg  IN CLOB);
DBMS_DEFER.ANY_VARCHAR2_ARG (arg  IN VARCHAR2);
DBMS_DEFER.ANY_CHAR_ARG     (arg  IN CHAR);
DBMS_DEFER.IDS_ARG          (arg  IN DSINTERVAL_UNCONSTRAINED);
DBMS_DEFER.IYM_ARG          (arg  IN YMINTERVAL_UNCONSTRAINED);
DBMS_DEFER.TIMESTAMP_ARG    (arg  IN TIMESTAMP_UNCONSTRAINED);
DBMS_DEFER.TSLTZ_ARG        (arg  IN TIMESTAMP_LTZ_UNCONSTRAINED);
DBMS_DEFER.TSTZ_ARG         (arg  IN TIMESTAMP_TZ_UNCONSTRAINED);

Parameters

Table 12-6 datatype_ARG Procedure Parameters

Parameter Description
arg

Value of the parameter that you want to pass to the remote procedure to which you previously deferred a call.


Exceptions

Table 12-7 datatype_ARG Procedure Exceptions

Exception Description
ORA-23323

Argument value is too long.



TRANSACTION Procedure

This procedure indicates the start of a new deferred transaction. If you omit this call, then Oracle considers your first call to DBMS_DEFER.CALL to be the start of a new transaction.

Syntax

DBMS_DEFER.TRANSACTION (
   nodes  IN   node_list_t);

Note:

This procedure is overloaded. The behavior of the version without an input parameter is similar to that of the version with an input parameter, except that the former uses the nodes in the DEFDEFAULTDEST view instead of using the nodes in the nodes parameter.

Parameters

Table 12-8 TRANSACTION Procedure Parameters

Parameter Description
nodes

A PL/SQL associative array of fully qualified database names to which you want to propagate the deferred calls of the transaction. The table is indexed starting at position 1 and continuing until a NULL entry is found, or the no_data_found exception is raised. The data in the table is case insensitive.


Exceptions

Table 12-9 TRANSACTION Procedure Exceptions

Exception Description
ORA-23304 (malformedcall)

Previous transaction was not correctly formed or terminated.

ORA-23319

Parameter value is not appropriate.

ORA-23352

Raised by DBMS_DEFER.CALL if the node list contains duplicates.