Executing Oracle 9i Stored Procedures with Oracle Managed Data Provider

The new Oracle managed provider provides optimized methods to access the Oracle 9i data store. Let’s see how we can do the same. We have a simple stored procedure which updates a particular table. The stored procedure accepts three parameters, two of them are input parameters and one is an output parameter.

The stored procedure checks that the update statement is executed successfully and if yes then, the OUT parameter status is set to TRUE else it is set to FALSE.

Here is the code for the Stored Procedure

CREATE OR REPLACE PROCEDURE sp_updateBalance(accNo number, amount number, status OUT varchar2)
IS
BEGIN
             UPDATE customer_account
             SET balance = balance + amount
             WHERE accountNo = accNo ;

             if SQL%ROWCOUNT = 1 then
                   status := 'TRUE' ;
                   commit ;
             else
                   status := 'FALSE' ;
             end if ;
END ;

You can run the above code in SQL*PLUS and the procedure is created. Of course, you should have the table Customer_Account with at least the required fields i.e
1. AccountNo which is a number and
2. Balance also a number.

Let's see the how can we execute it using .NET. Here, we will be a using a simple console application to execute the stored procedure. The language used is C#.

Here is the C# code (console.cs)

// import the required assemblies
using System;
using System.Data;
using System.Data.OracleClient;

class StoredProc
      {
            public static void Main()
                {
                 // your Database connection string
                 string sDBstr = "" ;
                 // create an instance of the connection object
                 OracleConnection oCn = new OracleConnection(sDBstr) ;

//create an instance of the command object giving the procedure name
OracleCommand oCm = new OracleCommand("sp_updateBalance",oCn) ;

// Define the command type u r executing as a Stored Procedure.
oCm.CommandType = CommandType.StoredProcedure ;

               //Add the parameter "accNo" giving it's value and defining it as a Input parameter
                        oCm.Parameters.Add("accNo",OracleType.Number,16);
                        oCm.Parameters["accNo"].Value = 1 ;
                        oCm.Parameters["accNo"].Direction = ParameterDirection.Input ;

               //Add the parameter "amount" giving it's value and defining it as a Input parameter
                        oCm.Parameters.Add("amount",OracleType.Number,16);
                       oCm.Parameters["amount"].Value = 200 ;
                        oCm.Parameters["amount"].Direction = ParameterDirection.Input ;

               //Add the parameter "status" giving it's value and defining it as a Output parameter
                       oCm.Parameters.Add("status",OracleType.VarChar,5);
                        oCm.Parameters["status"].Direction = ParameterDirection.Output ;

                        // using the Try Catch Finally Block.
                        try
                          {
                                // Open the connection
                                oCn.Open();

// giving screen output
Console.WriteLine("created connection") ;

// execute the stored procedure
oCm.ExecuteNonQuery() ;

// get the confirmation on the screen
Console.WriteLine(oCm.Parameters["status"].Value) ; ;

                          }
                       catch(Exception ex)
                          {

// catch the error message and put it in the string "msg"
string msg = ex.Message ;

//show the error message on the screen
Console.WriteLine(msg) ;

                          }
                       finally
                          {
                                 //Close the connection
                                 oCn.Close();
                                 oCn = null ;
                               oCm = null ;

}
}

}

You will have to compile this separately with the following command

csc /r:System.Data.OracleClient.dll console.cs

This creates console.exe, which you can run at command prompt.

Running the above executable should give you the appropriate result.

Happy Coding !!!

Comments

Plain text