Sponsored

An example is given below to stop the execution of a particular DDL command in Oracle Streams. In this example you will learn how to ignore Drop Table command at the target schema in Oracle Streams.

1. Connect to the target database with the streams admin credentials.

conn STRMADMIN/STREAM@TARGET

2. Create a procedure to handle the drop table statement.

create or replace procedure
 IGNORE_DROP_TABLE (in_any IN SYS.ANYDATA
 )

 is
 lcr SYS.LCR$_DDL_RECORD;
 rc PLS_INTEGER;

 begin
 rc := in_any.GETOBJECT(lcr);

 if lcr.GET_COMMAND_TYPE != 'DROP TABLE'
 then
lcr.execute();
 end if;
 END;
 /

3. Alter the Apply process.

 begin
 dbms_apply_adm.alter_apply(
 apply_name => 'STREAMS_APPLY',
 ddl_handler => 'IGNORE_DROP_TABLE');
 end;
 /

Now all DDL statements except "Drop Table" will execute in the target database.


Oracle Forms Recipes eBookDownload

How to prevent to drop a table at target schema in Oracle streams.

Post a Comment

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.