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?
execute immediate 'INSERT INTO myDatabase.myTable
to_timestamp( myDATE, ''yyyy-mm-dd HH24:MI:ss'' ) as myDATE
execute immediate 'commit';
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
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
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?
Using TO_TIMESTAMP also working as expected.
"INSERT INTO test_date SELECT TO_TIMESTAMP ('20171210', 'yyyymmdd') as myDATE FROM DUAL"
Why you need to commit. I think by default it will commit after execution. Why you are converting date to date time again ?
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.