January issue of ASPNet.Pro

The January issue of ASP.NetPro is available online at: http://www.aspnetpro.com/PDF/Issues/aspJAN2009.pdf.  The issue contains an extensive, practical article on developing with AJAX, an article on reporting in ASP.Net, and several other good articles.

Getting values back from Oracle Procedures in C# .Net

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”));

The January issue of Better Software magazine is online

The January issue of Better Software magazine is now available online at http://www.nxtbook.com/nxtbooks/sqe/bettersoftware0109/

The cover article is “Risk Based Testing in Action”, and there are several good articles in this issue, as always.

The January issue of Visual Studio magazine is online

The January issue of Visual Studio magazine is now available online at http://visualstudiomagazine.com/issue/

The cover article is “Partition Web Apps Intelligently”, and there are several good articles in this issue, as always.