I need to create a trigger inside a pl/sql block. When I do it inside an anonymous block, it works perfect, the trigger is created and all. If I name the procedure and try to compile it, it says "Insufficient Privileges" at times or "Table or View does not exist". Has anyone faced this kind of problem ?
The query is as below, comment the create portion for the trigger to be created and comment the declare portion to see the error.
create or replace procedure create_tr as
hand integer := dbms_sql.open_cursor;
v_string := 'create or replace trigger wsbenadmin.test_tr '||
' before insert on wsbenadmin.tbl_master for each row '||
'begin Null; end;';
feed := dbms_sql.execute(hand);
Does the owner of the procedure has CREATE TRIGGER privilege granted to her/him directly and not through a role.
Roles are not visible in stored procedures.
The user is a DBA user and the trigger works fine from an anonymos block. It fails only when I give a name to the procedure.