Help with DDL Trigger
I am trying to capture DDL events that happen on the source tables of materialized views. Below is the simplified trigger:
CREATE OR REPLACE TRIGGER DDL_TRG1 AFTER ALTER OR DROP ON DATABASE
BEGIN
DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_OWNER: ' || ORA_DICT_OBJ_OWNER ); /* displayed value is 'HR' */
DBMS_OUTPUT.PUT_LINE('ORA_DICT_OBJ_NAME: ' || ORA_DICT_OBJ_NAME ); /* displayed value is 'EMPLOYEE' */
FOR CUR1 IN (
SELECT NAME FROM DBA_DEPENDENCIES
WHERE OWNER = 'DATA_MART1'
AND TYPE = 'MATERIALIZED VIEW'
AND REFERENCED_OWNER = ORA_DICT_OBJ_OWNER
--AND REFERENCED_OWNER = 'HR'
AND REFERENCED_TYPE = 'TABLE'
AND REFERENCED_NAME = ORA_DICT_OBJ_NAME
--AND REFERENCED_NAME = 'EMPLOYEE'
)
LOOP
INSERT INTO
0