Un processus d’apply Streams ne peut contenir qu’un seul handler par table/type de commande. « DML apply » ou « Error », il faut donc choisir !
Seulement voilà, il se peut que vous vouliez à la fois gérer un DML et un Error Handler simultanément. Comment faire alors ? C’est la question qu’explore l’article ci-dessous ; vous y trouverez comment utiliser les 2 types de handlers et comment les combiner ; vous verrez également comment réaliser les tests associés :
Configuration de test
Pour les besoins de cet article, il faut mettre en oeuvre une configuration Streams ; pour aller vite, réutilisez l’exemple décrit dans un des articles précédent intitulé Oracle Streams One Way Table Replication 101. Exécutez simplement les étapes 1 à 9 en vous assurant que votre base de données et en mode archivelog au préalable.
Exemple de DML apply Handler
Pour commencer, mettez en place un DML apply Handler. Vous trouverez ci-dessous un script pour créer une table est une séquence qui seront utilisées dans le handler pour historiser les opérations INSERT ayant lieu dans la table DESTINATION.T1. Donnez les droits à STRMADMIN pour interagir avec ces tables :
connect / as sysdba
create table destination.c_history
(id number primary key,
log varchar2(4000),
datetime timestamp);
create sequence destination.c_history_seq;
grant select, insert on destination.c_history to strmadmin;
grant select on destination.c_history_seq to strmadmin;
Une fois les objets créés, créez votre handler et ajoutez-le à la configuration Streams à l’aide de la procédure dbms_apply_adm.set_dml_handler :
connect strmadmin/strmadmin
create or replace procedure dml_apply_handler(in_any anydata)
is
lcr sys.lcr$_row_record;
rc pls_integer;
command varchar2(10);
id number;
begin
-- Access the LCR and get the LCR command Type
rc := in_any.getobject(lcr);
command := lcr.get_command_type();
-- In the case of an insert, log the info in c_history
if command = 'INSERT' then
-- Get the DDL text
if lcr.get_value('new','ID').getTypeName = 'SYS.NUMBER' then
rc:=lcr.get_value('new','ID').getNumber(id);
else
id:=0;
end if;
insert into destination.c_history values
(destination.c_history_seq.nextval,
'Insert into table ' ||
lcr.get_object_name() ||
' ; ID='||to_char(id),
systimestamp);
end if;
lcr.execute(true);
end;
/
begin
dbms_apply_adm.set_dml_handler(
object_name => 'destination.t1',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => false,
user_procedure => 'dml_apply_handler',
apply_database_link => null,
apply_name => 'STREAMS_APPLY',
assemble_lobs => false);
end;
/
col name format a15
col operation_name format a6
col user_procedure format a32
col handler_type format a18
select OBJECT_OWNER||'.'||OBJECT_NAME name,
OPERATION_NAME,
USER_PROCEDURE,
handler_type
from DBA_APPLY_DML_HANDLERS;
NAME OPERAT USER_PROCEDURE HANDLER_TYPE
--------------- ------ -------------------------------- ------------------
DESTINATION.T1 INSERT "STRMADMIN"."DML_APPLY_HANDLER" PROCEDURE HANDLER
Une fois la configuration réalisée, vous pouvez tester le handler
insert into source.t1 values (4, 'Text 4');
commit;
col text format a6
col id format 999
select id,
text
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
col id format 999
col log format a40
col datetime format a30
select id,
log,
datetime
from destination.c_history;
ID LOG DATETIME
---- ---------------------------------------- ------------------------------
1 Insert into table T1 ; ID=4 29-APR-11 03.27.06.567153 PM
Créer un DML error Handler
Supposons, pour continuer notre exemple, que l’on veuille désormais créer un error handler pour gérer le cas où une valeur de la clé primaire existe déjà et est égale à 4. Pour comprendre la situation, on dira, par exemple que vous supprimez une donnée dans la table source sans qu’elle soit modifiée dans la table destination; on peut, puisque la configuration n’a pas été mise en oeuvre en ignorant certain tags, utiliser le script ci-dessous pour arriver à un tel décalage, même si ce n’est pas la solution la plus simple :
exec dbms_capture_adm.stop_capture('STREAMS_CAPTURE');
delete from source.t1 where id=4;
commit;
col apply_scn format 999999999999 new_value instantiation_scn
select dbms_flashback.get_system_change_number apply_scn
from dual;
APPLY_SCN
---------
1031856
begin
dbms_apply_adm.set_table_instantiation_scn(
source_object_name => 'source.t1',
source_database_name => 'BLACK',
instantiation_scn => &&instantiation_scn);
end;
/
col SOURCE_DATABASE format a6
col OBJECT format a10
col INSTANTIATION_SCN format 999999999999
select source_database,
source_object_owner||'.'||source_object_name object,
instantiation_scn
from dba_apply_instantiated_objects;
SOURCE OBJECT INSTANTIATION_SCN
------ ---------- -----------------
BLACK SOURCE.T1 1031856
exec dbms_capture_adm.start_capture('STREAMS_CAPTURE');
col text format a6
col id format 999
select *
from source.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
col text format a6
col id format 999
select *
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
Une fois que les 2 tables ne sont plus en phase, créez le un package qui contiendra votre error handler. Notez qu’il faut un type spécifique pour gérer la pile des erreurs dans ce cas, d’où l’utilisation préférée du package plutôt qu’une simple procédure comme dans le cas précédent :
connect strmadmin/strmadmin
create or replace package mypkg is
TYPE emsg_array IS TABLE OF VARCHAR2(76) index by binary_integer;
procedure dml_error_handler(
in_any anydata,
error_stack_depth number,
error_numbers dbms_utility.number_array,
error_messages emsg_array);
end;
/
create or replace package body mypkg is
procedure dml_error_handler(
in_any anydata,
error_stack_depth number,
error_numbers dbms_utility.number_array,
error_messages emsg_array)
is
lcr sys.lcr$_row_record;
rc pls_integer;
command varchar2(10);
id number;
begin
-- Access the LCR and get the LCR command Type
rc := in_any.getobject(lcr);
command := lcr.get_command_type();
-- In the case of an insert failing for value 4, ignore it
if command = 'INSERT' then
-- Get the DDL text
if lcr.get_value('new','ID').getTypeName = 'SYS.NUMBER' then
rc:=lcr.get_value('new','ID').getNumber(id);
else
id:=0;
end if;
if id=4 then
insert into destination.c_history values
(destination.c_history_seq.nextval,
'Error in table ' ||
lcr.get_object_name() ||
' ; ID='||to_char(id),
systimestamp);
else
lcr.execute(true);
end if;
end if;
end;
end;
/
begin
dbms_apply_adm.set_dml_handler(
object_name => 'destination.t1',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => true,
user_procedure => 'mypkg.dml_error_handler',
apply_database_link => null,
apply_name => 'STREAMS_APPLY',
assemble_lobs => false);
end;
/
set lines 120
col name format a15
col operation_name format a6
col user_procedure format a40
col handler_type format a18
select OBJECT_OWNER||'.'||OBJECT_NAME name,
OPERATION_NAME,
USER_PROCEDURE,
handler_type
from DBA_APPLY_DML_HANDLERS;
NAME OPERAT USER_PROCEDURE HANDLER_TYPE
--------------- ------ ---------------------------------------- ------------------
DESTINATION.T1 INSERT "STRMADMIN"."MYPKG"."DML_ERROR_HANDLER" ERROR HANDLER
Vous pouvez ensuite tester que ce error handler fonctionne comme attendu :
insert into source.t1 values (4,'Text 4');
commit;
col id format 999
col log format a35
col datetime format a30
select id,
log,
datetime
from destination.c_history;
ID LOG DATETIME
---- ----------------------------------- ------------------------------
1 Insert into table T1 ; ID=4 29-APR-11 03.27.06.567153 PM
2 Error in table T1 ; ID=4 29-APR-11 05.35.07.353688 PM
Voilà, ça marche donc très bien sauf que, comme vous l’avez remarqué, le dml error handler remplace le dml apply handler. Alors comment utiliser les 2 simultanément ?
Intégrer l’error handler dans l’apply handler
L’idée pour intégrer dml apply handler et dml error handler consiste assez logiquement à exécuter le code correspondant au premier et en cas d’erreur, d’enchainer avec le second. Il y a toutefois quelques astuces pour que cela fonctionne correctement :
- d’abord, il faut construire les structures correspondant aux erreurs retournées par le dml handler en cas d’erreur afin de les passer à l’error handler ; pour cela, on s’aide des fonctions
dbms_utility.format_error_stack,dbms_utility.format_error_backtraceet des expressions régulières pour remettre en tableau le résultat de la seconde fonction - Ensuite, on ne peut visiblement pas empiler l’utilisation du paramètre
anydataaprès avoir utilisé la fonctiongetobject; c’est surement dû au fait que les LCR générés par un process de capture ont une structure interne très différente de ceux créés manuellement. La punition est assez radicale puisque le processus d’apply fait alors un coredump avec uneORA-600et un message inconnu sur My Oracle Support[knglrow_copy809]. Pour contourner ce problème, ré-instanciez un nouveau LCR à l’aide du constructeur et en reprenant le contenu du LCR d’origine; la fonctionanydata.convertobjecttransforme le nouveau LCR dans une forme utilisable par les handlers. - Enfin, il faut référencer dans votre nouveau handler :
- le type tableau de message d’erreurs utilisé par le dml error handler
- les 2 procédures utilisées (dml apply handler d’abord puis error handler)
Le package qui reprend les 2 handlers a donc, au final, le code ci-dessous :
connect strmadmin/strmadmin
create or replace package mypkg_w_error is
procedure dml_both_handlers(in_any anydata);
end;
/
create or replace package body mypkg_w_error is
procedure dml_both_handlers(in_any anydata) is
lcr sys.lcr$_row_record;
rc pls_integer;
error_stack_depth number;
error_numbers dbms_utility.number_array;
-- CHANGE THIS TYPE TO MATCH THE PARAMETER PROC!!!
error_messages mypkg.emsg_array;
tmpstr varchar2(4000);
l number;
mylcr sys.lcr$_row_record;
my_any anydata;
begin
-- Access the LCR and get the LCR command Type
rc := in_any.getobject(lcr);
begin
-- execute the dml apply handler (CHANGE THIS CALL!!!)
mylcr := SYS.LCR$_ROW_RECORD.CONSTRUCT(
source_database_name => lcr.get_source_database_name,
command_type => lcr.get_command_type,
object_owner => lcr.get_object_owner,
object_name => lcr.get_object_name,
tag => lcr.get_tag,
scn => lcr.get_scn,
old_values => lcr.get_values('old'),
new_values => lcr.get_values('new'));
my_any := anydata.convertobject(mylcr);
dml_apply_handler(my_any);
exception when others then
-- rebuild the error handler parameters
error_stack_depth:=1;
error_messages(error_stack_depth)
:=substr(dbms_utility.format_error_stack, 1, 76);
error_numbers(error_stack_depth)
:=to_number(substr(error_messages(error_stack_depth),4,6));
tmpstr:=dbms_utility.format_error_backtrace;
l:=regexp_count(tmpstr,'^.*$',1,'m');
for i in 1..l loop
error_stack_depth:=error_stack_depth+1;
error_messages(error_stack_depth)
:=substr(regexp_substr(tmpstr,'^.*$',1,i,'m'),1,76);
error_numbers(error_stack_depth)
:=to_number(substr(error_messages(error_stack_depth),4,6));
end loop;
-- execute the dml error handler (CHANGE THIS CALL TOO!!!)
mypkg.dml_error_handler(
my_any,
error_stack_depth,
error_numbers,
error_messages);
end;
end;
end;
/
On peut ensuite l’intégrer dans le code en le déclarant sous la forme d’un dml apply handler (i.e. avec le paramètre error_handler à false) comme ceci :
begin
dbms_apply_adm.set_dml_handler(
object_name => 'destination.t1',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => false,
user_procedure => 'mypkg_w_error.dml_both_handlers',
apply_database_link => null,
apply_name => 'STREAMS_APPLY',
assemble_lobs => false);
end;
/
col name format a15
col operation_name format a6
col user_procedure format a34
col handler_type format a18
select OBJECT_OWNER||'.'||OBJECT_NAME name,
OPERATION_NAME,
substr(USER_PROCEDURE,
instr(USER_PROCEDURE,'.',1,1)+1,
length(USER_PROCEDURE)
- instr(USER_PROCEDURE,'.',1,1)-1) user_procedure,
handler_type
from DBA_APPLY_DML_HANDLERS;
NAME OPERAT USER_PROCE DURE HANDLER_TYPE
--------------- ------ ---------------------------------- ------------------
DESTINATION.T1 INSERT "MYPKG_W_ERROR"."DML_BOTH_HANDLERS PROCEDURE HANDLER
Tests du code
Pour valider le bon fonctionnement du code, on va tester 3 cas :
- Le premier déclenche le dml apply handler qui réussit
- Le second déclenche le dml apply handler qui échoue déclenchant l’error handler qui réussit
- Le dernier déclenche le dml apply handler qui échoue déclenchant l’error handler qui échoue également générant une erreur accessible via
DBA_APPLY_ERROR
Pour ses 3 tests, on prépare les données de la manière suivante on supprime à nouveau la ligne id=4 de source.t1 puis on ajoute 2 lignes dans la destination :
- la ligne
id=6sera résolue par le dml apply handler - la ligne
id=4sera résolue par le error handler - la ligne
id=5sera pas résolue par le error handler
delete from source.t1 where id=4;
commit;
insert into destination.t1 values (4, 'Text 4');
insert into destination.t1 values (5, 'Text 5');
commit;
Vous voilà prêt pour les 3 tests; nous allons commencer par le test qui réussi
col text format a6
col id format 999
select *
from source.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
col text format a6
col id format 999
select *
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
insert into source.t1
values (6, 'Text 6');
commit;
col id format 999
select *
from source.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
6 Text 6
col text format a6
col id format 999
select *
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
6 Text 6
col id format 999
col log format a35
col datetime format a30
select id,
log,
datetime
from destination.c_history;
ID LOG DATETIME
---- ----------------------------------- ------------------------------
1 Insert into table T1 ; ID=4 29-APR-11 03.27.06.567153 PM
2 Error in table T1 ; ID=4 29-APR-11 05.35.07.353688 PM
3 Insert into table T1 ; ID=6 29-APR-11 11.00.47.398107 PM
On enchaine ensuite sur le test qui échoue sur le dml apply handler et déclenche avec succès l’error handler :
col text format a6
col id format 999
select *
from source.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
6 Text 6
col text format a6
col id format 999
select *
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
6 Text 6
insert into source.t1
values (4, 'Text 4');
commit;
col id format 999
select *
from source.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
6 Text 6
col text format a6
col id format 999
select *
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
6 Text 6
col id format 999
col log format a35
col datetime format a30
select id,
log,
datetime
from destination.c_history;
ID LOG DATETIME
---- ----------------------------------- ------------------------------
1 Insert into table T1 ; ID=4 29-APR-11 03.27.06.567153 PM
2 Error in table T1 ; ID=4 29-APR-11 05.35.07.353688 PM
3 Insert into table T1 ; ID=6 29-APR-11 11.00.47.398107 PM
4 Insert into table T1 ; ID=4 29-APR-11 11.05.40.511182 PM
5 Error in table T1 ; ID=4 29-APR-11 11.05.40.515334 PM
Enfin, on peut déclencher le dernier test qui fait échouer les 2 handlers
col text format a6
col id format 999
select *
from source.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
6 Text 6
col text format a6
col id format 999
select *
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
6 Text 6
insert into source.t1
values (5, 'Text 5');
commit;
col id format 999
select *
from source.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
6 Text 6
col text format a6
col id format 999
select *
from destination.t1
order by 1;
ID TEXT
---- ------
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
6 Text 6
col id format 999
col log format a35
col datetime format a30
select id,
log,
datetime
from destination.c_history;
ID LOG DATETIME
---- ----------------------------------- ------------------------------
1 Insert into table T1 ; ID=4 29-APR-11 03.27.06.567153 PM
2 Error in table T1 ; ID=4 29-APR-11 05.35.07.353688 PM
3 Insert into table T1 ; ID=6 29-APR-11 11.00.47.398107 PM
4 Insert into table T1 ; ID=4 29-APR-11 11.05.40.511182 PM
5 Error in table T1 ; ID=4 29-APR-11 11.05.40.515334 PM
Comme vous pouvez vous en rendre compte en interrogeant dba_apply_error, l’apply a échoué :
select ERROR_MESSAGE from dba_apply_error;
ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-00001: unique constraint (DESTINATION.SYS_C0010963) violated
ORA-06512: at "SYS.LCR$_ROW_RECORD", line 459
ORA-06512: at "STRMADMIN.MYPKG", line 33
ORA-06512: at "STRMADMIN.MYPKG_W_ERROR", line 46
ORA-00001: unique constraint (DESTINATION.SYS_C0010963) violated
ORA-06512: at line 1
Conclusion
Si ça fonctionne, conserver les 2 handlers reste compliqué ! Mieux vaut de beaucoup fusionner le code des 2 handlers dans un seul dml apply handler; de cette manière vous n’aurez pas besoin de réinstancier votre propre LCR$_ROW_RECORD. Cette dernière opération n’est d’ailleurs pas transparente et peut avoir un impact important sur les performances si vous avez une base très active.
En outre, utiliser une méthode qui génère des coredump, même si le problème semble cisconscrit n’est sans doute pas l’idée du siècle. Cela étant vous pouvez toujours retenir qu’il est possible d’obtenir les paramètres correspondant au error handler. Il suffit pour cela d’utiliser le package dbms_utility et les expressions régulières avec l’option 'm' pour match_pattern. On arrive ainsi à traiter la pile d’erreurs ligne par ligne.
Amusez-vous avec vos Streams ; et n’oubliez pas les cas où vos données contiennent des LOB 😉
