ORACLE Job failed while extracting data with DATE format not recognized

We have designed a job to load data from ORACLE to HIVE. For three columns ‘CDR_CAL_STRT_TMST’,’ CDR_CAL_END_TMST’, ‘FILE_DATE’ the data type in the source is ‘DATE’ but when we checked the values in the source (ORACLE) we could see we have timestamp value as well. So we have changed the data type from DATE to TIMESTAMP. We could see the job failed with Error  ORA-01821: date format not recognized. Enclosed are the JSON and logs for the same.

Could you please check

Below is the source SQL which got generated after changing the datatype for below columns from DATE to TIMESTAMP:
coalesce(TO_CHAR(SRC_ZACT_ROAMING_SMS_CDR_USG.CDR_CAL_STRT_TMST,’YYYY-MM-DD HH24:MI:SS.FF9′),’NULL’) AS CDR_CAL_STRT_TMST,
coalesce(TO_CHAR(SRC_ZACT_ROAMING_SMS_CDR_USG.CDR_CAL_END_TMST,’YYYY-MM-DD HH24:MI:SS.FF9′),’NULL’) AS CDR_CAL_END_TMST,
coalesce(TO_CHAR(SRC_ZACT_ROAMING_SMS_CDR_USG.FILE_DATE,’YYYY-MM-DD HH24:MI:SS.FF9′),’NULL’) AS FILE_DATE

Beginner Asked on April 20, 2021 in Diyotta Studio.
Add Comment
1 Answer(s)

Coalesce(TO_CHAR(SRC_ZACT_ROAMING_SMS_CDR_USG.CDR_CAL_STRT_TMST,’YYYY-MM-DD HH24:MI:SS.FF9′),’NULL’) AS CDR_CAL_STRT_TMST

As per the above extract  SQL, the HH24:MI:SS: FF9 is included only when we have timestamp datatype included for the columns in the oracle source and oracle Data object.  But in the backend, the datatype is defined as DATE for these columns. Due to that, we are facing  ORA-01821: date format not recognized.  Can you change the data type in the  Oracle Dataobject and source to DATE and then run the jobflow or dataflow.

For extracting the nanoseconds from the date column present in the oracle source change the override query to the below format.  This will ingest the data into the Hive target table with nano seconds included in the timestamp.

TO_CHAR(SRC_ZACT_ROAMING_SMS_CDR_USG.FILE_DATE,’YYYY-MM-DD HH24:MI:SS.SSSSS’).

Expert Answered on April 22, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.