JDBC Template exception table or view does not exist but it actually exists

Question

I'm trying to get some data from Oracle DB using Spring JDBCTemplate:

String query = "SELECT * FROM snow.ar_incident WHERE ROWNUM < 10";

    Map<String, List<Attachment>> map = jdbcTemplate.query(query, new ResultSetExtractor<Map<String, List<Attachment>>>() {

        @Override
        public Map<String, List<Attachment>> extractData(ResultSet rs) throws SQLException, DataAccessException {
            Map<String, List<Attachment>> map = new HashMap<>();
            //Mapping results to map
            return map;
        }
    });

But I'm always getting an exception only for ar_incidient table:

Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT * from snow.ar_incident WHERE ROWNUM < 10]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

This code works perfectly fine for other tables but not for this one. I also tried to get data from this table using core Java sql connection:

Class.forName("oracle.jdbc.driver.OracleDriver");  
Connection con = DriverManager.getConnection(connString, user, pass);    
Statement stmt=con.createStatement();   
ResultSet rs = stmt.executeQuery("SELECT * from snow.ar_incident WHERE ROWNUM < 10");

And it worked without a problem, same when I run the query in SQL Developer. I have checked many times connection details for the both solutions and they are identical. Why can't I access ar_incident table using JDBCTemplate?

does it throws error even if you simplify the query into SELECT * FROM ar_incident ? — Leviand
JdbcTemplate does the same as you do. Also the error comes from the driver not Spring. So either the table/view really doesn't exists or you don't have the correct privileges or you are not looking at the database you think you are looking at. — M. Deinum

Rishikesh Fanse Rishikesh Fanse · Accepted Answer · 2020-08-20T12:23:43

1

votes

Please check if grants are there and if you need to prefix table name with schema.table_name.

JDBC Template exception table or view does not exist but it actually exists

3 Answers