This trigger fire whenever a table created in database and if table is containing unsupported data types then it will automatically added to the table capture rules to prevent errors.
Create Or Replace TRIGGER Ddltrigger
After Create On Database
DECLARE
N Number;
BEGIN
IF Ora_dict_obj_type = 'TABLE' And Ora_dict_obj_owner = 'SCOTT'
Then
BEGIN
Select 1
Into N
From DBA_STREAMS_UNSUPPORTED
Where Owner = 'SCOTT' And Table_name = Ora_dict_obj_name;
IF N = 1
Then
Dbms_streams_adm.Add_table_rules
(Table_name => 'SCOTT.'
|| ORA_DICT_OBJ_NAME,
Streams_type => 'capture',
Streams_name => 'STREAMS_CAPTURE',
Queue_name => 'STRMADMIN.STREAMS_CAPTURE_Q',
Include_dml => TRUE,
Include_ddl => FALSE,
Inclusion_rule => FALSE,
Source_database => 'STREAM1'
);
END IF;
EXCEPTION
When Others
Then
Null;
END;
END IF;
END;
/
