• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

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

0
Jeffreymarcum
Asked:
Jeffreymarcum
1 Solution
 
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
 
JeffreymarcumAuthor 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now