Search code examples
springoracle-databasejdbctemplate

JdbcTemplate throws Table or view doesn't exist if WHERE clause in UPDATE statement doesn't find any results

I have a simple UPDATE statement like so:

UPDATE SCHEMA.TABLE SET STATE = 'ACTIVE' WHERE STATE = 'INACTIVE'

In SQL Developer this works all the time, even if there aren't any records with 'INACTIVE' status, returning '0 rows updated' in such case.

However, when executing the same query with jdbcTemplate.update(query) or jdbcTemplate.execute(query), it only works if there are any records with 'INACTIVE' status at the time of execution. Otherwise, if there aren't any 'INACTIVE' records, I get:

class org.springframework.jdbc.BadSqlGrammarException StatementCallback; bad SQL grammar [UPDATE SCHEMA.TABLE SET STATE = 'ACTIVE' WHERE STATE = 'INACTIVE']; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

I want the query to execute regardless there are any records with 'INACTIVE' status, but I don't know what I'm missing/what to do to achieve this.

Thank you in advance!

Solution

  • I want the query to execute regardless there are any records with 'INACTIVE' status, but I don't know what I'm missing/what to do to achieve this.

    Your query does just that. But the table SCHEMA.TABLE does not exist or is not accessible by the db user executing that statement.

    There are actually a bunch of somewhat more involved scenarios where you get this exception. See SQL Error: ORA-00942 table or view does not exist