deleting rows from table using odbc

[arun_im4u]

Hi,

I have a job which retrieves data from a Oracle database and writes into a flat file. Then I have to ftp that file and go back to the database and delete the rows which have been extracted.

I have to delete the rows from the table by selecting the maximum time stamp from the flat file and delete all the rows which have timestamp equal or less than that. Basically deleting the rows from the table which was extracted and written into the flat file. The table gets populated dynamically.

I am using an ODBC stage to connect to Oracle and cannot use the ORAOCI or any other stages(due to license issues) at this point.

I tried using an user -defined query in the odbc stage but didnt know how to delete rows based on the time-stamp, bcos its in a flat file.

Any suggestions would be really helpful.

thanks,
arun.

[ray.wurlod]

Read the flat file using a Sequential File stage.
In a Transformer stage pass along only the timestamp column, and mark it as Key.
In the OBDC stage use user-defined SQL to construct a DELETE statement referring to this key in the WHERE clause.

Code: Select all

DELETE FROM tablename WHERE timestampcol = ?

"Key" in this context means "search key" rather than "primary key".

[arun_im4u]

Thanks Ray. It worked fine.