Thursday, April 23, 2009
Wednesday, April 22, 2009
Re load page using asp.net
Instead of using Response.Redirect you could try
CODE
ScriptManager.RegisterClientScriptBlock(this, typeof(string), "postBack", Page.ClientScript.GetPostBackEventReference(new PostBackOptions(this)), true);I know it's a bit long but if you have any update panels or ajax stuff on the page you need to use ScriptManager. Otherwise you can just use
CODE
ClientScript.RegisterClientScriptBlock(typeof(string), "postBack", Page.ClientScript.GetPostBackEventReference(new PostBackOptions(this)));I hope that helps
CODE
ScriptManager.RegisterClientScriptBlock(this, typeof(string), "postBack", Page.ClientScript.GetPostBackEventReference(new PostBackOptions(this)), true);I know it's a bit long but if you have any update panels or ajax stuff on the page you need to use ScriptManager. Otherwise you can just use
CODE
ClientScript.RegisterClientScriptBlock(typeof(string), "postBack", Page.ClientScript.GetPostBackEventReference(new PostBackOptions(this)));I hope that helps
Tuesday, April 21, 2009
Bulk Operations Using Oracle Data Provider for .NET (ODP.NET)
Introduction
In a typical multi-tier application, one of the biggest performance bottlenecks is the overhead of making round-trips to the database. Minimizing these round-trips is often the first area you should look at during performance tuning. Fortunately, the Oracle Data Provider for .NET (ODP.NET) makes it fairly easy to do this by providing several built-in methods to write and read data in bulk.
To run the code samples in this article, you will need to have:
ODP.NET 2.0
A table named "BULK_TEST".
Here's the script that creates the table BULK_TEST:
CREATE TABLE BULK_TEST
(
EMPLOYEE_ID NUMBER(10) NOT NULL,
FIRST_NAME VARCHAR2(64 BYTE) NOT NULL,
LAST_NAME VARCHAR2(64 BYTE) NOT NULL,
DOB DATE NOT NULL
); CREATE TABLE BULK_TEST
(
EMPLOYEE_ID NUMBER(10) NOT NULL,
FIRST_NAME VARCHAR2(64 BYTE) NOT NULL,
LAST_NAME VARCHAR2(64 BYTE) NOT NULL,
DOB DATE NOT NULL
);
Bulk Inserts Using Array Binding
The Array Binding feature in ODP.NET allows you to insert multiple records in one database call. To use Array Binding, you simply set OracleCommand.ArrayBindCount to the number of records to be inserted, and pass arrays of values as parameters instead of single values:
string sql =
"insert into bulk_test (employee_id, first_name, last_name, dob) "
+ "values (:employee_id, :first_name, :last_name, :dob)";
OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = numRecords;
// Instead of single values, we pass arrays of values as parameters
cmd.Parameters.Add(":employee_id", OracleDbType.Int32,
employeeIds, ParameterDirection.Input);
cmd.Parameters.Add(":first_name", OracleDbType.Varchar2,
firstNames, ParameterDirection.Input);
cmd.Parameters.Add(":last_name", OracleDbType.Varchar2,
lastNames, ParameterDirection.Input);
cmd.Parameters.Add(":dob", OracleDbType.Date,
dobs, ParameterDirection.Input);
cmd.ExecuteNonQuery();
cnn.Close(); string sql =
"insert into bulk_test (employee_id, first_name, last_name, dob) "
+ "values (:employee_id, :first_name, :last_name, :dob)";
OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = numRecords;
// Instead of single values, we pass arrays of values as parameters
cmd.Parameters.Add(":employee_id", OracleDbType.Int32,
employeeIds, ParameterDirection.Input);
cmd.Parameters.Add(":first_name", OracleDbType.Varchar2,
firstNames, ParameterDirection.Input);
cmd.Parameters.Add(":last_name", OracleDbType.Varchar2,
lastNames, ParameterDirection.Input);
cmd.Parameters.Add(":dob", OracleDbType.Date,
dobs, ParameterDirection.Input);
cmd.ExecuteNonQuery();
cnn.Close();
As you can see, the code does not look that much different from doing a regular single-record insert. However, the performance improvement is quite drastic, depending on the number of records involved. The more records you have to insert, the bigger the performance gain. On my development PC, inserting 1,000 records using Array Binding is 90 times faster than inserting the records one at a time. Yes, you read that right: 90 times faster! Your results will vary, depending on the record size and network speed/bandwidth to the database server.
A bit of investigative work reveals that the SQL is considered to be "executed" multiple times on the server side. The evidence comes from V$SQL (look at the EXECUTIONS column). However, from the .NET point of view, everything was done in one call.
Bulk Inserts Using PL/SQL Associative Arrays
PL/SQL Associative Arrays (formerly PL/SQL Index-By Tables) allow .NET code to pass arrays as parameters to PL/SQL code (stored procedure or anonymous PL/SQL blocks). Once the arrays are in PL/SQL, you are free to use them in whichever way you wish, including turning around and inserting them into a table using the "forall" bulk bind syntax.
Why would you want to insert bulk records using PL/SQL Associative Arrays instead of the simple syntax of Array Binding? Here are a few possible reasons:
You need to perform additional work in PL/SQL, in addition to the bulk insert.
The application login does not have permission to perform the work, but you can grant the necessary privilege to a stored procedure.
The major drawback with using Associative Arrays is that you have to write PL/SQL code. I have nothing against PL/SQL, but it's not part of the skill set of the typical .NET developer. To most .NET developers, PL/SQL will be harder to write and maintain, so you will have to weigh this drawback against the potential gain in performance.
In the following example, we use PL/SQL Associative Arrays to insert 1,000 records, and returning a Ref Cursor at the end. As you can see, there's quite a bit of more code to write:
OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
string sql = "declare "
+ "type t_emp_id is table of bulk_test.employee_id%type index by pls_integer; "
+ "type t_first_name is table of bulk_test.first_name%type index by pls_integer; "
+ "type t_last_name is table of bulk_test.last_name%type index by pls_integer; "
+ "type t_dob is table of bulk_test.dob%type index by pls_integer; "
+ "p_emp_id t_emp_id; "
+ "p_first_name t_first_name; "
+ "p_last_name t_last_name; "
+ "p_dob t_dob; "
+ "begin "
+ " p_emp_id := :emp_id; "
+ " p_first_name := :first_name; "
+ " p_last_name := :last_name; "
+ " p_dob := :dob; "
+ " forall i in p_emp_id.first..p_emp_id.last "
+ " insert into bulk_test (employee_id, first_name, last_name, dob) "
+ " values (p_emp_id(i), p_first_name(i) , p_last_name(i), p_dob(i)); "
+ " open :c1 for "
+ " select employee_id, first_name, last_name, dob from bulk_test;"
+ "end;";
cmd.CommandText = sql;
OracleParameter pEmpId = new OracleParameter(":emp_id",
OracleDbType.Int32,
numRecords, ParameterDirection.Input);
pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pEmpId.Value = employeeIds;
OracleParameter pFirstName = new OracleParameter(":first_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pFirstName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstName.Value = firstNames;
OracleParameter pLastName = new OracleParameter(":last_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pLastName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pLastName.Value = lastNames;
OracleParameter pDob = new OracleParameter(":dob",
OracleDbType.Date, numRecords,
ParameterDirection.Input);
pDob.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pDob.Value = dobs;
OracleParameter pRefCursor = new OracleParameter();
pRefCursor.OracleDbType = OracleDbType.RefCursor;
pRefCursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(pEmpId);
cmd.Parameters.Add(pFirstName);
cmd.Parameters.Add(pLastName);
cmd.Parameters.Add(pDob);
cmd.Parameters.Add(pRefCursor);
int rows = cmd.ExecuteNonQuery();
OracleDataReader dr = ((OracleRefCursor) pRefCursor.Value).GetDataReader();
while (dr.Read())
{
// Process cursor
}
cnn.Close(); OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
string sql = "declare "
+ "type t_emp_id is table of bulk_test.employee_id%type index by pls_integer; "
+ "type t_first_name is table of bulk_test.first_name%type index by pls_integer; "
+ "type t_last_name is table of bulk_test.last_name%type index by pls_integer; "
+ "type t_dob is table of bulk_test.dob%type index by pls_integer; "
+ "p_emp_id t_emp_id; "
+ "p_first_name t_first_name; "
+ "p_last_name t_last_name; "
+ "p_dob t_dob; "
+ "begin "
+ " p_emp_id := :emp_id; "
+ " p_first_name := :first_name; "
+ " p_last_name := :last_name; "
+ " p_dob := :dob; "
+ " forall i in p_emp_id.first..p_emp_id.last "
+ " insert into bulk_test (employee_id, first_name, last_name, dob) "
+ " values (p_emp_id(i), p_first_name(i) , p_last_name(i), p_dob(i)); "
+ " open :c1 for "
+ " select employee_id, first_name, last_name, dob from bulk_test;"
+ "end;";
cmd.CommandText = sql;
OracleParameter pEmpId = new OracleParameter(":emp_id",
OracleDbType.Int32,
numRecords, ParameterDirection.Input);
pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pEmpId.Value = employeeIds;
OracleParameter pFirstName = new OracleParameter(":first_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pFirstName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstName.Value = firstNames;
OracleParameter pLastName = new OracleParameter(":last_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pLastName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pLastName.Value = lastNames;
OracleParameter pDob = new OracleParameter(":dob",
OracleDbType.Date, numRecords,
ParameterDirection.Input);
pDob.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pDob.Value = dobs;
OracleParameter pRefCursor = new OracleParameter();
pRefCursor.OracleDbType = OracleDbType.RefCursor;
pRefCursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(pEmpId);
cmd.Parameters.Add(pFirstName);
cmd.Parameters.Add(pLastName);
cmd.Parameters.Add(pDob);
cmd.Parameters.Add(pRefCursor);
int rows = cmd.ExecuteNonQuery();
OracleDataReader dr = ((OracleRefCursor) pRefCursor.Value).GetDataReader();
while (dr.Read())
{
// Process cursor
}
cnn.Close();
Ref Cursors
ODP.NET Ref Cursors are objects that point to Oracle server-side cursors (or result sets). The important thing to .NET developers is that a Ref Cursor can be converted to the familiar OracleDataReader. With Ref Cursors, the logic to open result sets can be written entirely in PL/SQL and the results returned to .NET via Ref Cursors.
Why would you want to use Ref Cursors, instead of just doing an ExecuteReader with a SELECT statement? Here are some possible reasons:
You need to perform additional work in PL/SQL before returning the result set(s).
The user does not have direct access to the table(s) in question.
Here is a real world example. Say you need to update a record in the Orders table and insert a new record into the OrdersAudit table at the same time. Instead of executing two database calls, you can wrap everything into an anonymous PL/SQL block and make one database call.
See the previous section for code example using a Ref Cursor.
Controlling FetchSize
Controlling the FetchSize property is another method to minimize server round-trips. When you read data from the server using the OracleDataReader object, ODP.NET retrieves the data for you in chunks behind the scene. By default, a 64K chunk of data is retrieved each time. However, you can change the chunk size by setting the FetchSize property. By increasing FetchSize, you will lower the number of data retrieval round-trips and increase the overall retrieval operation.
It's typical to set the FetchSize property is to the RowSize (of the OracleCommand object) multiplied by the number of records expected:
OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRecords;
while (dr.Read())
{
// Perform reads...
} OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRecords;
while (dr.Read())
{
// Perform reads...
}
When working with a Ref Cursor, the OracleCommand.RowSize property is always zero. You either have to calculate the row size at design time or use reflection at run-time to determine the RowSize by looking at the instance variable m_rowSize on the OracleDataReader object:
FieldInfo fi = dr.GetType().GetField("m_rowSize"
, BindingFlags.Instance BindingFlags.NonPublic);
int rowSize = (int) fi.GetValue(dr); FieldInfo fi = dr.GetType().GetField("m_rowSize"
, BindingFlags.Instance BindingFlags.NonPublic);
int rowSize = (int) fi.GetValue(dr);
On my development PC, when reading 10,000 records from BULK_TEST, setting FetchSize = RowSize *improves the total elapsed time by a factor of two over leaving FetchSize at the default value (985 vs. 407 milliseconds
Notes
For clarity purposes, the example code in this article does not use the "using" pattern for IDisposable objects. It's recommended that you always use "using" with IDisposable objects such as OracleConnection or OracleCommand.
Summary
In this article, we looked at how various bulk operations in ODP.NET 2.0 can help you improve the performance of your ODP.NET application. Being familiar with these techniques can help you plan, design and implement applications that meet performance goals.
In a typical multi-tier application, one of the biggest performance bottlenecks is the overhead of making round-trips to the database. Minimizing these round-trips is often the first area you should look at during performance tuning. Fortunately, the Oracle Data Provider for .NET (ODP.NET) makes it fairly easy to do this by providing several built-in methods to write and read data in bulk.
To run the code samples in this article, you will need to have:
ODP.NET 2.0
A table named "BULK_TEST".
Here's the script that creates the table BULK_TEST:
CREATE TABLE BULK_TEST
(
EMPLOYEE_ID NUMBER(10) NOT NULL,
FIRST_NAME VARCHAR2(64 BYTE) NOT NULL,
LAST_NAME VARCHAR2(64 BYTE) NOT NULL,
DOB DATE NOT NULL
); CREATE TABLE BULK_TEST
(
EMPLOYEE_ID NUMBER(10) NOT NULL,
FIRST_NAME VARCHAR2(64 BYTE) NOT NULL,
LAST_NAME VARCHAR2(64 BYTE) NOT NULL,
DOB DATE NOT NULL
);
Bulk Inserts Using Array Binding
The Array Binding feature in ODP.NET allows you to insert multiple records in one database call. To use Array Binding, you simply set OracleCommand.ArrayBindCount to the number of records to be inserted, and pass arrays of values as parameters instead of single values:
string sql =
"insert into bulk_test (employee_id, first_name, last_name, dob) "
+ "values (:employee_id, :first_name, :last_name, :dob)";
OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = numRecords;
// Instead of single values, we pass arrays of values as parameters
cmd.Parameters.Add(":employee_id", OracleDbType.Int32,
employeeIds, ParameterDirection.Input);
cmd.Parameters.Add(":first_name", OracleDbType.Varchar2,
firstNames, ParameterDirection.Input);
cmd.Parameters.Add(":last_name", OracleDbType.Varchar2,
lastNames, ParameterDirection.Input);
cmd.Parameters.Add(":dob", OracleDbType.Date,
dobs, ParameterDirection.Input);
cmd.ExecuteNonQuery();
cnn.Close(); string sql =
"insert into bulk_test (employee_id, first_name, last_name, dob) "
+ "values (:employee_id, :first_name, :last_name, :dob)";
OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = numRecords;
// Instead of single values, we pass arrays of values as parameters
cmd.Parameters.Add(":employee_id", OracleDbType.Int32,
employeeIds, ParameterDirection.Input);
cmd.Parameters.Add(":first_name", OracleDbType.Varchar2,
firstNames, ParameterDirection.Input);
cmd.Parameters.Add(":last_name", OracleDbType.Varchar2,
lastNames, ParameterDirection.Input);
cmd.Parameters.Add(":dob", OracleDbType.Date,
dobs, ParameterDirection.Input);
cmd.ExecuteNonQuery();
cnn.Close();
As you can see, the code does not look that much different from doing a regular single-record insert. However, the performance improvement is quite drastic, depending on the number of records involved. The more records you have to insert, the bigger the performance gain. On my development PC, inserting 1,000 records using Array Binding is 90 times faster than inserting the records one at a time. Yes, you read that right: 90 times faster! Your results will vary, depending on the record size and network speed/bandwidth to the database server.
A bit of investigative work reveals that the SQL is considered to be "executed" multiple times on the server side. The evidence comes from V$SQL (look at the EXECUTIONS column). However, from the .NET point of view, everything was done in one call.
Bulk Inserts Using PL/SQL Associative Arrays
PL/SQL Associative Arrays (formerly PL/SQL Index-By Tables) allow .NET code to pass arrays as parameters to PL/SQL code (stored procedure or anonymous PL/SQL blocks). Once the arrays are in PL/SQL, you are free to use them in whichever way you wish, including turning around and inserting them into a table using the "forall" bulk bind syntax.
Why would you want to insert bulk records using PL/SQL Associative Arrays instead of the simple syntax of Array Binding? Here are a few possible reasons:
You need to perform additional work in PL/SQL, in addition to the bulk insert.
The application login does not have permission to perform the work, but you can grant the necessary privilege to a stored procedure.
The major drawback with using Associative Arrays is that you have to write PL/SQL code. I have nothing against PL/SQL, but it's not part of the skill set of the typical .NET developer. To most .NET developers, PL/SQL will be harder to write and maintain, so you will have to weigh this drawback against the potential gain in performance.
In the following example, we use PL/SQL Associative Arrays to insert 1,000 records, and returning a Ref Cursor at the end. As you can see, there's quite a bit of more code to write:
OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
string sql = "declare "
+ "type t_emp_id is table of bulk_test.employee_id%type index by pls_integer; "
+ "type t_first_name is table of bulk_test.first_name%type index by pls_integer; "
+ "type t_last_name is table of bulk_test.last_name%type index by pls_integer; "
+ "type t_dob is table of bulk_test.dob%type index by pls_integer; "
+ "p_emp_id t_emp_id; "
+ "p_first_name t_first_name; "
+ "p_last_name t_last_name; "
+ "p_dob t_dob; "
+ "begin "
+ " p_emp_id := :emp_id; "
+ " p_first_name := :first_name; "
+ " p_last_name := :last_name; "
+ " p_dob := :dob; "
+ " forall i in p_emp_id.first..p_emp_id.last "
+ " insert into bulk_test (employee_id, first_name, last_name, dob) "
+ " values (p_emp_id(i), p_first_name(i) , p_last_name(i), p_dob(i)); "
+ " open :c1 for "
+ " select employee_id, first_name, last_name, dob from bulk_test;"
+ "end;";
cmd.CommandText = sql;
OracleParameter pEmpId = new OracleParameter(":emp_id",
OracleDbType.Int32,
numRecords, ParameterDirection.Input);
pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pEmpId.Value = employeeIds;
OracleParameter pFirstName = new OracleParameter(":first_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pFirstName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstName.Value = firstNames;
OracleParameter pLastName = new OracleParameter(":last_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pLastName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pLastName.Value = lastNames;
OracleParameter pDob = new OracleParameter(":dob",
OracleDbType.Date, numRecords,
ParameterDirection.Input);
pDob.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pDob.Value = dobs;
OracleParameter pRefCursor = new OracleParameter();
pRefCursor.OracleDbType = OracleDbType.RefCursor;
pRefCursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(pEmpId);
cmd.Parameters.Add(pFirstName);
cmd.Parameters.Add(pLastName);
cmd.Parameters.Add(pDob);
cmd.Parameters.Add(pRefCursor);
int rows = cmd.ExecuteNonQuery();
OracleDataReader dr = ((OracleRefCursor) pRefCursor.Value).GetDataReader();
while (dr.Read())
{
// Process cursor
}
cnn.Close(); OracleConnection cnn = new OracleConnection(connectString);
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
string sql = "declare "
+ "type t_emp_id is table of bulk_test.employee_id%type index by pls_integer; "
+ "type t_first_name is table of bulk_test.first_name%type index by pls_integer; "
+ "type t_last_name is table of bulk_test.last_name%type index by pls_integer; "
+ "type t_dob is table of bulk_test.dob%type index by pls_integer; "
+ "p_emp_id t_emp_id; "
+ "p_first_name t_first_name; "
+ "p_last_name t_last_name; "
+ "p_dob t_dob; "
+ "begin "
+ " p_emp_id := :emp_id; "
+ " p_first_name := :first_name; "
+ " p_last_name := :last_name; "
+ " p_dob := :dob; "
+ " forall i in p_emp_id.first..p_emp_id.last "
+ " insert into bulk_test (employee_id, first_name, last_name, dob) "
+ " values (p_emp_id(i), p_first_name(i) , p_last_name(i), p_dob(i)); "
+ " open :c1 for "
+ " select employee_id, first_name, last_name, dob from bulk_test;"
+ "end;";
cmd.CommandText = sql;
OracleParameter pEmpId = new OracleParameter(":emp_id",
OracleDbType.Int32,
numRecords, ParameterDirection.Input);
pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pEmpId.Value = employeeIds;
OracleParameter pFirstName = new OracleParameter(":first_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pFirstName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstName.Value = firstNames;
OracleParameter pLastName = new OracleParameter(":last_name",
OracleDbType.Varchar2, numRecords,
ParameterDirection.Input);
pLastName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pLastName.Value = lastNames;
OracleParameter pDob = new OracleParameter(":dob",
OracleDbType.Date, numRecords,
ParameterDirection.Input);
pDob.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pDob.Value = dobs;
OracleParameter pRefCursor = new OracleParameter();
pRefCursor.OracleDbType = OracleDbType.RefCursor;
pRefCursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(pEmpId);
cmd.Parameters.Add(pFirstName);
cmd.Parameters.Add(pLastName);
cmd.Parameters.Add(pDob);
cmd.Parameters.Add(pRefCursor);
int rows = cmd.ExecuteNonQuery();
OracleDataReader dr = ((OracleRefCursor) pRefCursor.Value).GetDataReader();
while (dr.Read())
{
// Process cursor
}
cnn.Close();
Ref Cursors
ODP.NET Ref Cursors are objects that point to Oracle server-side cursors (or result sets). The important thing to .NET developers is that a Ref Cursor can be converted to the familiar OracleDataReader. With Ref Cursors, the logic to open result sets can be written entirely in PL/SQL and the results returned to .NET via Ref Cursors.
Why would you want to use Ref Cursors, instead of just doing an ExecuteReader with a SELECT statement? Here are some possible reasons:
You need to perform additional work in PL/SQL before returning the result set(s).
The user does not have direct access to the table(s) in question.
Here is a real world example. Say you need to update a record in the Orders table and insert a new record into the OrdersAudit table at the same time. Instead of executing two database calls, you can wrap everything into an anonymous PL/SQL block and make one database call.
See the previous section for code example using a Ref Cursor.
Controlling FetchSize
Controlling the FetchSize property is another method to minimize server round-trips. When you read data from the server using the OracleDataReader object, ODP.NET retrieves the data for you in chunks behind the scene. By default, a 64K chunk of data is retrieved each time. However, you can change the chunk size by setting the FetchSize property. By increasing FetchSize, you will lower the number of data retrieval round-trips and increase the overall retrieval operation.
It's typical to set the FetchSize property is to the RowSize (of the OracleCommand object) multiplied by the number of records expected:
OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRecords;
while (dr.Read())
{
// Perform reads...
} OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRecords;
while (dr.Read())
{
// Perform reads...
}
When working with a Ref Cursor, the OracleCommand.RowSize property is always zero. You either have to calculate the row size at design time or use reflection at run-time to determine the RowSize by looking at the instance variable m_rowSize on the OracleDataReader object:
FieldInfo fi = dr.GetType().GetField("m_rowSize"
, BindingFlags.Instance BindingFlags.NonPublic);
int rowSize = (int) fi.GetValue(dr); FieldInfo fi = dr.GetType().GetField("m_rowSize"
, BindingFlags.Instance BindingFlags.NonPublic);
int rowSize = (int) fi.GetValue(dr);
On my development PC, when reading 10,000 records from BULK_TEST, setting FetchSize = RowSize *
Notes
For clarity purposes, the example code in this article does not use the "using" pattern for IDisposable objects. It's recommended that you always use "using" with IDisposable objects such as OracleConnection or OracleCommand.
Summary
In this article, we looked at how various bulk operations in ODP.NET 2.0 can help you improve the performance of your ODP.NET application. Being familiar with these techniques can help you plan, design and implement applications that meet performance goals.
http://www.codersource.net/csharp_oracle_stored_procedures.html
Calling Oracle stored procedures from Microsoft.NET
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)isbegin 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 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 numberis 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 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_resourcesas 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_resourcesas 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_resourcesas 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_resourcesas 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_resourcesas 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_resourcesas 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.
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)isbegin 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 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 numberis 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 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_resourcesas 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_resourcesas 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_resourcesas 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_resourcesas 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_resourcesas 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_resourcesas 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.
Caching Oracle Data for ASP.NET Applications
Introduction
For building scalable and high-performance Web based applications, ASP.NET provides a feature called data caching. Data caching enables programmatic storing of frequently accessed data objects in memory. This feature can be extended to vastly improve performance for ASP.NET applications that query data stored in an Oracle database. This article describes a strategy for caching Oracle database data in ASP.NET Web applications deployed using a Web Farm environment. This technique enables caching of frequently accessed Oracle database data in memory rather than making frequent database calls to retrieve the data. This helps to avoid unnecessary roundtrips to the Oracle database server. Further the article proposes an implementation for maintaining the cached data so it is always in sync with the corresponding data in the Oracle database.
Data Caching in ASP.NET
Data Caching in ASP.NET is facilitated via the Cache class and the CacheDependency class in the System.Web.Caching namespace. The Cache class provides methods for inserting data and retrieving data from the cache. The CacheDependency class enables dependencies to be specified for the data items placed in the cache. An expiration policy for an item can be specified when we add it to the cache using the Insert method or Add method. We can define the life span for an item in the cache by using the absoluteExpiration parameter in the Insert method. This parameter allows one to specify the exact datetime that the corresponding data item will expire. One can also use the slidingExpiration parameter, specifying the elapsed time before the item will expire based on the time it was accessed. Once the item expires, it is removed from the cache. Attempts to access it will return a null value unless the item is added to the Cache again.
Specifying Dependencies for Cache
ASP.NET allows us to define the dependency of a cached item based on an external file, a directory, or another cached item. These are described as file dependencies and key dependencies. If a dependency changes, the cached item gets automatically invalidated and removed from the cache. We can use this approach to delete items from the cache when the corresponding data source changes. For example, if we write an application that retrieves data from an XML file and displays it in a grid, we can store the data from the file in the Cache and specify a Cache dependency on the XML file. When the XML file is updated, the data item gets removed from the cache. When this event occurs, the application reads the XML file again, and the latest copy of the data item is inserted into the cache again. Further, callback event handlers can be specified as a listener for getting notified when the cache item gets deleted from the cache. This eliminates the need to continuously poll the cache to determine whether the data item has been invalidated.
ASP.NET Cache Dependency on Oracle Database
Let us consider a scenario where data is stored in the Oracle database and accessed by an ASP.NET application using ADO.NET. Furthermore, let us assume that the data in the database table(s) is generally static but accessed frequently by the Web application. In a nutshell, there are very few DML operations on the table but lots of Selects on the data. Such a scenario is ideal for data caching. But unfortunately, ASP.NET does not allow a dependency to be specified whereby a cache item is dependent on data stored in a database table. Furthermore, in real world Web based systems, the Web server and the Oracle database server could be potentially running on different machines, making this cache invalidation process more challenging. Also most Web-based applications are deployed using Web farms with instances of the same application running on multiple Web servers for load balancing. This scenario makes the database caching problem slightly more complex.
For exploring the solution to the above problem, let's put together a sample Web application to illustrate how it can be implemented. For our example, we use ASP.NET application implemented in VB .Net communicating with the Oracle 9i database using Oracle Data Provider for .NET (ODP).
In this example, consider a table named Employee in the Oracle database. We define a trigger for insert, update and delete operations on the Employee table. This trigger calls a PL/SQL function that serves as a wrapper for a Java stored procedure. This Java stored procedure in turn will be responsible for updating the Cache dependency file.
ASP.NET Tier Implementation Using VB.NET
On the ASP.NET tier, we have a listener class containing a callback method to handle the notification when the cache item gets invalidated.
The callback method RemovedCallback is registered by using a delegate function. The callback method onRemove declaration must have the same signature as the CacheItemRemovedCallback delegate declaration.
Dim onRemove As CacheItemRemovedCallback = Nothing
onRemove = New CacheItemRemovedCallback(AddressOf RemovedCallback)
The definition for the listener event handler method RemovedCallback responsible for handling the notification from the database trigger is illustrated below. When the cache item gets invalidated, data is retrieved from the database by using the database method call getRecordFromdatabase(). The parameter "key" refers to the index location for the item removed from the cache. The parameter "value" refers to the data object removed from the cache. The parameter "CacheItemRemovedReason" specifies the reason causing the data item to be removed from the cache.
PublicSub RemovedCallback(ByVal key AsString, ByVal value AsObject,
ByVal reason As CacheItemRemovedReason)
Dim Source As DataView
Source = getRecordFromdatabase()
Cache.Insert("employeeTable ", Source, New
System.Web.Caching.CacheDependency("d:\download\tblemployee.txt"),
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.Normal, onRemove)
EndSub
The method getRecordFromdatabase() is responsible for querying the database table Employee and it returns a DataView object reference. It makes use of a stored procedure called getEmployee to abstract the SQL for retrieving the data from the Employee table. The method expects a parameter called p_empid representing the primary key for the Employee table.
PublicFunction getRecordFromdatabase (ByVal p_empid As Int32) As DataView
Dim con As OracleConnection = Nothing
Dim cmd As OracleCommand = Nothing
Dim ds As DataSet = Nothing
Try
con = getDatabaseConnection(
"UserId=scott;Password=tiger;Data Source=testingdb;")
cmd = New OracleCommand("Administrator.getEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("employeeId",
OracleDbType.Int64)).Value = p_empid
Dim param AsNew OracleParameter("RC1", OracleDbType.RefCursor)
cmd.Parameters.Add(param).Direction = ParameterDirection.Output
Dim myCommand AsNew OracleDataAdapter(cmd)
ds = New DataSet
myCommand.Fill(ds)
Dim table As DataTable = ds.Tables(0)
Dim index As Int32 = table.Rows.Count
Return ds.Tables(0).DefaultView
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getRecordFromdatabase () " + ex.Message, ex)
Finally
Try
cmd.Dispose()
Catch ex As Exception
Finally
cmd = Nothing
EndTry
Try
con.Close()
Catch ex As Exception
Finally
con = Nothing
EndTry
EndTry
EndFunction
The function getDatabaseConnection accepts a connectionstring as an argument and returns an OracleConnection object reference.
PublicFunction getDatabaseConnection(ByVal strconnection as string) As
OracleConnection
Dim con As Oracle.DataAccess.Client.OracleConnection = Nothing
Try
con = New Oracle.DataAccess.Client.OracleConnection
con.ConnectionString = strconnection
con.Open()
Return con
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getOracleConnection() " + ex.Message, ex)
EndTry
EndFunction
Oracle Database Tier Implementation
The Trigger body defined for DML events on the Employee Table is shown below. This trigger simply invokes a PL/SQL wrapper function for updating an operating system file called tblemployee.txt. Copies of this file are updated on two different machines called machine1 and machine2 that are running different instances of the same Web application to enable load balancing. Here administrator refers to the owner of the schema objects in the Oracle database.
begin
administrator.plfile('machine1\\download\\ tblemployee.txt');
administrator.plfile('machine2\\download\\ tblemployee.txt');
end;
For updating the cache dependency file, we will need to write a C function or a Java stored procedure. In our example, we chose a Java stored procedure since Oracle database server has a built-in JVM, making it easy to write Java stored procedures. Adequate memory must be allocated for the Java Pool in the System global area (SGA) of the Oracle instance. The static method updateFile accepts an absolute pathname as a parameter and creates the cache dependency file in the appropriate directory. If the file already exists, it is deleted and created again.
import java.io.*;
public class UpdFile {
public static void updateFile(String filename) {
try {
File f = new File(filename);
f.delete();
f.createNewFile();
}
catch (IOException e)
{
// log exception
}
}
};
The pl/sql wrapper implementation is shown below. The wrapper function accepts the filename as a parameter and invokes the method updateFile in the Java stored procedure.
(p_filename IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'UpdFile.updateFile (java.lang.String)';
Database Caching in a Web Farm Deployment
As illustrated in the example we have discussed, Web Servers machine1 and machine2 constitute the Web farm to provide load balancing for our Web application. Each machine runs an instance of the same Web application. In this scenario, each instance of the Web application can have its own copy of the cached data stored in its Cache object. When the employee table changes, the corresponding database trigger updates the file tblemployee.txt on both of these machines. Each instance of the Web application specifies a cache dependency on the local file tblemployee.txt, and the cache for both the instances in the Web Farm gets updated correctly, enabling the data cache on both the instances to remain in sync with the database table Employee.
Conclusion
Data Caching can be an effective technique for optimizing ASP.NET applications using the Oracle database. Although ASP.NET does not allow database dependency to be specified for the cache, Oracle triggers in conjunction with Java stored procedures can be used to extend the power of the ASP.NET cache to enable Oracle database caching. This technique can also be applied to Web Farm deployments
For building scalable and high-performance Web based applications, ASP.NET provides a feature called data caching. Data caching enables programmatic storing of frequently accessed data objects in memory. This feature can be extended to vastly improve performance for ASP.NET applications that query data stored in an Oracle database. This article describes a strategy for caching Oracle database data in ASP.NET Web applications deployed using a Web Farm environment. This technique enables caching of frequently accessed Oracle database data in memory rather than making frequent database calls to retrieve the data. This helps to avoid unnecessary roundtrips to the Oracle database server. Further the article proposes an implementation for maintaining the cached data so it is always in sync with the corresponding data in the Oracle database.
Data Caching in ASP.NET
Data Caching in ASP.NET is facilitated via the Cache class and the CacheDependency class in the System.Web.Caching namespace. The Cache class provides methods for inserting data and retrieving data from the cache. The CacheDependency class enables dependencies to be specified for the data items placed in the cache. An expiration policy for an item can be specified when we add it to the cache using the Insert method or Add method. We can define the life span for an item in the cache by using the absoluteExpiration parameter in the Insert method. This parameter allows one to specify the exact datetime that the corresponding data item will expire. One can also use the slidingExpiration parameter, specifying the elapsed time before the item will expire based on the time it was accessed. Once the item expires, it is removed from the cache. Attempts to access it will return a null value unless the item is added to the Cache again.
Specifying Dependencies for Cache
ASP.NET allows us to define the dependency of a cached item based on an external file, a directory, or another cached item. These are described as file dependencies and key dependencies. If a dependency changes, the cached item gets automatically invalidated and removed from the cache. We can use this approach to delete items from the cache when the corresponding data source changes. For example, if we write an application that retrieves data from an XML file and displays it in a grid, we can store the data from the file in the Cache and specify a Cache dependency on the XML file. When the XML file is updated, the data item gets removed from the cache. When this event occurs, the application reads the XML file again, and the latest copy of the data item is inserted into the cache again. Further, callback event handlers can be specified as a listener for getting notified when the cache item gets deleted from the cache. This eliminates the need to continuously poll the cache to determine whether the data item has been invalidated.
ASP.NET Cache Dependency on Oracle Database
Let us consider a scenario where data is stored in the Oracle database and accessed by an ASP.NET application using ADO.NET. Furthermore, let us assume that the data in the database table(s) is generally static but accessed frequently by the Web application. In a nutshell, there are very few DML operations on the table but lots of Selects on the data. Such a scenario is ideal for data caching. But unfortunately, ASP.NET does not allow a dependency to be specified whereby a cache item is dependent on data stored in a database table. Furthermore, in real world Web based systems, the Web server and the Oracle database server could be potentially running on different machines, making this cache invalidation process more challenging. Also most Web-based applications are deployed using Web farms with instances of the same application running on multiple Web servers for load balancing. This scenario makes the database caching problem slightly more complex.
For exploring the solution to the above problem, let's put together a sample Web application to illustrate how it can be implemented. For our example, we use ASP.NET application implemented in VB .Net communicating with the Oracle 9i database using Oracle Data Provider for .NET (ODP).
In this example, consider a table named Employee in the Oracle database. We define a trigger for insert, update and delete operations on the Employee table. This trigger calls a PL/SQL function that serves as a wrapper for a Java stored procedure. This Java stored procedure in turn will be responsible for updating the Cache dependency file.
ASP.NET Tier Implementation Using VB.NET
On the ASP.NET tier, we have a listener class containing a callback method to handle the notification when the cache item gets invalidated.
The callback method RemovedCallback is registered by using a delegate function. The callback method onRemove declaration must have the same signature as the CacheItemRemovedCallback delegate declaration.
Dim onRemove As CacheItemRemovedCallback = Nothing
onRemove = New CacheItemRemovedCallback(AddressOf RemovedCallback)
The definition for the listener event handler method RemovedCallback responsible for handling the notification from the database trigger is illustrated below. When the cache item gets invalidated, data is retrieved from the database by using the database method call getRecordFromdatabase(). The parameter "key" refers to the index location for the item removed from the cache. The parameter "value" refers to the data object removed from the cache. The parameter "CacheItemRemovedReason" specifies the reason causing the data item to be removed from the cache.
PublicSub RemovedCallback(ByVal key AsString, ByVal value AsObject,
ByVal reason As CacheItemRemovedReason)
Dim Source As DataView
Source = getRecordFromdatabase()
Cache.Insert("employeeTable ", Source, New
System.Web.Caching.CacheDependency("d:\download\tblemployee.txt"),
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
CacheItemPriority.Normal, onRemove)
EndSub
The method getRecordFromdatabase() is responsible for querying the database table Employee and it returns a DataView object reference. It makes use of a stored procedure called getEmployee to abstract the SQL for retrieving the data from the Employee table. The method expects a parameter called p_empid representing the primary key for the Employee table.
PublicFunction getRecordFromdatabase (ByVal p_empid As Int32) As DataView
Dim con As OracleConnection = Nothing
Dim cmd As OracleCommand = Nothing
Dim ds As DataSet = Nothing
Try
con = getDatabaseConnection(
"UserId=scott;Password=tiger;Data Source=testingdb;")
cmd = New OracleCommand("Administrator.getEmployee", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("employeeId",
OracleDbType.Int64)).Value = p_empid
Dim param AsNew OracleParameter("RC1", OracleDbType.RefCursor)
cmd.Parameters.Add(param).Direction = ParameterDirection.Output
Dim myCommand AsNew OracleDataAdapter(cmd)
ds = New DataSet
myCommand.Fill(ds)
Dim table As DataTable = ds.Tables(0)
Dim index As Int32 = table.Rows.Count
Return ds.Tables(0).DefaultView
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getRecordFromdatabase () " + ex.Message, ex)
Finally
Try
cmd.Dispose()
Catch ex As Exception
Finally
cmd = Nothing
EndTry
Try
con.Close()
Catch ex As Exception
Finally
con = Nothing
EndTry
EndTry
EndFunction
The function getDatabaseConnection accepts a connectionstring as an argument and returns an OracleConnection object reference.
PublicFunction getDatabaseConnection(ByVal strconnection as string) As
OracleConnection
Dim con As Oracle.DataAccess.Client.OracleConnection = Nothing
Try
con = New Oracle.DataAccess.Client.OracleConnection
con.ConnectionString = strconnection
con.Open()
Return con
Catch ex As Exception
ThrowNew Exception("Exception in Database Tier Method
getOracleConnection() " + ex.Message, ex)
EndTry
EndFunction
Oracle Database Tier Implementation
The Trigger body defined for DML events on the Employee Table is shown below. This trigger simply invokes a PL/SQL wrapper function for updating an operating system file called tblemployee.txt. Copies of this file are updated on two different machines called machine1 and machine2 that are running different instances of the same Web application to enable load balancing. Here administrator refers to the owner of the schema objects in the Oracle database.
begin
administrator.plfile('machine1\\download\\ tblemployee.txt');
administrator.plfile('machine2\\download\\ tblemployee.txt');
end;
For updating the cache dependency file, we will need to write a C function or a Java stored procedure. In our example, we chose a Java stored procedure since Oracle database server has a built-in JVM, making it easy to write Java stored procedures. Adequate memory must be allocated for the Java Pool in the System global area (SGA) of the Oracle instance. The static method updateFile accepts an absolute pathname as a parameter and creates the cache dependency file in the appropriate directory. If the file already exists, it is deleted and created again.
import java.io.*;
public class UpdFile {
public static void updateFile(String filename) {
try {
File f = new File(filename);
f.delete();
f.createNewFile();
}
catch (IOException e)
{
// log exception
}
}
};
The pl/sql wrapper implementation is shown below. The wrapper function accepts the filename as a parameter and invokes the method updateFile in the Java stored procedure.
(p_filename IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'UpdFile.updateFile (java.lang.String)';
Database Caching in a Web Farm Deployment
As illustrated in the example we have discussed, Web Servers machine1 and machine2 constitute the Web farm to provide load balancing for our Web application. Each machine runs an instance of the same Web application. In this scenario, each instance of the Web application can have its own copy of the cached data stored in its Cache object. When the employee table changes, the corresponding database trigger updates the file tblemployee.txt on both of these machines. Each instance of the Web application specifies a cache dependency on the local file tblemployee.txt, and the cache for both the instances in the Web Farm gets updated correctly, enabling the data cache on both the instances to remain in sync with the database table Employee.
Conclusion
Data Caching can be an effective technique for optimizing ASP.NET applications using the Oracle database. Although ASP.NET does not allow database dependency to be specified for the cache, Oracle triggers in conjunction with Java stored procedures can be used to extend the power of the ASP.NET cache to enable Oracle database caching. This technique can also be applied to Web Farm deployments