@DATE, @DATENOW … Date functions in GoldenGate

Dates are always fun to play with when it comes to the Oracle Database, much less any other relational database.  Dates are used for many thinks in a wide range of application and schemas.  You have birthdays, ship dates, order dates, registration date, etc….  You get the picture.  

In the Oracle Database you can have a few data types that can be used for dates as well.  Some of these data types are DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE; just to name a few.  If you go out to AskTom and do a search for “date”, the result set is pretty sizable.  That alone should show you how much dates are used within the relational database space.

Although it is great that we can do dates at the relational level, but what happens with dates when they are replicated?  What functions are provided for Oracle GoldenGate to replicat dates?  In this post, I’ll show you a few of the date related functions that can be used with Oracle GoldenGate.

Functions

There are two functions I want to focus on @DATE and @DATENOW.  What do these functions do and how are they different?

@DATE

The @DATE function is used on the Replicat (apply) side to return dates and times in a variety of formats.  These returned values can be placed into a column based on the format passed into the source column.  Then the source column value is converted into a valid SQL date.  @DATE can also extract potions of a date column or compute a numeric timestamp based on a data.

The syntax for the @DATE function is:

@DATE (‘output_descriptor’, ‘input_descriptor', source_column)

The output_descriptor is a string that is composed of the date descriptors and optional literal values that are required by the target column.  The input_descriptor is a valid string that is composed of the date descriptors and optional literal values.  Examples of each are below:

‘output_descriptor’ = ‘YYYY-MON-DD’

‘input_descriptor’ = ‘DD-MON-YY’

The acceptable Data Descriptors can be found in the Oracle GoldenGate docs (here).

@DATENOW

The @DATENOW function is much simpler than the @DATE function.  @DATENOW takes no arguments and returns the current data and time in the format of YYYY-MM-DD HH:MI:SS.  By default the function returns the local time with any adjustments for Daylight Savings time.

The syntax for the @DATENOW function is:

@DATENOW()

How to use 

Both of these functions are to be used in the Replicat parameter file in the MAP/TARGET statement. 

Example:

I have a table called TSTUSR.LRG_TABLE. This table is only a two column table on the source database (column: rid, text).  The table on the target side matches the source table but has three additional columns that accept date or timestamp values.

Source Table:

Name Null?    Type          
---- -------- ------------- 
RID NOT NULL NUMBER 
TEXT NOT NULL VARCHAR2(100)

Target Table:

Name            Null?    Type          
--------------- -------- ------------- 
RID NOT NULL NUMBER 
TEXT NOT NULL VARCHAR2(100) 
BIRTH_DATE DATE 
TODAY_DATE DATE 
TODAY_TIMESTAMP TIMESTAMP(6)

Now, I want to assign a default value to the birth_date column, assign today’s date to the today_date column and assign today’s date with timestamp to the today_timestamp column.

In order to do this, I would update the parameter file for the Replicat as such:

MAP oggtst1.tstusr.lrg_table, TARGET tstusr.lrg_table, colmap(usedefaults, birth_date=@DATE('YYYY-MMM-DD','YY-MMM-DD','20-JAN-08'),today_date=@DATENOW(), today_timestamp=@DATENOW());

Now, when I insert a record into the source database, the record is replicated to the target database.  What happens is that the record is applied to the table and the columns accepting the date values are populated.

RID     TEXT         BIRTH_DATE  TODAY_DATE  TODAY_TIMESTAMP 
———     ———————      ——————      ———————     ———————————————— 
76507   1234567890   08-JAN-20   14-JAN-20   14-JAN-20 05.53.13.000000000 PM

Where to go from here?

There are many more ways that dates can be replicated and transformed with Oracle GoldenGate.  These two functions (@DATE and @DATENOW) are just the tip of the options. Take a look at the other descriptors that these functions support and you can do a wide range of items with dates.

Enjoy!!

twitter: @dbasolved

Please follow and like: