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.
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
In this article I explained in an extensive way how to access Oracle procedures and functions using Microsoft.NET. I tried to cover all the possible scenario of one .NET application consuming the data provided by stored procedures in Oracle databases.
NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# Corner (http://www.c-sharpcorner.com/).