Four Stars

Pass Context Variables with date value in Oracle component to delete

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

  • Data Integration
4 REPLIES
Nine Stars TRF
Nine Stars

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

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.

TRF
Four Stars

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

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

 

Four Stars

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

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:MISmiley FrustratedS')".

 

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

Nine Stars TRF
Nine Stars

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

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

TRF