topic Re: Oracle - Truncate or Delete table in Talend Studio

Oracle - Truncate or Delete table

talendtester — Tue, 26 Sep 2017 01:46:38 GMT

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

Re: Oracle - Truncate or Delete table

Anonymous — Tue, 26 Sep 2017 07:10:45 GMT

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

Re: Oracle - Truncate or Delete table

talendtester — Tue, 26 Sep 2017 18:43:46 GMT

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

Re: Oracle - Truncate or Delete table

Anonymous — Wed, 27 Sep 2017 07:35:06 GMT

Hi,

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

Best regards

Sabrina

Re: Oracle - Truncate or Delete table

talendtester — Wed, 27 Sep 2017 17:11:38 GMT

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

Re: Oracle - Truncate or Delete table

abarjun — Thu, 31 May 2018 10:47:52 GMT

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

Re: Oracle - Truncate or Delete table

Anonymous — Wed, 13 Jun 2018 08:33:11 GMT

Hello,

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

Best regards

Sabrina