All Forums Database
Elr0ndK 11 posts Joined 01/08
01 Feb 2008
Problems with a cursor...

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);END TRANSACTION;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

Fred 1096 posts Joined 08/04
01 Feb 2008

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.

Elr0ndK 11 posts Joined 01/08
02 Feb 2008

Can you post me an example code I can use?Thanks!

Elr0ndK 11 posts Joined 01/08
04 Feb 2008

I found the way to enter in ANSI Session mode, but how I can do the encapsulation of sequence "DELETE-CURSOR-DELETE"?BT e ET are not allowed...

Elr0ndK 11 posts Joined 01/08
04 Feb 2008

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!

Fred 1096 posts Joined 08/04
04 Feb 2008

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.

Elr0ndK 11 posts Joined 01/08
04 Feb 2008

Thanks, I've investigated today and I found the same answer.

You must sign in to leave a comment.