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....
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.