EDIT: 2012\/05\/01 I changed the PL\/SQL code below, as I found that creating a filter per column eventually exhausts the hardcoded limit on the number of filters you can have for a table (20). I changed it to do a count of records where each and every column’s before value matches each and every columns current value in the target. I am having issues with line length when I do this, so this may be updated again.<\/b><\/p>\n
Out of the box, Golden Gate will not detect any conflicts in which the target data is different than what the source is prior to submission. If a row doesn’t exist that is sbumitted for update from the source, that will throw an ORA-01403. If you try to insert a row into the target for which the primary key value already exists in the target, and ORA-00001 will be thrown by the database, as you would expect.<\/p>\n
However, assume you have a row in the source such as…<\/p>\n
PRODUCT_ID 10
\nINVENTORY_QUANTITY 100<\/p>\n
…that is then updated to…<\/p>\n
PRODUCT_ID 10
\nINVENTORY_QUANTITY 90<\/p>\n
…and the target has an update made directly to it like…<\/p>\n
PRODUCT_ID 10
\nINVENTORY_QUANTITY 40<\/p>\n
When the update to 90 is sent to the replicat on the target, GoldenGate will not throw any exceptions, and will update the INVENTORY_QUANTITY to 90.<\/p>\n
Monday morning, the guys in the warehouse are trying to ship product that doesn’t exist in the inventory system. It might go something like, “hey, we are missing 50 units.” Ouch…<\/p>\n
What is below will programmatically create filters for checking prior to update to ensure there wasn’t an update on the target about which we didn’t know. We didn’t want to implement all kinds of conflict handlers as we are in the midst of an ATG implementation, and don’t understand the data well enough to make those decisions in advance. This will simply trap an issue if it exists and abend. That is acceptable to us in the short run for two reasons:<\/p>\n
What is below simply creates select of all columns other than the primary key where the value = what it was on the source before the update. If a matching “pre-version” row is not found on the target, an exception is thrown.<\/p>\n
The beautiful thing about this is that GoldenGate does not execute the “filter” SQL for every update. It must be smart enough to check the incoming values and what it is in the target before updating. I know this because I ran a 10046 trace against the replicat process, and the select for the “filter” was only parsed, never executed during a test with thousands of updates. However, as soon as I forced in a conflicting update that was considered for processing by the replicat, the select filter was executed and the replicat process abended (because the check didn’t pass).<\/p>\n
As such, you don’t have to worry about the overhead of the filter. I think that is what is meant by an extraction map, it just maintains it in memory.<\/p>\n
\r\nSET serveroutput ON size unlimited\r\nspool conflict.out\r\nSET lines 1000 trims ON\r\nSET echo off\r\nSET feedback off\r\nDECLARE\r\n \/*\r\n Simple anonymous block that will read the dictionary for a given schema and output rules that will detect conflicts\r\n if after an update all column values other than the primary key have changed since the update was issued on the\r\n source.\r\n *\/\r\n skip NUMBER := 0;\r\n p_source VARCHAR2(30) := 'ATGDB_EXPRESS_CORE';\r\n p_target VARCHAR2(30) := 'ATGDB_EXPRESS_CORE_REP';\r\n\r\n TYPE t_columns IS TABLE OF VARCHAR2(30);\r\n colns t_columns := t_columns();\r\n\r\n TYPE t_keys IS TABLE OF VARCHAR2(30);\r\n keys t_keys := t_keys();\r\n\r\n PROCEDURE get_columns(p_table IN VARCHAR2) IS\r\n i NUMBER := 0;\r\n l_skip NUMBER := 0;\r\n BEGIN\r\n FOR col IN (SELECT column_name\r\n FROM dba_cons_columns dcc\r\n WHERE table_name = p_table\r\n AND owner = p_source\r\n AND EXISTS (SELECT 1\r\n FROM dba_constraints dc\r\n WHERE owner = p_source\r\n AND dc.table_name = dcc.table_name\r\n AND dc.constraint_name = dcc.constraint_name\r\n AND dc.owner = dcc.owner\r\n AND constraint_type = 'P')\r\n ORDER BY position\r\n ) LOOP\r\n keys.extend(1);\r\n i := i + 1;\r\n keys(i) := col.column_name;\r\n END LOOP;\r\n i := 0;\r\n FOR col IN (SELECT column_name FROM dba_tab_columns WHERE table_name = p_table AND owner = p_source) LOOP\r\n FOR j IN 1..keys.COUNT LOOP\r\n IF keys(j) = col.column_name THEN\r\n l_skip := 1;\r\n EXIT;\r\n END IF;\r\n END LOOP;\r\n IF l_skip = 0 THEN\r\n colns.extend(1);\r\n i := i + 1;\r\n colns(i) := col.column_name;\r\n END IF;\r\n l_skip := 0;\r\n END LOOP;\r\n END;\r\nBEGIN\r\n FOR tab IN (SELECT table_name FROM dba_tables WHERE owner = p_source) LOOP\r\n DBMS_OUTPUT.put_line('map ' || p_source || '.' || tab.table_name || ', target ' || p_target || '.' || tab.table_name || ', &');\r\n DBMS_OUTPUT.put_line('sqlexec (id ' || tab.table_name || '_check, on update, beforefilter, & ');\r\n get_columns(tab.table_name);\r\n DBMS_OUTPUT.put('query \"select count(*) cnt from ' || p_target || '.' || tab.table_name || ' where ');\r\n FOR j IN 1..colns.COUNT LOOP\r\n IF j = 1 THEN\r\n DBMS_OUTPUT.put(colns(j) || ' = :p' || j);\r\n ELSE\r\n DBMS_OUTPUT.put(' and ' || colns(j) || ' = :p' || j);\r\n END IF;\r\n END LOOP;\r\n FOR j IN 1..colns.COUNT LOOP\r\n IF j = 1 THEN\r\n DBMS_OUTPUT.put('\", & ' || CHR(10) || 'params (p' || j || ' = ' || colns(j));\r\n ELSE\r\n DBMS_OUTPUT.put(',p' || j || ' = ' || colns(j));\r\n END IF;\r\n END LOOP;\r\n DBMS_OUTPUT.put_line(')), &');\r\n DBMS_OUTPUT.put_line('filter (on update, ' || tab.table_name || '_check.cnt > 0, raiseerror 9999);');\r\n DBMS_OUTPUT.put_line(CHR(10));\r\n keys.DELETE;\r\n colns.DELETE;\r\n END LOOP;\r\nEND;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"EDIT: 2012\/05\/01 I changed the PL\/SQL code below, as I found that creating a filter per column eventually exhausts the hardcoded limit on the number of filters you can have for a table (20). I changed it to do a…<\/p>\n