Calling Oracle stored procedures from Microsoft.NET

Started by dhilipkumar, Dec 22, 2008, 08:43 PM

Previous topic - Next topic

dhilipkumar

Introduction

This article is intended to illustrate how to illustrate how to call Oracle stored procedures and functions from Microsoft.NET through the Microsoft.NET Oracle provider and its object model residing in the namespace System.Data.OracleClient. I will cover several possible scenarios with advanced examples.

Executing a stored procedure

Let's begin with definitions. A procedure is a module that performs one or more actions. A function is a module that returns a value and unlike procedures a call to a function can exist only as part of an executable such as an element in an expression or the value assigned as default in a declaration of a variable.

The first example illustrates how to call an Oracle procedure passing input parameters and retrieving value by output parameters. For all the examples, we're going to use the default database ORCL which comes with the Oracle database installation. The following code in Listing 1 shows how to create a procedure named count_emp_by_dept which receives as its input parameter the department number and sends as its output parameter the number of employees in this department.

create or replace procedure count_emp_by_dept(pin_deptno number, pout_count out number)
is
begin
select count(*) into pout_count
from scott.emp
where deptno=pin_deptno;
end count_emp_by_dept;


Listing 1: Creating the procedure  count_emp_by_dept.

Now let's create a console application and add a reference to the assembly System.Data.OracleClient.dll to your project.

The code for this example is illustrated in Listing 2. The first thing to do is to import the object's class residing in the namespace System.Data.OracleClient with the using directive. Then you must set up the parameters and finally call the procedure using ExecuteNonQuery method of the OracleCommand object.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.OracleClient
Imports System.Data

Namespace CallingOracleStoredProc
    Class Program
        Private Shared Sub Main(ByVal args As String())
            Using objConn As New OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger")
                Dim objCmd As New OracleCommand()
                objCmd.Connection = objConn
                objCmd.CommandText = "count_emp_by_dept"
                objCmd.CommandType = CommandType.StoredProcedure
                objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20
                objCmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output

                Try
                    objConn.Open()
                    objCmd.ExecuteNonQuery()
                    System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters("pout_count").Value)
                Catch ex As Exception
                    System.Console.WriteLine("Exception: {0}", ex.ToString())
                End Try

                objConn.Close()
            End Using
        End Sub
    End Class
End Namespace

Listing 2: The application code calling the stored procedure.
Executing a function

As function is similar to procedures except they return a value, we need to set up a return parameter. Let's see the example.

The following code in Listing 3 shows how to create a function named get_count_emp_by_dept which receives as its input parameter the department number and returns the number of employees in this department. It's very similar to the former procedure in the previous section.

create or replace function get_count_emp_by_dept(pin_deptno number)
return number
is
var_count number;
begin
select count(*) into var_count
from scott.emp
where deptno=pin_deptno;
return var_count;
end get_count_emp_by_dept;

Listing 3: Creating an Oracle function.

Now let's see in the Listing 4 the application code which calls the function. As you can see, we need to define a return parameter to get the returned value. The other part of the code is similar for calling a procedure.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.OracleClient
Imports System.Data

Namespace CallingOracleStoredProc
    Class Program
        Private Shared Sub Main(ByVal args As String())
            Using objConn As New OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger")
                Dim objCmd As New OracleCommand()
                objCmd.Connection = objConn
                objCmd.CommandText = "get_count_emp_by_dept"
                objCmd.CommandType = CommandType.StoredProcedure
                objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20
                objCmd.Parameters.Add("return_value", OracleType.Number).Direction = ParameterDirection.ReturnValue

                Try
                    objConn.Open()
                    objCmd.ExecuteNonQuery()
                    System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters("return_value").Value)
                Catch ex As Exception
                    System.Console.WriteLine("Exception: {0}", ex.ToString())
                End Try

                objConn.Close()
            End Using
        End Sub
    End Class
End Namespace


Listing 4: The application code calling the function.

Working with cursors

You can use the REF CURSOR data type to work with Oracle result set. To retrieve the result set, you must define a REF CURSOR output parameter in a procedure or a function to pass the cursor back to your application.

Now we're going to define a procedure which opens and sends a cursor variable to our application.

Let's define the package and procedure header as shown in Listing 5.

create or replace package human_resources
as
type t_cursor is ref cursor;
procedure get_employee(cur_employees out t_cursor);
end human_resources;

Listing 5: Creation of the package human_resources and the procedure get_employee.

And now the package definition as shown in Listing 6.

create or replace package body human_resources
as
procedure get_employee(cur_employees out t_cursor)
is
begin
  open cur_employees for select * from emp;
end get_employee;
end human_resources;


Listing 6. The creation of the package body.

Now let's see in Listing 7 the application code calling the procedure inside the package. See the name syntax for calling the procedure contained within a package [package_name].[procedure_name]. In order to get a cursor, you need to define a cursor parameter with the ParameterDirection set up to Output and finally call the ExecuteReader method in the OracleCommand instance.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.OracleClient
Imports System.Data

Namespace CallingOracleStoredProc
    Class Program
        Private Shared Sub prvPrintReader(ByVal objReader As OracleDataReader)
            For i As Integer = 0 To objReader.FieldCount - 1
                System.Console.Write("{0}" & Chr(9) & "", objReader.GetName(i))
            Next
            System.Console.Write("" & Chr(10) & "")

            While objReader.Read()
                For i As Integer = 0 To objReader.FieldCount - 1
                    System.Console.Write("{0}" & Chr(9) & "", objReader(i).ToString())
                Next
                System.Console.Write("" & Chr(10) & "")
            End While
        End Sub

        Private Shared Sub Main(ByVal args As String())
            Using objConn As New OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger")
                Dim objCmd As New OracleCommand()
                objCmd.Connection = objConn
                objCmd.CommandText = "human_resources.get_employee"
                objCmd.CommandType = CommandType.StoredProcedure
                objCmd.Parameters.Add("cur_employees", OracleType.Cursor).Direction = ParameterDirection.Output

                Try
                    objConn.Open()
                    Dim objReader As OracleDataReader = objCmd.ExecuteReader()
                    prvPrintReader(objReader)
                Catch ex As Exception
                    System.Console.WriteLine("Exception: {0}", ex.ToString())
                End Try

                objConn.Close()
            End Using
        End Sub

    End Class
End Namespace

Listing 7: The application code.

If the procedure returns more than one cursor, the DataReader object accesses them by calling the NextResult method to advance the next cursor.

Let's see the following example.

Listing 8 shows how to create the package header.

create or replace package human_resources
as
type t_cursor is ref cursor;
procedure get_employee_department(cur_employees out t_cursor, cur_departments out t_cursor);
end human_resources;

Listing 8: Package reader.

The package body is shown in Listing 9.

create or replace package body human_resources
as
procedure get_employee_department(cur_employees out t_cursor, cur_departments out t_cursor)
is
begin
  open cur_employees for select * from emp;
  open cur_departments for select * from dept;
end get_employee_department;
end human_resources;

Listing 9: Creation of the package body.

dhilipkumar

Let's see the application code in Listing 10.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.OracleClient
Imports System.Data

Namespace CallingOracleStoredProc
    Class Program
        Private Shared Sub prvPrintReader(ByVal objReader As OracleDataReader)
            For i As Integer = 0 To objReader.FieldCount - 1
                System.Console.Write("{0}" & Chr(9) & "", objReader.GetName(i))
            Next
            System.Console.Write("" & Chr(10) & "")

            While objReader.Read()
                For i As Integer = 0 To objReader.FieldCount - 1
                    System.Console.Write("{0}" & Chr(9) & "", objReader(i).ToString())
                Next
                System.Console.Write("" & Chr(10) & "")
            End While
        End Sub

        Private Shared Sub Main(ByVal args As String())
            Using objConn As New OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger")
                Dim objCmd As New OracleCommand()
                objCmd.Connection = objConn
                objCmd.CommandText = "human_resources.get_employee_department"
                objCmd.CommandType = CommandType.StoredProcedure
                objCmd.Parameters.Add("cur_employees", OracleType.Cursor).Direction = ParameterDirection.Output
                objCmd.Parameters.Add("cur_departments", OracleType.Cursor).Direction = ParameterDirection.Output

                Try
                    objConn.Open()
                    Dim objReader As OracleDataReader = objCmd.ExecuteReader()
                    prvPrintReader(objReader)
                    objReader.NextResult()
                    prvPrintReader(objReader)
                Catch ex As Exception
                    System.Console.WriteLine("Exception: {0}", ex.ToString())
                End Try

                objConn.Close()
            End Using
        End Sub

    End Class
End Namespace

Listing 10: The application code.

Working with DataSet and DataAdapter

The final example shows how to fill and update a DataSet object through a DataAdapter object.

The first thing to do is create four CRUD procedure to the emp table.  Listing 11 shows how to create the package header.

create or replace package human_resources
as
type t_cursor is ref cursor;
procedure select_employee(cur_employees out t_cursor);
procedure insert_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number);
procedure update_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number);
procedure delete_employee(p_empno number);
end human_resources;

Listing 11: The creation of the package header.

Now let's define the package body as shown in Listing 12

create or replace package body human_resources
as
procedure select_employee(cur_employees out t_cursor)
is
begin
   open cur_employees for select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
end select_employee;
procedure insert_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number)
is
begin
   update emp
   set ename=p_ename, job=p_job, mgr=p_mgr, hiredate=p_hiredate, sal=p_sal, comm=p_comm, deptno=p_deptno
   where empno=p_empno;
end insert_employee;
procedure update_employee(p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number)
is
begin
   insert into emp
   values(p_empno,p_ename,p_job,p_mgr,p_hiredate,p_sal,p_comm,p_deptno);
end update_employee;
procedure delete_employee(p_empno number)
is
begin
    delete from emp
    where empno=p_empno;
end delete_employee;
end human_resources;

Listing 12: The package body creation.

And finally, let's see the application code in Listing 13. As you can see, to fill the data table, we need to define the CRUD (create, read, update, delete) operations through the OracleCommand and associate it to the DataAdapter. I fill the data table, and print out a message with the number of employees so far, and then add a new row representing one employee entity.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.OracleClient
Imports System.Data

Namespace CallingOracleStoredProc
    Class Program
        Private Shared Sub Main(ByVal args As String())
            Using objConn As New OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger")
                Dim objAdapter As New OracleDataAdapter()

                Dim objSelectCmd As New OracleCommand()
                objSelectCmd.Connection = objConn
                objSelectCmd.CommandText = "human_resources.select_employee"
                objSelectCmd.CommandType = CommandType.StoredProcedure
                objSelectCmd.Parameters.Add("cur_employees", OracleType.Cursor).Direction = ParameterDirection.Output
                objAdapter.SelectCommand = objSelectCmd

                Dim objInsertCmd As New OracleCommand()
                objInsertCmd.Connection = objConn
                objInsertCmd.CommandText = "human_resources.insert_employee"
                objInsertCmd.CommandType = CommandType.StoredProcedure
                objInsertCmd.Parameters.Add("p_empno", OracleType.Number, 4, "empno")
                objInsertCmd.Parameters.Add("p_ename", OracleType.VarChar, 10, "ename")
                objInsertCmd.Parameters.Add("p_job", OracleType.VarChar, 9, "job")
                objInsertCmd.Parameters.Add("p_mgr", OracleType.Number, 4, "mgr")
                objInsertCmd.Parameters.Add("p_hiredate", OracleType.DateTime, 12, "hiredate")
                objInsertCmd.Parameters.Add("p_sal", OracleType.Number, 7, "sal")
                objInsertCmd.Parameters.Add("p_comm", OracleType.Number, 7, "comm")
                objInsertCmd.Parameters.Add("p_deptno", OracleType.Number, 7, "deptno")
                objAdapter.InsertCommand = objInsertCmd

                Dim objUpdateCmd As New OracleCommand()
                objUpdateCmd.Connection = objConn
                objUpdateCmd.CommandText = "human_resources.update_employee"
                objUpdateCmd.CommandType = CommandType.StoredProcedure
                objUpdateCmd.Parameters.Add("p_empno", OracleType.Number, 4, "empno")
                objUpdateCmd.Parameters.Add("p_ename", OracleType.VarChar, 10, "ename")
                objUpdateCmd.Parameters.Add("p_job", OracleType.VarChar, 9, "job")
                objUpdateCmd.Parameters.Add("p_mgr", OracleType.Number, 4, "mgr")
                objUpdateCmd.Parameters.Add("p_hiredate", OracleType.DateTime, 10, "hiredate")
                objUpdateCmd.Parameters.Add("p_sal", OracleType.Number, 7, "sal")
                objUpdateCmd.Parameters.Add("p_comm", OracleType.Number, 7, "comm")
                objUpdateCmd.Parameters.Add("p_deptno", OracleType.Number, 7, "deptno")
                objAdapter.UpdateCommand = objUpdateCmd

                Dim objDeleteCmd As New OracleCommand()
                objDeleteCmd.Connection = objConn
                objDeleteCmd.CommandText = "human_resources.delete_employee"
                objDeleteCmd.CommandType = CommandType.StoredProcedure
                objDeleteCmd.Parameters.Add("p_empno", OracleType.Number, 4, "empno")
                objAdapter.DeleteCommand = objDeleteCmd

                Try
                    Dim dtEmp As New DataTable()
                    objAdapter.Fill(dtEmp)

                    System.Console.WriteLine("Employee count = {0}", dtEmp.Rows.Count)
                    dtEmp.Rows.Add(7935, "John", "Manager", 7782, DateTime.Now, 1300, _
                    0, 10)


                    objAdapter.Update(dtEmp)
                Catch ex As Exception
                    System.Console.WriteLine("Exception: {0}", ex.ToString())
                End Try

                objConn.Close()
            End Using
        End Sub

    End Class
End Namespace


Listing 12: The application code.

Conclusion