Solved

Oracle function does not properly return with ODP.net 10g and C#

Posted on 2009-03-30
2
798 Views
Last Modified: 2013-12-18
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
Comment
Question by:Jeffreymarcum
2 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24021385
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
 

Accepted Solution

by:
Jeffreymarcum earned 0 total points
ID: 24021406
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question