The JDBC storage plugin failed while trying setup the SQL query- Oracle

1

I get “The JDBC storage plugin failed while trying setup the SQL query” error when I join 2 datasets, one oracle and one SQL server. Looking into the header.json profile I can see an error (java.sql.SQLSyntaxErrorException) ORA-00902: invalid datatype. The query generated is

SELECT
CAST(FLOOR(“WELL_CODE”) AS INTEGER) “WELL_CODE”
, CAST(CAST(FLOOR(“WELL_CODE”) AS INTEGER) AS BIGINT) “WELL_CODE0”
FROM (SELECT * FROM “XXXXX”.“XXXXX_DETAILS” WHERE ROWNUM <= 2000)

BIGINT is not a dataType in Oracle hence the error. The only change I’ve made to dataset is a data conversion of WELL_CODE from float\real to integer using the UI to generate the SQL.

Is this a bug or am I missing something?

Sorry if this is a simple question, this is my first attempt at a virtual dataset outside of the training.

2

Hi there,

Which version of Dremio are you using? As of Dremio 3.1, we have moved the Oracle source into a new framework and this error may no longer occur. When creating or editing Oracle as a source in 3.1, under the Advanced options there will be a checkbox for “Enable legacy dialect”. If this is checked, can you uncheck this and try the query again? If this is already unchecked, please let us know and we’ll attempt to replicate.

3

Originally I was running 3.0 community. Have now upgraded to 3.1 community. Same issue occurred in 3.1 but did not occur in 3.1 when the “Enable legacy dialect” option was turned off.

From what I can see the SQL changed from CAST(CAST(FLOOR(“WELL_CODE”) AS INTEGER) AS BIGINT) which fails to CAST(CAST(FLOOR(“WELL_CODE”) AS NUMBER) AS NUMBER) that works.

I can’t find any help on what “Enable legacy dialect” actually does. Do you have any further information? My Oracle DB’s are all Oracle 11 and above so I assume I should turn “Enable legacy dialect” option off for all my Oracle connections.

Thanks for helping to resolve my issue

4

I am getting similar error when joining two datasets/tables from Oracle. I am using latest version 3.1 (Linux tarball).

It looks like the aliases on the JDBC query are “misaligned” to the actual columns. Maybe something to do with the query including the ROWNUM column aliased as ORA_RNUMOFFSET$.

Here is excerpt sample of the query that was generated to illustrate:

    select
    "POLICY_TERM"."ORA_RNUMOFFSET$" "ID"
    , "POLICY_TERM"."ID" "POLICY_NUM"
    , "POLICY_TERM"."POLICY_NUM" "EFFECTIVE_DATE"
    , "POLICY_TERM"."EFFECTIVE_DATE" "EXPIRATION_DATE"
    , "POLICY_TERM"."EXPIRATION_DATE" "COVA_LIMIT"
    , "POLICY_TERM"."COVA_LIMIT" "COVB_LIMIT"
    , "POLICY_TERM"."COVB_LIMIT" "COVC_LIMIT"
    , "POLICY_TERM"."COVC_LIMIT" "COVD_LIMIT"
    , "POLICY_TERM"."COVD_LIMIT" "COVE_LIMIT"
    , "POLICY_TERM"."COVE_LIMIT" "COVF_LIMIT"
    ... it goes on like this
    , "POLICY_TERM"."OVMF_OVERALL" "ASSOCIATE_ID"
    , "POLICY_TERM"."HOLD_FLAG" "POLICY_ID"
    , "POLICY_TERM"."ASSOCIATE_ID" "ID0"
    , "POLICY"."ORA_RNUMOFFSET$" "POLICY_NUM0"
    , "POLICY"."ID" "BILL_ID"
    , "POLICY"."POLICY_NUM" "RENEWAL_TYPE_ID"
    , "POLICY"."BILL_ID" "INCEPTION_DATE"
    , "POLICY"."RENEWAL_TYPE_ID" "TERMINATION_DATE"
    , "POLICY"."INCEPTION_DATE" "ACCOUNTING_STATUS_CODE"
    , "POLICY"."TERMINATION_DATE" "STATUS_ID0"
    ... goes on some more
   from
    (
        select ROWNUM "ORA_RNUMOFFSET$"
        , "POLICY_TERM"."ID"
        , "POLICY_TERM"."POLICY_NUM"
        , "POLICY_TERM"."EFFECTIVE_DATE"
        , "POLICY_TERM"."EXPIRATION_DATE"
... and some more
        from
        "REPORTADMIN"."POLICY_TERM"
    where
        ROWNUM <= (2000 + 0)) "POLICY_TERM"
where
    "POLICY_TERM"."ORA_RNUMOFFSET$" > 0) "POLICY_TERM"
... and more
    from
    (
        select ROWNUM "ORA_RNUMOFFSET$"
        , "POLICY"."ID"
        , "POLICY"."POLICY_NUM"
        , "POLICY"."BILL_ID"
        , "POLICY"."RENEWAL_TYPE_ID"
.... and ....
         from
        "RATERADMIN"."POLICY"
        where
        ROWNUM <= (2000 + 0)) "POLICY"
where
    "POLICY"."ORA_RNUMOFFSET$" > 0) "POLICY" on
"POLICY_TERM"."HOLD_FLAG" = "POLICY"."ORA_RNUMOFFSET$"

The actual error thrown is coming from that last WHERE portion

(java.sql.SQLSyntaxErrorException) ORA-00904: "POLICY"."ORA_RNUMOFFSET$": invalid identifier

Attached query profile

485f9fe0-34cf-4d79-9e25-75696184f367.zip (35.4 KB)

5

As an update to my prior comment. That error occured with the Enable legacy dialect unchecked.

I just changed my Oracle connection setting and checked Enable legacy dialect. I then get no error when running same query as before.

So problem appears to be in the implementation of the new framework.

6

One more piece of info. I tried out with a MySQL database and joining worked fine with Enable legacy dialect unchecked. So error seems specific to Oracle at least.

7

Hi @mpcarter

This is a known issue and we are trying to address this within the next couple of minor releases

Thanks
@balaji.ramaswamy