VARIABLE g_message varchar2(50); ACCEPT p_ID PROMPT 'Please enter the desired ID to revoke privileges:' BEGIN for rec in (Select distinct privilege, grantee, Table_name From dba_tab_privs Where grantee = p_ID;) loop revoke rec.privilege on rec.Table_name from rec.grantee; END; / print g_message;
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;
Dig deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.