Oracle Command can't handle default parameters in functions!

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Oracle Command can't handle default parameters in functions!

Post by maxcpr » Wed 10 Dec 2008 15:09

Hi.
It's very annoying but seems to Oracle Command can't handle default parameters in functions!

Example:

function Create_Something
(
iClass double default -1,
iLabel double default -1
)

////

Devart.Data.Oracle.OracleCommand oraCommand = new Devart.Data.Oracle.OracleCommand("Create_Something",OracleConnection);


oraCommand.CommandType = System.Data.CommandType.StoredProcedure;

oraCommand.ParameterCheck = true;

oraCommand.Parameters.Add("iLabel",Devart.Data.Oracle.OracleDbType.Double).Value = 3;

oraCommand.ExecuteNonQuery();

//Here exception throwed - ORA-01403: no data found.
In the same time with System.Data.Oracle - all work fine!!!

And because of it i had to use in my application 2 connection, first for - select and second for procedure invokation :evil:

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

Post by Shalex » Fri 12 Dec 2008 09:37

Here is a sample I am using:

Code: Select all

create or replace FUNCTION TEST.Create_Something (
iClass double precision default -1,
iLabel double precision default -1
) 
  RETURN double precision
AS
BEGIN
  return iClass+iLabel;
END;

//////

      oracleConnection1.Open();
      Devart.Data.Oracle.OracleCommand oraCommand = new Devart.Data.Oracle.OracleCommand("Create_Something", oracleConnection1);
      oraCommand.CommandType = System.Data.CommandType.StoredProcedure;
      oraCommand.ParameterCheck = true;
      oraCommand.Parameters.Add("iLabel", Devart.Data.Oracle.OracleDbType.Double).Value = 3;
      oraCommand.ExecuteNonQuery();
      oracleConnection1.Close();
But there is no any error. Please specify the body of your function. Which version of dotConnect for Oracle do you use?

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Post by maxcpr » Wed 17 Dec 2008 13:01

Hi
Other example:

create or replace function Create_Something
(
niClass Number default -1,
siDescription varchar default '-1'
) return number

as
begin
return -1;
end;

///

Devart.Data.Oracle.OracleCommand oraCommand = new Devart.Data.Oracle.OracleCommand("NB.Create_Something", OracleConnection);

oraCommand.CommandType = System.Data.CommandType.StoredProcedure;

oraCommand.Parameters.Add("siDescription", Devart.Data.Oracle.OracleDbType.VarChar, 254).Value = "xxx";

oraCommand.Parameters.Add(null, Devart.Data.Oracle.OracleDbType.Number).Direction = System.Data.ParameterDirection.ReturnValue;

object obj = oraCommand.ExecuteNonQuery();

////

In this case exception rised

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 18 Dec 2008 13:02

Try to change your code like this:

Code: Select all

Devart.Data.Oracle.OracleCommand oraCommand = new Devart.Data.Oracle.OracleCommand("NB.Create_Something", OracleConnection); 
oraCommand.CommandType = System.Data.CommandType.StoredProcedure;      oraCommand.Parameters.Add("niClass", Devart.Data.Oracle.OracleDbType.Number);
oraCommand.Parameters.Add("siDescription", Devart.Data.Oracle.OracleDbType.VarChar, 254).Value = "xxx";
oraCommand.Parameters.Add("Return", Devart.Data.Oracle.OracleDbType.Number).Direction = System.Data.ParameterDirection.ReturnValue;
object obj = oraCommand.ExecuteNonQuery();

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Thank but...

Post by maxcpr » Thu 18 Dec 2008 16:36

:D Yes it's work and i always knew what it's work. But in my project i need to pass parameters not in the same sequency as they flow. And it's very straight restriction for me. I had to use 2 connection for this purpose and this very SAD solution.

Please HELP!!!

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 19 Dec 2008 11:03

As an alternative, you can set the ParameterCheck property to true and change the name of the return parameter to "RETURN", like this:

Code: Select all

Devart.Data.Oracle.OracleCommand oraCommand = new Devart.Data.Oracle.OracleCommand("Create_Something", OracleConnection);
oraCommand.CommandType = System.Data.CommandType.StoredProcedure;
oraCommand.ParameterCheck = true;
oraCommand.Parameters.Add("siDescription", Devart.Data.Oracle.OracleDbType.VarChar, 254).Value = "xxx";
oraCommand.Parameters.Add("RESULT", Devart.Data.Oracle.OracleDbType.Number).Direction = System.Data.ParameterDirection.ReturnValue;
object obj = oraCommand.ExecuteNonQuery();

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Post by maxcpr » Fri 26 Dec 2008 08:40

This still don't work
As an alternative, you can set the ParameterCheck property to true and change the name of the return parameter to "RETURN", like this:


as for as in place of default parameter value -1 for "niClass" parameter in function pass NULL!!!

HELP!!!

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

Post by Shalex » Fri 26 Dec 2008 14:31

Creation of the parameter with the assigned default value in the OracleParameter.Value field requires the additional requests to the server. It can cause the loss of performance. That's why dotConnect for Oracle doesn't have this functionality.

We recommend you to create an OracleParameter object and to set its Value property manually.

Post Reply