Page 1 of 1
Oracle Command can't handle default parameters in functions!
Posted: Wed 10 Dec 2008 15:09
by 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
Posted: Fri 12 Dec 2008 09:37
by 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?
Posted: Wed 17 Dec 2008 13:01
by 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
Posted: Thu 18 Dec 2008 13:02
by 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();
Thank but...
Posted: Thu 18 Dec 2008 16:36
by 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!!!
Posted: Fri 19 Dec 2008 11:03
by 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();
Posted: Fri 26 Dec 2008 08:40
by 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!!!
Posted: Fri 26 Dec 2008 14:31
by 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.