Install Microsoft Visual Studio 2008

Install Oracle Database 10g or later or Oracle Database XE

Install Oracle 11g Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio version 11.1.0.6.20 or later from OTN

Download and unzip the codesnippets.zip file into your working directory.

Open Visual Studio. Select File > Open Project. Navigate to the directory where you unzipped the files in the prerequisites, select the ODP.NET Performance Best Practices solution and click Open.

Select View > Solution Explorer.

Double-click ODP.NET Best Practices.cs to open the code for this tutorial.

Review your connection string to allow the ODP.NET application to connect to Oracle's sample HR schema. You may have to modify the Password and Data Source values for the conString String variable.

Note: If you are accessing a remote Oracle Database instance, then you need to specify the Data Source in //<hostname>/<SID> format.

Scroll down to the Demo 1 section of the code. Note, the code file is interspersed with comments to mark areas where you need to enter code in the following steps. So, make sure you enter the relevant code under the relevant comment section. This is done to ensure the code is entered in proper sequence and to avoid mixing up of code in the file.

To see how statement caching works, you first want to execute a statement 10000 times with Statement Cache Size set to 0.

Set the Statement Cache Size to 0. Append the following code to the con1.ConnectionString statement, as shown in the screenshot below:

"Statement Cache Size=0";

Further down in the same file, the same code is executed, but with statement caching on. Set Statement Cache Size to 1 and re-execute the statement 10000 times. Scroll down and append the following code to the con2.ConnectionString statement, as shown in the screenshot below:

"Statement Cache Size=1";

The application calculates the performance percentage improvement with caching enabled for the statement.

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

The output window should not show any errors or warnings. The code should compile successfully.

Select Debug > Start Without Debugging or press Ctrl+F5.

A command window opens. Wait for the output. Do not close the window.

Note that your results may differ slightly from the results in the screenshot.

As you can see, there can be a substantial performance improvement when using statement caching for SQL or PL/SQL statements that are repeatedly executed. To enable statement caching across all your ODP.NET applications, you can enable statement caching in the Windows Registry as well.

Do not press Enter. To continue program execution, you add code in the program in subsequent topics. Close the command window.

You first execute a query with FetchSize set to 1 row on the OracleDataReader.

Note: When ODP.NET executes an OracleCommand, it first retrieves metadata on how large the row size is and populates the RowSize property. A developer can then set the number of rows to retrieve from the database per round trip by multiplying RowSize by that number. In this example, you are only retrieving one row per round trip.

Scroll down to the Demo 2 section of the code. Locate the for loop. Enter the following code:

reader.FetchSize = cmd.RowSize * 1;

Since RowSize is determined at runtime, the query or underlying schema can change, but the same number of rows will still be retrieved per round trip. You can compare time difference between when FetchSize is set to 100 rows in this step and the time when FetchSize was set to one row.

Scroll down to the next for loop. Enter the following code:

reader.FetchSize = cmd.RowSize * 100;

If you do not comment the for loops in the Demo 1 section, then press Enter after you get Demo 1 section's results to continue program execution for this section.

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

Select Debug > Start Without Debugging or press Ctrl+F5.

Note that your results may differ slightly from the results in the screenshot.

Do not press Enter. To continue program execution, you add code in subsequent topics. Close the command window.

In the Solution Explorer, right-click array.sql and select Open.

Review the procedure. Note that a PL/SQL package MYPACK is created with a PL/SQL stored procedure MYSP. PL/SQL must be used since a PL/SQL associative array is used. The stored procedure has a PL/SQL associative array input parameter that just passes its values back as an output PL/SQL associative array. Select all the code in the file and select ctrl+c to copy it to the clipboard.

Select View > Server Explorer.

You need to create a data connection for the HR user. You can review how to do this in Oracle Developer Tools for Visual Studio tutorial. Right-click the HR.ORCL connection and select Query Window. In this example, ORCL is the database alias, which may be different from the database alias on your system.

Press [Ctrl]+V to paste the code in the Query Window. Select all the text in the Query Window and click Execute.

Once the stored procedure has executed successfully, switch back to ODP.NET Best Practices.cs code to review the code.

Scroll down to Demo 3 section. Enter the following code:

cmd = new OracleCommand("MYPACK.MYSP", con);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2);
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

Note: ODP.NET sets up the call to the MYPACK.MYSP stored procedure and binds its array parameters. The arrays' elements contain values of data type Varchar2. To indicate that arrays are passed in and out of the stored procedure, ODP.NET sets the parameters as PL/SQL associative arrays, which is an Oracle database collection data type.

Now, setup the parameter direction (input or output) and then specify the value for each parameter. Set the param2 to NULL.

Enter the following code:

param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Output;
param1.Value = new string[3]{"Oracle", "Database", "Rules"};
param2.Value = null;

Then you specify the maximum number of elements in the arrays (in this case, 3) and the maximum size of the varchar2 (in this case, 20 characters).

Enter the following code:

param1.Size = 3;
param2.Size = 3;
param1.ArrayBindSize = new int[3]{20,20,20};
param2.ArrayBindSize = new int[3]{20,20,20};

To execute the stored procedure and output the results, enter the following code:

cmd.ExecuteNonQuery();

for(int i=0; i<3; i++)
{
Console.Write((param2.Value as OracleString[])[i]);
Console.WriteLine();
}

If you do not comment the for loops in the Demo 1 and Demo 2 sections, then press Enter twice(after results for previous demos are shown) to continue program execution for this section.

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

Select Debug > Start Without Debugging or press Ctrl+F5.

Examine the results.

Do not press Enter. To continue program execution, you add code in the program in the subsequent topic. Close the command window.

For ODP.NET to batch SQL, anonymous PL/SQL is used.

Note: Anonymous PL/SQL is just a string that starts with a "BEGIN" and terminates with an "END;". In between lies the SQL that needs to be executed in a single round trip.

In this example, you batch three queries to return three result sets. Parameters are bound so that ODP.NET can return the query results to the client. Oracle executes anonymous PL/SQL like any other text command. As such, the command is bound as CommandType.Text.

Scroll down to the Demo 4 section of the code. Enter the following code:

string cmdtxt = "BEGIN " +
"OPEN :1 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 10; " +
"OPEN :2 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 20; " +
"OPEN :3 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 30; " +
"END;";
         

cmd = new OracleCommand(cmdtxt, con);
cmd.CommandType = CommandType.Text;

You then bind three output REF Cursors to the command. In this case you select employees in department 10, 20 and 30 each in a different REF Cursor.

Enter the following code:

OracleParameter p1 = cmd.Parameters.Add("refcursor1", OracleDbType.RefCursor);
p1.Direction = ParameterDirection.Output;

OracleParameter p2 = cmd.Parameters.Add("refcursor2", OracleDbType.RefCursor);
p2.Direction = ParameterDirection.Output;

OracleParameter p3 = cmd.Parameters.Add("refcursor3", OracleDbType.RefCursor);
p3.Direction = ParameterDirection.Output;

Execute the batched statements and then retrieve the data from the 2nd and 3rd parameter without having to fetch the results from the first parameter. This is one of the benefits of using REF Cursors whereby you can retrieve data only when the client requires it. With REF Cursors, you can selectively choose data within a result set to fetch as well. With MARS, you can observe that both OracleDataReaders are actively reading data at the same time.

Enter the following code after the relevant comments:

cmd.ExecuteNonQuery();

OracleDataReader dr1 = ((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader();
OracleDataReader dr2 = ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader();

Add a while loop to retrieve both DataReaders at one time to test if MARS works. Enter the following code to output the results of the two REF Cursors:

while (dr1.Read() && dr2.Read())
{
Console.WriteLine("Employee Name: " + dr1.GetString(0) + ", " + 
           "Employee Dept:" + dr1.GetDecimal(1));
Console.WriteLine("Employee Name: " + dr2.GetString(0) + ", " + 
           "Employee Dept:" + dr2.GetDecimal(1)); 
Console.WriteLine();
}

Now that you have reviewed the code, you can execute the application to see the results. Select Build > Build Solution.

Select Debug > Start Without Debugging or press Ctrl+F5.

Examine the results.