?
Solved

why does oracle statement dml handler not work?

Posted on 2011-03-20
7
Medium Priority
?
906 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

764 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