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 and above

    Note

    Databricks Runtime 18 is newer than Databricks Runtime 18.0, 18.1, and 18.2. Features that would previously have shipped as a later numbered version now ship as dated updates to Databricks Runtime 18 instead. For details, see About unified release notes.

    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