SQL & PL/SQL

Hi,

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
AFTER CREATE
ON DATABASE
DECLARE
v_owner dba_tables.owner%TYPE;
v_role dba_roles.role%TYPE;
v_object dba_tables.table_name%TYPE;
v_object_type dba_objects.object_type%TYPE;
n_temp_int INTEGER DEFAULT 0 ;
l_job NUMBER;
n_temp dba_tables.temporary%TYPE;
BEGIN
--selecting owner
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')
THEN
SELECT COUNT (1)
INTO n_temp_int
FROM dba_roles
WHERE role = v_role;

-- if a role does not exist already, then create a role
IF n_temp_int != 0
THEN
SELECT temporary
INTO n_temp
FROM dba_tables
WHERE table_name = v_object;

IF (n_temp = 'N')
THEN
DBMS_JOB.submit (
job => l_job,
what => 'BEGIN EXECUTE IMMEDIATE ''GRANT SELECT ON '
|| v_owner
|| '.'
|| v_object
|| ' TO '
|| v_role
|| '''; END;'
);
END IF;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
/


Please someone let me know how to modify this.