HPL/SQL is included to Apache Hive since version 2.0
Sidebar
resignal
RESIGNAL Statement - HPL/SQL Reference
RESIGNAL statement is used in a condition or exception handler to re-raise an error so it can be processed at a higher level.
Syntax:
RESIGNAL | RESIGNAL SQLSTATE [VALUE] sqlstate [SET MESSAGE_TEXT = message_text]
Example 1:
Re-raise the same error:
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
PRINT 'Error raised';
RESIGNAL;
END;
PRINT 'Before executing SQL';
SELECT * FROM abc.abc; -- Table does not exist, error will be raised
PRINT 'After executing SQL - will not be printed in case of error';
END;
Result:
Before executing SQL Error raised
Example 2:
Catch the re-raised condition in the outer condition handler:
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
PRINT 'Error raised, outer handler';
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
PRINT 'Error raised, resignal';
RESIGNAL;
END;
PRINT 'Before executing SQL';
SELECT * FROM abc.abc; -- Table does not exist, error will be raised
PRINT 'After executing SQL - must not be printed';
END;
PRINT 'Continue outer block after exiting inner';
END;
Result:
Before executing SQL Error raised, resignal Error raised, outer handler Continue outer block after exiting inner
Example 3:
Re-raise a condition with the specified SQLSTATE and message text:
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 text = MESSAGE_TEXT;
PRINT 'SQLSTATE: ' || SQLSTATE;
PRINT 'Text: ' || text;
END;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
RESIGNAL SQLSTATE '02031' SET MESSAGE_TEXT = 'Some error';
SELECT * FROM abc.abc; -- Table does not exist, raise an exception
END;
END;
Result:
SQLSTATE: 02031 Text: Some error
Compatibility: IBM DB2, Teradata and MySQL
Version: HPL/SQL 0.03
See also:
Page Tools
