13 DBMS_DEFER_QUERY

DBMS_DEFER_QUERY enables querying the deferred transactions queue data that is not exposed through views.

This chapter contains this topic:

Summary of DBMS_DEFER_QUERY Subprograms

Summary of DBMS_DEFER_QUERY Subprograms

Table 13-1 DBMS_DEFER_QUERY Package Subprograms

Subprogram	Description
"GET_ARG_FORM Function"	Determines the form of an argument in a deferred call.
"GET_ARG_TYPE Function"	Determines the type of an argument in a deferred call.
"GET_CALL_ARGS Procedure"	Returns the text version of the various arguments for the specified call.
"GET_datatype_ARG Function"	Determines the value of an argument in a deferred call.
"GET_OBJECT_NULL_VECTOR_ARG Function"	Returns the type information for a column object.

GET_ARG_FORM Function

This function returns the character set form of a deferred call parameter.

See Also:

The Advanced Replication interface's online Help for information about displaying deferred transactions and error transactions in the Advanced Replication interface in Oracle Enterprise Manager Cloud Control

Syntax

DBMS_DEFER_QUERY.GET_ARG_FORM (
   callno                IN   NUMBER,
   arg_no                IN   NUMBER,
   deferred_tran_id      IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 13-2 GET_ARG_FORM Function Parameters

Parameter	Description
callno	Call identifier from the `DEFCALL` view.
arg_no	Position of desired parameter in calls argument list. Parameter positions are 1...number of parameters in call.
deferred_tran_id	Deferred transaction identification.

Exceptions

Table 13-3 GET_ARG_FORM Function Exceptions

Exception	Description
NO_DATA_FOUND	Input parameters do not correspond to a parameter of a deferred call.

Returns

Table 13-4 GET_ARG_FORM Function Returns

Constant Return Value	Return Value	Possible Data Type
DBMS_DEFER_QUERY.ARG_FORM_NONE	0	DATE NUMBER ROWID RAW BLOB User-defined types
DBMS_DEFER_QUERY.ARG_FORM_IMPLICIT	1	CHAR VARCHAR2 CLOB
DBMS_DEFER_QUERY.ARG_FORM_NCHAR	2	NCHAR NVARCHAR2 NCLOB

GET_ARG_TYPE Function

This function determines the type of an argument in a deferred call. The type of the deferred remote procedure call (RPC) parameter is returned.

See Also:

Syntax

DBMS_DEFER_QUERY.GET_ARG_TYPE (
   callno            IN   NUMBER,
   arg_no            IN   NUMBER,
   deferred_tran_id  IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 13-5 GET_ARG_TYPE Function Parameters

Parameter	Description
callno	Identification number from the `DEFCALL` view of the deferred remote procedure call.
arg_no	Numerical position of the argument to the call whose type you want to determine. The first argument to a procedure is in position 1.
deferred_tran_id	Identifier of the deferred transaction.

Exceptions

Table 13-6 GET_ARG_TYPE Function Exceptions

Exception	Description
NO_DATA_FOUND	Input parameters do not correspond to a parameter of a deferred call.

Returns

Table 13-7 GET_ARG_TYPE Function Returns

Constant Return Value	Return Value	Corresponding Data Type
DBMS_DEFER_QUERY.ARG_TYPE_VARCHAR2	1	VARCHAR2
DBMS_DEFER_QUERY.ARG_TYPE_NUM	2	NUMBER
DBMS_DEFER_QUERY.ARG_TYPE_ROWID	11	ROWID
DBMS_DEFER_QUERY.ARG_TYPE_DATE	12	DATE
DBMS_DEFER_QUERY.ARG_TYPE_RAW	23	RAW
DBMS_DEFER_QUERY.ARG_TYPE_CHAR	96	CHAR
DBMS_DEFER_QUERY.ARG_TYPE_ANYDATA	109	ANYDATA
DBMS_DEFER_QUERY.ARG_TYPE_CLOB	112	CLOB
DBMS_DEFER_QUERY.ARG_TYPE_BLOB	113	BLOB
DBMS_DEFER_QUERY.ARG_TYPE_BFIL	114	BFILE
DBMS_DEFER_QUERY.ARG_TYPE_OBJECT_NULL_VECTOR	121	OBJECT_NULL_VECTOR
DBMS_DEFER_QUERY.ARG_TYPE_TIMESTAMP	180	TIMESTAMP
DBMS_DEFER_QUERY.ARG_TYPE_TSTZ	181	TSTZ
DBMS_DEFER_QUERY.ARG_TYPE_IYM	182	IYM
DBMS_DEFER_QUERY.ARG_TYPE_IDS	183	IDS
DBMS_DEFER_QUERY.ARG_TYPE_TSLTZ	231	TSLTZ

Note:

The ANYDATA data type supports the following user-defined types: object types, collections, and REFs. See Oracle Database SQL Language Reference and Oracle Database Object-Relational Developer's Guide for more information about the ANYDATA data type.
This function uses abbreviations for some datetime and interval data types. For example, TSTZ is used for the TIMESTAMP WITH TIME ZONE data type. For information about these abbreviations, see "Abbreviations for Datetime and Interval Data Types".

GET_CALL_ARGS Procedure

This procedure returns the text version of the various arguments for the specified call. The text version is limited to the first 2000 bytes.

See Also:

"GET_datatype_ARG Function"
Oracle Database SQL Language Reference and Oracle Database Object-Relational Developer's Guide for more information about the ANYDATA data type

Syntax

DBMS_DEFER_QUERY.GET_CALL_ARGS (
   callno    IN  NUMBER,
   startarg  IN  NUMBER := 1,
   argcnt    IN  NUMBER,       
   argsize   IN  NUMBER,     
   tran_db   IN  VARCHAR2, 
   tran_id   IN  VARCHAR2, 
   date_fmt  IN  VARCHAR2, 
   types     OUT TYPE_ARY,  
   forms     OUT TYPE_ARY,
   vals      OUT VAL_ARY);

DBMS_DEFER_QUERY.GET_CALL_ARGS (
   callno         IN  NUMBER,
   startarg       IN  NUMBER := 1,
   argcnt         IN  NUMBER,       
   argsize        IN  NUMBER,     
   tran_id        IN  VARCHAR2, 
   date_fmt       IN  VARCHAR2, 
   time_fmt       IN  VARCHAR2, 
   ttz_fmt        IN  VARCHAR2, 
   timestamp_fmt  IN  VARCHAR2, 
   tstz_fmt       IN  VARCHAR2, 
   types          OUT TYPE_ARY,  
   forms          OUT TYPE_ARY,
   vals           OUT VAL_ARY);

DBMS_DEFER_QUERY.GET_CALL_ARGS (
   callno         IN  NUMBER,
   startarg       IN  NUMBER := 1,
   argcnt         IN  NUMBER,       
   argsize        IN  NUMBER,     
   tran_id        IN  VARCHAR2, 
   date_fmt       IN  VARCHAR2, 
   time_fmt       IN  VARCHAR2, 
   ttz_fmt        IN  VARCHAR2, 
   timestamp_fmt  IN  VARCHAR2, 
   tstz_fmt       IN  VARCHAR2, 
   tsltz_fmt      IN  VARCHAR2, 
   types          OUT TYPE_ARY,  
   forms          OUT TYPE_ARY,
   vals           OUT VAL_ARY);

Note:

This procedure is overloaded.

Parameters

Table 13-8 GET_CALL_ARGS Procedure Parameters

Parameter	Description
callno	Identification number from the `DEFCALL` view of the deferred remote procedure call (RPC).
startarg	Numerical position of the first argument you want described.
argcnt	Number of arguments in the call.
argsize	Maximum size of returned argument.
tran_db	Global name of the database where the transaction originated.
tran_id	Identifier of the deferred transaction.
date_fmt	Format in which the date is returned.
time_fmt	Format in which the time is returned.
ttz_fmt	Format in which the time with time zone is returned.
timestamp_fmt	Format in which the timestamp is returned.
tstz_fmt	Format in which the timestamp with time zone is returned.
tsltz_fmt	Format in which the timestamp with local time zone is returned.
types	Array containing the types of arguments.
forms	Array containing the character set forms of arguments.
vals	Array containing the values of the arguments in a textual form.

Exceptions

Table 13-9 GET_CALL_ARGS Procedure Exceptions

Exception	Description
NO_DATA_FOUND	Input parameters do not correspond to a parameter of a deferred call.

GET_datatype_ARG Function

This function determines the value of an argument in a deferred call.

The ANYDATA type supports the following user-defined types: object types, collections and REFs. Not all types supported by this function can be enqueued by the ANYDATA_ARG procedure in the DBMS_DEFER package.

The returned text for type arguments includes the following values: type owner, type name, type version, length, precision, scale, character set identifier, character set form, and number of elements for collections or number of attributes for object types. These values are separated by a colon (:).

See Also:

"datatype_ARG Procedure"
The Advanced Replication interface's online Help for information about displaying deferred transactions and error transactions in the Advanced Replication interface in Oracle Enterprise Manager Cloud Control
Oracle Database SQL Language Reference and Oracle Database Object-Relational Developer's Guide for more information about the ANYDATA data type
This function uses abbreviations for some datetime and interval data types. For example, TSTZ is used for the TIMESTAMP WITH TIME ZONE data type. For information about these abbreviations, see "Abbreviations for Datetime and Interval Data Types".

Syntax

Depending upon the type of the argument value that you want to retrieve, the syntax for the appropriate function is as follows. Each of these functions returns the value of the specified argument.

DBMS_DEFER_QUERY.GET_datatype_ARG (
   callno             IN   NUMBER,
   arg_no             IN   NUMBER,
   deferred_tran_id   IN   VARCHAR2 DEFAULT NULL)
  RETURN datatype;

where datatype is:

{ ANYDATA
| NUMBER
| VARCHAR2
| CHAR
| DATE
| RAW
| ROWID
| BLOB
| CLOB
| NCLOB
| NCHAR
| NVARCHAR2 
| IDS
| IYM
| TIMESTAMP
| TSLTZ
| TSTZ }

Parameters

Table 13-10 GET_datatype_ARG Function Parameters

Parameter	Description
callno	Identification number from the `DEFCALL` view of the deferred remote procedure call.
arg_no	Numerical position of the argument to the call whose value you want to determine. The first argument to a procedure is in position 1.
deferred_tran_id	Identifier of the deferred transaction. Defaults to the last transaction identifier passed to the `GET_ARG_TYPE` function. The default is `NULL`.

Exceptions

Table 13-11 GET_datatype_ARG Function Exceptions

Exception	Description
NO_DATA_FOUND	Input parameters do not correspond to a parameter of a deferred call.
ORA-26564	Argument in this position is not of the specified type or is not one of the types supported by the `ANYDATA` type.

GET_OBJECT_NULL_VECTOR_ARG Function

This function returns the type information for a column object, including the type owner, name, and hashcode.

Syntax

DBMS_DEFER_QUERY.GET_OBJECT-NULL_VECTOR_ARG (
   callno                IN   NUMBER,
   arg_no                IN   NUMBER,
   deferred_tran_id      IN   VARCHAR2)
  RETURN SYSTEM.REPCAT$_OBJECT_NULL_VECTOR;

Parameters

Table 13-12 GET_OBJECT_NULL_VECTOR_ARG Function Parameters

Parameter	Description
callno	Call identifier from the `DEFCALL` view.
arg_no	Position of desired parameter in calls argument list. Parameter positions are 1...number of parameters in call.
deferred_tran_id	Deferred transaction identification.

Exceptions

Table 13-13 GET_OBJECT_NULL_VECTOR_ARG Function Exceptions

Exception	Description
NO_DATA_FOUND	Input parameters do not correspond to a parameter of a deferred call.
ORA-26564	Parameter is not an `object_null_vector` type.

Returns

Table 13-14 GET_OBJECT_NULL_VECTOR_ARG Function Returns

Return Value	Type Definition
`SYSTEM.REPCAT$_OBJECT_NULL_VECTOR` type	CREATE TYPE SYSTEM.REPCAT$_OBJECT_NULL_VECTOR AS OBJECT ( type_owner VARCHAR2(30), type_name VARCHAR2(30), type_hashcode RAW(17), null_vector RAW(2000));