ORA-00907 missing right parenthesis on Oracle 12

ORA-00907 missing right parenthesis on Oracle 12

Postby rohlicher » Fri 30 Oct 2015 16:04

Hi,

The LINQ statement below works fine with an Oracle 11 database but fails on an Oracle 12 database, a "missing right parenthesis" Oracle error is thrown.

Oracle exception:
ORA-00907 missing right parenthesis

Code sample:
Code: Select all
const string id = "some id";
DateTime date = DateTime.Today;
var pricesQuery = database.SdOtcFxOptionSet
  .Where(sdOtcFxOption => sdOtcFxOption.Id == id && sdOtcFxOption.ValuationDate.Value <= date);

var askPricesQuery = pricesQuery
  .Where(price => price.BaseMktPricePercAsk != null)
  .Select(price => new SearchResult
  {
    PriceDate = price.ValuationDate,
    PriceAsk = price.BaseMktPricePercAsk,
    PriceBid = null
  })
  .OrderByDescending(price => price.PriceDate)
  .Take(1);

var bidPricesQuery = pricesQuery
  .Where(price => price.BaseMktPricePercBid != null)
  .Select(price => new SearchResult
  {
    PriceDate = price.ValuationDate,
    PriceAsk = null,
    PriceBid = price.BaseMktPricePercBid
  })
  .OrderByDescending(price => price.PriceDate)
  .Take(1);

var allPricesQuery = askPricesQuery.Concat(bidPricesQuery);
var result = allPricesQuery.ToList();


The .Concat() method (close to the end of the code sample above) results in an UNION statement which is different from a "paranthesis" POV on Oracle 11 and Oracle 12.

Can you please investigate this issue?

Thanks,
robert
rohlicher
 
Posts: 3
Joined: Fri 30 Oct 2015 15:52

Re: ORA-00907 missing right parenthesis on Oracle 12

Postby Shalex » Mon 02 Nov 2015 08:36

Please give us the following information:
a) your ORM (Entity Framework or LinqConnect)
b) a generated SQL which fails to execute (turn on dbMonitor)
c) send us a small test project with the corresponding DDL script
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00907 missing right parenthesis on Oracle 12

Postby rohlicher » Mon 02 Nov 2015 11:11

Hello,

We are using EntityFramework 6.1.3 and Devart 8.5.506.

Generated queries:
/* Oracle 11 */
Code: Select all
SELECT
"UnionAll1".C1,
"UnionAll1".C2,
"UnionAll1".VALUATION_DATE AS C3,
"UnionAll1".BASE_MKT_PRICE_PERC_ASK AS C4,
"UnionAll1".C3 AS C5
FROM  (SELECT
   "top".C3 AS C1,
   "top".C1 AS C2,
   "top".VALUATION_DATE,
   "top".BASE_MKT_PRICE_PERC_ASK,
   "top".C2 AS C3
   FROM ( SELECT
      "Project1".VALUATION_DATE,
      "Project1".BASE_MKT_PRICE_PERC_ASK,
      "Project1".C1,
      "Project1".C2,
      "Project1".C3
      FROM ( SELECT
         "Extent1".VALUATION_DATE,
         "Extent1".BASE_MKT_PRICE_PERC_ASK,
         :p__linq__3 AS C1,
         TO_NUMBER(NULL) AS C2,
         1 AS C3
         FROM SD_OTC_FX_OPTION "Extent1"
         WHERE (("Extent1".GAIN_ID = :p__linq__0) AND ((:p__linq__1 IS NULL) OR ("Extent1".VALUATION_DATE <= :p__linq__2))) AND ("Extent1".BASE_MKT_PRICE_PERC_ASK IS NOT NULL)
      )  "Project1"
      ORDER BY "Project1".VALUATION_DATE DESC
   )  "top"
   WHERE  ROWNUM <= 1
UNION ALL
   SELECT
   "top".C3 AS C1,
   "top".C1 AS C2,
   "top".VALUATION_DATE,
   "top".C2 AS C3,
   "top".BASE_MKT_PRICE_PERC_BID
   FROM ( SELECT
      "Project3".VALUATION_DATE,
      "Project3".BASE_MKT_PRICE_PERC_BID,
      "Project3".C1,
      "Project3".C2,
      "Project3".C3
      FROM ( SELECT
         "Extent2".VALUATION_DATE,
         "Extent2".BASE_MKT_PRICE_PERC_BID,
         :p__linq__7 AS C1,
         TO_NUMBER(NULL) AS C2,
         1 AS C3
         FROM SD_OTC_FX_OPTION "Extent2"
         WHERE (("Extent2".GAIN_ID = :p__linq__4) AND ((:p__linq__5 IS NULL) OR ("Extent2".VALUATION_DATE <= :p__linq__6))) AND ("Extent2".BASE_MKT_PRICE_PERC_BID IS NOT NULL)
      )  "Project3"
      ORDER BY "Project3".VALUATION_DATE DESC
   )  "top"
   WHERE  ROWNUM <= 1) "UnionAll1"


/* Oracle 12 */
Code: Select all
SELECT
"UnionAll1".C1,
"UnionAll1".C2,
"UnionAll1".VALUATION_DATE AS C3,
"UnionAll1".BASE_MKT_PRICE_PERC_ASK AS C4,
"UnionAll1".C3 AS C5
FROM  (SELECT
   "Project1".C3 AS C1,
   "Project1".C1 AS C2,
   "Project1".VALUATION_DATE,
   "Project1".BASE_MKT_PRICE_PERC_ASK,
   "Project1".C2 AS C3
   FROM ( SELECT
      "Extent1".VALUATION_DATE,
      "Extent1".BASE_MKT_PRICE_PERC_ASK,
      :p__linq__3 AS C1,
      TO_NUMBER(NULL) AS C2,
      1 AS C3
      FROM SD_OTC_FX_OPTION "Extent1"
      WHERE (("Extent1".GAIN_ID = :p__linq__0) AND ((:p__linq__1 IS NULL) OR ("Extent1".VALUATION_DATE <= :p__linq__2))) AND ("Extent1".BASE_MKT_PRICE_PERC_ASK IS NOT NULL)
   )  "Project1"
   ORDER BY "Project1".VALUATION_DATE DESC
   FETCH FIRST 1 ROWS ONLY
UNION ALL
   SELECT
   "Project3".C3 AS C1,
   "Project3".C1 AS C2,
   "Project3".VALUATION_DATE,
   "Project3".C2 AS C3,
   "Project3".BASE_MKT_PRICE_PERC_BID
   FROM ( SELECT
      "Extent2".VALUATION_DATE,
      "Extent2".BASE_MKT_PRICE_PERC_BID,
      :p__linq__7 AS C1,
      TO_NUMBER(NULL) AS C2,
      1 AS C3
      FROM SD_OTC_FX_OPTION "Extent2"
      WHERE (("Extent2".GAIN_ID = :p__linq__4) AND ((:p__linq__5 IS NULL) OR ("Extent2".VALUATION_DATE <= :p__linq__6))) AND ("Extent2".BASE_MKT_PRICE_PERC_BID IS NOT NULL)
   )  "Project3"
   ORDER BY "Project3".VALUATION_DATE DESC
   FETCH FIRST 1 ROWS ONLY) "UnionAll1"


I'll try to come up with a sample but I'm curious why you also need a sample project, I mentioned the used LINQ statement in the first forum entry and now you have the resulting queries.

Regards,
robert
rohlicher
 
Posts: 3
Joined: Fri 30 Oct 2015 15:52

Re: ORA-00907 missing right parenthesis on Oracle 12

Postby Shalex » Mon 02 Nov 2015 13:33

rohlicher wrote:I'll try to come up with a sample but I'm curious why you also need a sample project, I mentioned the used LINQ statement in the first forum entry and now you have the resulting queries.

We need the model to run your code with.
The corresponding DDL script is not necessary (we will run Update To Database wizard in Entity Developer with your model).
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00907 missing right parenthesis on Oracle 12

Postby rohlicher » Mon 02 Nov 2015 13:43

Sample created and sent as requested.

Regards,
robert
rohlicher
 
Posts: 3
Joined: Fri 30 Oct 2015 15:52

Re: ORA-00907 missing right parenthesis on Oracle 12

Postby Shalex » Tue 03 Nov 2015 13:02

We have reproduced the issue and are investigating it. We will notify you when the bug is fixed.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00907 missing right parenthesis on Oracle 12

Postby Shalex » Wed 04 Nov 2015 10:45

The bug with using extension methods Take and Skip with Concat/Except/Intersect when talking to Oracle 12c is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00907 missing right parenthesis on Oracle 12

Postby Shalex » Thu 12 Nov 2015 16:59

The new build of dotConnect for Oracle 8.5.535 is available for download now: http://forums.devart.com/viewtopic.php?f=1&t=32778.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle