Oracle Delete locks table and hangs

[reinier1]

I have  a job with 1 single tDBRow  connecting to Oracle with the following SQL:

"DELETE FROM USER_SDO_GEOM_METADATA
WHERE
TABLE_NAME = 'RDM_ADMIN_BOUNDARIES'
AND
COLUMN_NAME = 'GEOM'"

I have die on error checked, and NB_LINE on DELETED (but also tried with NONE).

The statement hangs on the Java line where this SQL is executed. There is a lock on the table in Oracle, and the lock is not released, unless the process is killed.

If I execute the same SQL in Oracle SQL Developer, it neatly returns the number of rows deleted.

I tried adding a tDBCommit component after the tDBRow, but there I can not select the tDBRow Component (and the code never reaches there anyways, the debugger hangs much earlier:

stmt_tDBRow_1.execute(query_tDBRow_1);

How can I tell Oracle or Talend to continue after deleting the record?

[Anonymous]

Hi,

   Please select "Die on Error" option as the first step.

    The next item to be verified is the execution plan of the delete statement. If it is bad, check whether index is needed for the DML statement to increase the performance.

     Add a On SubJob OK and do a tOracleCommit. If you are still facing the issue, could you please provide the Table Data volume, Table DDL with sample data so that I can try it myself.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[reinier1 · Accepted solution]

I figured it out. This is an Oracle Spatial system table.

I can create my own Spatial columns on my own tables, and even create an index.

But in order to add or delete the row from the USER_SDO_GEOM_METADATA table, I need different access rights.

So, running the delete and insert statements with a user with higher access level solved the issue.

Still, a bit funny from Oracle not to throw an error, but not Talends problem.

thanx anyways.