Exception handling within a stored procedure

JamesSmithJamesSmith
in General Discussion

I need to manage exception handling within a stored procedure. I use EXCEPTION WHEN OTHERS, but it only catches some errors. When an error occurs in a INSERT INTO ... SELECT ... FROM query, it cannot catch the correct error and whole procedure fall into [57014] [4278] [Vertica]VJDBC ERROR: Operation canceled.

When I removed the EXCEPTION WHEN OTHERS part during testing, the error message on the constraint was correct, but I need to manage EXCEPTIONS. Do you have any recommendations on how to work with that within a stored procedure? How to handle all exceptions?

Here is simple script to test this issue:

CREATE TABLE test_schema.SRC_TABLE (
COLUMN_1 INT,
COLUMN_2 TIMESTAMP,
COLUMN_3 VARCHAR(32)
)
;

INSERT INTO test_schema.SRC_TABLE VALUES(1,getdate(),NULL);
INSERT INTO test_schema.SRC_TABLE VALUES(2,NULL,'string2');
INSERT INTO test_schema.SRC_TABLE VALUES(NULL,getdate(),'string3');

CREATE TABLE test_schema.TGT_TABLE (
COLUMN_1 INT NOT NULL,
COLUMN_2 TIMESTAMP NOT NULL,
COLUMN_3 VARCHAR(32) NOT NULL
)
;

CREATE OR REPLACE PROCEDURE test_schema.SP_TGT_TABLE()
AS
$$
BEGIN

EXECUTE
        'INSERT INTO test_schema.TGT_TABLE (COLUMN_1, COLUMN_2, COLUMN_3)
            SELECT
                 COLUMN_1
                ,COLUMN_2
                ,COLUMN_3
            FROM test_schema.SRC_TABLE';

/*** This query correctly handles exception ***/
/* 
        'INSERT INTO test_schema.TGT_TABLE (COLUMN_1, COLUMN_2, COLUMN_3)
            SELECT
                 NULL AS COLUMN_1
                ,NULL AS COLUMN_2
                ,NULL AS COLUMN_3
            FROM test_schema.SRC_TABLE';
*/

EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'Error message: %', SQLERRM;

END;
$$;

CALL test_schema.SP_TGT_TABLE();

I work with Vertica version 24.4.

Thank you for feedback and ideas.

Comments

This discussion has been closed.