Pass Context Variables with date value in Oracle component to delete

[Anonymous]

Hi

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

[TRF]

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.

[Anonymous]

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

[Anonymous]

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

[TRF]

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

[Anonymous]

Hi

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).

[Anonymous]

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