GET DIAGNOSTICS statement

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.3 and above

Retrieves information about a condition handled in an exception handler, the active-transaction state, or the number of rows affected by the most recent DML statement.

The CONDITION form may only be used within a condition handler in a compound statement. The TRANSACTION_ACTIVE form may be used as a standalone SQL statement or inside a compound statement. The ROW_COUNT form may only be used within a compound statement, including the body of a SQL stored procedure.

Syntax

GET DIAGNOSTICS CONDITION 1
  { variable_name = condition_info_item } [, ...]

GET DIAGNOSTICS
  { variable_name = statement_info_item } [, ...]

condition_info_item
  { MESSAGE_TEXT |
    RETURNED_SQLSTATE |
    MESSAGE_ARGUMENTS |
    CONDITION_IDENTIFIER |
    LINE_NUMBER }

statement_info_item
  { TRANSACTION_ACTIVE |
    ROW_COUNT }

Parameters

  • variable_name

    A local variable or session variable.

  • CONDITION

    Returns the condition that triggered the condition handler. You must issue GET DIAGNOSTICS CONDITION 1 as the first statement in the handler.

    • MESSAGE_TEXT

      Returns the message text associated with the condition as a STRING. variable_name must be a STRING.

    • RETURNED_SQLSTATE

      Returns the SQLSTATE associated with the condition being handled as a STRING. variable_name must be a STRING.

    • MESSAGE_ARGUMENTS

      Returns a MAP<STRING, STRING> mapping provided as arguments to the parameters of Databricks conditions. For declared conditions, the only map key is MESSAGE_TEXT. variable_name must be a MAP<STRING, STRING>

    • CONDITION_IDENTIFIER

      Returns the condition name that caused the exception. variable_name must be a STRING.

    • LINE_NUMBER

      Returns the line number of the statement raising the condition. NULL if not available.

  • TRANSACTION_ACTIVE

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18.2 and above

    Returns 1 when the statement runs inside an atomic compound statement (BEGIN ATOMIC ... END); otherwise returns 0. variable_name must be an INT.

    Atomic compound statements provide the same multi-statement transactional semantics as interactive transactions; see BEGIN ATOMIC for the surrounding scope.

  • ROW_COUNT

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18.3 and above

    Returns the number of rows affected by the most recently executed DML statement as a BIGINT. variable_name must be a BIGINT.

    Returns NULL when:

    • No statement has executed yet in the enclosing compound body.
    • The most recent statement is not a DML statement (for example, a SELECT, DDL, or SET VAR).
    • The most recent DML statement does not report an affected-row count. Built-in Delta Lake writes — INSERT, UPDATE, DELETE, MERGE INTO, and COPY INTO — populate ROW_COUNT. Writes to non-Delta tables and writes through Apache Spark data source V2 catalogs return NULL.
    • The handler is entered. Each statement resets ROW_COUNT before it runs, so an exception handler always observes NULL.
    • A CALL statement returns. Returning from a procedure resets ROW_COUNT in the caller, so the caller cannot observe DML executed inside the callee.

    At most one variable per GET DIAGNOSTICS statement may be assigned from ROW_COUNT. Combine with TRANSACTION_ACTIVE by adding more assignments to the same statement.

    To capture the affected-row count, place GET DIAGNOSTICS ... = ROW_COUNT immediately after the DML statement. A nested BEGIN ... END block does not hide the outer block's DML — the value remains visible across nested compound bodies — but any intervening non-DML statement or CALL clears the slot.

Examples

-- Capture details of a handled condition in an exception handler.
> CREATE OR REPLACE TABLE emp(name STRING, salary DECIMAL(10, 2));

> BEGIN
    DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
      BEGIN
        DECLARE cond STRING;
        DECLARE message STRING;
        DECLARE state STRING;
        DECLARE args MAP<STRING, STRING>;
        DECLARE line BIGINT;
        DECLARE argstr STRING;
        DECLARE log STRING;
        GET DIAGNOSTICS CONDITION 1
           cond    = CONDITION_IDENTIFIER,
           message = MESSAGE_TEXT,
           state   = RETURNED_SQLSTATE,
           args    = MESSAGE_ARGUMENTS,
           line    = LINE_NUMBER;
        SET argstr = array_join(transform(map_entries(args), t -> concat_ws(' ', 'Param:', t.key, 'Val:', t.value)), ' ');
        SET log = 'Condition: ' || cond ||
                  ' Message: ' || message ||
                  ' SQLSTATE: ' || state ||
                  ' Args: ' || argstr ||
                  ' Line: ' || line;
        VALUES (log);
      END;
    SELECT 10/0;
  END;
 Condition: DIVIDE_BY_ZERO Message: Division by zero. Use try_divide to tolerate divisor being 0 and return NULL instead. If necessary, set <config> to “false” to bypass this error. SQLATTE: 22012 Args:  Parm: config Val: ANSI_MODE Line: 28

-- Check whether the current statement runs inside an atomic transaction.
> DECLARE VARIABLE tx INT;

> GET DIAGNOSTICS tx = TRANSACTION_ACTIVE;
> SELECT tx;
 0

> BEGIN ATOMIC
    DECLARE tx INT;
    GET DIAGNOSTICS tx = TRANSACTION_ACTIVE;
    SELECT tx;
  END;
 1

-- Capture the number of rows affected by the most recent DML statement.
> CREATE OR REPLACE TABLE emp(name STRING, salary DECIMAL(10, 2));

> BEGIN
    DECLARE rc BIGINT;
    INSERT INTO emp VALUES ('Alice', 100.00), ('Bob', 200.00), ('Carol', 300.00);
    GET DIAGNOSTICS rc = ROW_COUNT;
    VALUES ('Inserted ' || rc || ' rows.');
    DELETE FROM emp WHERE salary >= 200.00;
    GET DIAGNOSTICS rc = ROW_COUNT;
    VALUES ('Deleted ' || rc || ' rows.');
  END;
 Inserted 3 rows.
 Deleted 2 rows.
  • Last updated on