DBMS JOBS fails inside DDL trigger
854200Apr 27 2011 — edited Apr 27 2011
I am posting this again, since I need a solution, to rectify the trigger in production.
Dom Brooks , thanks for replying, but can you let me know how to modify the trigger? the DBMS_JOBS part.
I am working on a project to create roles for each schema in the DB and assign the tables in those schemas to the roles. So suppose a new table is added, then the roles will become redundant. So I have created the following trigger
I have a DDL Trigger through which I am granting SELECT on newly created tables to existing read-only roles
CREATE OR REPLACE TRIGGER DBADMIN.grant_permission_trigger
n_temp_int INTEGER DEFAULT 0 ;
SELECT ora_dict_obj_owner INTO v_owner FROM DUAL;
v_role := v_owner || '_S';
--selecting table name
SELECT ora_dict_obj_name INTO v_object FROM DUAL;
SELECT ORA_DICT_OBJ_TYPE INTO v_object_type FROM DUAL;
IF (v_object_type = 'TABLE')
SELECT COUNT (1)
WHERE role = v_role;
-- if a role does not exist already, then create a role
IF n_temp_int != 0
WHERE table_name = v_object;
IF (n_temp = 'N')
job => l_job,
what => 'BEGIN EXECUTE IMMEDIATE ''GRANT SELECT ON '
|| ' TO '
|| '''; END;'
Please someone let me know how to modify this.