Archives of the TeradataForum

Message Posted: Mon, 12 Apr 2004 @ 18:10:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Monday, April 12, 2004 13:14 -->

I am working on V2R4.1.2 with a stored procedure that includes a FOR cursor. This is defined in the stored procedure as:

       FOR  AS  CURSOR FOR
     SELECT
     ----------- ;
     ----------- ;
     FROM TBLA
     WHERE
     ------------;
     ------------;
     DO
     BEGIN
     INSERT TBLB------------;
     INSERT TBLC------------;
     UPDATE TBLC------------;
     END;
     END FOR;

I have two INSERT and one UPDATE statements in the FOR loop of the cursor. The transaction semantics are set to BTET. This is not an updatable cursor. According to the manual, SQL Reference Vol 6. Chapter 7 :

If you do not specify an explicit updatability clause, then FOR READ ONLY is the default.

I also have BEGIN and END blocks with CONTINUE handlers for SQLEXCEPTION as well as specific SQLSTATE conditions.

1) The requirement is that for every iteration of the cursor, none of the UPDATE or INSERT statements should commit if any of them fails. The exception handler will insert the bad row in to an error table. If the first INSERT statement fails, the other INSERT and UPDATE should be skipped and the cursor should continue with the next iteration. Therefore, I am trying to enclose the INSERT and UPDATE statements for every iteration in a BT- ET block. However, I cannot place the BT and ET within the FOR cursor ("SPL1073:E, Transaction Control statements are not allowed inside Updatable Cursors.")

Why do I get this error message if the cursor is read-only?

2) To work around this, I have placed the BT and ET outside of the cursor.However, when I am trying to test the stored procedure to see how it handles exception conditions by simulating a situation of "invalid timestamp" or "duplicate row error", I get an error message that says - 7631 : Fetch/Close attempted on a closed cursor in the stored procedure. Within the transaction context, the cursor closes as soon as it hits an error condition. Is there an ET issued implicitly when the cursor encounters the error condition?

How do I get the INSERTs and UPDATE to commit for successful iterations only?

If TBLA has 10 rows, of which the 3rd has an invalid timestamp, how do I get the stored procedure to work for the 9 good rows and insert the 3rd row in to an error table?

Thanks!

	<Prev	Next>		<<First	<Prev	Next>	Last>>