I need to encapsulate a cursor in a transational block which includes 1 delete before and 1 delete after cursor declaration (in a stored procedure). For example: BEGIN TRANSACTION;
DELETE FROM table1 WHERE (filtering conditions);
FOR var AS CURSOR cur FOR SELECT COL1 AS C1, COL2 AS C2,...., COLN AS CN FROM table2 WHERE (filtering conditions) DO INSERT INTO table1 VALUES(var.C1,var.C2,...,var.CN) END FOR;
DELETE FROM table2 WHERE (filtering conditions);
This statement fails with message: "Fetch/Close operation attemped on a closed cursor (procedure name)". I try different ways to resolve this error (that occurs when a CAST operation fails in one row), but I can't... The procedure must pursue its execution until the normal termination even in the presence of errors, because the tuples that will generate the errors must be used to update a counter that counts the discards. If I don't encapsulate the same instructions sequence in a transational block, it works good but it's a specifications violation... Thanks
To do this, you'll need to run in ANSI session mode (not Teradata BT/ET mode). ANSI transaction semantics roll back the current request without failing the entire transaction. That should let you declare a CONTINUE handler for the error condition, so you can resume inside the cursor FOR loop.
Don't forget to do explicit COMMIT either after the last delete or in the calling program.
It's ok, I found the correct syntax to do this. I've another question: when I call a procedure from another procedure in ANSI Mode and this procedure have to do an insert in a table, when I commit in the "internal procedure", all the statement before the call will be committed? For example:
Main procedure: UPDATE FROM table1 SET ...; CALL INSERTROW(); COMMIT;
Internal procedure: INSERT INTO table2 VALUES(...); COMMIT;
The internal procedure commit instruction will commit the update of main procedure? Or it will commit only the insert in "procedure scope"? Thanks!
As you seem to suspect, COMMIT applies to the entire transaction - so you would not normally include a COMMIT within an "internal procedure". Teradata does not currently support intermediate SAVEPOINTs nested within a transaction.