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

asked 30 Aug '12, 12:25

Maciej's gravatar image

Maciej
1715514
accept rate: 0%

edited 30 Aug '12, 14:03

Graeme%20Perrow's gravatar image

Graeme Perrow
8.7k374111


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.

permanent link

answered 30 Aug '12, 12:28

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.4k33578
accept rate: 40%

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;
permanent link

answered 30 Aug '12, 17:14

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 03 Sep '12, 08:55

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×23
×18
×10

question asked: 30 Aug '12, 12:25

question was seen: 1,301 times

last updated: 03 Sep '12, 08:55