PL/SQL code to grant a new user select privs on views owned by another user

Question

Need help with this...New to PL/SQL. Where am I going wrong?

 DECLARE

 CREATE or REPLACE PROCEDURE grant_view_privs

      IS sys_refcursor;
       strVIEWS Varchar2(1000);
       strQuery varchar2(4000);
  BEGIN

  open result for

    select object_name from user_objects where object_type='VIEW'
      and status !='INVALID';
   loop
     fetch result into strVIEWS;

  IF SQL%NOTFOUND then
    DBMS_OUTPUT.PUT_LINE ('TABLE DOES NOT EXIST');

    ELSIF SQL%FOUND then

    DBMS_OUTPUT.PUT('Granting select on '||strVIEWS||' to BIOTICS_REPORT');

    strQuery := 'grant SELECT on '||strVIEWS||' to BIOTICS_REPORT';

    execute immediate strQuery;

    DBMS_OUTPUT.PUT_LINE('SUCCES');
    END IF;
end loop;

close result;

end;
/

Alex Poole · Answer 1 · 2013-10-23T17:13:23.717

You seem to be mixing a few things up here.

If you're creating a stored procedure you don't start that with a declare keyword, that's for anonymous PL/SQL blocks.
You're missing a variable name between IS and sys_refcursor; presumably that should be result.
SQL%NOTFOUND will be true when there are no more rows in the result set; it does not indicate that there was no data at all, and certainly wouldn't indicate that the table (user_objects, which is actually a view) doesn't exist.
Your loop will go on forever at the moment because you aren't detecting when you reach the end of the result set. You'll get sensible output from the ELSE part for all the views that do exist, but then it'll get SQL%NOTFOUND for each subsequent iteration; without an exit from the loop it will just try to fetch again.

If you're using SQL*Plus or SQL Developer you can use the 'show errors' command to see why a stored block of code wouldn't compile, or you can query the user_errors view which will work in other clients too. But in this case that wouldn't have done much until you got past the PLS-00103: Encountered the symbol "CREATE"... error from the declare being there. (It always heps if you actually state in the question what error you are getting).

I think this is the equivalent of what you seem to be aiming for:

create or replace procedure grant_view_privs is
  result sys_refcursor;
  strView user_objects.object_name%TYPE;
  strQuery varchar2(4000);
begin
  open result for
    select object_name
    from user_objects
    where object_type='VIEW'
    and status !='INVALID';
  loop
    fetch result into strView;
    exit when SQL%NOTFOUND;
    strQuery := 'grant SELECT on '||strView||' to BIOTICS_REPORT';
    dbms_output.put_line(strQuery);
    execute immediate strQuery;
  end loop;
  close result;
end grant_view_privs;
/

You can simplify that a bit with a different form of the cursor syntax:

create or replace procedure grant_view_privs is
  strQuery varchar2(4000);
begin
  for curViews in (
    select object_name
    from user_objects
    where object_type='VIEW'
    and status !='INVALID'
  )
  loop
    strQuery := 'grant SELECT on '||curViews.object_name||' to BIOTICS_REPORT';
    dbms_output.put_line(strQuery);
    execute immediate strQuery;
  end loop;
end grant_view_privs;
/

You don't even have to have strQuery defined if you generate the whole dynamic statement in the select:

create or replace procedure grant_view_privs is
begin
  for curViews in (
    select 'grant SELECT on '||object_name||' to BIOTICS_REPORT' as command
    from user_objects
    where object_type='VIEW'
    and status !='INVALID'
  )
  loop
    dbms_output.put_line(curViews.command);
    execute immediate curViews.command;
  end loop;
end grant_view_privs;
/

PL/SQL code to grant a new user select privs on views owned by another user

1 Answers1