Can I see where my Teradata stored procedure is failing?

70 Views Asked by At

I have a stored procedure in Teradata that used to work but some changes recently have rendered the output of the procedure non-existent. Partial code is pasted below in-case anyone can find the error or how to turn off the error handling. But what I'm looking for here is a way to track the error in the procedure. If I try to run the individual elements of the procedure separately they fail and if I run the full procedure it runs but doesn't tell me where it failed and moved on.

Is there a way in Teradata to see where things are going wrong?

REPLACE PROCEDURE HP_FL_OWN.RX_INITIATIVES_UM_SP()

BEGIN
        DECLARE VAR_DB_NAME, VAR_TABLE_NAME VARCHAR(60);
        DECLARE ACT_INFO VARCHAR(1000);
        DECLARE STEP_NAME VARCHAR(100);
        DECLARE ACT_NAME VARCHAR(100);
        DECLARE ACT_ID INT; 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -- Error Handling step
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    /*  DECLARE EXIT HANDLER FOR SqlException */
        BEGIN

                SELECT SqlState INTO ACT_INFO;
            --  SET ACT_INFO=SqlState;
                
                -- if error occurs in 01 or 02
                IF STEP_NAME IN('01-Verify EDW Completion', '02-Verify Current Execution')THEN 
                    -- insert error log record for step 01 or step 02
                    CALL HP_FL_OWN.OPR_ACTIVITY_LOG_INSERT_SP(ACT_NAME, Current_Timestamp(0), 'E',STEP_NAME,'ERROR_SQLSTATE:'||ACT_INFO,ACT_ID);--create record in log table for the day
                
                ELSE -- if error occurs in any other step
                    -- update error log record with that step
                    CALL HP_FL_OWN.OPR_ACTIVITY_LOG_UPDATE_SP(ACT_ID,'E',STEP_NAME,'ERROR_SQLSTATE:'||ACT_INFO);---update record in log table when process completes
                
                END IF;
                
        END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -- Begin SP 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I've tried looking at the history but it does not involved any detailed info on what line(s) are failing.

0

There are 0 best solutions below