Rob Kraft's Software Development Blog

Software Development Insights

Getting values back from Oracle Procedures in C# .Net

Posted by robkraft on January 14, 2009

We have an application that uses Oracle Procedures for modifying data.  We decided that we wanted to get the number of records affected from each procedure.  We discovered that you cannot get that information if you are using Oracle Procedures.  Yes, you read that correctly, Oracle Procedures are incapable of returning values to the calling program.  Coming from a SQL Server background this was very surprising to me, but I found this truth in several places, including (http://it.toolbox.com/blogs/oracle-guide/learn-plsql-procedures-and-functions-13030).  Let me be clear though, Oracle Procedures can return values in output parameters, they just don’t provide a “return” value.  They also cannot return result sets.

 

Therefore, with Oracle, you have 3 ways to get the number of records affected.

  1. Use Output parameters on a Procedure,
  2. Use Return parameters on a Function,
  3. Wrap a Procedure in a Package and return the value from the Package.

 

For our development, we are using C# and we are using the Oracle provided .Net DLLs, not the Microsoft version of the Oracle .Net DLLs.

 

We decided to use Procedures to return the values, although we could have just as easily used functions.  I include here examples of each approach.

 

Oracle Functions

1) Functions CAN return values.  Here is an example of one of our functions.

CREATE OR REPLACE FUNCTION “MYSCHEMA”.”FUNC_CUSTOMERUPDATE”

(

  P_VER_TARGET IN CUSTOMER.VER_TARGET%TYPE

)

RETURN NUMBER

AS

BEGIN

  UPDATE CUSTOMER

  SET VER_TARGET = P_VER_TARGET

  WHERE VER_ID < 4;

RETURN SQL%ROWCOUNT ;

END FUNC_CUSTOMERUPDATE;

 

CREATE OR REPLACE PUBLIC SYNONYM “FUNC_CUSTOMERUPDATE”

FOR “MYSCHEMA”.”FUNC_CUSTOMERUPDATE”;

GRANT EXECUTE ON FUNC_CUSTOMERUPDATE TO ACCTGROUP;

 

2) In the function, we tell it to return the value in the SQL%ROWCOUNT reserved function.

 

3) To test our function in Oracle we use SQL Developer. Here is the query to test our function:

 

exec dbms_output.enable(1000);

declare var1 NUMBER;

BEGIN

  var1 := FUNC_CUSTOMERUPDATE(‘abcde’);

dbms_output.put_line( var1);

END;

 

4) Now that we know we get the output we expect in Oracle, we can try to make it work in our C# program.

 

     private Oracle.DataAccess.Client.OracleConnection _cnOra;

     private Oracle.DataAccess.Client.OracleCommand _cmOra;

     //Connection open was called…

     _cmOra = _cnOra.CreateCommand();

     _cmOra.CommandType = System.Data.CommandType.StoredProcedure;

     _cmOra.CommandText = “FUNC_CUSTOMERUPDATE”;

     _cmOra.BindByName = true; //I don’t recall why we do this – including it just in case it is relevant    

     _cmOra.Parameters.Add(“VER_TARGET”), “abcde”);

     Oracle.DataAccess.Client.OracleParameter oraParm = new Oracle.DataAccess.Client.OracleParameter();

     oraParm.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Int32;

     oraParm.Direction = System.Data.ParameterDirection.ReturnValue;

     _cmOra.Parameters.Add(“RETVAL“);

     _cmOra.ExecuteNonQuery();

     Int32 recordsAffected = Convert.ToInt32(Parameters(“RETVAL”));

 

 

Procedures

1) After finally getting all that working; we then easily made it work with procedures by using an OUT parameter on the procedure:

CREATE OR REPLACE PROCEDURE “MYSCHEMA”.”PROC_CUSTOMERUPDATE”

(

  P_VER_TARGET IN CUSTOMER.VER_TARGET%TYPE,

  P_RETVAL OUT NUMBER

)

IS

BEGIN

  UPDATE CUSTOMER

  SET VER_TARGET = P_VER_TARGET

  WHERE VER_ID < 4;

P_RETVAL:= SQL%ROWCOUNT ;

END FUNC_CUSTOMERUPDATE;

 

CREATE OR REPLACE PUBLIC SYNONYM “FUNC_CUSTOMERUPDATE”

FOR “MYSCHEMA”.”FUNC_CUSTOMERUPDATE”;

GRANT EXECUTE ON FUNC_CUSTOMERUPDATE TO ACCTGROUP;

 

     private Oracle.DataAccess.Client.OracleConnection _cnOra;

     private Oracle.DataAccess.Client.OracleCommand _cmOra;

     //Connection open was called…

     _cmOra = _cnOra.CreateCommand();

     _cmOra.CommandType = System.Data.CommandType.StoredProcedure;

     _cmOra.CommandText = “FUNC_CUSTOMERUPDATE”;

     _cmOra.BindByName = true; //I don’t recall why we do this – including it just in case it is relevant    

     _cmOra.Parameters.Add(“VER_TARGET”), “abcde”);

     Oracle.DataAccess.Client.OracleParameter oraParm = new Oracle.DataAccess.Client.OracleParameter();

     oraParm.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Int32;

     oraParm.Direction = System.Data.ParameterDirection.Output;

     oraParm.ParameterName = “RETVAL“;

     _cmOra.Parameters.Add(oraParm);

     _cmOra.ExecuteNonQuery();

     Int32 recordsAffected = Convert.ToInt32(Parameters(“RETVAL”));

Advertisements

3 Responses to “Getting values back from Oracle Procedures in C# .Net”

  1. sedoy1 said

    If you want execute Function from Oracle and return result and use Oracle.DataAccess do it like this

    using Oracle.DataAccess.Types;

    OracleCommand oraCommand = new OracleCommand(“sprut.get_dir_name1”, GetConnection());
    GetConnection().Open();
    string sql = “declare res VARCHAR2(2000); BEGIN :res := SPRUT.GET_DIR_NAME1(:p_id_user); END;”;

    oraCommand.CommandText = sql;
    oraCommand.CommandType = CommandType.Text;

    oraCommand.Parameters.Add(“res”, OracleDbType.Varchar2, 999999);
    oraCommand.Parameters[“res”].Direction = ParameterDirection.ReturnValue;

    oraCommand.Parameters.Add(“p_id_user”, OracleDbType.Varchar2);
    oraCommand.Parameters[“p_id_user”].Direction = ParameterDirection.Input;
    oraCommand.Parameters[“p_id_user”].Value =TextBox1.Text;

    oraCommand.ExecuteNonQuery();

    string string_data = (string)(((OracleString)oraCommand.Parameters[“res”].Value).Value);// (string)(((Oracle.DataAccess.Types.OracleDecimal)oraCommand.Parameters[1].Value).Value);

    GetConnection().Close();

  2. mahesh said

    ParmNameForOracle iam not getting this can u help me out

    • robkraft said

      Sorry – that was my error. I changed it. It should just have read:
      oraParm.ParameterName = “RETVAL”;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: