How do I pass the value of a timestamp from a lookup in the source database, for example, replace the following macro from their GoldenGate Capture which passes a token value?
Problem Summary
The query is
select /*+ RESULT_CACHE */ max(systemdate) dtval
from iods_stg.odstmpsystemdate_hist
where change_date <= :optime',
PARAMS (optime = @getenv('ggheader','committimestamp’));
Description
To pass along a value from the source that is not readily available in the environment you will need to add a column to the table definition in the repository. You cannot just use ColumnProperties /Extra to add it.
Instructions
These are the 4 steps to accomplish this task
Manually create the new column on the target, mine is a ‘date’ datatype.
2. Manually update the hvr_column table in the HVR_hub to reflect the new column in the Target
SQL> desc hvr_column;
Name Null? Type
---------------------------------------- -------- ----------------------------
CHN_NAME NOT NULL VARCHAR2(12)
TBL_NAME NOT NULL VARCHAR2(124)
COL_SEQUENCE NOT NULL NUMBER(11)
COL_NAME NOT NULL NVARCHAR2(128)
COL_KEY VARCHAR2(32)
COL_DATATYPE NOT NULL VARCHAR2(128)
COL_LENGTH VARCHAR2(128)
COL_NULLABLE NOT NULL NUMBER(4)
SQL> insert into hvr_column values ('ora2ora','sirius',4,'col4','0','date','0',0);
Note:
My channel is ‘ora2ora’ and ’sirius’ is my table.
Your Col_sequence will be the column number in the table, ie 4.
3. Create a Column Properties Action to include your new column
Define the Group to ’Source’ and the Table to ‘your table'
Select Name and give it the column name(col4)
Select ‘Absent’
Select ‘Capture Expression’ and put in your query
select /*+ RESULT_CACHE */ max(systemdate) dtval
from iods_stg.odstmpsystemdate_hist
where change_date <= optime;
Select ‘Datatype’ = datetime
4. Initialize - choose Table Enrollment and Replace Old Enrollment