In my proc I have a cursor, which in turn has a while loop inside it. There is an IF statement that can cause me to leave the while loop and also the cursor. I have given the while loop a Label Loop0.
IF v_lintErrorVar <> 0 THEN ROLLBACK WORK; LEAVE LOOP0; END IF;
FETCH NEXT FROM weeks_to_arc_cursor INTO v_intWeekid_id; END WHILE; -- LOOP0 CLOSE weeks_to_arc_cursor;
So by my thinking it shoould go to the end while and then close the cursor? But I receive an error
7631 Fetch/Close attempted on a closed cursor in the stored procedure (%FSTR). Explanation: The stored procedure in execution is trying to fetch a row from a closed cursor.
This used to work fine but since I have included BEGIN and END TRANSACTION statements in my proc and I keep receiving this error now. The proc also contains dynamic sql but I have enclosed it in bt and et statements e.g.
Still getting the same error - 7631. I commented out all the ROLLBACK statements to make sure this was the cause but I get the same message. The WHILE Loop has a condition of
OPEN weeks_to_arc_cursor; FETCH weeks_to_arc_cursor into v_intWeekid_id;
LOOP0: WHILE (SQLCODE <> 7632) DO
FETCH NEXT FROM weeks_to_arc_cursor INTO v_intWeekid_id; END WHILE LOOP0; CLOSE weeks_to_arc_cursor;
The error is saying Im trying to fetch from a closed cursor but I cant work out what is closing the cursor. I have a while loop (LOOP1) within the bigger loop and at one point I issue a LEAVE LOOP1; statement, would this cause the cursor to close? Or maybe I have reached the last record and for some reason it tries to grab another??
1) Either a rename or a create table is run after select * from dbc.tables to see whether the table already exists. 2) An INSERT..SELECT * into the new table from the old 3) DELETE records from old table that were inserted into the new table
Its an archiving process. e.g. SET v_lnvchCommand = 'CREATE MULTISET TABLE ' || v_arc_db || '.' || TRIM(TRAILING FROM CAST(v_vchTable_To_Archive AS CHAR(23))) || '_' || TRIM(LEADING FROM CAST(v_intWeekid_id AS CHAR(6))) || ' AS (SELECT * FROM '|| v_vchTable_To_Archive ||') WITH NO DATA PRIMARY INDEX('||v_primary_index||');' ;
You can't have Dynamic SQLs in macro, and you can't have DDL's unless it's last statement of transaction.
It's ok to have DDL's in a SP, but you can't have it the way you are looking (ie tablename etc is unknown till runtime).
I think you will have to forget about using transaction semantics in here, and just stick with the implicit transaction mode (where every statement is a transaction in itself).
Now depending on how your SP is called, the implementation would vary.
if it's called from something like bteq, in TD implicit transaction mode (ie your call to SP is not embedded BT/ET) , Or if it's called by a third party software in auto-commit enabled mode then just build the SP with no BT/ET and it will work fine.
if it's called from an explicit transaction mode , it's tricky ... put an ET; before your open cursor and and BT; before you exit the SP.
I personally don't recommend the second approach, unless you are totally lost, this is because an ET in the SP would also commit any changes that was done in the application/bteq and it might sabotage the business logic of why a transaction was needed for doing all that operation. So that decision would be based on why you need a transaction at the first place, and what are the consequences of not having one.