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 !!!

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish