PetaPoco. Providers: When using Oracle, calling FetchAsync will throw exception

[jackzhang520]

PetaPoco. Providers: When using Oracle, calling FetchAsync will thow exception
SQL will start with "SELECT * FROM (SELECT ROW-NUMBER() OVER (ORDER BY (SELECT NULL)) peta_ Rn", but in ORACLE, "SELECT NULL" will prompt ORA-00923: The required FROM keyword was not found. After reading PetaPoco's source code, the BuildPageQuery of OracleDatabaseProvider will be redirected to the BuildPageQuery of SqlServerDatabaseProvider, causing this issue.

Change "SELECT NULL" to " SELECT NULL from DUAL" can fix this issue

[Ste1io]

@jackzhang520 is there a specific test that fails from this? I'm currently updating the docker configuration for the test projects, and can look into this once that's done.

[jackzhang520]

@Ste1io To solve this problem, it is necessary to modify PetaPoco.Providers.OracleDatabaseProvider like this:

[jackzhang520]

Please test by
Dababase: Oracle
Method: PetaPoco.IQueryAsync.PageAsync
SqlOrderBy=null

[Ste1io]

Testing anything for Oracle will have to wait until I complete #697 and merge it into the Development branch. It looks like you have already found a fix for this issue, based on your screenshots. Could you open a PR with your changes, so we have something ready once Oracle tests are added?

[jackzhang520]

@Ste1io
Oracle中不能直接将SELECT NULL作为SQL输出NULL，需要使用SELECT NULL FROM DUAL，这是它与其它数据库不同的地方。
In Oracle, SELECT NULL cannot be directly used as SQL output NULL. Instead, SELECT NULL from DUAL needs to be used, which is different from other databases.

see https://www.databasestar.com/dual-table-in-oracle/
The DUAL table is a one row, one column, dummy table used by Oracle.
SELECT statements need a table, and if you don’t need one for your query, you can use the DUAL table
Don’t modify or delete the DUAL table.