tOracleRow - Insert case statement to cast timestamp?

talendtester — Wed, 27 Dec 2017 00:39:57 GMT

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

Re: tOracleRow - Insert case statement to cast timestamp?

talendtester — Wed, 27 Dec 2017 17:09:32 GMT

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

Re: tOracleRow - Insert case statement to cast timestamp?

talendtester — Tue, 02 Jan 2018 23:25:30 GMT

Still hoping someone can help with an answer...

topic Re: tOracleRow - Insert case statement to cast timestamp? in Talend Studio

tOracleRow - Insert case statement to cast timestamp?

Re: tOracleRow - Insert case statement to cast timestamp?

Re: tOracleRow - Insert case statement to cast timestamp?