Error when trying to leave a while loop

Database
Enthusiast

Error when trying to leave a while loop

Hi,

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.

ET;
BT;
CALL DBC.SysExecSQL(v_lnvchCommand);
ET;
BT;

Any help with this would be greatly appreciated, or if anyone needs more information or needs me to explain things a bit clearer just ask.

Thanks.
9 REPLIES
Enthusiast

Re: Error when trying to leave a while loop

When a rollback happens, any open cursors gets automatically closed. In your case

CLOSE weeks_to_arc_cursor;

fails after the transaction abort, because it's already closed as part of the rollback.
Enthusiast

Re: Error when trying to leave a while loop

Thanks for replying, thats explains a lot!!!

Enthusiast

Re: Error when trying to leave a while loop

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??

Any suggestions are greatly appreciated.
Enthusiast

Re: Error when trying to leave a while loop

sorry for continually changing the problem but it appears now the problem has to do with some dynamic sql that is used in the proc.

I have a BEGIN TRANSACTION; and END TRANSACTION; in my proc and so due to TD restrictions on dynamic sql I have to enclose it like so

ET;
BT;
CALL DBC.SysExecSQL(v_lnvchCommand);
ET;
BT;

When I comment out the transaction statements the proc runs fine. Which leads me to think that tmaybe an ET; statement has caused the cursor to close?

Is there a better alternative on how to execute my dynamic sql?
Any suggestions are greatly appreciated.
Enthusiast

Re: Error when trying to leave a while loop

End Transaction has to automatically release any locks acquired by the transaction, so ET would result in closing any cursors.

What is getting executed as part of the dynamic SQL ?
Enthusiast

Re: Error when trying to leave a while loop

There are a few dynamic statements in the cursor.

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||');' ;

Enthusiast

Re: Error when trying to leave a while loop

Would I be able to execute the dynamic sql in a macro

REPLACE MACRO create_rcm_snap_arc (v_lnvchCommand VARCHAR(4000))
AS
(
CALL DBC.SysExecSQL(v_lnvchCommand);
);

Thus negating the need for ET;, BT; and allowing the cursor to stay open and function properly?
Although I have noticed when trying to build the MACRO that it cant find the procedure DBC.SysExecSQL().

Also am I right in thinking I cant hardcode the DDL as DDL statements arent allowed in stored procedures?

Thank You for the help.
Enthusiast

Re: Error when trying to leave a while loop

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

Re: Error when trying to leave a while loop

Thanks very much for all the help!

Ive decided to go with the implicit transaction approach and things seem fine now.
Cheers.