Using EXCEPTIONSONLY
EXCEPTIONSONLY
is valid for one pair of source and target tables that are explicitly named and mapped one-to-one in a MAP
statement; that is, there cannot be wildcards. To use EXCEPTIONSONLY
, create two MAP
statements for each source table that you want to use EXCEPTIONSONLY
for on the target:
-
The first, a standard
MAP
statement, maps the source table to the actual target table. -
The second, an exceptions MAP statement, maps the source table to the exceptions table (instead of to the target table). An exceptions
MAP
statement executes immediately after an error on the source table to send the row values to the exceptions table.To identify a
MAP
statement as an exceptionsMAP
statement, use theINSERTALLRECORDS
andEXCEPTIONSONLY
options. The exceptionsMAP
statement must immediately follow the regularMAP
statement that contains the same source table. Use aCOLMAP
clause in the exceptionsMAP
statement if the source and exceptions-table columns are not identical, or if you want to map additional information to extra columns in the exceptions table, such as information that is captured by means of column-conversion functions orSQLEXEC
.
For more information about these parameters, see Parameters and Functions Reference for Oracle GoldenGate.
-
A regular
MAP
statement that maps the source tableggs.equip_account
to its target tableequip_account2.
-
An exceptions
MAP
statement that maps the same source table to the exceptions tableggs.equip_account_exception
.
In this case, four extra columns were created, in addition to the same columns that the table itself contains:
DML_DATE OPTYPE DBERRNUM DBERRMSG
To populate the DML_DATE
column, the @DATENOW
column-conversion function is used to get the date and time of the failed operation, and the result is mapped to the column. To populate the other extra columns, the @GETENV
function is used to return the operation type, database error number, and database error message.
The EXCEPTIONSONLY
option of the exceptions MAP
statement causes the statement to execute only after a failed operation on the source table. It prevents every operation from being logged to the exceptions table.
The INSERTALLRECORDS
parameter causes all failed operations for the specified source table, no matter what the operation type, to be logged to the exceptions table as inserts.
Note:
There can be no primary key or unique index restrictions on the exception table. Uniqueness violations are possible in this scenario and would generate errors.
Example 11-21 EXCEPTIONSONLY
This example shows how to use REPERROR
with EXCEPTIONSONLY
and an exceptions MAP
statement. This example only shows the parameters that relate to REPERROR
; other parameters not related to error handling are also required for Replicat.
REPERROR (DEFAULT, EXCEPTION) MAP ggs.equip_account, TARGET ggs.equip_account2, COLMAP (USEDEFAULTS); MAP ggs.equip_account, TARGET ggs.equip_account_exception, EXCEPTIONSONLY, INSERTALLRECORDS COLMAP (USEDEFAULTS, DML_DATE = @DATENOW (), OPTYPE = @GETENV ('LASTERR', 'OPTYPE'), DBERRNUM = @GETENV ('LASTERR', 'DBERRNUM'), DBERRMSG = @GETENV ('LASTERR', 'DBERRMSG'));
In this example, the REPERROR
parameter is set for DEFAULT
error handling, and the
EXCEPTION
option causes the Replicat process to treat failed operations as exceptions and continue processing.