Hi ,
I am trying to handle Exception in GG_EXCEPTION table whenever i will get any error in Replicat file. For that i have created below table.
CREATE TABLE GGADMIN.GG_EXCEPTION
(
SRC_PK VARCHAR2(500 CHAR),
TAB_NM VARCHAR2(100 CHAR),
SCHEMA_NM VARCHAR2(50 CHAR),
TRG_SCHEMA_NM VARCHAR2(50 CHAR),
REPROCEED_FLG VARCHAR2(10 CHAR),
OP_TYP VARCHAR2(50 CHAR),
ERR_MSG VARCHAR2(1000 CHAR),
ERR_NBR NUMBER,
ERR_TYP VARCHAR2(100 CHAR),
SRC_TRANS_TMSTMP TIMESTAMP(6),
STG_INSRT_TMSTMP TIMESTAMP(6) DEFAULT SYSDATE,
PRCD_TMSTMP TIMESTAMP(6)
);
below is the configuration in replicat file
MACRO #stg_col_map_exception()
BEGIN
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP (
SRC_PK = SRC_PK,
TAB_NM = @GETENV('GGHEADER', 'TABLENAME'),
SCHEMA_NM = 'DATA_LAKE',
TRG_SCHEMA_NM = 'DB_ODS',
OP_TYP = @GETENV('GGHEADER', 'OPTYPE'),
ERR_MSG = @GETENV('LASTERR', 'DBERRMSG'),
ERR_NBR = @GETENV('LASTERR', 'DBERRNUM'),
ERR_TYP = @GETENV('LASTERR', 'ERRTYPE'),
SRC_TRANS_TMSTMP = @GETENV('GGHEADER', 'COMMITTIMESTAMP'),
STG_INSRT_TMSTMP = @DATENOW()
PRCD_TMSTMP = @COLSTAT (NULL),
REPROCEED_FLG = 'N')
END;
MAP DATA_LAKE.SRC_TAB, TARGET DB_ODS.TRG_TAB,
KEYCOLS(ODS_SRC_PK_ID),
SQLEXEC (SPNAME DB_ODS.PKG_INST_GG.INSRT_SS_ITM_PRC, ID INSRT_SS_ITM, PARAMS (P_IN_SRC_PK = SRC_PK), &
ERROR RAISE, TRACE ALL, BEFOREFILTER),
COLMAP(ODS_SRC_PK_ID = -999,
DEL_FLG=1);
MAP DATA_LAKE.SRC_TAB, TARGET GGADMIN.GG_EXCEPTION , #stg_col_map_exception();
Error :-
2017-11-27 03:58:45 ERROR OGG-00665 OCI Error calling OCITransCommit (status = 2091-ORA-02091: transaction rolled back
ORA-02291: integrity constraint (TM_ODS.R_ST_CD_IT) violated - parent key not found), SQL<UPDATE /*+ RESTRICT_ALL_REF_CONS */ "DM_ODS"."TRG_TAB" x SET x."ODS_SRC_PK_ID " = :a1,x."ODS_STAT_ID" = :a2,x."DEL_FLG" = :a3 WHERE x."ODS_SRC_PK_ID " = :b0>.
ideally process should be running and all the exception should record in GG_EXCEPTION table.