tOracleRow - Execute SQL which calls to_timestamp function?

Nine Stars

tOracleRow - Execute SQL which calls to_timestamp function?

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

Nine Stars

Re: tOracleRow - Execute SQL which calls to_timestamp function?

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?

tOracle.PNG

Veeru Boppudi
Nine Stars

Re: tOracleRow - Execute SQL which calls to_timestamp function?

Oracle database.

What happens when you change to use the to_timestamp function?
Nine Stars

Re: tOracleRow - Execute SQL which calls to_timestamp function?

Using TO_TIMESTAMP  also working as expected. 

 

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

 

Regards,

 

Veeru Boppudi
Nine Stars

Re: tOracleRow - Execute SQL which calls to_timestamp function?

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?
Nine Stars

Re: tOracleRow - Execute SQL which calls to_timestamp function?

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

Regards,

 

Veeru Boppudi
Nine Stars

Re: tOracleRow - Execute SQL which calls to_timestamp function?

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.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now