Error PLS-00103 when using revoke inside a stored procedure

Question

It seems I'm missing something. I can't seem to use revoke inside a stored procedure.

CREATE PROCEDURE revokeRight 
AS 
BEGIN
    REVOKE ALL ON Barang FROM WorkerA;
END;

user_3989608 · Answer

You cannot directly use REVOKE statement inside PL/SQL block, you need to use EXECUTE IMMEDIATE:

CREATE or replace PROCEDURE revokeRight 
AS
BEGIN
    EXECUTE IMMEDIATE 'REVOKE ALL ON Barang FROM WorkerA';
END;
/

I would not recommend to use DCL commands inside PL/SQL to grant/revoke on the fly. You need to be cautious before doing so.