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;

I don't know tools you use, but - if it is about the WHERE clause, try to use single quotes (as dealing with a string): `where (order.Id = '2M')` — Littlefoot, Apr 11 '19 at 11:40
Thank you for tip. I'm using f#'s type providers. To be more specific the `SQLProvider`, which is strongly typed. The provider does it's ORM thing, and the `order.Id` in the where clause is already `decimal`, so I can't write `string` there — ntohl, Apr 11 '19 at 11:54
Sorry, it's just that `2M` looked like a string to me (still does, to be honest). — Littlefoot, Apr 11 '19 at 11:56
Oh. That's just decimal literal. https://stackoverflow.com/questions/977484/what-does-the-m-stand-for-in-c-sharp-decimal-literal-notation — ntohl, Apr 11 '19 at 12:02
Aha. I see; thank you. I'm afraid I can't help. Hopefully, someone else will be able to assist. Good luck! — Littlefoot, Apr 11 '19 at 12:17

score 1 · Accepted Answer · answered Apr 11 '19 at 13:40

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.

answered Apr 11 '19 at 13:40

Jim Foye

1,918
1
13
15

yes. I finally was able to debug the query with `FSharp.Data.Sql.Common.QueryEvents.SqlQueryEvent |> Event.add (printfn "Executing SQL: %O")` . Now I see it's generating the `;` after the `--`. So the closing of the select is commented out – ntohl Apr 11 '19 at 13:45

score 0 · Answer 2 · answered Apr 11 '19 at 14:07

The problem is related to the following Issue: https://github.com/fsprojects/SQLProvider/issues/595

I used the order keyword in the query. Seemingly the problem is because the generated query contained a comment, which after the semicolon came, but it's not problem.

Oracle missing expression with a where - query generated by SqlProvider

2 Answers2