英文:
quill (scala library) generate query that correct for Oracle 12, but not for Oracle 11
问题
我必须使用两个不同的数据库：
Oracle 11.2.0.3.0
Oracle 12.2.0.1.0
使用 quill-jdbc-zio 4.6.0（以及 zio 2.0.12 和 scala 2.13.10），我发现 quill 生成的查询中使用了 FETCH FIRST 2 ROWS ONLY，这对 Oracle 11 不适用。
此查询在 Oracle 12 中成功执行，但在 Oracle 11 中会引发 ORA-00933：SQL 命令未正确结束错误。
我可以在 11 和 12 上使用不同的 quill 设置吗？或者我可以配置数据源？
我想对于 Oracle 11，必须生成类似于以下内容的东西：
select id, name, age
from (select a.*, rownum rnum
from (
---------------------
SELECT p.id, p.name, p.age FROM person p ORDER BY p.id ASC NULLS FIRST
---------------------
) a
where rownum <= 2)
where rnum >= 1
英文:
I must work with 2 different databases:
Oracle 11.2.0.3.0
Oracle 12.2.0.1.0
Using quill-jdbc-zio 4.6.0 (and zio 2.0.12 scala 2.13.10)
I found that quill generate queries with using FETCH FIRST 2 ROWS ONLY that is no applicabe to Oracle 11.
import io.getquill._
val ctx = new SqlMirrorContext(OracleDialect, SnakeCase)
import ctx._
case class Person(id: Int, name: String, age: Int)
val m = ctx.run(query[Person].sortBy(p => p.id).take(2))
println(m.string)
output
SELECT p.id, p.name, p.age FROM person p ORDER BY p.id ASC NULLS FIRST FETCH FIRST 2 ROWS ONLY
This query is successful executing in Oracle 12 and raise ORA-00933: SQL command not properly ended in Oracle 11.
Can I use different settings of quill for 11 and 12?
Or can I configure datasource?
I suppose for Oracle 11 it must be generated something like this:
select id, name, age
from ( select a.*, rownum rnum
from (
---------------------
SELECT p.id, p.name, p.age FROM person p ORDER BY p.id ASC NULLS FIRST
---------------------
) a
where rownum <= 2)
where rnum >= 1
答案1
得分: 3
很显然不支持Oracle 11及以下版本。
英文:
Apparently no.
From the Quill Contexts documentation:
> ### Oracle (quill-jdbc)
>
> Quill supports Oracle version 12c and up although due to licensing restrictions, version 18c XE is used for testing.
and:
> ### Oracle (quill-jdbc-zio)
>
> Quill supports Oracle version 12c and up although due to licensing restrictions, version 18c XE is used for testing.
and:
> ### Oracle (quill-jdbc-monix)
>
> Quill supports Oracle version 12c and up although due to licensing restrictions, version 18c XE is used for testing.
It would appear that Quill does not support Oracle 11 and below.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论