Six Stars

Oracle - Truncate or Delete table

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

Tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

Re: Oracle - Truncate or Delete table

Hi,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
4 REPLIES
Employee

Re: Oracle - Truncate or Delete table

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

Six Stars

Re: Oracle - Truncate or Delete table

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
Moderator

Re: Oracle - Truncate or Delete table

Hi,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Oracle - Truncate or Delete table

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