I have seen several blogs demonstrating using database exception table for error handling.
Truth be told, I have never used the exception table method and curious to know what’s the difference.
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
First, there’s manual work to create database exception table for error handling.
Let me know how long it takes you to configure database exception table for error handling.
Second, with new features, you may need to update database exception table for error handling.
Third, there is no values for bind variables.
Discard functionality is built-in and with OGG 12.2 and the missing delete can easily be converted into insert.
Which method will you be implementing and why?
$ cat dirrpt/r_hawk.dsc
Oracle GoldenGate Delivery for Oracle process started, group R_HAWK discard file opened: 2016-06-11 13:04:48.441744
Current time: 2016-06-11 13:09:07
OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (JANE, 1995-11-11:13:52:00, 256, PLANE) does not exist in table DEMO.TCUSTORD
ORA-01403: no data found
Operation failed at seqno 0 rba 190519
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = JANE
000000: 4a 41 4e 45 |JANE |
ORDER_DATE = 1995-11-11 13:52:00
000000: 31 39 39 35 2d 31 31 2d 31 31 20 31 33 3a 35 32 |1995-11-11 13:52|
000010: 3a 30 30 |:00 |
PRODUCT_CODE = PLANE
000000: 50 4c 41 4e 45 |PLANE |
ORDER_ID = 256
000000: 32 35 36 |256 |
PRODUCT_PRICE = 133300.00
000000: 31 33 33 33 30 30 2e 30 30 |133300.00 |
PRODUCT_AMOUNT = 1
000000: 31 |1 |
TRANSACTION_ID = 100
000000: 31 30 30 |100 |
*
Current time: 2016-06-11 13:09:07
OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (WILL, 1994-09-30:15:33:00, 144, CAR) does not exist in table DEMO.TCUSTORD
ORA-01403: no data found
Operation failed at seqno 0 rba 190727
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = WILL
000000: 57 49 4c 4c |WILL |
ORDER_DATE = 1994-09-30 15:33:00
000000: 31 39 39 34 2d 30 39 2d 33 30 20 31 35 3a 33 33 |1994-09-30 15:33|
000010: 3a 30 30 |:00 |
PRODUCT_CODE = CAR
000000: 43 41 52 |CAR |
ORDER_ID = 144
000000: 31 34 34 |144 |
PRODUCT_PRICE = 17520.00
000000: 31 37 35 32 30 2e 30 30 |17520.00 |
PRODUCT_AMOUNT = 3
000000: 33 |3 |
TRANSACTION_ID = 100
000000: 31 30 30 |100 |
*
Current time: 2016-06-11 13:09:07
OCI Error ORA-26787: The row with key ("CUST_CODE") = (JANE) does not exist in table DEMO.TCUSTMER
ORA-01403: no data found
Operation failed at seqno 0 rba 190905
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = JANE
000000: 4a 41 4e 45 |JANE |
NAME = ROCKY FLYER INC.
000000: 52 4f 43 4b 59 20 46 4c 59 45 52 20 49 4e 43 2e |ROCKY FLYER INC.|
CITY = DENVER
000000: 44 45 4e 56 45 52 |DENVER |
STATE = CO
000000: 43 4f |CO |
*
Current time: 2016-06-11 13:09:07
OCI Error ORA-26787: The row with key ("CUST_CODE") = (WILL) does not exist in table DEMO.TCUSTMER
ORA-01403: no data found
Operation failed at seqno 0 rba 191043
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = WILL
000000: 57 49 4c 4c |WILL |
NAME = BG SOFTWARE CO.
000000: 42 47 20 53 4f 46 54 57 41 52 45 20 43 4f 2e |BG SOFTWARE CO. |
CITY = SEATTLE
000000: 53 45 41 54 54 4c 45 |SEATTLE |
STATE = WA
000000: 57 41 |WA |
*
Process Abending : 2016-06-11 13:09:24
select * from exceptions
ARROW:(GGS_ADMIN@thor):PRIMARY> @pr "select * from exceptions";
REP_NAME : R_HAWK
TABLE_NAME : DEMO.TCUSTORD
ERRNO : 1403
DBERRMSG : OCI Error ORA-01403: no data found, SQL
OPTYPE : DELETE
ERRTYPE : DB
LOGRBA : 52
LOGPOSITION : 25819152
COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME : R_HAWK
TABLE_NAME : DEMO.TCUSTORD
ERRNO : 1403
DBERRMSG : OCI Error ORA-01403: no data found, SQL
OPTYPE : DELETE
ERRTYPE : DB
LOGRBA : 52
LOGPOSITION : 25819772
COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME : R_HAWK
TABLE_NAME : DEMO.TCUSTMER
ERRNO : 1403
DBERRMSG : OCI Error ORA-01403: no data found, SQL
OPTYPE : DELETE
ERRTYPE : DB
LOGRBA : 52
LOGPOSITION : 25821712
COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME : R_HAWK
TABLE_NAME : DEMO.TCUSTMER
ERRNO : 1403
DBERRMSG : OCI Error ORA-01403: no data found, SQL
OPTYPE : DELETE
ERRTYPE : DB
LOGRBA : 52
LOGPOSITION : 25822128
COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM
-------------------------
PL/SQL procedure successfully completed.
Convert DELETE into INSERT
Not all the columns’ value are available and because I did not configured properly.
ARROW:(DEMO@thor):PRIMARY> desc DEMO.TCUSTORD
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
CUST_CODE NOT NULL VARCHAR2(4)
ORDER_DATE NOT NULL DATE
PRODUCT_CODE NOT NULL VARCHAR2(8)
ORDER_ID NOT NULL NUMBER
PRODUCT_PRICE NUMBER(8,2)
PRODUCT_AMOUNT NUMBER(6)
TRANSACTION_ID NUMBER
ARROW:(DEMO@thor):PRIMARY> select count(*) from DEMO.TCUSTORD
2
ARROW:(DEMO@thor):PRIMARY> select count(*) from DEMO.TCUSTORD;
COUNT(*)
----------
0
ARROW:(DEMO@thor):PRIMARY> insert into DEMO.TCUSTORD ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE")
2 values ('JANE', '1995-11-11:13:52:00', 256, 'PLANE');
1 row created.
ARROW:(DEMO@thor):PRIMARY> select count(*) from DEMO.TCUSTORD;
COUNT(*)
----------
1
ARROW:(DEMO@thor):PRIMARY>
Thinking Out Loud 