RE: Streams dml hander
- From: Mike Killough <mwkillough@xxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Apr 2011 16:46:01 -0500
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@xxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
