One Star

[resolved] How to call DELETE FROM on Oracle?

Hello,
I need to execute a DELETE FROM on an Oracle table (need to be able to rollback thus no truncate possible). Is there a way of executing an SQL statement that does not return any values (as DELETE FROM) in Talend without calling a stored procedure? I tried to use tOracleOutput component but it requires an input flow to perform DELETE in 'action on data' (would need to read all table data to delete it) and truncate in 'action on table' is not an option. tOracleInput could do the thing but I don't know how to execute an one-line SQL DELETE statement that would actually return any value to cheat tOracleInput.
I found a related thread in the bug tracker: http://www.talendforge.org/bugs/view.php?id=1608
Apparently the decision was made to use truncate over delete for 'clear table' operation in tOracleOutput.
It would be nice though to have use of both methods possible because there exist situations when we want to clear the table and be able to rollback afterward. Imagine a scenario with a table which contents are loaded from a source changing rapidly over time implying that most of the rows change between data loads. You don't want to keep rows that no longer exist in the source (no update or insert) and moreover for some reason it is better to have the table populated with old data than with no data (rollback is necessary in case load fails). How do we clear the table then?
Thanks in advance for any hints!
Michal
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: [resolved] How to call DELETE FROM on Oracle?

You can issue the delete in a tOracleRow. In the component's query text box, you simply put the "DELETE FROM <table>" statement.
2 REPLIES
Seven Stars

Re: [resolved] How to call DELETE FROM on Oracle?

You can issue the delete in a tOracleRow. In the component's query text box, you simply put the "DELETE FROM <table>" statement.
One Star

Re: [resolved] How to call DELETE FROM on Oracle?

Haven't thought of that. Works great!
Thanks for your help!