Oracle Command can't handle default parameters in functions!

Oracle Command can't handle default parameters in functions!

Postby 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:
maxcpr
 
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Postby 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?
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Postby 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
maxcpr
 
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Postby 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();
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Thank but...

Postby 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!!!
maxcpr
 
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Postby 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();
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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!!!
maxcpr
 
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Postby 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.
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle