Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 17.3 DBMS_DEFER: Building Deferred CallsChapter 17
Deferred Transactions and Remote Procedure Calls
Next: A. What's on the Companion Disk?
 

17.4 DBMS_DEFER_QUERY: Performing Diagnostics and Maintenance

Occasionally, you may want to see details about deferred RPCs in the queue, such as what procedure and parameters are used. The DBMS_DEFER_QUERY package contains procedures to display this data.

17.4.1 Getting Started with DBMS_DEFER_QUERY

The DBMS_REPCAT_QUERY package is created when the Oracle database is installed. The dbmsdefr.sql script (found in the built-in packages source directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catrep.sql, which must be run to install the advanced replication packages. The wrapped sql script prvtrctf.sql creates the public synonym DBMS_REPCAT_QUERY. No EXECUTE privileges are granted on DBMS_REPCAT_QUERY; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.

17.4.1.1 DBMS_DEFER_QUERY programs

Table 17.14 lists the programs available in the DBMS_DEFER_QUERY package.


Table 17.14: DBMS_DEFER_QUERY Programs

Name

Description

Use in SQL?

GET_ARG_TYPE

Returns the type of a parameter in a deferred call

No

GET_CALL_ARGS

Returns information about parameters in text form

No

GET_<datatype>_ARG

Returns the value of a parameter whose type is <datatype>; values can be CHAR, DATE, NUMBER, RAW, ROWID, or VARCHAR2

No

There are no exceptions defined for this package.

17.4.1.2 DBMS_DEFER_QUERY nonprogram elements

Table 17.15 lists the nonprogram elements defined for the DBMS_DEFER_QUERY package.


Table 17.15: DBMS_DEFER_QUERY Other Elements

Name/Type

Description

TYPE type_ary

Table of NUMBER

TYPE val_ary

Table of VARCHAR2(2000)

The PL/SQL tables type_ary and val_ary are both used in parameters to the procedure GET_CALL_ARGS; type_ary is an output array for RPC parameter datatypes and val_ary is an output array of the parameter values. Table 17.16 shows the mapping of numbers to datatypes in type_ary.


Table 17.16: Mapping Numbers to Datatypes

Datatype

Numeric Value

in type_ary

BFILE (Oracle8 only)

114

BLOB (Oracle8 only)

113

CFIL (Oracle8 only)

115

CHAR

96

CFIL (Oracle8 only)

112

DATE

12

NUMBER

2

RAW

23

ROWID

11

VARCHAR2

1

Before examining the details of the individual procedures, let's look at a basic example of how they are used. Suppose that we have queued a call to the ProductMaint.AddProduct procedure, as described in an earlier example.

 SQL> EXECUTE qAddProduct
PL/SQL procedure successfully completed.

We now have an entry in the DEFCALL data dictionary view:


  1  SELECT	callno,
  2  		deferred_tran_db,
  3  		deferred_tran_id,
  4  		schemaname,
  5  		packagename,
  6  		procname,
  7  		argcount
  8* FROM	defcall
SYSTEM@D7CA SQL> /

	     Deferred          Deferred
	     Tran              Tran         Schema    Package	  Procedure Arg
Call No      DB                ID           Name      Name	  Name	    Count
------------ ----------------- ------------ --------- ----------- ---------------
9929966326029D7CA.BIGWHEEL.COM 3.58.14      SPROCKET  PRODUCTMAINTAddProduct6

1 row selected.

Here we see that the procedure ProductMaint.AddProduct is queued, and that it has six parameters. To determine what these parameters are, first determine their data types with the GET_ARG_TYPE procedure, and then determine their values with GET_<datatype>_ARG. Alternatively, you can use GET_CALL_ARGS, which returns all the information in a single call. The examples in the following sections illustrate the use of each technique.

17.4.1.3 The DBMS_DEFER_QUERY.GET_ARG_TYPE function

You can use this function in conjunction with the GET_<datatype>_ARG or GET_CALL_ARGS functions to determine information about the deferred RPCs in the queue. GET_ARG_TYPE returns a number corresponding to the argument's datatype. Here is the specification for GET_ARG_TYPE:

FUNCTION DBMS_DEFER_QUEUE.GET_ARG_TYPE
   (callno IN NUMBER,
    deferred_tran_db IN VARCHAR2,
    arg_no IN  NUMBER,
    deferred_tran_id IN VARCHAR2)
RETURN NUMBER;

The following table shows the mapping of datatypes to return values.

Argument

Datatype

GET_ARG_TYPE

Return Code

BFIL (Oracle8 only)

114

BLOB (Oracle8 only)

113

CFIL (Oracle8 only)

115

CHAR

96

CFIL (Oracle8 only)

112

DATE

12

NUMBER

2

RAW

23

ROWID

11

VARCHAR2

1

Notice that the datatypes here are limited to the Oracle-supplied datatypes; you cannot, for example, defer a call to a procedure that accepts a PL/SQL table as a parameter.

Parameters are summarized in the following table.

Name

Description

callno

The CALLNO of the RPC, as stored in the DEFCALL data dictionary view

deferred_tran_db

Global name of the database deferring the call (also stored in DEFCALL)

arg_no

The position of the argument in the RPC

deferred_tran_id

The deferred_tran_id for the call (also stored in DEFCALL)

There are no restrictions on calling GET_ARG_TYPE.

17.4.1.3.1 Exceptions

The GET_ARG_TYPE function may raise the following exception:

Name

Number

Description

NO_DATA_FOUND

-100

Specified argument does not exist for specified RPC call

17.4.1.3.2 Example

This example shows how you use the GET_ARG_TYPE function to determine the datatypes of a queued call:

  1  DECLARE
  2	     vDataType NUMBER;
  3  BEGIN
  4	     vDataType := DBMS_DEFER_QUERY.GET_ARG_TYPE(
  5			 callno => 9929966326029,
  6			 deferred_tran_db => 'D7CA.BIGWHEEL.COM',
  7			 arg_no => 3,
  8			 deferred_tran_id => '3.58.14');
  9	     dbms_output.put_line('Datatype for arg 1 is '|| vDataType);
 10* END;
SYSTEM@D7CA SQL> /
Datatype for arg 3 is 1

PL/SQL procedure successfully completed.

Here we see that the third argument passed to ProductMaint.AddProduct is of type VARCHAR2. Now you can use the GET_VARCHAR2_ARG function (described in the next section) to determine the value passed.

  1  DECLARE
  2	     vArgValue VARCHAR2(80);
  3  BEGIN
  4	     vArgValue := DBMS_DEFER_QUERY.GET_VARCHAR2_ARG(
  5			 callno => 9929966326029,
  6			 deferred_tran_db => 'D7CA.BIGWHEEL.COM',
  7			 arg_no => 3,
  8			 deferred_tran_id => '3.58.14');
  9	     dbms_output.put_line('Argument 3 is '|| vArgValue);
 10* END;
SYSTEM@D7CA SQL> /
Argument 3 is Mens 18 Speed Racer

PL/SQL procedure successfully completed.

Here we see that the actual value passed was "Mens 18 Speed Racer."

17.4.1.4 The DBMS_DEFER_QUERY.GET_CALL_ARGS procedure

The GET_CALL_ARGS procedure allows you to obtain the datatypes and values for all arguments passed to a procedure in a single call. This is the easiest way to obtain information about the datatypes and values of all passed parameters. Here is the specification:

PROCEDURE 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,          
    vals OUT VAL_ARY);        

Parameters are summarized in the following table.

Name

Description

callno

The CALLNO of the RPC as stored in the DEFCALL data dictionary view

start_arg

First argument to fetch

argcnt

Number of arguments to fetch

argsize

Largest size of a returned argument

tran_db

Global name of database deferring the call (also stored in DEFCALL)

tran_id

The deferred_tran_id parameter for the call (also stored in DEFCALL)

date_fmt

Date format mask

types

Output array for argument types

vals

Output array for argument values

There are no restrictions on calling the GET_CALL_ARGS procedure.

17.4.1.4.1 Exceptions

GET_CALL_ARGS may raise the following exception:

Name

Number

Description

NO_DATA_FOUND

-100

Specified argument does not exist for specified RPC call

17.4.1.4.2 Example

The following example illustrates the use of the GET_CALL_ARGS procedure:

  1  DECLARE
  2	     vTypes DBMS_DEFER_QUERY.TYPE_ARY;
  3	     vVals  DBMS_DEFER_QUERY.VAL_ARY;
  4	     indx NUMBER;
  5  BEGIN
  6	     DBMS_DEFER_QUERY.GET_CALL_ARGS(
  7			 callno => 9929966326029,
  8			 startarg => 1,
  9			 argcnt => 6,
 10			 argsize => 128,
 11			 tran_db => 'D7CA.BIGWHEEL.COM',
 12			 tran_id => '3.58.14',
 13			 date_fmt => 'DD-Mon-YYYY hh24:MI:SS',
 14			 types => vTypes,
 15			 vals => vVals );
 16	     FOR indx IN 1..6 LOOP
 17		     dbms_output.put_line('Arg '|| indx || ': Datatype '||
 18			     vTypes(indx) || ' Value: '|| vVals(indx) );
 19	     END LOOP;
 20* END;
SYSTEM@D7CA SQL> /
Arg 1: Datatype 2 Value: 10
Arg 2: Datatype 1 Value: BIKE-0018
Arg 3: Datatype 1 Value: Mens 18 Speed Racer
Arg 4: Datatype 1 Value: 19971031-01
Arg 5: Datatype 12 Value: 31-Oct-1997 00:00:00
Arg 6: Datatype 1 Value: PLANNED

PL/SQL procedure successfully completed.

17.4.1.5 The DBMS_DEFER_QUERY.GET_<datatype>_ARG function

The GET_<datatype>_ARG function returns a value of a certain type (specified by <datatype>). The type of the returned value corresponds to the value of the argument specified by arg_no in the deferred RPC corresponding to callno.

There is one variant of the GET_<datatype>_ARG function for each of the Oracle-supplied datatypes. Here is the specification:

FUNCTION DBMS_DEFER_QUERY.GET_<datatype>_ARG
   (callno IN NUMBER,                   
   deferred_tran_db IN VARCHAR2
   arg_no IN NUMBER,
   deferred_tran_id IN VARCHAR2 DEFAULT NULL)
RETURN arg;

<datatype> can be one of the following:

CHAR
DATE
NUMBER
RAW
ROWID
VARCHAR2
NCHAR (Oracle8 only)
NVARCHAR2 (Oracle8 only)
BLOB (Oracle8 only)
CLOB (Oracle8 only)
NCLOB (Oracle8 only)

Therefore, any of the following are valid:

FUNCTION DBMS_DEFER_QUERY.GET_CHAR_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_DATE_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_NUMBER_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_RAW_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_ROWID_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_VARCHAR2_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_NCHAR_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_NVARCHAR2_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_BLOB_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_CLOB_ARG...
FUNCTION DBMS_DEFER_QUERY.GET_NCLOB_ARG...

Parameters have the same meanings described for the GET_ARG_TYPE procedure.

17.4.1.5.1 Exceptions

The GET_<datatype>_ARG function may raise the following exceptions:

Name

Number

Description

NO_DATA_FOUND

-100

Specified argument does not exist for specified RPC call

WRONG_TYPE

-26564

Specified argument is not of type <datatype>

17.4.1.5.2 Example

Assuming that argument number 3 in the deferred call has CALLNO = 8 and DEFERRED_TRAN_ID = 45.12.3 in the DEFCALL data dictionary view is of type CHAR, follow these steps to determine the argument's value:

VARIABLE vChar	 CHAR;

BEGIN
	vChar := DBMS_QUERY.GET_CHAR_ARG(callno => 8,
					deferred_tran_db	=> 'D8CA.BIGWHEEL.COM',
					arg_no => 3,
					deferred_tran_id => 45.12.3);
END;
/


Previous: 17.3 DBMS_DEFER: Building Deferred CallsOracle Built-in PackagesNext: A. What's on the Companion Disk?
17.3 DBMS_DEFER: Building Deferred CallsBook IndexA. What's on the Companion Disk?

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference