How to Use A DDL Apply Handler to Manage "RENAME" Commands

In my last post, This Is Why Streams Cannot Apply "RENAME"!, I've explained why the Streams Apply processes don't manage the RENAME operations. Like I said, the easiest way to deal with this is to use the ALTER ... RENAME TO ... command instead. Unfortunately, it's not always easy to change the code of an application. That's especially the case when it's a packaged application or if the application is maintain by a third-party.

In this post, I'll explain how to use a DDL Apply Handler to workaround that limit. What the handler does is simply change the RENAME into an ALTER TABLE... RENAME TO .... and set the instantiation SCN of the renamed table. If you want to test this example, you should implement a schema or global replication. There is a complete example in my previous post entitled Renaming The Schema Of a DDL Statement in a LCR and it requires only one database. What follows is based on that example but you can easily adapt it to your needs...

This post is made of the following sections:

• Setup a Schema Replication
  
• Create the DDL Apply Handler
  
• Add the DDL Handler to the Apply
  
• Execute a test
  
• Clean the environment

Note:
Before you start, there is a few things that are worth noting:

• In order to parse the DDL text, I use a package of my own named arkzoyd_sqlcode. You can download and install it in your Streams administrator's schema, e.g. STRMADMIN. I won't publish the source of it, at least for now, but if you are interested, leave a comment with your email; I won't publish the comment but I'll contact you.
• It's more experimental than production ready; All comments are welcome but I don't guaranty you'll make it work. So far, I've successfully set it up on a 11.1.0.7 Enterprise Edition database running on Linux x86 32bits and 64bits.
• I would have expected changing the DDL text in a custom rule-based transformation would have worked. But for some reasons, despite its change as well as the change of the operation type, the apply process doesn't instantiate the table after its renaming, like it does with a ALTER TABLE... RENAME TO .... That's why I've used a DDL Handler instead of a custom rule-based transformation.
• If the new DDL text, modified with LCR$_DDL_RECORD.SET_DDL_TEXT, is too small, junk characters are appended to it. That's look like a bug or at least that's something else I don't understand. To workaround that issue I pad the string with spaces at its end.

Setup a Schema Replication

If you don't want to use 2 separate databases, you can follow my previous blog post where I explain how to setup a schema-level replication with one only database: Renaming The Schema Of a DDL Statement in a LCR.

Create the DDL Apply Handler

The DDL Apply Handler is a procedure that takes the DDL LCR as an input. You'll find below a template of a handler that manages the RENAME. What it does is (1) get DDL text, (2) if the command type is NULL and it's related to a table, check if that's a rename and build the corresponding ALTER TABLE ... RENAME TO ..., (3) if that's a rename, change the command type to ALTER TABLE and set the instantiations SCN for the new table, (4) execute the DDL LCR; below is the script that creates it:

connect strmadmin/strmadmin

@arkzoyd_sqlcode.sql

create or replace procedure ddl_apply_handler(in_any anydata) 
is
  lcr       sys.lcr$_ddl_record;
  rc        pls_integer;
  ddl_text  clob;
  seq       number;
  tlist     arkzoyd_sqlcode.ddl_rename_output_t; 
begin
  -- Access the LCR and create a clob for the ddl text
  rc := in_any.getobject(lcr);
  dbms_lob.createtemporary(ddl_text, true);

  -- Get the DDL text
  lcr.get_ddl_text(ddl_text);

  -- Transform the DDL
  if (lcr.get_command_type is null)
      and (lcr.get_object_type='TABLE') then
  begin
     tlist:=arkzoyd_sqlcode.get_renamed_tables(ddl_text);
  exception when others then
     -- That's not a rename command
     tlist.old_table_name:=null;       
  end;       
  if (tlist.old_table_name is not null) then
      lcr.set_ddl_text(rpad(to_clob('alter table '||
                              tlist.old_table_name||
                              ' rename to '       ||
                              tlist.new_table_name)
                           ,200));
      lcr.set_command_type('ALTER TABLE');

      dbms_apply_adm.set_table_instantiation_scn(
                  lcr.get_base_table_owner()||'.'||
                                 tlist.new_table_name ,
                  lcr.get_source_database_name()      ,
                  lcr.get_scn());
       end if;   
    end if;

    lcr.execute();
    -- Free temporary LOB space
    dbms_lob.freetemporary(ddl_text);
end;
/

Add the DDL Handler to the Apply

Once the procedure created, add it to the apply:

begin
  dbms_apply_adm.stop_apply('streams_apply');

  dbms_apply_adm.alter_apply(
               apply_name  => 'streams_apply',
               ddl_handler => 'strmadmin.ddl_apply_handler');

  dbms_apply_adm.start_apply('streams_apply');

end;
/

col apply_name format a13
col ddl_handler format a40
select apply_name,ddl_handler
 from dba_apply;

APPLY_NAME    DDL_HANDLER
------------- ----------------------------------------
STREAMS_APPLY "STRMADMIN"."DDL_APPLY_HANDLER"

Execute a test

Once the DDL Handler configure, we can run a test check if it's working:

connect source/source

create table test (id number);

insert into test values (1);

commit;

rename test to newtest;

insert into newtest values (2);

commit;

connect destination/destination

select * from destination.newtest;

ID
--
1
2

connect source/source

drop table newtest purge;

connect destination/destination

select * from destination.newtest
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

Clean the environment

To leave the environment like it was before you've created it; You can run the script below and refer to the last section of Renaming The Schema Of a DDL Statement in a LCR to remove the schema level replication:

begin
  dbms_apply_adm.stop_apply('streams_apply');

  dbms_apply_adm.alter_apply(
               apply_name  => 'streams_apply',
               remove_ddl_handler => true);

  dbms_apply_adm.start_apply('streams_apply');

end;
/

col apply_name format a13
col ddl_handler format a40

select apply_name,ddl_handler
 from dba_apply;

APPLY_NAME    DDL_HANDLER
------------- ----------------------------------------
STREAMS_APPLY

drop package body arkzoyd_sqlcode;
drop package arkzoyd_sqlcode;
drop procedure ddl_apply_handler;