pdi-cd-8.1.0.0-365 / 8.2.x.x Excel Writer Step DATE TIMESTAMP Column null in Sheet

[athor]

Hi All,

Test Environment

Windows 8.1/10 64 bit
Oracle 12.2.0.1
java version "1.8.0_131"
pdi-cd-(7.1,8.1,8.2)

What I did

Create simple transformation
Table Input step (with select * from user_objects)
Excel Writer Step (xlsx) writes file to local folder

What I found

The Excel file has been written but no content (null) in the DATA/TIMESTAMP Cells. This means the oracle table contains two DATE fields CREATED and LAST_DDL_TIME. This fields where empty in the excel file.

Further I tried to set in the Excel Writer Step a format for the date fields --> nothing changed --> column are null
Further I set the values in the select statement of table input step to a formated string for the DATE fields, means: to_char(DATE,'dd.mm.yyyy') --> now the fields are filled in the excel file. If I use the "format cells" use case in the file it sais "User defined format", but it is not a DATE.

Question/s

in previous versions (e.g. pdi-4.4 )we did not need formatting. Do I always have to specify formatting in the table input step?
A statement like "select * from my_table" with TIMESTAMP or DATE columns would be null for the columns?
Do I use the Excel Writer Step in a wrong manner?

Thank you for helping me

T.

[Ana GH]

I have tried with PDI 6.1, Java 1.8.0_191, Oracle DB 12.2.0.1.0, JDBC Driver: ojdbc6.jar, and I can reproduce your problem. If you don't want to transform the TIMESTAMP columns to VARCHAR, you can use the step Select values, and use the Meta-data tab to change all the Timestamp columns to Date type columns (you only need to specify the columns you want to change) that way the Write to excel step works.
Maybe with a newer ojdbc library it might work directly, I use ojdbc6 because it was the one published at the time to work with Oracle 11.2 databases.
Regards