Grant read access using for loop

1
votes

QUERY

BEGIN

FOR R IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'ONAME')

LOOP

    GRANT SELECT ON R.TABLE_NAME TO UNAME;

END LOOP;

END; /

I want to grant read access of tables in ONAME to UNAME. But, I've the following errors:

PLS-00103: Encountered the symbol "GRANT" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Please help me. Thanks in advance.

1
sqloracleplsql

1 Answers

1
votes

In Oracle, you can't do any DDL statements directly in PL/SQL. You have to use EXECUTE IMMEDIATE to execute them, e.g.:

FOR R IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'ONAME')
LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON '||R.TABLE_NAME||' TO UNAME';
END LOOP;