The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.3k369106


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
4.8k33269
accept rate: 41%

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
29.3k287438644
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:

×22
×16
×10

question asked: 30 Aug '12, 12:25

question was seen: 1,184 times

last updated: 03 Sep '12, 08:55