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