Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Replace GoldenGate COLMAPwith custom stored procedure
azemerov
post Mar 3 2017, 04:38 PM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 3-March 17
Member No.: 51,818



Hi,
Oracle GoldenGate uses COLMAP to specify mapping in replicat configuration. Also it is possible to execute stored procedures using SQLEXEC clause - this way you can implement your mapping inside your stored procedure. My question is - is it possible to completely replace built-in GG mechanism for target records insert/update with custom stored procedure wich takes care of both mapping and serialization logic? Something like

MAP TEST_TAB1, TARGET TEST_TAB1, SQLEXEC (
SPNAME MY_PROCEDURE,
PARAMS(c1=COL2, c2=COL2, c3=COL3)),
NOCOLMAP -- !!!
);

or

MAP TEST_TAB1, SQLEXEC ( -- no TARGET !!!
SPNAME MY_PROCEDURE,
PARAMS(c1=COL2, c2=COL2, c3=COL3))
);

the idea is - GG should execute the procedure for each input record and does nothing else. The reason for custom implementation is a complexity of record mappings - we need to map several input records into one aggregated target record. Basically GG would provide data feed only.
Go to the top of the page
 
+Quote Post
burleson
post Mar 8 2017, 11:28 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,492
Joined: 26-January 04
Member No.: 13



Hi,

>> is it possible to completely replace built-in GG mechanism for target records insert/update with custom stored procedure wich takes care of both mapping and serialization logic?

Yes, I'm sure it's possible, but I'm not a GG expert, sorry . . .

This might help, my notes on colmap:

http://www.dba-oracle.com/t_goldengate_column_mapping.htm




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
azemerov
post Mar 28 2017, 02:08 PM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 3-March 17
Member No.: 51,818



QUOTE (burleson @ Mar 8 2017, 11:28 AM) *
Hi,

>> is it possible to completely replace built-in GG mechanism for target records insert/update with custom stored procedure wich takes care of both mapping and serialization logic?

Yes, I'm sure it's possible, but I'm not a GG expert, sorry . . .

This might help, my notes on colmap:

http://www.dba-oracle.com/t_goldengate_column_mapping.htm


Thank you. Have tested - that is possible indeed. Simply remove "TARGET" clause in replicat configuration and provide SQLEXEC. Another importatnt element - you need to add "EXEC SOURCEROW" to SQLEXEC expression to force GG to execute SQL on each row in transaction.

Example (I use SP in Oracle package):

MAP SCOTT.MYTABLE, SQLEXEC (
SPNAME SCOTT.TEST_PKG.map_MYTABLE, ID map_MYTABLE,
PARAMS(P_ID=ID, P_REC_CREATE_DT=REC_CREATE_DT, P_NAME=NAME, P_ADDRESS=ADDRESS),
EXEC SOURCEROW);

Such mapping results in warning message in error log, like
2017-03-24 14:11:28 WARNING OGG-10173 Oracle GoldenGate Delivery for Oracle, test_replicat.prm: (test_replicat.prm) line 93: Parsing error, required parameter option [target] is missing.
But despite the warning the replicat works well.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd August 2017 - 12:12 AM