1  GET_DIAGNOSTICS
   Extracts diagnostic information about the execution of the
   previous SQL statement or SQL routine environment.

   The GET DIAGNOSTICS statement captures diagnostic information
   from an Oracle Rdb maintained data structure called the
   diagnostics area. In the ANSI/ISO SQL standard, the diagnostics
   area consists of two components: a single header area and an
   array of detail areas. Oracle Rdb extracts information only from
   the header component and the first element of the detail area
   (Exception 1):

   o  Header area

      Contains status information about rows and transactions, for
      example, the number of rows affected by an INSERT, UPDATE, or
      DELETE statement or the type of transaction that is active.

      See the statement-item-name argument for a complete list of
      the status information you can retrieve from the header area.

   o  Detail area (Exception 1)

      Contains diagnostic information that corresponds to the status
      that would be reported in the SQLSTATE or SQLCODE status
      parameter. The EXCEPTION . . . RETURNED_SQLSTATE argument
      retrieves the SQLSTATE status information from the detail
      area. The EXCEPTION . . . RETURNED_SQLCODE argument retrieves
      the SQLCODE status information from the detail area.
 

2  Environment
   You can use the GET DIAGNOSTICS statement only within the
   compound statement of a multistatement procedure:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a multistatement procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 (B)0GET DIAGNOSTICS qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk            
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj            
 mqwqwqwq> <parameter> wqqq> = statement-item-name qqqqqwqqqqqqqqqqqqqw> 
   x x mq> <variable> qj                                x             x  
   x mqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj             x  
   mq> EXCEPTION qq> 1 qwqwq> <parameter> w> = statement-info-clause wj  
                        x mq> <variable> qj                          x   
                        mqqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqj   
                                                                         



 (B)0statement-item-name =                     
                                           
 qqqqwq> ACCESS_MODE qqqqqqqqqqqqqqqqqwqq> 
     tq> CALLING_ROUTINE qqqqqqqqqqqqqu    
     tq> CONNECTION_NAME qqqqqqqqqqqqqu    
     tq> CURRENT_ROW qqqqqqqqqqqqqqqqqu    
     tq> DATABASE_HANDLE qqqqqqqqqqqqqu
     tq> GLOBAL_TRANSACTION qqqqqqqqqqu    
     tq> HOT_STANDBY_MODE qqqqqqqqqqqqu
     tq> IMAGE_NAME  qqqqqqqqqqqqqqqqqu    
     tq> ISOLATION_LEVEL qqqqqqqqqqqqqu    
     tq> LIMIT_CPU_TIME qqqqqqqqqqqqqqu
     tq> LIMIT_ELAPSED_TIME qqqqqqqqqqu
     tq> LIMIT_ROWS_FETCHED qqqqqqqqqqu
     tq> ROW_COUNT qqqqqqqqqqqqqqqqqqqu   
     tq> SERVER_IDENTIFICATION qqqqqqqu    
     tq> TRACE_ENABLED qqqqqqqqqqqqqqqu
     tq> TRANSACTION_ACTIVE qqqqqqqqqqu   
     tq> TRANSACTION_CHANGE_ALLOWED qqu
     tq> TRANSACTION_SEQUENCE qqqqqqqqu
     tq> TRANSACTION_TIMESTAMP qqqqqqqu    



 (B)0
 
 
     tq> TRANSACTIONS_COMMITTED  qqqqqu    
     mq> TRANSACTIONS_ROLLED_BACK qqqqj    
                                            
 
                                           
 
                                        
                                           
                                            
 
                                           
 
                                          
 
                                            
 
                                          
 
                                          



 (B)0statement-info-clause =        
                                
 qwq> RETURNED_SQLSTATE qqwqqq> 
  mq> RETURNED_SQLCODE qqqj     
                                

 

2  Arguments
 

3  IMAGE_NAME

   Requests that the activating image name be returned to the
   caller. The image name includes the node name from which the
   attach was started. This might be a node different to that on
   which the Oracle Rdb server is running.

   The data is returned to the caller as a VARCHAR (255) value and
   should be assigned to either a VARCHAR or CHAR data type that
   supports the ASCII character set.
 

3  EXCEPTION

   Returns the exception condition following the execution of an
   SQL statement (other than GET DIAGNOSTICS). Either SQLCODE or
   SQLSTATE can be returned.
 

3  parameter

   Retrieves information about the statement execution recorded
   in the diagnostics area and stores it in a simple target
   specification (a parameter or variable).
 

3  RETURNED_SQLCODE

   Requests the SQLCODE be returned to the target variable or
   parameter. The data type of the returned information is
   INTEGER. Oracle Rdb only returns success (0) and warning status
   (positive value) for SQLCODE. Any error status will cause the
   compound statement, or stored routine to return to the calling
   application.
 

3  RETURNED_SQLSTATE

   Requests the SQLSTATE be returned to the target variable or
   parameter. The data type of the returned information is CHAR(5).
   Oracle Rdb only returns success ('00000') and warning status for
   SQLSTATE. Any error status will cause the compound statement, or
   stored routine to return to the calling application.
 

3  statement-item-name

   Specifies the kind of diagnostic information you can retrieve
   about a previously executed SQL statement. You can gather the
   following diagnostic data:

   o  ACCESS_MODE returns the character string READ ONLY, READ
      WRITE, or BATCH UPDATE to indicate the type of transaction
      that is active. These character strings are of the CHAR data
      type. The argument also returns NONE when no transaction is
      active. See the SET_TRANSACTION statement for a description of
      transaction access modes.

   o  CALLING_ROUTINE returns a string of data type CHAR(31) of
      the name of the calling routine. If there is no name for the
      calling routine, spaces are returned.

   o  CONNECTION_NAME returns the current connection name.

   o  DATABASE_HANDLE returns the current database handle (or
      stream) identifier.

   o  CURRENT_ROW returns the integer value for the number of rows
      that have been fetched by the innermost FOR control statement.

   o  GLOBAL_TRANSACTION returns an integer of 1 when a global
      transaction is active and an integer of 0 otherwise.

   o  ISOLATION_LEVEL returns the character string READ COMMITTED,
      REPEATABLE READ, or SERIALIZABLE to indicate the isolation
      level of a transaction. These character strings are of
      the CHAR data type. The argument also returns NONE when no
      transaction is active. See the SET_TRANSACTION statement for a
      description of transaction isolation
      levels.

   o  LIMIT_CPU_TIME returns an INTEGER value for the session's
      execution CPU time limit in seconds. If zero (0)
      is returned it is equivalent to no CPU time limit.
      This value is established by either the logical name
      RDMS$BIND_QG_EXEC_CPU_TIMEOUT or the SET QUERY EXECUTION LIMIT
      CPU TIME statement.

   o  LIMIT_ELAPSED_TIME returns an INTEGER value for the session's
      execution elapsed time limit in seconds. If zero (0) is
      returned it is equivalent to no elapsed time limit. This value
      is established by either the logical name RDMS$BIND_QG_EXEC_
      ELAPSED_TIMEOUT or the SET QUERY EXECUTION LIMIT ELAPSED TIME
      statement.

   o  LIMIT_ROWS_FETCHED returns a BIGINT value for the session's
      row limit. If zero (0) is returned, it is equivalent to no
      row limit. This value is established by the logical name
      RDMS$BIND_QG_REC_LIMIT.

   o  ROW_COUNT returns an integer for the number of rows affected
      by an INSERT, searched UPDATE, searched DELETE, or a FOR
      cursor loop statement.

   o  TRACE_ENABLED returns an INTEGER value to indicate if the
      TRACE flag has been enabled using the statement SET FLAGS
      'TRACE', or by either of the logical names RDMS$SET_FLAGS
      or RDMS$DEBUG_FLAGS. A zero (0) is returned if the flag is
      disabled, otherwise a one (1) is returned to indicate that
      tracing is enabled.

   o  TRANSACTION_ACTIVE returns an integer of 1 when a transaction
      is active and an integer of 0 otherwise.

   o  TRANSACTIONS_COMMITTED returns an integer value for the number
      of transactions that have been committed during the processing
      of a multistatement procedure.

   o  TRANSACTIONS_ROLLED_BACK returns an integer value for the
      number of transactions that have been rolled back during the
      processing of a multistatement procedure.

   o  TRANSACTION_CHANGE_ALLOWED

      There are many situations where the SQL language programmer
      would like to start or end a transaction but does not know if
      a transaction statement (SET TRANSACTION, START TRANSACTION,
      COMMIT or ROLLBACK) is currently permitted. The transaction
      statements are not permitted in the following cases:

      o  During a multi-database or global transaction. In this case
         the transaction must be coordinated by the client, not a
         server-based procedure.

      o  When a BEGIN ATOMIC compound statement is in the outer
         scope.

      o  When a FOR cursor loop is active in an outer scope.

      The TRANSACTION_CHANGE_ALLOWED clause allows the programmer
      to detect these restricted locations and conditionally execute
      a COMMIT, ROLLBACK, START TRANSACTION or SET TRANSACTION as
      needed.

      The result data type is INTEGER. If transaction changes are
      permitted then a value one (1) will be assigned. Otherwise the
      result will be zero (0).

   o  HOT_STANDBY_MODE

      This option returns a text string that indicates if this
      database is participating in a Hot Standby configuration as
      master (returns 'MASTER'), or as standby (returns 'STANDBY'),
      or is not in such a configuration (returns 'NONE').

      The result data type is CHAR (31).

   o  SERVER_IDENTIFICATION

      This option returns a text string containing the Oracle Rdb
      release number. This is useful for log file annotation.

      The result data type is CHAR (31).

   o  TRANSACTION_TIMESTAMP

      This option returns the date and time that the last
      transaction was started. If a transaction is not active, the
      returned date and time may be for a prior transaction.

                                     NOTE

         The database server will start transactions when
         performing database operations. So, this timestamp may
         reflect the time of an internal transaction.

      If the default date format is SQL99, this option returns a
      value with the data type TIMESTAMP(2), otherwise it returns a
      DATE (VMS) data type. The default date format can be changed
      using either the SET DIALECT or SET DEFAULT DATE FORMAT
      statements, or one of the associated module attributes.

   o  TRANSACTION_SEQUENCE

      This is the transaction sequence number (TSN) assigned to
      the most recently started transaction. The TSN is a unique
      indicator of database transaction activity, however, please
      note that the TSN may be reused in some cases. The TSN
      for a READ ONLY transaction reflects the transaction state
      which is visible to the transaction and, therefore, it was
      previously assigned to a READ WRITE transaction. If a READ
      WRITE transaction does not perform database I/O or was rolled
      back, that TSN may be reused by a subsequent READ WRITE
      transaction.

      This option returns a BIGINT data type.
 

3  variable

   Retrieves information about the statement execution recorded
   in the diagnostics area and stores it in a simple target
   specification (a parameter or variable).
 

2  Examples
   Example 1: Using a GET DIAGNOSTICS statement to retrieve row
   count

   PROCEDURE increate_nh (SQLSTATE, :rows_affected INTEGER);
       BEGIN ATOMIC
           UPDATE salary_history
           SET    salary_amount = salary_amount * 1.05
           WHERE  salary_end IS NULL
             AND  employee_id IN (SELECT    employee_id
                                  FROM      employees
                                  WHERE     state = 'NH' );
           GET DIAGNOSTICS :rows_affected = ROW_COUNT;
       END;

   Example 2: Using RETURNED_SQLSTATE

   SQL> DECLARE :Y CHAR(5);
   SQL> BEGIN
   cont> SET :Y = 'Hello';
   cont> GET DIAGNOSTICS EXCEPTION 1 :Y = RETURNED_SQLSTATE;
   cont> END;
   SQL> PRINT :Y;
    Y
    00000
   SQL>

   Example 3: Using RETURNED_SQLCODE

   SQL> DECLARE :X INTEGER;
   SQL> BEGIN
   cont> SET :X = 100;
   cont> GET DIAGNOSTICS EXCEPTION 1 :X = RETURNED_SQLCODE;
   cont> END;
   SQL> PRINT :X;
              X
              0

   Example 4: Returning the current connection name

   SQL> CONNECT TO 'ATTACH FILENAME mf_personnel' AS 'my_connection';
   SQL> DECLARE :conn_name VARCHAR(20);
   SQL> BEGIN
   cont>    GET DIAGNOSTICS :conn_name = CONNECTION_NAME;
   cont> END;
   SQL> PRINT :conn_name;
    CONN_NAME
    my_connection

   Example 5: Using the TRANSACTION_TIMESTAMP and TRANSACTION_
   SEQUENCE options

   SQL> set transaction read write;
   SQL> show transaction
   Transaction information:
       Statement constraint evaluation is off
   On the default alias
   Transaction characteristics:
           Read Write
   Transaction information returned by base system:
   a read-write transaction is in progress
     - updates have not been performed
     - transaction sequence number (TSN) is 0:256
     - snapshot space for TSNs less than 0:256 can be reclaimed
     - recovery unit journal filename is USER2:[RDM$RUJ]SCRATCH$00018679B3AD.RUJ;1
     - session ID number is 8
   SQL>
   SQL> declare :x date vms;
   SQL>
   SQL> begin get diagnostics :x = transaction_timestamp; end;
   SQL> print :x;
    X
    27-MAY-1999 22:39:17.02
   SQL>
   SQL> declare :y bigint;
   SQL>
   SQL> begin get diagnostics :y = transaction_sequence; end;
   SQL> print :y;
                       Y
                     256
   SQL>
   SQL> select current_timestamp from rdb$database;

    27-MAY-1999 22:39:18.20
   1 row selected
   SQL>
   SQL> commit;

   Example 6: Using the HOT_STANDBY_MODE and SERVER_IDENTIFICATION
   options

   SQL> set flags 'trace';
   SQL> declare :id, :hsmode char(31);
   SQL> begin
   cont> get diagnostics :id = SERVER_IDENTIFICATION,
   cont>                 :hsmode = HOT_STANDBY_MODE;
   cont> trace :id, :hsmode;
   cont> end;
   ~Xt: Oracle Rdb V7.1              NONE

   Example 7: Using the LIMIT_CPU_TIME, LIMIT_ROWS_FETCHED, and
   LIMIT_ELAPSED_TIME options

   SQL> set flags 'trace';
   SQL> set query execution limit elapsed time 10 minutes;
   SQL> begin
   cont> declare :row_limit integer;
   cont> declare :elapsed_limit integer;
   cont> declare :cpu_limit integer;
   cont> get diagnostics
   cont>     :cpu_limit = LIMIT_CPU_TIME,
   cont>     :row_limit = LIMIT_ROWS_FETCHED,
   cont>     :elapsed_limit = LIMIT_ELAPSED_TIME;
   cont> trace 'LIMIT_ROWS_FETCHED: ', :row_limit;
   cont> trace 'LIMIT_CPU_TIME:     ', :cpu_limit;
   cont> trace 'LIMIT_ELAPSED_TIME: ', :elapsed_limit;
   cont> end;
   ~Xt: LIMIT_ROWS_FETCHED: 0
   ~Xt: LIMIT_CPU_TIME:     0
   ~Xt: LIMIT_ELAPSED_TIME: 600
   SQL>

   Example 8: Using the TRACE_ENABLED keyword in a compound
   statement

   SQL> declare :x integer;
   SQL> begin
   cont> get diagnostics :x = TRACE_ENABLED;
   cont> end;
   SQL> print :x;
              X
              0
   SQL> set flags 'trace';
   SQL> begin
   cont> get diagnostics :x = TRACE_ENABLED;
   cont> end;
   SQL> print :x;
              X
              1