|
15.1. Introduction to DML_HANDLER
A DML_HANDLER is a user defined procedure called by an apply handler.
if you define a DML_HANDLER for a specific or all type of operation, then this
DML hander is called for each LCR.
You must specify a DML_HANDLER for each type
of operation (INSERT, UPDATE, DELETE).
There are 5 types of DML_HANDLERS
:
- DML
- DDL
- Message
- Error handler
- Conflict handler
You add a DML_HANDLER to an apply process using the
dbms_apply_adm.set_dml_handler:
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name IN VARCHAR2,
object_type IN VARCHAR2,
operation_name IN VARCHAR2,
error_handler IN BOOLEAN DEFAULT FALSE, /* Note the presence of this argument, it makes all the difference */
user_procedure IN VARCHAR2, /* between a DML_HANDLER (always executed) and a ERROR_HANDLER */
apply_database_link IN VARCHAR2 DEFAULT NULL, /* (only executed in case of error */
apply_name IN VARCHAR2 DEFAULT NULL,
assemble_lobs IN BOOLEAN DEFAULT FALSE);
It is a current practice to assign the same procedure (column USER_PROCEDURE)
for the 3 operations and decode the LCR within the procedure. However, this will
not spare you the multiple assignments of the dml procedure to the apply process. Oracle as not keyword to assign
the 3 DML operations to a single procedure.
For instance if you wrote a
DML_HANDLER 'emp_dml_hdl' for user scott on table 'emp'. it will deals with all
dml operation only if you declare it this way :
SQL> exec
dbms_streams_adm.set_dml_handler('scott',
'emp', 'INSERT', false, 'emp_dml_hdl',
null, 'scott_app__emp_1', false );
SQL>
exec dbms_streams_adm.set_dml_handler('scott', 'emp', 'UPDATE', false, 'emp_dml_hdl', null,
'scott_app__emp_1', false );
SQL>
exec dbms_streams_adm.set_dml_handler('scott', 'emp', 'DELETE', false, 'emp_dml_hdl', null,
'scott_app__emp_1', false );
And within the 'emp_dml_hdl'
procedure we will see a code like that :
Create are replace procedure
scott.emp_dml_hdl( inAny ANYDATA ) is
InLcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
CmdType VARCHAR2(60);
begin
rc:=InAny.GetObject(InLcr);
-- Extract the LCR from the ANYDATA
CmdType := InLcr.GET_COMMAND_TYPE();
-- Get the command type of the LCR
IF CmdType = 'INSERT'
THEN
.
.
|
The view DBA_APPLY_DML_HANDLERS list all the DML_HANDLERS, the
operation for which they are defined and the user procedure name that is
called.
|
SQL> desc DBA_APPLY_DML_HANDLERS
Name
Null? Type
------------------------------------ -----
------------
OBJECT_OWNER
NOT NULL VARCHAR2(30)
OBJECT_NAME
NOT NULL VARCHAR2(30)
OPERATION_NAME
VARCHAR2(13)
USER_PROCEDURE
VARCHAR2(98)
ERROR_HANDLER
VARCHAR2(1)
APPLY_DATABASE_LINK
VARCHAR2(128)
APPLY_NAME
VARCHAR2(30)
ASSEMBLE_LOBS
VARCHAR2(1)
|
If the column ERROR_HANDLER = Y then
the DML_HANDLER is an ERROR_HANDLER.
In this chapter we will deals only with the conflict handlers |
15.2. Conflict DML_HANDLER
During the apply process, a conflict may occurs and the
DML operation cannot be performed. There are 3 main reasons in streams why a DML
fails :
We will discuss them in detail providing an example code to
show what type of conflict handler we may use.
the view DBA_APPLY_CONFLICT_COLUMNS contains information
about conflict handlers defined for tables in the database.
SQL> desc DBA_APPLY_CONFLICT_COLUMNS
Name
Null? Type
-------------------------------- ---------
-------
OBJECT_OWNER
VARCHAR2(30)
OBJECT_NAME
VARCHAR2(30)
METHOD_NAME
VARCHAR2(92)
RESOLUTION_COLUMN
VARCHAR2(4000)
COLUMN_NAME
VARCHAR2(30)
APPLY_DATABASE_LINK
VARCHAR2(128)
|
Update conflicts handlers are the only prebuilt conflict
handler provided by Oracle. Four types of conflict resolution has been
defined and their names are self-explanatory:
- OVERWRITE
- DISCARD
- MAXIMUM
- MINIMUM
When you decide to use a prebuilt update conflict handler for
a table, you must provide a column list. The prebuilt handlers
trigger only for conflicts of one of these columns.
For conflict that occurs in column not in the list, the error handler is called
to resolve the conflict. If it cannot resolve the conflict then the LCR goes to
the exception queue.
It is mandatory to specify a resolution column for each
prebuilt update conflict handler. Although the resolution column is not used for
OVERWRITE and DISCARD conflict handlers, a resolution column must be specified
for these conflict handlers.
|
Any columns specified in rule-based transformations or used within
DML Handlers or ERROR Handler specified for UPDATE at target site must be unconditionally logged at the
source site |
Uniqueness conflict are usually resolved by the transformation
of the incoming insert LCR into an update LCR.
15.2.3 Delete Conflicts
|
