Invalid SQL generation, ORA-00907: missing right parenthesis

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
TheCoolest
Posts: 22
Joined: Tue 24 Mar 2020 11:02

Invalid SQL generation, ORA-00907: missing right parenthesis

Post by TheCoolest » Wed 03 Feb 2021 10:34

We are using version 9.14.1180 with the changes suggested here: viewtopic.php?f=1&t=44230#p178438

Code: Select all

public class Client
{
	public User User { get; private set; }
	public int UserId { get; private set; }

	public int ClientId { get; private set; }
	// ... Other fields...
}

public class User
{
	private readonly List<Client> _clientsList = new List<Client>();
	public IReadOnlyCollection<Client>  ClientsList => _clientsList;

	public int UserId { get; private set; }
	public string IsUserActive { get; private set; }
	// ... Other fields...
}
The selector code we had was inadvertently modified, and lead to the ORA-00907 exception.
This does not happen with MSSQL and PostgreSQL, so we believe this is a bug with dotConnect.

The original code, which works fine:

Code: Select all

var clients = dbContext.Clients.Select(client => new ClientDto()
            {
                Id = client.Id,
                // ... Other fields...
                IsUserActive = client.User.IsActive.ToUpper() == BoolUtils.Y,
                UserName = client.User == null ? string.Empty : StringUtils.Sanitize(client.User.Name),
            });
The code that does not work with dotConnect, but does work ok with other providers.

Code: Select all

var clients = dbContext.Clients.Select(client => new ClientDto()
            {
                Id = client.Id,
                // ... Other fields...
                IsUserActive = client.User == null ? false : client.User.IsActive.ToUpper() == BoolUtils.Y,
                UserName = client.User == null ? string.Empty : StringUtils.Sanitize(client.User.Name),
            });
Here are the respective SQL code.
Works:

Code: Select all

SELECT "c".CLIENT_ID, (CASE
    WHEN (UPPER("u".US_IS_ACTIVE) = 'Y') AND UPPER("u".US_IS_ACTIVE) IS NOT NULL THEN 1
    ELSE 0
END), "u".USER_NAME
FROM CLIENTS "c"
INNER JOIN USERS "u" ON "c".USER_ID = "u".USER_ID
ORA-00907:

Code: Select all

SELECT "c".CLIENT_ID, (CASE
    WHEN 0 = 1 THEN 0
    ELSE (UPPER("u".US_IS_ACTIVE) = 'Y') AND UPPER("u".US_IS_ACTIVE) IS NOT NULL
END), "u".USER_NAME
FROM CLIENTS "c"
INNER JOIN USERS "u" ON "c".USER_ID = "u".USER_ID
We have reverted the changes in our code, which were not required, but I thought it important to let you know of a potential issue.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Invalid SQL generation, ORA-00907: missing right parenthesis

Post by Shalex » Thu 04 Feb 2021 13:01

Thank you for your report. We have reproduced the issue and are investigating it.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Invalid SQL generation, ORA-00907: missing right parenthesis

Post by Shalex » Sat 20 Feb 2021 12:42

The bug with throwing ORA-00907, when using conditional operator within LINQ query, in EF Core 3 is fixed in dotConnect for Oracle v9.14.1204: viewtopic.php?f=1&t=44490.

Post Reply