Unicode property breaks procedure call

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
MattKnowles
Posts: 5
Joined: Tue 26 Jun 2018 16:46

Unicode property breaks procedure call

Post by MattKnowles » Tue 26 Jun 2018 18:32

We're using dotConnect.Express.for.Oracle version 9.6.540 in Visual Studio 2017 Professional.

We have a package with a procedure like this:

procedure my_proc (
in_name in varchar2 default null,
out_data out sys_refcursor
);

When we create our OracleConnection like this:

new OracleConnection {
ConnectionString = ourConnString,
PassParametersByName = true,
}

the procedure call works as expected when we have an OracleCommand with a single parameter named "OUT_DATA".

However, if we change to this:

new OracleConnection {
ConnectionString = ourConnString,
PassParametersByName = true,
Unicode = true,
}

and make the exact same call, we get the following:

Devart.Data.Oracle.OracleException
ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'MY_PROC'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

We're also seeing failures in other calls to procedures with default parameters. Is this expected/intended behavior?

MattKnowles
Posts: 5
Joined: Tue 26 Jun 2018 16:46

Re: Unicode property breaks procedure call

Post by MattKnowles » Tue 26 Jun 2018 20:48

Further testing has isolated the problem to setting Unicode = true somehow turning off PassParametersByName = true.

I created a test proc which took multiple optional parameters, and added parameters to the OracleCommand in an order different than they were declared in the stored procedure. With Unicode = false, the parameters showed up with the proper values. With Unicode = true, it was obvious that the parameters were being passed by position rather than by name.

Hope this helps you find and fix the bug.

MattKnowles
Posts: 5
Joined: Tue 26 Jun 2018 16:46

Re: Unicode property breaks procedure call

Post by MattKnowles » Tue 26 Jun 2018 20:55

I'm very confused as to why, but just changing the order in which the Properties are set fixed the issue.

new OracleConnection {
ConnectionString = ourConnString,
PassParametersByName = true,
Unicode = true,
}

The statement above results in an OracleConnection instance where the PassParametersByName property is false, despite our attempts to set it true.

The statement below results in an OracleConnection instance where the PassParametersByName property is true, and the Unicode property is also set to true.

new OracleConnection {
ConnectionString = ourConnString,
Unicode = true,
PassParametersByName = true,
}

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Unicode property breaks procedure call

Post by Pinturiccio » Wed 27 Jun 2018 15:13

MattKnowles wrote:The statement above results in an OracleConnection instance where the PassParametersByName property is false, despite our attempts to set it true.
We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
MattKnowles wrote:PLS-00306: wrong number or types of arguments in call to 'MY_PROC'
We could not reproduce the issue. Please create and send us a small test project, which reproduces the issue.

MattKnowles
Posts: 5
Joined: Tue 26 Jun 2018 16:46

Re: Unicode property breaks procedure call

Post by MattKnowles » Wed 27 Jun 2018 17:43

create or replace procedure my_proc (
in_alpha in number default null,
in_beta in varchar2 default null
) is
begin
null;
end;
/

Now, create an OracleCommand to call this procedure with a single parameter named "IN_BETA" with a value that can't be explicitly converted to a number, such as "beta".

There are three different ways to create the OracleConnection:

1. new OracleConnection { ConnectionString = something, PassParametersByName = true, }
Succeeds

2. new OracleConnection { ConnectionString = something, PassParametersByName = true, Unicode = true, }
Fails, and produces "PLS-00306: wrong number or types of arguments in call to 'MY_PROC'" error.

3. new OracleConnection { ConnectionString = something, Unicode = true, PassParametersByName = true, }
Succeeds.

The reason #2 fails is because setting Unicode = true **after** setting PassParametersByName = true results in PassParametersByName being false, which means the OracleCommand attempts to bind the "IN_BETA" parameter by position to in_alpha.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Unicode property breaks procedure call

Post by Pinturiccio » Fri 29 Jun 2018 13:19

Please provide the C# or Visual Basic code of creation of OracleCommand, creation and initialization of parameters, and execution of the command.

MattKnowles
Posts: 5
Joined: Tue 26 Jun 2018 16:46

Re: Unicode property breaks procedure call

Post by MattKnowles » Tue 03 Jul 2018 15:53

As is, this test will fail with "wrong number or types of arguments". If you comment out the Unicode = true line, or if you move it up above the PassParametersByName = true line, then it will succeed.

The real problem here is that the Unicode = true line has the side effect of setting PassParametersByName = false. This specific error is trivial compared to that. There are a wide variety of errors I can generate in Oracle because of this, but they all are a result of the PassParametersByName property being set back to false as a side-effect of setting Unicode = true.

create or replace procedure my_schema.my_proc (
in_alpha in varchar2 default null,
out_data out sys_refcursor
) is
begin
null;
end;
/

var conn = new OracleConnection
{
ConnectionString = ourConnString,
PassParametersByName = true,
Unicode = true,
};

var cmd = new OracleCommand
{
Connection = conn,
CommandText = "MY_SCHEMA.MY_PROC",
CommandType = CommandType.StoredProcedure,
};
cmd.Parameters.Add(
new OracleParameter
{
ParameterName = "OUT_DATA",
OracleDbType = OracleDbType.Cursor,
Direction = ParameterDirection.Output,
});

conn.Open();
try
{
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Unicode property breaks procedure call

Post by Pinturiccio » Mon 09 Jul 2018 13:56

We have reproduced the issue with the "wrong number or types of arguments in call to 'MY_PROC'" error when PassParametersByName is false. But this is the designed behaviour. You need to use PassParametersByName for your stored procedure when you define only the second parameter.

As for bug with reseting PassParametersByName value when the Unicode parameter is assigned after this, we have fixed this bug. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Unicode property breaks procedure call

Post by Pinturiccio » Wed 18 Jul 2018 10:21

We have fixed the bug with reseting values of the OracleConnection PassParametersByName, TrimFixedChar, and NumberMappings properties when another property is assigned. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Unicode property breaks procedure call

Post by Pinturiccio » Thu 19 Jul 2018 14:06

New build of dotConnect for Oracle 9.6.558 is available for download.
It can be downloaded from https://www.devart.com/dotconnect/oracle/download.html (trial version) or from Customer Portal (for users with valid subscription only). We have also updated our NuGet package: https://www.nuget.org/packages/Devart.Data.Oracle/
For more information, please refer to viewtopic.php?t=37474

Post Reply