Default value SYSTIMESTAMP and SYSDATE are misinterpreted during publishing

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

When data is generated in a Oracle table and the default value of a field of Data_Type "TIMESTAMP" is SYSTIMESTAMP, or  the default value of a field of Data_Type "DATE" is SYSDATE, the publishing job fails for the TDM Generator when publishing directly to the Oracle table.

The error messages are:

• "Value for TMS_CREA is invalid for datatype, value:SYSTIMESTAMP"
• "Value for DA_EFCTV is invalid for datatype, value:SYSDATE"

Environment

Release : 4.10.x

Component : CA TDM Portal

Cause

This is behaving as designed. From the below screenshot of the Generator, the default values are seen as String values, so when attempting to publish the default values, Oracle does not accept the String value as a valid input for the Data Types. This is because TDM Portal doesn't understand what SYSTIMESTAMP and SYSDATE are. TDM treat them as any other String value. So when the publishing is run, the value is not in the expected format for Oracle, and the values are invalid.

Resolution

Need to convert SYSTIMESTAMP and SYSDATE to variables, which pass values in the format acceptable to Oracle. In Oracle, SYSTIMESTAMP is a keyword that returns the system timestamp of the Oracle server. Likewise, SYSDATE is a keyword that returns the system date of the Oracle server. Therefore, when running the Publish job from the Generator in TDM Portal, we need to pass the system timestamp and the system date of the TDM Portal server, instead of the string values.

In TDM Portal there are two predefined variables that pass the System data and timestamp. They are STIMESTAMP and SDATE (as seen in the below screenshot in Data Painter):

 There are two options:

1.  Use the TDM predefined variables in the Generator to publish the system timestamp and system date to the Oracle table.NOTE: in the Generator, variables are encapsulated between Tilda (~) characters, otherwise they are just strings. 

2. Or create two custom variables, with the names SYSTIMESTAMP and SYSDATE, and map these two variables to call the corresponding ~STIMESTAMP~ and ~SDATE~ TDM variables.

And use ~SYSTIMESTAMP~ and ~SYSDATE~ in your Generator