2

I was learning new feature of Oracle 12 C (Top-N Queries and Pagination). I tried few examples with offset and fetch keywords and it worked well when i use sql developer. I tried implementing the same with Spring jdbcTemplate and I see a strange behavior. Ex: Here is the code snippet:

// get first ten entities
String query = "SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next ? rows only";
SqlRowSet rowSet = this.jdbcTemplate.queryForRowSet(query, 1, 10);
while(rowSet.next()){
  //do something
}

Spring throws following exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next ? rows only]; nested exception is : ERROR: syntax error at or near "$2"

I tried few things same on postgres database but no luck, finally i thought of removing parameters and it worked when i remove third parameter and set a hard coded value in query string.ex:

"SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next 10 rows only"

Is it not allowed to use bind parameter in fetch clause?

Inzimam Tariq IT
  • 6,548
  • 8
  • 42
  • 69
Dharm
  • 73
  • 1
  • 12
  • Will not be possible to parameterize via java but can be accomplished via stored procedure. Follow the link http://stackoverflow.com/questions/12352471/getting-total-row-count-from-offset-fetch-next – mhasan Sep 21 '16 at 09:49
  • have you tryed to use named parameters? – Ralph Sep 21 '16 at 10:13
  • @mhasan: where does the question you mentioned state that it is not possible to use parameters in the fetch next term? – Ralph Sep 21 '16 at 10:15

1 Answers1

2

If you add () on the fetch parameter, it works fine:

String query = "SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next (?) rows only";
Nacho Soriano
  • 589
  • 5
  • 15