'Unspecified error' on ExecuteNonQuery function in ORACLE

Dmitriy · #1 (**permalink**) July 14th, 2009, 10:36 AM

Hello,

I am trying to execute the function in ORACLE database from my .NET code, but I am getting this 'Unspecified error' error message by some reason. This is my function in the database:

Code:

create or replace FUNCTION eqn( a NUMBER, b NUMBER ) RETURN NUMBER AS
 BEGIN
    IF a = b THEN
       RETURN -1;
    ELSE
       RETURN 0;
    END IF;
 END;

And this is the function in my .NET code which is trying to execute this function:
To simplefy the code, I've entered rendom values into the code

Code:

public object ExecuteFunctionTwoParameters()
{
OleDbCommand oracleCommand;
string stringReturn = null;
string param1_Name = "a";
string param2_Name = "b";
 
oracleCommand = newOleDbCommand();
oracleCommand.Connection = currentOLEDBConnection;
oracleCommand.CommandText = "EQN";
oracleCommand.CommandType = CommandType.StoredProcedure;
 
oracleCommand.Parameters.AddWithValue(param1_Name, 1);
oracleCommand.Parameters.AddWithValue(param2_Name, 2);
OleDbParameter retval = newOleDbParameter("return_value", OleDbType.Numeric);
retval.Direction = ParameterDirection.ReturnValue;
oracleCommand.Parameters.Add(retval);
 
try{
sErrorMessage = "";
oracleCommand.ExecuteNonQuery();
stringReturn = oracleCommand.Parameters["return_value"].Value.ToString();
return (object)stringReturn;
} //End trycatch (System.Data.OleDb.OleDbException e)
{
sErrorMessage = e.Message;
stringReturn = "ERROR";
return (object)stringReturn;
}
finally{
//do the clean up:oracleCommand.Dispose();
oracleCommand = null;
}
}

What am I doing wrong?
Could you help me with this problem, please?
Thanks in advance,
Dmitriy

gbianchi · #2 (**permalink**) July 16th, 2009, 10:37 AM

I'm not an expert on Oracle, but you are calling a function like it was a Store Procedure??

can you really do that??

Dmitriy · #3 (**permalink**) July 16th, 2009, 10:56 AM

Hi,
I have found the source of my error. The procedure name was not explicitly pointed to the correct database schema.
It was like:

Code:

oracleCommand.CommandText = "EQN";

It supposes to be like:

Code:

oracleCommand.CommandText = "SCHEMA_NAME.EQN"

Thanks anyway for the reply.
-Dmitriy

Need to download code?

Navigation