I believe that the reason you're getting the 24502 error message is that you're referencing the wrong pointer within your FOR loop. In the statement
FOR stmtCursor AS statementCursor CURSOR FOR
SELECT
Statement_Str
FROM
Statement_Table
ORDER BY Stmt_Id ASCENDING
DO
Current_Statement_Id = Current_Statement_Id + 1;
SET Stmt = stmtCursor.Statement_Str; <----- This should be statementCursor.Statement_Str.
I'm assuming you noticed your typo on CURRENT_STATEMETN_ID as well.
Hope that helps get you past your issue.
David, thanks a lot, actually I found the problem, the error message I saw was not mapped right. I was only using sqlstate to map message, actually I should use sqlcode to look for details of information. The exception was properly handled, just I was not generating correct error message by just looking at sqlstate.
One question though , in the FOR c1 AS c2 CURSOR FOR structure, I think the c1 is the cursor name , right? this syntax is confusing . c2 looks like redundant to me. Please elaborate, thanks!
How to handle exceptions raised within the cursor iteration? Basically what I have here is a For cursor and I want to handle the exception and exit the for loop. The exception handling are MULTIPLE SQL statements which cleans up and record error messages. The Teradata documentation's example shows only one SQL statement for handler action statement. I tried to enclose multiple by using BEGIN ... END block the code looks like this
exception handling syntax is:
/*** Previous logic codes here **/
BEGIN --(1)begin the block for record process and error handling
DECLARE CURRENT_STATEMENT_ID INTEGER DEFAULT 0;
DECLARE STMT VARCHAR(1000) DEFUALT '';
DECLARE EXIT HANDLER FOR SQLException
BEGIN -- (2)begin of the block if exception code
SET errorcode = SQLState;
INSERT INTO ERRORTABLE(ERRORCODE , CURRENT_STATEMENT_STR, CURRENT_TIMESTAMP(0));
FOR undoCursor AS undostatementcur CURSOR FOR
SELECT STATEMENT_STR INTO :STMT FROM STATEMENT_TABLE WHERE STMT_ID <= CURRENT_STMT_ID -1
ORDER BY STMT_ID DESC
DO
CALL DBC.SYSEXECSQL(STMT);
END FOR;
END; -- (2)end of exception handling block
-- (3)begin of the real record process cursor iteration
FOR stmtCursor AS statementCursor CURSOR FOR
SELECT STATEMENT_STR FROM STATEMENT_TABLE ORDER BY STMT_ID ASC
DO
CURRENT_STATEMETN_ID = CURRENT_STATEMENT_ID + 1;
SET STMT = stmtCursor.STATEMENT_STR;
CALL DBC.SYSEXECSQL(STMT); --- (**)
END FOR;
-- (3) end of the real record process cursor iteration
END; -- (1)end of record process and error handling
-- actions will take place even when exception happens.
However, this does not work, when I check the SQLSTATE message I got, apparently, I got the following SQLCode and the corresponding error message:
24502|Invalid Cursor state in the stored procedure (%FSTR).
This is not what I expected, I am expecitng the error message like duplicate secondary index violation or something like that.
So, my questions are: first, does exit handler can be coded using begin..end block like this? (apparently, that worked)
second, what cursor is in invalid state? does that mean that the exit handler exit the BEGIN(1)..END(1) with the cursor left open? well, if that is the case, how to close a for loop cursor? Even if it is the case, i should see the real error code for the actual failed action caused by CALL DBC.SYSEXECSQL(STMT) ; --(**) right?
Now, if I move the whole exit handler block into the for loop (3), when exit handler triggered, will it exit for..loop(3) or it is still bounded by BEGIN...END(1)?