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

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:

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

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.

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:

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));