Reporting Handler-Raised Conditions | Teradata Vantage - Rules for Reporting Handler Action-Raised Conditions - Advanced SQL Engine - Teradata Database

An important aspect of Case 3 and Case 4 is how conditions raised by a handler action associated with the completion of an SQL statement are reported to the calling stored procedure or application.

• If a raised condition is handled without exceptions, then the status variables are set to reflect the successful completion condition. No information about the raised condition is sent to the caller. Thus, if a failure occurs in a stored procedure and it is handled, the caller is not aware of the occurrence of failure or the transaction rollback.

  An appropriate mechanism like application rules must be defined to get information about the occurrence of the condition.

• If a statement within the handler action associated with a completion condition handler raises a completion condition other than successful completion, and if there is no suitable handler for that condition, the execution continues with the next statement inside the handler action clause. The status variables contain the values reflecting the original completion condition.

  On completion of the handler action, the status variables are set to reflect the successful completion of the handler action.

• If the possibility of a handler action clause raising an exception condition is known, a suitable handler can be placed inside the handler action clause, while creating the stored procedure, to handle such exceptions. The handlers can be nested as deep as necessary.

Example: An Exception Raised By a Handler

In this example, an exception raised by a handler remains unhandled, and the original condition that invoked the handler is propagated outwards.

CREATE PROCEDURE spSample2(IN pName CHARACTER(30), IN pAmt INTEGER)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23505'
    INSERT INTO Error_Tbl VALUES (:SQLSTATE,CURRENT_TIMESTAMP,
            'spSample2', 'Failed to insert record');
    ...
    L1:BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
      BEGIN
         INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE,
           CURRENT_TIMESTAMP, spSample2',
            'Failed to Insert record');
      END;
      INSERT INTO tab1 VALUES (pName, pAmt);
      INSERT INTO tab1 VALUES (pName, pAmt);
      -- Duplicate row error
      ...
      END L1;
      ...
END;

Assume that the table tab1 is created as follows:

CREATE SET TABLE tab1(c1 CHARACTER(30), c2 INTEGER);

Drop the table Proc_Error_Tbl:

DROP TABLE Proc_Error_Tbl;

Now execute the procedure spSample2:

CALL spSample2('Richard', 100);

During stored procedure execution, the last INSERT statement of the compound statement L1 raises a duplicate row exception. The CONTINUE handler declared for SQLSTATE '23505' is invoked. The handler action statement (INSERT) results in another exception '42000'.

Because there is no handler within this handler to handle SQLSTATE '42000', the original condition that invoked the handler, SQLSTATE '23505', is propagated outwards. The outer compound statement has an EXIT handler defined for SQLSTATE '23505'. This handler handles the exception and control exits the compound statement. Because the procedure contains no other statement, the procedure terminates.

Example: Ignoring a Completion Condition Raised Within a Handler

In this example, a completion condition raised within a handler is ignored.

CREATE PROCEDURE spSample1(IN pName CHARACTER(30), IN pAmt INTEGER)
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
  BEGIN
     DELETE FROM temp_table;
     INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE,
        CURRENT_TIMESTAMP, 'spSample1',
        'Failed to Insert record');
  END;
  INSERT INTO tab1 VALUES (pName, pAmt);
  INSERT INTO tab1 VALUES (pName, pAmt);
  -- duplicate row error
  ...
END;

Assume that the tables temp_table and tab1 are defined as follows:

CREATE TABLE temp_table(c1 INTEGER, c2 CHARACTER(30));
CREATE SET TABLE tab1(c1 CHARACTER(30), c2 DECIMAL(18,2));

Now execute the procedure:

CALL spSample1('Richard', 10000);

The last INSERT statement raises a duplicate row exception and the CONTINUE handler declared for this error is invoked. The DELETE statement in the handler action clause results in a “no data found” completion condition.

Since there is no handler defined within the handler to handle this condition, the condition is ignored and the stored procedure execution continues from the next statement (INSERT) in the handler action clause.

Example: Combining Conditions Raised by Statements Within and Outside a Handler Action Clause

This example combines conditions raised by statements within and outside a handler action clause, and shows how an exception raised by a handler action remains unhandled.

REPLACE PROCEDURE han1(INOUT IOParam1 INTEGER,
                       INOUT IOParam2 CHARACTER(20))
Loutermost: BEGIN
  DECLARE Var1 INTEGER DEFAULT 10;
  L1: BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '42000'
       -- Statement 3_1a
       SET IOParam2 = 'Table does not exist in the outer                                           block';
    DECLARE EXIT HANDLER FOR SQLSTATE '23505'
       L2: BEGIN
       DECLARE EXIT HANDLER FOR SQLSTATE '23505'
  
            -- Statement 3_2a
         SET IOParam2 = ' Duplicate row error ';
       DECLARE EXIT HANDLER
         FOR SQLSTATE '42000'
         BEGIN
 
       -- Statement 3_3a
          SET IOParam2 = 'Nonexistent table in inner block ';
       -- Statement 3_3b
        INSERT INTO tab1 VALUES (IOParam1);
                -- duplicate row error
        END;
       -- Statement 3_3c
        INSERT INTO notable VALUES (IOParam1, IOParam2);
                                          -- 42000
       END L2; /* End Label L2 */
  -- Statement 3_4a
  DELETE tab1 ALL;
  -- Statement 3_4b
  SET IOParam1 = Var1;
  -- Statement 3_4c
  INSERT INTO tab1 VALUES (IOParam1);
  -- Statement 3_4d
  INSERT INTO tab1 VALUES (IOParam1);
             -- duplicate row error
  END L1; /* End Label L1 */
END Loutermost;

During stored procedure execution, the INSERT statement (Statement 3_4d) raises a duplicate row exception. The first EXIT handler declared for SQLSTATE ‘23505’ is invoked because the handler is in the same compound statement labeled L1.

Then the Statement 3_3c in L2 raises an exception with SQLSTATE '42000'. The EXIT handler defined for ‘42000’ is invoked to handle this exception. The INSERT statement (Statement 3_3b within the handler) raises a duplicate row exception. Since there is no handler within the handler to handle this new condition, the handler exits.

The original condition corresponding to SQLSTATE '23505', which invoked the outermost handler, is propagated outwards. Since there is no suitable handler for that in the outermost compound statement Loutermost, the stored procedure terminates with the error corresponding to '23505'.

Example: Condition Handlers in Nested Stored Procedures

The example in this section is based on the following stored procedure:

CREATE PROCEDURE spSample7a()
BEGIN
   DECLARE hNumber INTEGER;
  
   -- Statement_7a_1
   UPDATE Employee SET Salary_Amount = 10000
   WHERE Employee_Number = 1001;
  
   -- Statement_7a_2
   INSERT INTO EmpNames VALUES (1002, ’Thomas’);
  
   -- Statement_7a_3
   UPDATE Employee
   SET Salary_Amount = 10000
   WHERE Employee_Number = 1003;
END;

If the EmpNames table had been dropped, Statement_7a_2 in the preceding stored procedure returns an error with SQLSTATE code ‘42000’ that is not handled because no condition handler is defined for it.

Note that the second procedure calls the first procedure at Statement_7b_2.

Consider a second stored procedure definition:

CREATE PROCEDURE spSample7b()
BEGIN
   DECLARE hNumber INTEGER;
   DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    INSERT INTO Proc_Error_Table
            (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample7b',
                         'Failed to Insert Row');
   
   -- Statement_7b_1
   SELECT nextEmpNum INTO hNumber
       FROM EmpNext;
     UPDATE Employee
        SET nextEmpNum = hNumber+1;
  
   -- Statement_7b_2
   CALL spSample7a();
    
    -- Statement_7b_3
    UPDATE Employee SET Salary_Amount = 10000
    WHERE Employee_Number = 1003;
END;

Example: ANSI Session Mode for Reporting Handler Action-Raised Conditions

This example assumes that the following three SQL statements are invoked interactively from BTEQ in ANSI session mode:

INSERT INTO Department VALUES (’10’, ’Development’);
    
UPDATE Employee SET Salary_Amount = 10000
     WHERE Employee_Number = 1000;
    
CALL spSample7b();

When the preceding three SQL statements are invoked in ANSI session mode, the following sequence of events occurs:

1. The stored procedure statement marked as Statement_7b_2 calls stored procedure spSample7a.
2. Statement_7a_2 in stored procedure spSample7a raises an exception with SQLSTATE code '42000'.
3. Control returns to the calling procedure, spSample7b, along with the exception because there is no condition handler defined in spSample7a.
4. The exception is handled in spSample7b and the handler action is executed.
5. Control exits the calling compound statement because the handler type is EXIT.
6. The following items are left uncommitted:
   • The first two interactive SQL statements
   • Statement_7a_1 from spSample7a
   • Statement_7b_1 from spSample7b
   • The INSERT statement from the condition handler in spSample7b.
7. The following items are not executed:
   • Statement_7a_3 from spSample7a
   • Statement_7b_3 from spSample7b
8. End of process.