Goldengate calling plsql procedure

802427

Hi Friends ,
as i am new to Goldengate features i need your help on working on this task.
i am actually need pl/sql procedure since i am working on Goldengate replication.in my parameter i will call this procdure .actuall requirement is like this .

i have tables A,B,C at source and at target i have tables B , C AND D Tables .

TABLE A has columns ID,TT_STATUS ,COUNTRY,DB_NAME

TABLE B HAS COLUMNS OPEN_BY,CREATED

TABLE C HAS COLUMNS NAME,DEPT

TABLE D HAS COLUMNS NAME,DEPT,OPEN_BY,CREATED ,OPEN_BY,CREATED,ID,TT_STATUS ,COUNTRY,DB_NAME

AT MY SOURCE when ever on TABLE A .TT_STATUS column changes or update like open to close or some other status i have to compare before.tt_status with tt_status if it is diffrent then i have to do an insert operation like as below and

insert in to table D AS SELECT NAME,DEPT,OPEN_BY,CREATED ,OPEN_BY,CREATED,ID,TT_STATUS ,COUNTRY,DB_NAME FROM a,b,c if both the status are same igonore

so here i have two tables B&C as lookup tables at target and other table A i dont have it at target side i want to know how to achive that with out bringing table A to target side .

i need your help in achiving this task since some of the features like calling procedure i am using it for the 1st time . i know its easy if i use pl/sql to achive this task but i dont how to pass the values in procedure.

thanks and regards
Tom

-joe

Hi Tom.

Check out the OGG 11.1 reference manuel pages 245-258 that talks about SQLEXEC. It works the same for extract and replicat with one significant difference: extract does not support REPERROR yet.

Here's an example from said doc:

MAP sales.srctab, TARGET sales.targtab, &
SQLEXEC (SPNAME lookup, ID lookup1, PARAMS (param1 = srccol)), &
COLMAP (targcol1 = lookup1.param2), &
SQLEXEC (SPNAME lookup, ID lookup2, PARAMS (param1 = srccol)), &
COLMAP (targcol = lookup2.param2);

A few things to point out here:

1. The string after reserved word SPNAME is the name of your procedure. If logged in as the owner you don't need to qualify the schema but it's always good to do so
2. "param1" is the name of the IN parameter from your procedure
3. "param2" is the OUT parameter from your procedure
4. "srccol" is the name/value of a column in the table
5. You don't need the ampersand (&) to continue the line anymore with Table and Map statements

If we want to write this using a Table statement in the extract (Map statements are used in the replicat) you would store the data in a user defined token, which are declared on the fly and will be written with the OGG change record to the trail and available to the target. You'll want to read the document section on tokens (use @TOKEN in Map statements to pull out the value).

We can write a simpler version for example purposes like this in extract:

Tablle sales.srctab,
SQLEXEC (SPNAME lookup, ID lookup1, PARAMS (param1 = srccol)),
TOKENS ( TKN_STATUS = lookup1.param2),
;

If you want to do string comparisons on the target then check the @STR* functions. Otherwise you can do this logic in your procedure.

Hope this helps and good luck.

-joe