Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
|
|
View PDF |
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:
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. |
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. Thenodes
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 of the parameters have defaults. |
nodes |
A PL/SQL index-by table 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. |
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. |
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 need to 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.WORLD'; 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:
The ANYDATA_ARG
procedure supports the following user-defined types: object types, collections, and REF
s. See Oracle Database SQL Reference and Oracle Database Application Developer's Guide - Object-Relational Features for more information about the ANYDATA
datatype.
This procedure uses abbreviations for some datetime and interval datatypes. For example, TSTZ
is used for the TIMESTAMP
WITH
TIME
ZONE
datatype. For information about these abbreviations, see "Abbreviations for Datetime and Interval Datatypes".
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. |
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 thenodes
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 index-by table 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