CDC plus declarative transformation to delete XML column?

From: Adric Norris <spikey.mcmarbles_at_gmail.com>
Date: Thu, 2 Apr 2009 13:15:15 -0600
Message-ID: <d4beff360904021215k2e829b09sfed76ea90db2c7be_at_mail.gmail.com>

Has anyone used *dbms_streams_adm.delete_column* with Change Data Capture? I need to capture changes for a table containing an XMLType column, which isn't supported and kills the capture process, even though we've excluded that column from the change table. The specific error messages are:

---

Streams CAPTURE CP01 for CDC$C_CDC_TEST_SRC with pid=39, OS id=4076 stopped Errors in file
/oracle/admin/diag/rdbms/cdcsrc/CDCSRC/trace/CDCSRC_cp01_4076.trc:
ORA-26744: STREAMS capture process "CDC$C_CDC_TEST_SRC" does not support "CDC"."TEST" because of the following reason: ORA-26783: Column data type not supported

---

We're trying to avoid the need to pre-capture changes into a shadow table, and Metalink note 748941.1 strongly implies that we can avoid the error via proper use of *dbms_streams_adm.delete_column*. So far my attempts have all been variants of the following, run by the CDC Source Publisher.

begin

   dbms_streams_adm.delete_column(

       rule_name => 'TEST54',
       table_name => 'CDC.TEST',
       column_name => 'COLXML');

end;
/

The rule name was identified via *dbms_streams_rules*, by simply looking for a DML-capture rule referencing this table.

We have no previous experience with streams, and no exposure to CDC prior to the current project (which began only recently), so it's *very* possible that I'm simply not implementing the transformation rule correctly.

We're using CDC in Asynchronous Distributed Hotlog mode, and both databases are currently at 11.1.0.6 and running on Solaris 10 servers.

Any suggestions and/or pointers?

-- 
"I'm too sexy for my code." - Awk Sed Fred.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 02 2009 - 14:15:15 CDT