where clause queryString giving SQL Error: 920, SQLState: 42000 in Oracle DB

Issue

I have below code trying to fetch some data from Oracle DB tableName based on post request sent.

I form query queryString using all post request inputs by appending over a loop, then send this queryString as whereClause parameter to Query in repository class.

POST REQUEST:

{
  "list": ["002-02-0284","029-00-0100"]
}

@PostMapping(“/fetch”)
fun sync(@RequestBody @Valid DataRequest: request): ResponseEntity<SyncResponse> {

   val list = request.list

   return ResponseEntity(service.getContainers(list), HttpStatus.ACCEPTED)
}


  fun getContainers(list: List<String>){

  val conditions = mutableListOf<String>()

  for (item in list) {
    val values = extractValues(item)
    val departmentId = values[0]
    val classId = values[1]
    val itemId = values[2]
    conditions.add("(DEPT_I = :departmentId AND CLASS_I = :classId AND ITEM_I = :itemId)")
  }

   val whereClause = conditions.joinToString(" OR ")

   println("--< whereClause --> $whereClause")

   val responseList = service.getContainers(whereClause)
 }

  fun extractValues(item: String): Array<Int> {
     return item.split("-").map { it.toInt() }.toTypedArray()
  }

  fun getContainers(whereClause: String): List<yourClass> {
    return repository.getContainers(queryString)
  }

@Query(
nativeQuery = true,
value = "SELECT PALT_I " +
    "FROM tableName " +
    "WHERE (:whereClause) AND STAT_C NOT IN ('PL', 'CN') " +
    "ORDER BY PALT_I "
  )
  fun getContainers(
  @param("whereClause") whereClause: String): List<tableName>

I am seeing error as below

Hibernate: SELECT PALT_I, SIZE_C, AREA_C, DEPT_I, CLASS_I, ITEM_I, AISLE_I, BIN_I, LVL_I, PALT_UNIT_Q, AVAIL_PALT_UNIT_Q, PALT_PEND_POST_Q,PALT_PEND_PULL_Q, PALT_PEND_PTWY_Q, PULL_PALT_UNIT_Q, VCP_Q, SSP_Q, PALT_Q, CTN_PER_PALT_Q, EXPIRE_D, LOT_I, HOLD_STAT_C, STAT_C, MODF_USER_ID, MODF_PGM_N, MODF_TS FROM RSV_PALT WHERE (?) AND STAT_C NOT IN ('PL', 'CN') ORDER BY PALT_I

Hibernate: select inventoryi0_.container_id as container_id1_6_0_, inventoryi0_.update_timestamp as update_timestamp2_6_0_ from inventory_in_progress inventoryi0_ where inventoryi0_.container_id=? Hibernate: select inventoryi0_.container_id as container_id1_6_0_, inventoryi0_.update_timestamp as update_timestamp2_6_0_ from inventory_in_progress inventoryi0_ where inventoryi0_.container_id=?

[WARN] 2023-11-22 11:25:42,993 http-nio-8080-exec-1 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - {X-API-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, X-CORRELATION-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, team=Ole} - SQL Error: 920, SQLState: 42000

[ERROR] 2023-11-22 11:25:42,994 http-nio-8080-exec-1 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - {X-API-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, X-CORRELATION-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, team=Ole} - ORA-00920: invalid relational operator

any help is greatly appreciated. Thank you.

Solution

You define the query using:

@Query(
  nativeQuery = true,
  value = "SELECT PALT_I " +
          "FROM RSV_PALT " +
          "WHERE (:whereClause) AND STAT_C NOT IN ('PL', 'CN') " +
          "ORDER BY PALT_I "
)
fun getContainers(
  @Param("whereClause") whereClause: String
): List<TableName>

The bind variable :whereClause is a placeholder for a single scalar value which is passed directly to the database and is not used to build the query as part of a template string.

Therefore, when the database evaluates the string it is the equivalent of evaluating:

SELECT PALT_I
FROM   RSV_PALT
WHERE  ('some text')
AND    STAT_C NOT IN ('PL', 'CN')
ORDER BY PALT_I

and WHERE ('some text') is an invalid SQL expression as it is missing a relational operator because the database is expecting WHERE ('some text' = 'some other text').

To fix it you need to generate the dynamic query text as part of the string and use bind variables to pass only values into the query and not trying to add the dynamic part of the query using bind variables.

Something like:

@Query(
  nativeQuery = true,
  value = "SELECT PALT_I " +
          "FROM   RSV_PALT " +
          "WHERE  (DEPT_I = :departmentId OR :departmentId IS NULL) " +
          "AND    (CLASS_I = :classId OR :classId IS NULL) " +
          "AND    (ITEM_I = :itemId OR :itemId IS NULL) "+
          "AND    STAT_C NOT IN ('PL', 'CN') " +
          "ORDER BY PALT_I "
)

and then always pass departmentId, classId and itemId into the query but pass NULL values when you do not want to filter on them.

Answered By - MT0