Oracle Command can't handle default parameters in functions!

[maxcpr]

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

[Shalex]

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]

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]

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]

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]

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]

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]

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.