ORA-01747: invalid user.table.column, table.column, or column specification

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Velu
Posts: 19
Joined: Wed 02 Aug 2017 14:01

ORA-01747: invalid user.table.column, table.column, or column specification

Post by Velu » Tue 08 Aug 2017 11:50

Hi,
We are using .netCore Entityframework (Code First).

1) We migrate database using -update-database command working fine.
but we don't want to case sensitivity in oracle, Thats why we use config.Workarounds.DisableQuoting = true;

2) After applying this given error occured in following code block.
var query = _ClientRepository.GetAll()
.WhereIf(!input.Filter.IsNullOrWhiteSpace(),
u =>
u.Name.Contains(input.Filter) ||
u.Surname.Contains(input.Filter)
);

var Pagedcount = await query.CountAsync();

var Client = await query
.OrderBy(input.Sorting)
.PageBy(input)
.ToListAsync();


Thanks.

Velu
Posts: 19
Joined: Wed 02 Aug 2017 14:01

Re: ORA-01747: invalid user.table.column, table.column, or column specification

Post by Velu » Tue 08 Aug 2017 13:51

Hi,

We Extract Query Created by this block of code:

Code: Select all

var Client = await query
.OrderBy(input.Sorting)
.PageBy(input)
.ToListAsync();
SELECT t.Id, t.CreationTime, t.CreatorUserId, t.DOB, t.DeleterUserId, t.DeletionTime, t.EmailAddress, t.IsDeleted, t.LastModificationTime, t.LastModifierUserId, t.Mobile, t.Name, t.Surname
FROM (
SELECT e.Id, e.CreationTime, e.CreatorUserId, e.DOB, e.DeleterUserId, e.DeletionTime, e.EmailAddress, e.IsDeleted,
e.LastModificationTime, e.LastModifierUserId, e.Mobile, e.Name, e.Surname, ROWNUM
FROM Client e
WHERE e.IsDeleted = 0
ORDER BY e.Name
) t
WHERE (t.ROWNUM > :p__p_0) AND (t.ROWNUM <= (:p__p_0 + :p__p_1))
And this query return Error:
ORA-01747: invalid user.table.column, table.column, or column specification

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

Re: ORA-01747: invalid user.table.column, table.column, or column specification

Post by Shalex » Wed 09 Aug 2017 15:40

Its not clear how your repository is implemented. Please upload a small test project with the corresponding DDL script to ftp://ftp.devart.com (credentials: anonymous / yourEmail).

Velu
Posts: 19
Joined: Wed 02 Aug 2017 14:01

Re: ORA-01747: invalid user.table.column, table.column, or column specification

Post by Velu » Thu 10 Aug 2017 07:10

Hi,

We are make some changes in Query, Its working.

Code: Select all

SELECT t.Id, t.CreationTime, t.CreatorUserId, t.DOB, t.DeleterUserId, t.DeletionTime, t.EmailAddress, t.IsDeleted, t.LastModificationTime, t.LastModifierUserId, t.Mobile, t.Name, t.Surname 
FROM (
SELECT e.Id, e.CreationTime, e.CreatorUserId, e.DOB, e.DeleterUserId, e.DeletionTime, e.EmailAddress, e.IsDeleted, 
e.LastModificationTime, e.LastModifierUserId, e.Mobile, e.Name, e.Surname, ROWNUM R
FROM Client e
WHERE e.IsDeleted = 0
ORDER BY e.Name
) t
WHERE (t.R > 0) AND (t.R <= (0 + 1))
We are sending application on given FTP.

Thanks.

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

Re: ORA-01747: invalid user.table.column, table.column, or column specification

Post by Shalex » Thu 10 Aug 2017 15:12

Thank you for the test project. We have reproduced the problem and are investigating it. We will notify you about the result as soon as possible.

Velu
Posts: 19
Joined: Wed 02 Aug 2017 14:01

Re: ORA-01747: invalid user.table.column, table.column, or column specification

Post by Velu » Wed 16 Aug 2017 07:38

HI,

We are waiting for your reply.

Thanks.

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

Re: ORA-01747: invalid user.table.column, table.column, or column specification

Post by Shalex » Wed 16 Aug 2017 14:14

The bug with throwing ORA-01747 by LINQ query in EF Core is fixed. We will notify you when the new public version is available. A current workaround is provided by email.

Devart
Site Admin
Posts: 3276
Joined: Tue 26 Oct 2004 13:51

Re: ORA-01747: invalid user.table.column, table.column, or column specification

Post by Devart » Fri 08 Sep 2017 11:00

New build of dotConnect for Oracle 9.4.348 is available for download now: viewtopic.php?f=1&t=35921.

Post Reply