RE: Streams dml hander

From: Mike Killough <mwkillough_at_hotmail.com>
Date: Mon, 11 Apr 2011 16:46:01 -0500
Message-ID: <COL103-W29A865D1A37270F5429F6DC2A80_at_phx.gbl>

The secondary issue I'm having is when I convert to the insert from the update. If my update doesn't update all of the columns, then I don't have all of the old values for the insert. So far to get around this I change the update to all of the columns. What's a better way to handle this?

Change from

update cwms_20.AT_TSV_2011

   set value = 22
 where DATE_TIME = to_date('10-APR-2011 18:03:00','DD-MON-YYYY HH24:MI:SS');

to

update cwms_20.AT_TSV_2011 
   set value = 22, ts_code = ts_code, version_date = version_date, data_entry_date = data_entry_date, quality_code = quality_code
 where DATE_TIME = to_date('10-APR-2011 18:03:00','DD-MON-YYYY HH24:MI:SS');

Mike

From: mwkillough_at_hotmail.com
To: oracle-l_at_freelists.org
Subject: Streams dml hander
Date: Mon, 11 Apr 2011 15:31:20 -0500

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 - 16:46:01 CDT