GET DIAGNOSTICS statement
Applies to: Databricks Runtime 16.3 and later
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
-
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 aSTRING
. -
RETURNED_SQLSTATE
Returns the
SQLSTATE
associated with the condition being handled as aSTRING
.variable_name
must be aSTRING
. -
MESSAGE_ARGUMENTS
Returns a
MAP<STRING, STRING>
mapping provided as arguments to the parameters of Databricks conditions. For declared conditions, the only map key isMESSAGE_TEXT
.variable_name
must be aMAP<STRING, STRING>
-
CONDITION_IDENTIFIER
Returns the condition name that caused the exception.
variable_name
must be aSTRING
. -
LINE_NUMBER
Returns the line number of the statement raising the condition.
NULL
if not available.
-
Examples
-- 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> to “false” to bypass this error. SQLATTE: 22012 Args: Parm: config Val: ANSI_MODE Line: 28