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

Question

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

What do you see in the database? Is the database session active? Is it blocking on something? What is it blocking on? — Justin Cave, Nov 04 '16 at 15:00
No...i already mentioned that in my post.I cant provide much info as i dont have any error/stacktrace — soumitra chatterjee, Nov 04 '16 at 15:01
Show us your Java code. And if you already deleted those rows from SQL Developer I'm not surprised that the statement no longer deletes something when run from your application. — , Nov 04 '16 at 15:12
Did you `ROLLBACK` the `DELETE` you issued in SQLDeveloper before running your Java code? If not, your active transaction in SQLDeveloper is locking the same rows you're trying to update with `executeUpdate()`. — Mick Mnemonic, Nov 04 '16 at 22:17

score 0 · Answer 1 · answered Nov 04 '16 at 15:06

Since there is no Exception or Stack Trace one can only guess.

Probably the Exception is being swallowed in the Java code. Have a look at that and Print it if possible.
where TRUNC(CREATED_TS) <= TRUNC(ADD_MONTHS(SYSDATE,-3)) will prevent index on CREATED_TS (if there was one) from being used and may slow down the process. And a timeout may have occurred. I would check the connection/statement timeout settings on the Java side of the setup.

score 0 · Accepted Answer · edited May 23 '17 at 12:13

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

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

2 Answers2