Oracle missing expression with a where - query generated by SqlProvider

Question

I'm trying to query an Oracle database, where the connection is very slow, and the table's rowcount is very high in F# with SqlDataProvider. For my purpose I have to use IQueryable, so not all the rows are in memory.

The problem is when I add the where clause of the query it generates a select which is malformed. It's throwing 'ORA-00936: missing expression'.

I tried the same thing in MSSQL, and it just works.

type sql = SqlDataProvider<Common.DatabaseProviderTypes.ORACLE, "Data Source=localhost;PASSWORD=somePassword;USER ID=someUserId", ResolutionPath="C:\\Program Files (x86)\\Oracle Developer Tools for VS2017\\odp.net\\managed\\common\\">
let ctx = sql.GetDataContext()

type OrderDTO = { Id: decimal; SomeColumn: string }

[<EntryPoint>]
let main _ = 
    let someList =
        query {
            for order in ctx.Scheme.Tablea do
            // remove the where and it's working
            where (order.Id = 2M)
            take 1
            select { Id = order.Id; SomeColumn = order.SomeColumn }
        }
    // missing expression thrown next line
    let firstItem =
        someList
        |> Seq.head
    printfn "%A" firstItem
    0 // return an integer exit code

Installation details:

.NET Framework 4.7.1

<package id="FSharp.Core" version="4.6.2" targetFramework="net471" />
<package id="FSharp.Data" version="3.0.1" targetFramework="net471" />
<package id="SQLProvider" version="1.1.50" targetFramework="net471" />
<package id="System.ValueTuple" version="4.4.0" targetFramework="net471" />

At "C:\Program Files (x86)\Oracle Developer Tools for VS2017" there is a ODAC for VS 2017 18.3.0.0 installed.

Oracle version:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Table definition:

CREATE TABLE "SCHEME"."TABLEA"
( "ID" NUMBER(*,0),
"SOME_COLUMN" VARCHAR2(20 BYTE),
"SOME_COLUMN2" VARCHAR2(20 BYTE)
)

How can I fix the where? The generated query is the following:

select * from (SELECT order.ID as "ID",order.SOME_COLUMN as "SOME_COLUMN" FROM SCHEME.TABLEA order WHERE ((order.ID = :param1))) where ROWNUM <= 1 -- params :param1 - 2M;

Answer 1

To debug the query, hook SqlQueryEvent. Here's some code I used for that:

let logSqlEvent eventData =
    eventData
    |> sprintf "Executing SQL: %O"
    |> Debug.WriteLine

QueryEvents.SqlQueryEvent 
|> Event.add logSqlEvent

If your query works for SQL Server, but not Oracle, there's a good chance it's a bug. When I did some Access queries with SQLProvider a while back, I found bugs in the generated queries. I fixed them and submitted PRs - that's actually pretty easy to do.