Solved

why does oracle statement dml handler not work?

Posted on 2011-03-20
7
889 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 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now