Spring PreparedStatementCallback; uncategorized SQLException for SQL Invalid Column Type Oracle

Question

I have migrated mysql database to oracle. When I used sql query with modification as with rownum as following,

SELECT id, frameTypeId, ownerId, locationId FROM 
    (SELECT id, vv_frame_type_id AS
    frameTypeId, vv_owner_id AS ownerId, vv_location_id AS locationId, rownum AS rn 
    FROM signage) 
WHERE rn BETWEEN ? AND ?

I get the result when I run the query against oracle database (parameters provided). But when I try to maven install the project, I get the following error.

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
    ... 53 more
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select id, frameTypeId, ownerId, locationId from ( select id, vv_frame_type_id as frameTypeId, vv_owner_id as ownerId, vv_location_id as locationId, rownum as rn from vivo_signage) where rn between ? and ?]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
    at 
Caused by: java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8761)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8259)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9012)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8993)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
    at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:641)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
    ... 66 more

Answer 1

You can you try creating the query as a view, and select from that

CREATE VIEW v_frames AS
SELECT id, frameTypeId, ownerId, locationId, rn 
FROM      
    (
    SELECT 
        id, 
        vv_frame_type_id AS frameTypeId, 
        vv_owner_id AS ownerId, 
        vv_location_id AS locationId, 
        rownum AS rn      
    FROM signage)