soumitra chatterjee soumitra chatterjee - 29 days ago 13
SQL Question

Java JDBC executeUpdate not working on Delete SQL with TRUNC function in where clause

The following delete statement is working fine in SQL developer but when executed using JDBC api executeUpdate() is not working.

After removing the where clause its working fine.

Delete from Tab1
where TRUNC(CREATED_TS) <= TRUNC(ADD_MONTHS(SYSDATE,-3))


I am not able to figure out the problem as no exception or error is printed.Only code execution is getting hanged at the executeUpdate() method.

Database : Oracle 11g
Java: 1.6

Answer

The issue was not with the delete SQL.The issue was with another session in which there was some uncommitted changes.Because of that the delete SQL from java code was getting hanged indefinitely. On issuing a commit on the another session ,java api executeUpdate() responded and it started working fine.

The question is similar to the issue as in the below link:

Oracle database is hanging infinitly in UPDATE queries