Home » Oracle DBA » Creating DDL Trigger in Oracle to exclude tables to be captured which are not supported by Oracle Streams

Creating DDL Trigger in Oracle to exclude tables to be captured which are not supported by Oracle Streams

Creating DDL Trigger in Oracle to exclude tables to be captured which are not supported by Oracle Streams. Create this trigger in Oracle Stream Admin user and modify the owner with your schema name.

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;
/

Oracle Streams examples