topic Re: tOracleRow - Execute SQL which calls to_timestamp function? in Talend Studio

tOracleRow - Execute SQL which calls to_timestamp function?

talendtester — Fri, 15 Dec 2017 19:47:14 GMT

I have a tOracleRow component and SQL for an insert statement that calls the to_timestamp function. The SQL runs fine outside of Talend DI.

Why do I need two single quotes around the date time formatting in order for the component not to throw an error?

"begin
execute immediate 'INSERT INTO myDatabase.myTable
SELECT
to_timestamp( myDATE, ''yyyy-mm-dd HH24:MI:ss'' ) as myDATE
FROM myDatabase.myOtherTable';
execute immediate 'commit';
end;
"

This is the error I get without the two single quotes:

ORA-06550: line 4, column 23:
PLS-00103: Encountered the symbol "YYYY" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like like2 like4 likec between into using || multiset bulk
   member submultiset
The symbol "*" was substituted for "YYYY" to continue.
ORA-06550: line 4, column 37:
PLS-00103: Encountered the symbol "HH24" when expecting one of the following:

   . ( * @ % & = - + ; < / > at in is mod remainder not rem
   return returning <an exponent (**)> <> or != or ~= >= <= <>
   and or like like2 like4 likec between into using || bulk
   member submultiset

Re: tOracleRow - Execute SQL which calls to_timestamp function?

vboppudi — Fri, 15 Dec 2017 20:09:16 GMT

I tried this "INSERT INTO test_date SELECT TO_DATE('20171210','YYYYMMDD') as myDATE FROM DUAL"

This is working fine. Are you inserting data into Oracle DB or SQL Server?

Re: tOracleRow - Execute SQL which calls to_timestamp function?

talendtester — Fri, 15 Dec 2017 20:19:20 GMT

Oracle database.

What happens when you change to use the to_timestamp function?

Re: tOracleRow - Execute SQL which calls to_timestamp function?

vboppudi — Fri, 15 Dec 2017 20:24:00 GMT

Using TO_TIMESTAMP also working as expected.

"INSERT INTO test_date SELECT TO_TIMESTAMP ('20171210', 'yyyymmdd') as myDATE FROM DUAL"

Regards,

Re: tOracleRow - Execute SQL which calls to_timestamp function?

talendtester — Fri, 15 Dec 2017 20:38:14 GMT

What happens when you are pulling the datetime from a different table and converting the time too? And then doing the 'commit' in the same component?

Re: tOracleRow - Execute SQL which calls to_timestamp function?

vboppudi — Fri, 15 Dec 2017 20:53:46 GMT

Why you need to commit. I think by default it will commit after execution. Why you are converting date to date time again ?

Regards,

Re: tOracleRow - Execute SQL which calls to_timestamp function?

talendtester — Fri, 15 Dec 2017 21:06:08 GMT

I was running into the problem of the table being locked after I ran the insert if I didn't commit.

I am running the insert for over 33 million rows to convert from VARCHAR datatype in one table to timestamp(0) in the final table.