Oracle - Truncate or Delete table

[talendtester]

What is the best practice to truncate or delete rows in an Oracle table?

When I have tOracleInput with the Query "TRUNCATE TABLE myTable" I get the error "ORA-03291: Invalid truncate option - missing STORAGE keyword".

When I run the same statement in Oracle SQL Developer I get "table myTable truncated."

If I added "REUSE STORAGE;" or "DROP STORAGE; " that doesn't seem to help in tOracleInput.

When I have tOracleInput with the Query "DELETE FROM myTable" I get the error "ORA-01002: fetch out of sequence".

When I run the same statement in Oracle SQL Developer I get "# rows deleted."

[Anonymous]

Which version of oracle you are using ? whats the JDBC driver version ?  Can you attached your job here ?

[talendtester]

We are using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production and I think the JDBC driver is "orai18n.jar"

There really isn't much to the job, just the SQL in the tOracleInput component.

The job looks like:
tOracleInput > tLogRow

[Anonymous · Accepted solution]

Hi,

Could you please try to use tOracleRow with your truncate or delete table query to see if it works?

Best regards

Sabrina

[talendtester]

Why does the tOracleRow component not like ";" in the query?

---

@xdshi wrote:

Hi,

Could you please try to use tOracleRow with your truncate or delete table query to see if it works?

Best regards

Sabrina

---

[abarjun]

Hi, I had used tOracleRow for truncate table, it's working still, it throws the error in attachment

[Anonymous]

Hello,

Your attachment is not available on forum. Could you please try to upload it again?

Best regards

Sabrina