?
Solved

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

Posted on 2009-03-30
2
Medium Priority
?
824 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

762 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