SQL command not properly ended-CodePudding

I get this error in the spring boot application for the below query:

SELECT * 
FROM ACTIONS 
WHERE (var1 LIKE %:query% OR var2 LIKE %:query%) AND (ACTION IN :actions OR :length = 0) AND (code = :code)

how can I solve it?

This query was working fine, but today it is broken

CodePudding user response：

If you are using a native SQL statement (and not something that you are running through a pre-processor) then you need to concatenate the % literal (which needs to be '%') with your bind variables (and using ACTION IN :actions is the same as ACTION = :actions as a bind variable is a singular value):

SELECT * 
FROM   ACTIONS 
WHERE  (var1 LIKE '%' || :query || '%' OR var2 LIKE '%' || :query || '%')
AND    (ACTION = :actions OR :length = 0)
AND    (code = :code)

However, if :actions is supposed to be a delimited list then that will not work and you need to match on sub-string (with surrounding delimiters). So if you are passing a comma-delimited list:

SELECT * 
FROM   ACTIONS 
WHERE  (var1 LIKE '%' || :query || '%' OR var2 LIKE '%' || :query || '%')
AND    (','||:actions||',' LIKE '%,'||ACTION||',%' OR :length = 0)
AND    (code = :code)

CodePudding user response：

after I checked, I discovered that my oracle DB version is old but my application driver is up to date for this reason it didn't support the pageable. after of update my DB my query has worked very well.

pageable is for version 12 and above.