Five Stars PK
Five Stars

datatype conversion issue from oracle date to postgresql timestamp

Hi,

i am trying to develop a job where source is oracle and target is Postgresql. 

 

I am getting issue with oracle date datatype conversion to Postgresql Timestamp without time zone.

 

And i am getting below error:

 

java.lang.NullPointerException
	at java.util.Calendar.setTime(Unknown Source)
	at java.text.SimpleDateFormat.format(Unknown Source)
	at java.text.SimpleDateFormat.format(Unknown Source)
	at java.text.DateFormat.format(Unknown Source)
	at routines.TalendDate.formatDate(TalendDate.java:130)
	at data_archival.read_and_load_wf_table_0_1.Read_And_Load_WF_Table.tOracleInput_1Process(Read_And_Load_WF_Table.java:2723)
	at data_archival.read_and_load_wf_table_0_1.Read_And_Load_WF_Table.runJobInTOS(Read_And_Load_WF_Table.java:7391)
	at data_archival.read_and_load_wf_table_0_1.Read_And_Load_WF_Table.main(Read_And_Load_WF_Table.java:7152)

I have used below conversion for date:

 

TalendDate.parseDate("yyyy-mm-dd HH:mm:ss",TalendDate.formatDate("dd-MM-yyyy HH:mm:ss",row2.ATTACHED_DTTM)) 

 

Can anyone help me to fix this error.

 

Regards

PK

I am also attaching screenshot of talend job from source to tMap to target.

  • Big Data
  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Eleven Stars

Re: datatype conversion issue from oracle date to postgresql timestamp

 I suspect that the row2ATTACHED_DTTM column is null. To test this, add a tLogRow to the flow to see exactly what is being passed in that column. 

 

When dealing with columns which may be null, it is best to introduce some logic to prevent NullPointerExceptions. Below is a way of stopping this in your job if you don't mind null dates.....

 

row2.ATTACHED_DTTM!=null ? TalendDate.parseDate("yyyy-mm-dd HH:mm:ss",TalendDate.formatDate("dd-MM-yyyy HH:mm:ss",row2.ATTACHED_DTTM))  : null

The other thing I noticed is that your expression that I modified above seems to be converting a date to a formatted String and then converting the formatted String back to a Date. Is this what you want to do? There is absolutely no need to do this. Dates and the format you read them in is of no consequence to the value. The code above could simply be ...

 

row2.ATTACHED_DTTM

....and you would get the same result and no error (....unless you have stipulated that the column is not nullable.

Rilhia Solutions
3 REPLIES
Eleven Stars

Re: datatype conversion issue from oracle date to postgresql timestamp

This is a NullPointerException which basically says that there is no data being passed to whatever is calling it. Looking at your last screenshot, there is a "dummy" column that looks like it uses a very similar expression to the one you have specified above. I can't see all of it. However it does not appear to be receiving a Date value. Can you show this?

 

By the way, you may be able to get more info about this issue if you can read Java and the Java error stacks. The error stack you have included seems to show the problem is caused at line 2723 in your Read_And_Load_WF_Table job. If you click on the code tab in your Studio, you should be able to see this and go to the line of code. This may give you a clue as to what is happening.

Rilhia Solutions
Five Stars PK
Five Stars

Re: datatype conversion issue from oracle date to postgresql timestamp

Hi,

 

I have looked at the code at line number 2723, and it is showing :

 

TalendDate.parseDate("yyyy-mm-dd HH:mm:ss",TalendDate.formatDate("dd-MM-yyyy HH:mm:ss",row2.ATTACHED_DTTM)) 

 

I don't think there is issue with Dummy Column its expression is :

 

TalendDate.parseDate("yyyy-mm-dd HH:mm:ss", TalendDate.getDate("yyyy-MM-dd HH:mm:ss")) 

Eleven Stars

Re: datatype conversion issue from oracle date to postgresql timestamp

 I suspect that the row2ATTACHED_DTTM column is null. To test this, add a tLogRow to the flow to see exactly what is being passed in that column. 

 

When dealing with columns which may be null, it is best to introduce some logic to prevent NullPointerExceptions. Below is a way of stopping this in your job if you don't mind null dates.....

 

row2.ATTACHED_DTTM!=null ? TalendDate.parseDate("yyyy-mm-dd HH:mm:ss",TalendDate.formatDate("dd-MM-yyyy HH:mm:ss",row2.ATTACHED_DTTM))  : null

The other thing I noticed is that your expression that I modified above seems to be converting a date to a formatted String and then converting the formatted String back to a Date. Is this what you want to do? There is absolutely no need to do this. Dates and the format you read them in is of no consequence to the value. The code above could simply be ...

 

row2.ATTACHED_DTTM

....and you would get the same result and no error (....unless you have stipulated that the column is not nullable.

Rilhia Solutions