topic Re: Pass Context Variables with date value in Oracle component to delete in Talend Studio

Pass Context Variables with date value in Oracle component to delete

Anonymous — Fri, 09 Jun 2017 06:08:36 GMT

I have a job, where I need to delete records from a Oracle Table for that particular date (Dynamic like a batch date), before I insert records into the table (Delete before Insert).

I have set context variables for that job, with one of the variables as BusinessDate with prompt.

So whenever I run the job, I provide a BusinessDate (preferabbly current date) at runtime. This Context variable BusinessDate is used in Deleting the records from Oracle Table & then later Insert records into the same table with same BusinessDate value. I am unable to Delete the records, this is the query I provide

"DELETE FROM "+context.Schema+".TableName WHERE DATE="+context.BusinessDate+"".

It is throwing a error, ORA-00933: SQL command not properly ended. Pl help

Re: Pass Context Variables with date value in Oracle component to delete

TRF — Fri, 09 Jun 2017 06:43:32 GMT

Try this syntax:
"DELETE FROM "+context.Schema+".TableName WHERE DATE = to_date('"+context.BusinessDate+"', 'YYYY-MM-DD'"
Here I suppose DATE just after the WHERE represents the column name else you will have a problem as it is an oracle reserved word. Also, you have to adapt the format mask depending on the way the is entered by user.

Re: Pass Context Variables with date value in Oracle component to delete

Anonymous — Fri, 09 Jun 2017 07:11:35 GMT

Yes, DATE is a column name in the OracleTable, actually its BIZ_DATE.

Re: Pass Context Variables with date value in Oracle component to delete

Anonymous — Fri, 09 Jun 2017 07:20:33 GMT

I tried giving it the way that you had mentioned by converting TO_DATE function, but it doesnt work either.

The context variable BusinessDate is set as Date Type & the value I provide is: 2017-06-05 00:00:00

So this is my query:

"DELETE FROM "+context.Schema+".Table WHERE BUSINESS_DATE=TO_DATE('"+context.BusinessDate+"','YYYY-MM-DD HH24:MIS')".

This is the error I get, but this query works in Database

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Re: Pass Context Variables with date value in Oracle component to delete

TRF — Fri, 09 Jun 2017 08:12:46 GMT

So you're right with the syntax, jus have to adjust the mask. Check the oracle documentation for that.

Re: Pass Context Variables with date value in Oracle component to delete

Anonymous — Tue, 22 Aug 2017 05:46:25 GMT

I just wanted to update what worked for me & what I identified.

This statement worked for me.

"DELETE FROM "+context.Schema+".Table WHERE BUSINESS_DATE=TO_DATE('"+context.BusinessDate+"','DD-MON-YY')".

Also in Talend tMap, year must be specified as yy or yyyy (case sensitive, not to specify in CAPS). Similarly, date must be specified as dd (case sensitive, not to specify in CAPS).

Re: Pass Context Variables with date value in Oracle component to delete

Anonymous — Thu, 18 Oct 2018 09:06:28 GMT

Hi, I've tried doing your way but it keeps giving me the error ORA-01841. My context variable is a date