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

5 REPLIES
Twelve Stars TRF
Twelve 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

Twelve Stars TRF
Twelve 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
Four Stars

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

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