Oracle ODP.NET parameters problem

Hello.

I trying to connect to Oracle using Oracle.ManagedDataAccess.dll.

There seems to be a problem with sequential same name parameters.

ODP12c.NET set in DataAbstract.daConfig has <ParameterHandling Type = “SequentialWithName” …

I changed it to “ByName” but it did not work.

I wonder what is mean “SequentialWithName” and how can use “ByName”.

I can’t find what is problem.

.Net framework 4.5
Visualstudio 2017
Remobjects Data Abstract for .NET 10.0.0.1457

case 1 : ORA-01008 error
SELECT *
FROM
(SELECT 1 “COL1”, 2 “COL2”
FROM DUAL)
WHERE COL1= :COL1
AND COL1 = :COL1
AND COL2 = :COL2

case 2 : success
SELECT *
FROM
(SELECT 1 “COL1”, 2 “COL2”
FROM DUAL)
WHERE COL1= :COL1
AND COL2 = :COL2
AND COL1 = :COL1

case 3 : success
SELECT *
FROM
(SELECT 1 “COL1”, 2 “COL2”
FROM DUAL)
WHERE COL2 = :COL2
AND COL1= :COL1
AND COL1 = :COL1

Hello

Oracle data provider binds parameters by position unless told otherwise via setting a vendor-specific property of the OracleCommand object ( BindByName )

  • “ByName” means that query parameter should be bound by their names.

  • “SequentialWithName” means that query parameter should be bound sequentially, but their names should be retained in the query.

  • “Sequential” means that query parameter should be bound sequentially, and their names are replaced by ?

Need to check is it possible to handle your case at all with the “SequentialWithName” binding mode.

Regards

Thanks, logged as bugs://83679

Hello

Unfortunately I was unable to reproduce the issue using Data Abstract 10.0.0.1466 + Oracle XE 18c + their latest Oracle.ManagedAccess driver. The statement

SELECT *
FROM
(SELECT 1 "COL1", 2 "COL2"
FROM DUAL)
WHERE COL1= :COL1
AND COL1 = :COL1
AND COL2 = :COL2

validates and executes without any issues.

If possible, please provide more details on how to reproduce the issue.

Thanks in advance

bugs://83679 got closed with status cannotrepro.

Sorry for the late reply.
I have attached a test sample.
Thank you.

VisualStudio 2017, Remobjects 10.0.0.1457, Oracle XE 18c

DAOracleParamTest.zip (3.8 MB)

bugs://83679 got reopened.

Hello

You should have mentioned that you use a custom database access code.

The approach you use is a very dangerous one and it is highly not recommended to use it.

Anyway, the only change required to make the method work is to add to the GetSqlData method code (DataService class in the server project) this code line

lConnection.RearrangeCommandParameters(lCommand);

right before the line

using (System.Data.IDataReader lReader = lCommand.ExecuteReader()) {

This method call allows Data Abstract to properly rebind query parameters if needed.

Regard

bugs://83679 got closed with status testcaseerr.