Jeffreymarcum
asked on
Oracle function does not properly return with ODP.net 10g and C#
I have an Oracle Function defined in a package. I'm calling the function sf_is_overlap which should return a 1 for Success or 2 on an Exception. When I make the call, the C# parameters are created and the ExecuteNonQuery() is performed without any errors. When I test the ReturnValue, I always receive the value "0".
-------- PL / SQL -------------
FUNCTION sf_is_overlap(p_procedure_code IN pnd_proc_code.procedure_code%TYPE)
RETURN NUMBER
IS
l_exists NUMBER;
BEGIN
RETURN 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 2;
END sf_is_overlap;
-------- C# Code --------------
// Package/Procedure Name
const string SF_ISPROCODE_OVERLAP = "SSPT.PKG_PROC_CODE.SF_IS_OVERLAP_TEST";
// Parameters
const string P_PROCEDURE_CODE = "P_PROCEDURE_CODE";
string conStr = ConfigurationManager.ConnectionStrings["SSPTConnString"].ToString();
OracleConnection con = new OracleConnection(conStr);
// Create an OracleCommand and define the ref cursor procedure
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = SF_ISPROCODE_OVERLAP;
try
{
OracleParameter p_PROCEDURE_CODE = cmd.Parameters.Add(P_PROCEDURE_CODE, OracleDbType.Varchar2, ParameterDirection.Input);
p_PROCEDURE_CODE.Size = 8;
p_PROCEDURE_CODE.Value = ProcCode;
OracleParameter retVal = cmd.Parameters.Add("RETURN_VALUE", OracleDbType.Int32, ParameterDirection.ReturnValue);
cmd.Connection = con;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["RETURN_VALUE"].Value.ToString() == "1")
{
return true;
}
else
{
return false;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window