why does oracle statement dml handler not work?

I'm planning on logging all LCRs being applied at a target Oracle 11g R2 database. For this purpose I've created a logging table as follows:

CREATE TABLE repadmin.history_lcrs(
  timestamp             timestamp(4) with time zone,
  source_database_name  VARCHAR2(128),
  command_type          VARCHAR2(30),
  object_owner          VARCHAR2(32),
  object_name           VARCHAR2(32),
  tag                   RAW(10),
  transaction_id        VARCHAR2(15),
  scn                   NUMBER,
  commit_scn            NUMBER,
  lcr                   SYS.AnyData);

Open in new window


Then I'm creating a statement DLM handler as follows:

BEGIN
  DBMS_STREAMS_HANDLER_ADM.CREATE_STMT_HANDLER(
    handler_name => 'history_lcr',
    comment      => 'Tracks all applied LCRs');
END;
/
DECLARE
  stmt CLOB;
BEGIN
  stmt := ':lcr.execute TRUE';
  DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
    handler_name       => 'history_lcr',
    statement          => stmt,
    execution_sequence => 10);
END;
/
DECLARE
  stmt CLOB;
BEGIN
  stmt := 'INSERT INTO repadmin.history_lcrs(
             timestamp,
             source_database_name, 
             command_type,
             object_owner,
             object_name,
             tag,
             transaction_id,
             scn,
             commit_scn,
             lcr) 
           VALUES(
             SYSTIMESTAMP,
             :source_database_name,
             :command_type,
             :object_owner,
             :object_name,
             :tag,
             :transaction_id,
             :scn,
             :commit_scn,
             AnyData.ConvertObject(:lcr))';
  DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
    handler_name       => 'history_lcr',
    statement          => stmt,
    execution_sequence => 20);
END;
/

Open in new window


The following statement is then used to associate the statement DML handler with a table where I want to log LCR:
BEGIN
  DBMS_APPLY_ADM.ADD_STMT_HANDLER(
  object_name    => 'hr.employees',
  operation_name => 'INSERT',
  handler_name   => 'history_lcr');
  END;
  /

Open in new window


Unfortunately when inserting a row into this very table from the source database, the row is not logged into repadmin.history_lcrs. There are no entries in history_lcrs and there are also no errors in dba_apply_error. The row is, in fact, inserted correctly into the employee table in the target database - but no logging. What am I doing wrong?
nirvanastorageAsked:
Who is Participating?
 
nirvanastorageConnect With a Mentor Author Commented:
The problem was that the statement DML handler does not have access to the LCR object - only to one of its methods namely :lcr.execute. So the following handler works - although it doesn't log the entire LCR:

DECLARE
  stmt CLOB;
BEGIN
  stmt := 'INSERT INTO repadmin.history_lcrs(
             timestamp,
             source_database_name, 
             command_type,
             object_owner,
             object_name,
             tag,
             transaction_id,
             scn,
             commit_scn,
             lcr) 
           VALUES(
             SYSTIMESTAMP,
             :source_database_name,
             :command_type,
             :object_owner,
             :object_name,
             :tag,
             :transaction_id,
             :scn,
             :commit_scn,
             null)';
             --AnyData.ConvertObject(:lcr)
  DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
    handler_name       => 'history_lcr',
    statement          => stmt,
    execution_sequence => 20);
END;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I've done some with Streams but am not familiar with those specific packages.

When I can get on my dev database, I'll take a better look at them.

Why I'm posting now is: can you provide the link for the setup you are following?

Also in my Streams setup (for replication), I had to have a Capture process.  This might not be necessary with the packages you are using but you might want to look into it until I can get to my dev database.
0
 
nirvanastorageAuthor Commented:
the setup of streams is very complex. i'm using a number of scripts to set them up but the main documentation is at Streams Replication Administrator's Guide.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
Streams is very complex.

So to clarify: are you trying to set up streams replication or use the Streams procedures to create your own 'custon' replication?
0
 
nirvanastorageAuthor Commented:
streams replication is setup and works perfectly. The issue is in particular with the "statement DML handler" setup. The setup reports no errors but the "statement DML handler" just doesn't seem to do anything.
0
 
slightwv (䄆 Netminder) Commented:
If no other Expert assists with this, I should be able to take a look at this tomorrow when I'm at the office.
0
 
nirvanastorageAuthor Commented:
as this is a very complex setup no other experts seemed to be able to respond to this problem. So we ended-up contacting Oracle support for the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.