Problems with a cursor...

Database
Enthusiast

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
6 REPLIES
Teradata Employee

Re: Problems with a cursor...

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.
Enthusiast

Re: Problems with a cursor...

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

Re: Problems with a cursor...

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...
Enthusiast

Re: Problems with a cursor...

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!
Teradata Employee

Re: Problems with a cursor...

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.
Enthusiast

Re: Problems with a cursor...

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