Streams dml hander

From: Mike Killough <mwkillough_at_hotmail.com>
Date: Mon, 11 Apr 2011 15:31:20 -0500
Message-ID: <COL103-W13206F57D0F91697386C5FC2A80_at_phx.gbl>

Has anyone done a custom DML handler for Streams that will capture a failed update and change it to an insert? The version is 11gR2 and this is bi-directional between 3 databases. The problem that I'm having is when:

1. Database A Streams is stopped
2. A row is deleted from the other 2 databases
3. That row is updated on Database A with streams down
4. Streams is started on database A
5. The row gets inserted into databases B & C
6. But then the row gets deleted in database A

I get no apply errors, but now the databases are out of sync. Any suggestions appreciated. Below is what I have for the DML handler.

create or replace package strmadmin.pkg_dml_handler as
  TYPE emsg_array IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;   procedure proc_at_tsv_2011_dml
    (in_any IN ANYDATA,

     error_stack_depth IN NUMBER,
     error_numbers IN DBMS_UTILITY.NUMBER_ARRAY,
     error_messages IN EMSG_ARRAY);

END pkg_dml_handler;
/

create or replace package body strmadmin.pkg_dml_handler as
PROCEDURE proc_at_tsv_2011_dml
  ( in_any IN ANYDATA,

    error_stack_depth IN NUMBER, 
    error_numbers IN DBMS_UTILITY.NUMBER_ARRAY,
    error_messages IN EMSG_ARRAY)

is
  lcr sys.lcr$_row_record;
  rc pls_integer;
  command VARCHAR2(10);
begin
  rc := in_any.GETOBJECT(lcr);
  command := lcr.GET_COMMAND_TYPE();

  if error_numbers(1) = 1403
  then
    if command = 'UPDATE'
    then

      lcr.set_command_type('INSERT');
      lcr.set_values('NEW',lcr.get_values('NEW'));
      lcr.set_values('OLD',NULL);
      -- lcr.set_command_type('DELETE');
      -- lcr.set_values('old',lcr.GET_VALUES('NEW'));
      -- lcr.set_values('new', NULL);
      lcr.execute(true);

    elsif command = 'DELETE'
    then
      null;
    end if;

        INSERT INTO strmadmin.history_row_lcrs VALUES 
         (SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(),   
          lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_TAG(), 
          lcr.GET_TRANSACTION_ID(), lcr.GET_SCN(), lcr.GET_COMMIT_SCN,
          lcr.GET_VALUES('old'), lcr.GET_VALUES('new', 'n'));

  elsif error_numbers(1) = 1
  then

      if command = 'INSERT'
      then
         lcr.set_command_type('DELETE');
         lcr.set_values('old',lcr.GET_VALUES('NEW'));
         lcr.set_values('new', NULL);
         lcr.execute(true); -- apply the LCR
      end if;
        INSERT INTO strmadmin.history_row_lcrs VALUES 
         (SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(),   
          lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_TAG(), 
          lcr.GET_TRANSACTION_ID(), lcr.GET_SCN(), lcr.GET_COMMIT_SCN,
          lcr.GET_VALUES('old'), lcr.GET_VALUES('new', 'n'));

  else

• null; RAISE_APPLICATION_ERROR(-20000,command || ' failed: ' ||sqlerrm); INSERT INTO strmadmin.history_row_lcrs VALUES (SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(), lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_TAG(), lcr.GET_TRANSACTION_ID(), lcr.GET_SCN(), lcr.GET_COMMIT_SCN, lcr.GET_VALUES('old'), lcr.GET_VALUES('new', 'n')); end if; end; end; /
  --
  show errors
  --
  

Mike                                                

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 11 2011 - 15:31:20 CDT