Bookmark and Share

Sunday, March 29, 2009

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

1 comment:

  1. That's very likely the reason why I have to use rpad in the ddl apply handler is due to a bug similar to bug 8338362 (Not published yet). I've asked Oracle to publish the bug so that we can follow up with the fix.

    ReplyDelete