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.
The first 100 community members completing the Open Studio survey win a $10 gift voucher.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Pick up some tips and tricks with Context Variables
Learn how media organizations have achieved success with Data Integration
Learn how and why companies are moving to the Cloud