EXPERT RESPONSE
Hi,
The problem that you are having with your anonymous PL/SQL block is that
the revoke command is not valid in PL/SQL. Therefore, you will have to
execute it dynamically using the EXECUTE IMMEDIATE statement. The
statement below shows how to use the execute immediate statement:
execute immediate ('revoke '||rec.privilege||' on
'||rec.table_name||' from '||rec.grantee);
Also you will need an "End Loop." Your block should look like this:
begin
for rec in (select distinct privilege, grantee, table_name
from dba_tab_privs
where grantee = p_id)
loop
execute immediate ('revoke '||rec.privilege||' on
'||rec.table_name||' from '||rec.grantee);
end loop;
end;
Note: To view your PL/SQL errors you can use the following statement:
select * from user_errors;
|
