?
Solved

why does oracle statement dml handler not work?

Posted on 2011-03-20
7
Medium Priority
?
912 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:nirvanastorage
  • 4
  • 3
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35178448
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
 

Author Comment

by:nirvanastorage
ID: 35181501
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35181656
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:nirvanastorage
ID: 35181837
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35181873
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
 

Accepted Solution

by:
nirvanastorage earned 0 total points
ID: 35209060
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
 

Author Closing Comment

by:nirvanastorage
ID: 35239106
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question