Using Stored Procedures to Return Data from Oracle

[VBAHole22]

Stored Procedures

We have all heard the rallying cry for stored procedures. Compiled SQL on the server improves performance over pass-through queries because the SQL is, well, compiled.

 

Performance gains are realized when parameters are submitted to the RDBMS server and injected into the query server-side as opposed to sending raw SQL strings built on the client-side, so the mantra goes.

 

The .NET framework provides a good toolset for accessing Oracle data from within .NET code, although it pales in comparison to the SQL Server toolset.

There are promises from Microsoft that improvements will be forthcoming in future versions.

 

The Oracle .NET Data Provider (the System.Data.OracleClient namespace) supports many of the same classes used in the SQL version including DataReader, DataAdapter, Command, and OracleParameter to name a few.

Don�t confuse this with the ODP.NET provider from Oracle, they are not one in the same. Retrieving values from a single record using System.Data.OracleClient in VB.NET is fairly straightforward:

 

Dim objConnORA As New OracleConnection(strConn)
Dim strSQL As String = "SELECT x, y FROM MyTable Where ID = �Ralph�"
Dim cmdOra As New OracleCommand(strSQL, objORAConn)
cmdOra.CommandType = CommandType.Text
cmdOra.CommandText = strSQL
Dim dr As OracleDataReader
       Try
           objConnORA.Open()
           dr = cmdOra.ExecuteReader()
           Do While dr.Read
               MyX = CType(dr.GetValue(0), String)
               MyY = CType(dr.GetValue(1), String)
           Loop
dr.Close
       Catch ex As Exception
           Console.WriteLine("***** : {0}", ex.Message)
           Return False
       Finally
           objConnORA.Close()
       End Try

 

 

But what if we wished to take the same SQL query and convert it to a stored procedure to take advantage of all those performance gains? This is a three step process outlined below:

 

• Create a package in Oracle to hold your stored procedure
  
• Create the stored procedure in the package
  
• Write the .NET code to add parameters to the OracleCommand before executing it
  

 

If you have written stored procedures in SQL Server before then you are familiar with T-SQL, Microsoft�s implementation of the SQL language.

Oracle uses it�s own incarnation of SQL called PL/SQL. Besides the syntactically difference in the languages one major divergence is the concept of the Oracle package.

The package is a wrapper for organizing related stored procedures. Open your favorite IDE for interacting with Oracle (TOAD, Aqua, Enterprise, etc.) and lets create our package:

 

 
CREATE OR REPLACE PACKAGE pack_Test AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE usp_Test
(P_ID                       IN VARCHAR2
,P_OUT_REF                OUT T_CURSOR);

END pack_Test;

 

That�s all there is to it. Notice that we are using a REF CURSOR object to return our results set to us. This is the only supported data type for returning results sets from Oracle back to .NET.

 

The next step is to build the body of the package which contains our actual stored procedure code:

 
CREATE OR REPLACE PACKAGE BODY pack_Test AS
PROCEDURE usp_Test
(P_ID                       IN VARCHAR2
,P_OUT_REF                OUT T_CURSOR)

IS
BEGIN
 OPEN P_OUT_REF FOR
 SELECT X, Y from MYTABLE
    where ID = P_ID;
END usp_Test;
END pack_Test;

 

Looks very similar to the package script doesn�t it? The package defines the procedures and their parameter signatures.

The BODY defines the actual SQL that you wish to submit and repeats the parameter signatures.

 

All that remains is the actual .NET code to use this stored procedure.

 
       Dim Oracmd As New OracleCommand
       Dim Oradr As OracleDataReader
       Dim P_ID As New OracleParameter
       Dim P_OUT_REF As New OracleParameter

       With Oracmd
           .Connection = OracleConn
           .CommandType = CommandType.StoredProcedure
           .CommandText = "pack_Test.usp_Test"
           .Parameters.Add(P_ID)
           .Parameters.Add(P_OUT_REF)
       End With

       With P_OUT_REF
           .ParameterName = "P_OUT_REF"
           .OracleType = OracleType.Cursor
           .Direction = ParameterDirection.Output
       End With


       With P_DGN_PATH
           .ParameterName = "P_ID"
           .OracleType = OracleType.VarChar
           .Direction = ParameterDirection.Input
           .Value = DGNPath.ToUpper
       End With
       Dim i As Integer
       Try
           OracleConn.Open()
           Oradr = Oracmd.ExecuteReader()
           While Oradr.Read
               For i = 0 To Oradr.FieldCount - 1
                   Console.WriteLine(Oradr(i).ToString & �  �)
               Next
           End While

           OracleConn.Close()
       Catch ex As Exception
                  Console.WriteLine("***** : {0}", ex.Message)
       Finally
           OracleConn.Close()
       End Try

 

Of importance here are the Oracle type values and the parameter directions. I have chosen to use the OracleDataReader but I believe one could just as easily use the OracleDataAdapter to load the results set into a Dataset.

 

Ease of Use

 

Personally, I find it easier to work with SQL strings in my calling code rather than having to have a separate database interface (TOAD, Aqua Studio, Enterprise Manager) up and running to modify and compile my stored procedures and package.

I often work with the raw SQL during the development stages and then convert it just before production into nice tidy stored procedures on the server.

Parameterized commands tend to require quite a bit of code on the client to assure data typing, directionality, and naming structure.

 

Realized Performance Gains?

 

Based on some very preliminary testing, I have found that I do not gain any appreciable performance gain when using an Oracle stored procedure coupled with an OracleDataReader.

In fact I saw better performance from raw Select statements and DataReaders when simply looping through each repetitively. Of course this may not be the most accurate testing scenario but it does reproduce some real world situations in which consecutive calls are made to acquire data for processing.

My experiences up to this point have only centered on Selecting data from Oracle. Inserting and Updating records is the subject for another article and perhaps that is where stored procedures will show their true colors.

 

What Next, What Else?

• 
  

I plan to run some more realistic benchmark test using stored procedures versus raw SQL strings. These will include updating and inserting large amounts of data.

I also want to look into the ODP.NET data provider furnished by Oracle. If anyone has any experience with this please respond with your findings. I welcome any suggestions or constructive criticism.

I have only been working with Oracle for about a year and I am still a novice when it comes to PL/SQL. Before that I worked mostly with SQL Server and it�s no surprise that .NET is more tightly coupled with its sister product than with Oracle.

 

But there are many, many Oracle users out there who are working with MS clients so we need to stick together and get the word out!