tOracleRow - Execute SQL which calls to_timestamp function?

[talendtester]

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

[vboppudi]

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?

[talendtester]

Oracle database.

What happens when you change to use the to_timestamp function?

[vboppudi]

Using TO_TIMESTAMP  also working as expected. 

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

Regards,

[talendtester]

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?

[vboppudi]

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

Regards,

[talendtester]

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.