メインコンテンツまでスキップ

GET DIAGNOSTICS statement

Applies to: check marked yes Databricks Runtime 16.3 and later

Preview

This feature is in Public Preview.

Retrieve information about a condition handled in an exception handler.

This statement may only be used within a condition handler in a compound statement.

Syntax

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

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

Parameters

  • variable_name

    A local variable or session variable.

  • CONDITION 1

    Returns the condition that triggered the condition handler. You must call 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.

Examples

SQL
-- Retrieve the number of rows inserted by an INSERt statement
> 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 =
(SELECT aggregate(array_agg('Parm:' || key || ' Val: value '),
'', (acc, x)->(acc || ' ' || x))
FROM explode(args) AS args(key, val));
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> tofalseto bypass this error. SQLATTE: 22012 Args: Parm: config Val: ANSI_MODE Line: 28