Calling Oracle stored procedures from Microsoft.NET

Introduction

This article is intended 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 an Oracle 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.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
static void Main(string[] args)
{
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
{
OracleCommand objCmd = 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 (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
objConn.Close();
}
}
}
}

Listing 2: The application code calling the stored procedure.

Executing an Oracle function

As the 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.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
static void Main(string[] args)
{
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
{
OracleCommand objCmd = 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 (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
objConn.Close();
}
}
}
}

Listing 4: The application code calling the function.

Working with Oracle Cursors

You can use the REF CURSOR data type to work with the 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 that 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.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
private static void prvPrintReader(OracleDataReader objReader)
{
for (int i = 0; i < objReader.FieldCount; i++)
{
System.Console.Write("{0}\t",objReader.GetName(i));
}
System.Console.Write("\n");
while (objReader.Read())
{
for (int i = 0; i < objReader.FieldCount; i++)
{
System.Console.Write("{0}\t", objReader[i].ToString());
}
System.Console.Write("\n");
}
}
static void Main(string[] args)
{
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
{
OracleCommand objCmd = 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();
OracleDataReader objReader = objCmd.ExecuteReader();
prvPrintReader(objReader);
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
objConn.Close();
}
}
}
}

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.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
private static void prvPrintReader(OracleDataReader objReader)
{
for (int i = 0; i < objReader.FieldCount; i++)
{
System.Console.Write("{0}\t",objReader.GetName(i));
}
System.Console.Write("\n");
while (objReader.Read())
{
for (int i = 0; i < objReader.FieldCount; i++)
{
System.Console.Write("{0}\t", objReader[i].ToString());
}
System.Console.Write("\n");
}
}
static void Main(string[] args)
{
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
{
OracleCommand objCmd = 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();
OracleDataReader objReader = objCmd.ExecuteReader();
prvPrintReader(objReader);
objReader.NextResult();
prvPrintReader(objReader);
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
objConn.Close();
}
}
}
}

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.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
static void Main(string[] args)
{
using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))
{
OracleDataAdapter objAdapter = new OracleDataAdapter();
OracleCommand objSelectCmd = 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;
OracleCommand objInsertCmd = 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;
OracleCommand objUpdateCmd = 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;
OracleCommand objDeleteCmd = 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
{
DataTable dtEmp = 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 (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
objConn.Close();
}
}
}
}

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.