You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Knex version: 3.1.0
Database + version: Oracle 19c EE
OS: Red Hat Enterprise Linux Server 7.9
Suggestion/improvement to cover Oracle bug (?)
When building a query using the limit() function, in Oracle it is translate to the clause "where rownum <= ?" (which is correct). But sometimes, inconsistently and for unknown reasons, Oracle raises the error "ORA-01722: invalid number".
This can be solved forcing rownum to be a number -> "where to_number(rownum) <= ?"
Error message: Error: select * from (select "COL1", "COL2", "COL3", "COL4", "COL5", "COL6" from "TABLE" order by "COL3" ASC) where rownum <= :1 - ORA-01722: invalid number
This is not a Knex bug, and for what I can see, it is not a consistent error in Oracle. I use the same limit() function with the same setup on other tables/query, and it works without problem. However, changing "where rownum <= ?" to "where to_number(rownum) <= ?" should cover all cases, also the special ones.
Knex version: 3.1.0
Database + version: Oracle 19c EE
OS: Red Hat Enterprise Linux Server 7.9
Suggestion/improvement to cover Oracle bug (?)
When building a query using the limit() function, in Oracle it is translate to the clause "where rownum <= ?" (which is correct). But sometimes, inconsistently and for unknown reasons, Oracle raises the error "ORA-01722: invalid number".
This can be solved forcing rownum to be a number -> "where to_number(rownum) <= ?"
Error message:
Error: select * from (select "COL1", "COL2", "COL3", "COL4", "COL5", "COL6" from "TABLE" order by "COL3" ASC) where rownum <= :1 - ORA-01722: invalid number
This is not a Knex bug, and for what I can see, it is not a consistent error in Oracle. I use the same limit() function with the same setup on other tables/query, and it works without problem. However, changing "where rownum <= ?" to "where to_number(rownum) <= ?" should cover all cases, also the special ones.
@atiertant
The text was updated successfully, but these errors were encountered: