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;
                }

Open in new window

JeffreymarcumAsked:
Who is Participating?
 
JeffreymarcumConnect With a Mentor Author Commented:
Sorry, found the answer. I needed to move the "RETURN_VALUE" parameter to be created as the first parameter in the C# parameters list.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have a function, hence have to call a function:
           // Package/Procedure Name
            const string SF_ISPROCODE_OVERLAP = "SELECT SSPT.PKG_PROC_CODE.SF_IS_OVERLAP_TEST(:P_PROCEDURE_CODE) FROM DUAL";
 
            // 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.CommandText = SF_ISPROCODE_OVERLAP;
            //cmd.CommandType = CommandType.StoredProcedure; //no, this is NOT a procedure
 
            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;
  
                cmd.Connection = con;
                cmd.Connection.Open();
 
                string res = cmd.ExecuteScalar().ToString();
 
                if ( res == "1")
                {
                    return true;
                }
                else
                {
                    return false;
                }

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.