Nested exception - java.sql.SQLException: Invalid column name ORACLE

Question

I tried to execute the following oracle query using JdbcTemplate in Java:

select RESOURCE_ID from REPRO_PRINTING_JOB where (USER_ID=? and PRINTING_CENTER_ID=?) group by RESOURCE_ID union all select RESOURCE_ID from REPRO_PRINTING_JOB_OLD where (USER_ID=? and PRINTING_CENTER_ID=? and STATE='CM') group by RESOURCE_ID 

The query works fine in the oracle query browser, but does not work at run time in java. What could be the source of this problem? I heard something about Jdbc not being able to handle case. It's true?

ADDED JAVA CODE (FIXED): I call the request using getStatement (), which retrieves the request from an external source (properties file) in the form:

  public List<PrintingJob> getPrintingJobsByCreatedUserId(String createdUserId, String userId) { if(log.isDebugEnabled()) { log.debug("getReportItemsByUserId(" + createdUserId + "," + userId + ")"); } try { System.out.println("getPrintingJobsByResourceId : createdUserId :"+createdUserId+",userId : "+userId); return getJdbcTemplate().query(getStatement("gen.report.userid"), new Object[]{createdUserId, userId, createdUserId, userId}, new PrintingJobMapper()); } catch (DataAccessException ex) { log.error("Error executing query: " + ex.getClass() + ":" + ex.getMessage()); return null; } } 

Answer 1

The reason you get this error is because it is an error from the JDBC driver or java.sql API, and not from the database. Note the absence of ORA-XXXXX in the exception message. If it contains this, then it will be due to syntax, an invalid name, or something that will go wrong on the server side of the database.

SQLExceptions are set in java for everything related to the SQL api, and not only from errors that occur when sending statements over the connection ... In this case, most likely the query really worked correctly, but the API result set gives you errors (rs. getLong ("blah"), where the "blah" column does not exist in your choice) causes problems.