|
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 |
|
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. That is an excellent answer that solved my problem in a split second. Thank you!
(30 Aug '12, 12:47)
Maciej
It worked just fine only...never stopped. Does this cursor need to be closed in a special manner?
(31 Aug '12, 04:15)
Maciej
1
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;
(31 Aug '12, 09:46)
Reg Domaratzki
Comment Text Removed
Comment Text Removed
Comment Text Removed
|
|
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; |
