SQL & PL/SQL

Hi everybody,

I'm trying to get the column name of any column added to the table "TEST", So I create a trigger to ensure that job.

My problem is that the view "all_tab_columns" doesn't contain the column "column_test " yet when the trigger audit_ddl_trg is executed.

Please Help me to resolve this problem

CREATE TABLE TEST(id varchar2(10));

-- add a column to the Table "TEST"
alter table TEST add column_test varchar2(10);

-- column_test not detected !!?
CREATE OR REPLACE TRIGGER audit_ddl_trg AFTER ALTER ON SCHEMA
DECLARE
col_name varchar2(30);
BEGIN
IF (ORA_DICT_OBJ_TYPE = 'TABLE' AND ora_dict_obj_owner = 'OP' AND ORA_DICT_OBJ_NAME = 'TEST') THEN
FOR col_name IN (SELECT column_name FROM all_tab_columns WHERE owner = 'OP') LOOP
DBMS_OUTPUT.PUT_LINE('Column : ' || col_name);
END LOOP;
END IF;

END;


Thanks In Advance ;)

Edited by: 909366 on Jan 26, 2012 1:22 AM

Edited by: 909366 on Jan 26, 2012 1:57 AM