The script is one of my streams implementation and can save my time to not always keep catching on the dba_apply_error.
CREATE OR REPLACE PROCEDURE fix_err_dtl(in_any IN SYS.ANYDATA, arg_object_owner varchar2, arg_object_name varchar2) IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
i NUMBER;
l_dmlcommand varchar2(20);
l_dummy number;
lcrrow sys.lcr$_row_list;
pkdata sys.anydata;
pknumber number;
pkdate date;
pkchar10 char(10);
pkchar4 char(4);
pkchar1 char(1);
pkvarchar2 VARCHAR2(32000);
-- field1 through field3 is unique/pk index for table test_control
l_field1 number;
l_field2 number;
l_field3 varchar2(100);
BEGIN
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();
IF arg_object_owner='TEST' and arg_object_name='TEST_CONTROL' then
IF dmlcommand in ('INSERT','UPDATE','DELETE') THEN
if dmlcommand in ('UPDATE','DELETE') THEN
lcrrow := lcr.GET_VALUES('OLD');
elsif dmlcommand = 'INSERT' THEN
lcrrow := lcr.GET_VALUES('NEW');
end if;
for i in 1..lcrrow.count loop
if lcrrow(i).column_name = 'FIELD1' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getnumber(pknumber);
l_field1 := pknumber;
elsif lcrrow(i).column_name = 'FIELD2' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getnumber(pknumber);
l_field2 := pknumber;
elsif lcrrow(i).column_name = 'FIELD3' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getvarchar2(pkvarchar2);
l_field3 := pkvarchar2;
end if;
end loop;
begin
select count(*) into l_dummy
from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;
if l_dummy >= 1 then
if dmlcommand = 'INSERT' then
delete from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;
elsif dmlcommand in ('UPDATE','DELETE') and l_dummy >= 1 then
delete from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;
insert into TEST.TEST_CONTROL(field1,field2,field3)
values(l_field1,l_field2,l_field3);
end if;
elsif l_dummy = 0 then
if dmlcommand in ('UPDATE','DELETE') then
insert into TEST.TEST_CONTROL(field1,field2,field3)
values(l_field1,l_field2,l_field3);
end if;
end if;
end;
END IF;
elsIF arg_object_owner='TEST' and arg_object_name='TEST2' then
...........
...........
...........
...........
end if; -- if last
END;
/
the above procedure will be called by this procedure:
CREATE or replace PROCEDURE fix_err(arg_apply_name varchar2) IS cursor c1 is select LOCAL_TRANSACTION_ID, MESSAGE_NUMBER, MESSAGE_COUNT FROM DBA_APPLY_ERROR where apply_name=arg_apply_name order by SOURCE_COMMIT_SCN; i NUMBER; lcr ANYDATA; lcr2 sys.lcr$_row_record; lcrrow sys.lcr$_row_list; rc2 pls_integer; l_objname varchar2(50); l_object_owner varchar2(50); l_MESSAGE_NUMBER number; l_LOCAL_TRANSACTION_ID varchar2(50); l_msg varchar2(100); BEGIN for h in c1 loop for j in 1..h.MESSAGE_COUNT loop begin select MESSAGE_NUMBER,LOCAL_TRANSACTION_ID into l_MESSAGE_NUMBER, l_LOCAL_TRANSACTION_ID from dba_apply_error where LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID; if l_MESSAGE_NUMBER is null or l_MESSAGE_NUMBER=0 then begin dbms_apply_adm.execute_error(h.LOCAL_TRANSACTION_ID); exception when others then null; end; elsif j = l_MESSAGE_NUMBER then lcr := NULL; lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(j, h.LOCAL_TRANSACTION_ID); rc2 := lcr.getobject(lcr2); l_objname := lcr2.GET_OBJECT_NAME; l_object_owner := lcr2.GET_OBJECT_OWNER(); fix_err_dtl(lcr, l_object_owner, l_objname); begin i:=h.MESSAGE_COUNT; dbms_apply_adm.execute_error(h.LOCAL_TRANSACTION_ID); exception when others then select MESSAGE_NUMBER into i from dba_apply_error where LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID; if l_LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID and i<>l_MESSAGE_NUMBER then null; else l_msg := ' id: '||h.LOCAL_TRANSACTION_ID||' num: '||to_char(i); raise_application_error(-20001,'err: '||SQLCODE||' -ERROR- '||SQLERRM||l_msg); end if; end; end if; exception when no_data_found then null; end; end loop; commit; end loop; commit; END; /
and finally create scheduler by interval 10 secs for this fix error apply:
conn strmadmin/passwd
create procedure fix_strm_err is
l_status varchar2(10);
l_cnt number;
l_name varchar2(50) := 'BPK_APPLY';
begin
select count(1) into l_cnt from dba_apply_error where apply_name=l_name;
if l_cnt >= 1 then
fix_err(l_name);
end if;
select status into l_status
from dba_apply where apply_name=l_name;
if l_status<>'ENABLED' then
dbms_apply_adm.start_apply(l_name);
end if;
end;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'PRG_FIX_STRM'
,program_type => 'PLSQL_BLOCK'
,program_action => 'begin fix_strm_err; end;'
,number_of_arguments => 0
,enabled => FALSE
,comments => NULL
);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'PRG_FIX_STRM');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'JOB_FIX_STRM'
,start_date => TO_TIMESTAMP_TZ('2012/02/01 16:59:59.467189 +07:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=SECONDLY;INTERVAL=10'
,end_date => NULL
,program_name => 'PRG_FIX_STRM'
-- ,job_class => 'BATCH_JOB_CLASS'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'RESTARTABLE'
,value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
--SYS.DBMS_SCHEDULER.LOGGING_FULL
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'JOB_PRIORITY'
,value => 1);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'AUTO_DROP'
,value => FALSE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'JOB_FIX_STRM');
END;
/
happy streaming….:)
Advertisements
Leave a Reply