Can anyone please tell me what's going on here:
CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA cols ora_name_list_t; BEGIN IF (ora_dict_obj_type = 'TABLE') THEN select column_name bulk collect into cols from user_tab_cols where table_name = ora_dict_obj_name; FOR i IN cols.first .. cols.last LOOP dbms_output.put_line(cols(i)); END LOOP; END IF; END;
The above trigger is expected to display the list of all the columns in a table after the ALTER TABLE command is executed.
The following works just fine when I ALTER a table with column(s) without any constraints:
Alter Table tab_xxx ADD(nucol char);
However, if I add a new column(s) with a constraint like
ALTER TALE tab_xxx ADD(n number NOT NULL);
then this new column(s) is not immediately reflected in the USER_TAB_COLS DD view and the FOR loop above to display the names of all the columns DOESN'T show this NEW column added.