tOracleRow - Insert case statement to cast timestamp?

Nine Stars

tOracleRow - Insert case statement to cast timestamp?

I have an Oracle database table with datetime values stored in a VARCHAR column.

Some of the rows of data have null for the RECORD_START_TIME.

I am attempting to insert into a table with a TIMESTAMP(0) column.

 

This SQL runs fine outside of Talend DI, but it errors when I try running it in the tOracleRow component.

 

begin
execute immediate 'INSERT /*+ append parallel (8) */   INTO myDatabase.myTable
SELECT
 CASE WHEN RECORD_START_TIME is null THEN null ELSE to_timestamp( RECORD_START_TIME,''yyyy-mm-dd HH24:MI:ss'' ) END  as  RECORD_START_TIME
FROM myDatabase.myOtherTable';
execute immediate 'commit';
end;

 

The error is:

ORA-01830: date format picture ends before converting entire input string

Nine Stars

Re: tOracleRow - Insert case statement to cast timestamp?

Anyone have any ideas on how to make this insert statement work in Talend DI?

Nine Stars

Re: tOracleRow - Insert case statement to cast timestamp?

Still hoping someone can help with an answer...

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download