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

[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;