Multiple DDL/Grant statements inside Procedure

Database
Enthusiast

Multiple DDL/Grant statements inside Procedure

Open Exec_cursor;

SET RECCOUNT = ACTIVITY_COUNT;


REPEAT

FETCH Exec_cursor into sql_text2 ;

CALL DBC.SYSEXECSQL(sql_text2);

--CALL dbc.sysexecsql('COMMIT;');  

If success_status='N' Then

UPDATE WORK_DB.Tablex set status='N'  WHERE CURRENT OF Exec_cursor;

End If;

SET success_status='Y';

SET RECCOUNT = RECCOUNT - 1;

UNTIL RECCOUNT <= 0

END REPEAT;

Close Exec_cursor ;

I have a snippet like above and the table contains multiple GRANT Statements..

1. After every execute statement (GRANT), it requires a COMMIT.

2. If I give commit, it closes the CURSOR and gives error FETCH wihout open cursor.

Any idea, Whats the workaround for this. I'm are using ANSI mode.

1 REPLY
Teradata Employee

Re: Multiple DDL/Grant statements inside Procedure

In ANSI mode, SP cursors are not "holdable". Is using Teradata mode with implicit auto-commit feasible?

Maybe as a workaround you could use a Queue Table and instead of fetching from cursor do SELECT AND CONSUME.