Solved

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

Posted on 2009-03-30
2
790 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 142

Expert Comment

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now