Solved

why does oracle statement dml handler not work?

Posted on 2011-03-20
7
905 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 77

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 77

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 77

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

690 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