Oracle Command can't handle default parameters in functions!
Oracle Command can't handle default parameters in functions!
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
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
Here is a sample I am using:
But there is no any error. Please specify the body of your function. Which version of dotConnect for Oracle do you use?
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();
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
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
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...
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!!!
Please HELP!!!
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();
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.
We recommend you to create an OracleParameter object and to set its Value property manually.