tOracleRow - Insert case statement to cast timestamp?

Highlighted
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 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog