Execute statement in the loop

[Former Member]

Is there any reason why such a block of code wouldn't work? I'm trying to grant permissions to a particular user on multiple tables. The results of it is "Cursor not open". Ideas?

BEGIN
 DECLARE tabl_name CHAR(128);
 DECLARE sql CHAR(128);
 DECLARE cur_employee CURSOR FOR select table_name from systab where table_name like '%_ST_VIEW' OR table_name like '%_CFG_VIEW';

 OPEN cur_employee;
 LOOP
 FETCH NEXT cur_employee into tabl_name ;
     SET sql = 'GRANT SELECT ON ' + tabl_name + ' TO ASW_Service_RO';
     EXECUTE immediate sql;
 END LOOP;
 CLOSE cur_employee;
END

[regdomaratzki · Accepted solution]

You need to use the "WITH HOLD" clause on the OPEN cursor command.

The GRANT command performs a COMMIT as a side effect, and your cursor is closed on commit without the "WITH HOLD" clause.

[Former Member · Accepted solution]

That is an excellent answer that solved my problem in a split second. Thank you!

[Former Member · Accepted solution]

It worked just fine only...never stopped. Does this cursor need to be closed in a special manner?

[regdomaratzki · Accepted solution]

Just realized you don't have an exit condition in your loop. Try something like :

OPEN cur_employee WITH HOLD;
FETCH FIRST cur_employee INTO tabl_name;
WHILE sqlcode = 0 LOOP
  // do something
  FETCH NEXT cur_employee INTO tabl_name;
END LOOP;
CLOSE cur_employee;

[VolkerBarth]

A even simpler method would be to use a FOR loop - which does open the cursor WITH HOLD as a side effect. Personally, I prefer this over the explicit statements to declare/open/fetch/close cursors (and declaring the fetch variables) since FOR does it all in a combined fashion. - Therefore it does require only half as much SQL code...

BEGIN
  DECLARE sql CHAR(128);
  FOR for_employee AS cur_employee CURSOR
    FOR select table_name as tabl_name from systab
      where table_name like '%_ST_VIEW' OR table_name like '%_CFG_VIEW'
  DO
     SET sql = 'GRANT SELECT ON ' + tabl_name + ' TO ASW_Service_RO';
     EXECUTE immediate sql;
 END FOR;
END;