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