Question Re: Execute statement in the loop in Technology Q&A

Execute statement in the loop

Former Member — Thu, 30 Aug 2012 12:25:12 GMT

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

Re: Execute statement in the loop

regdomaratzki — Thu, 30 Aug 2012 12:28:49 GMT

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.

Re: Execute statement in the loop

Former Member — Thu, 30 Aug 2012 12:47:17 GMT

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

Re: Execute statement in the loop

VolkerBarth — Thu, 30 Aug 2012 17:14:30 GMT

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;

Re: Execute statement in the loop

Former Member — Fri, 31 Aug 2012 04:15:34 GMT

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

Re: Execute statement in the loop

regdomaratzki — Fri, 31 Aug 2012 09:46:55 GMT

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;