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