Oracle RMAN Backups: Pushing the "Easy" Button
Shared via AddThis
Friday, 26 June 2009
Wednesday, 24 June 2009
Saturday, 20 June 2009
Debugging PL/SQL from .NET
Debug PL/SQL in Microsoft Visual Studio with Oracle Developer Tools.
Debugging PL/SQL code in Microsoft Visual Studio is as easy as debugging native .NET code, thanks to the PL/SQL debugger in Oracle Developer Tools for Visual Studio .NET (ODT). This column demonstrates how to set up PL/SQL debugging in ODT and how to create and step through PL/SQL code from within your .NET solution.
To use PL/SQL debugging in ODT, you must have the following:
* Access to Oracle9i Database Release 2 (9.2) or Oracle Database 10g
* Oracle Data Provider for .NET (ODP .NET) release 10.2.0.2 or later installed
* ODT (10.2.0.2 or later) installed
* Microsoft Visual Studio 2005 (or Microsoft Visual Studio .NET 2003) installed
To step through the PL/SQL code that accompanies this column, you must also have access to the HR sample schema included with Oracle Database.
The Integrated PL/SQL Debugger
The new, integrated PL/SQL debugger in ODT 10.2.0.2 allows you to remain inside Visual Studio for end-to-end debugging of your .NET and Oracle solution. You can now debug PL/SQL code such as procedures and functions (both standalone and packaged), object methods, and triggers from within the Visual Studio environment in the same way you would debug your C# or VB code. With the ODT integrated PL/SQL debugger, you can do traditional debugging tasks such as setting break points, viewing and modifying variable values, and examining the call stack.
You use the PL/SQL debugger in one of three modes:
Direct database debugging. The direct database debugging mode allows you to debug PL/SQL code directly from the Visual Studio environment. When you use the direct database debugging mode, you do not need a Visual Studio solution or .NET code—you work directly with the PL/SQL code in the database.
External application debugging. If you need to debug the PL/SQL code that is called by a compiled binary or an application that is on a non-Windows platform, use the external application debugging mode. This mode allows you to debug PL/SQL programs that are called by any application (built with Oracle Database client libraries 9.2 or later) located on any platform without requiring you to modify the application.
Multitier application debugging. The most powerful option during the development of a .NET application is the multitier application debugging mode, which enables you to seamlessly debug both .NET and PL/SQL code from within your Visual Studio solution. You can step directly from your .NET code into the PL/SQL code and back out again.
This article shows how to use the multitier application debugging mode. Because this mode allows you to work with both .NET and PL/SQL code simultaneously during a debugging session, I've found it to be the most commonly used of the three modes.
Create a New Connection
The first step to PL/SQL debugging with ODT is to create a new connection in Oracle Explorer in Visual Studio. If Oracle Explorer is not visible, select View -> Oracle Explorer from the main menu. The top-level node of Oracle Explorer is Data Connections. To create a new connection in the Data Connections node, right-click Data Connections and select Add Connection.... In Data source name, select or type in the name of the datasource you want to use. The datasource is also commonly known as the tns-alias or tnsnames file entry. Click Use a specific user name and password. In the User name field, enter HR. In the Password field, enter the password for the HR user in your database. Ensure that the Role list has the value Default. Change the proposed Connection name to HR.ORAMAG. Click Test connection to make sure the connection details are all correctly specified. Click OK to complete the connection setup.
Repeat the steps above to establish an additional connection for the SYSTEM user with the SYSDBA role if you have the privileges to do so. Name the connection SYSTEM.ORAMAG. You will need this SYSTEM connection to grant the appropriate debugging privileges to HR.
Grant the Required Privileges
If you are using Oracle Database 10g (10.1 or later), you or your DBA must grant two necessary privileges to the HR user: DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE. If you are using Oracle9i Database (9.2), only the DEBUG ANY PROCEDURE privilege is required. (As an alternative to the DEBUG ANY PROCEDURE privilege, you can grant both DEBUG and EXECUTE permissions on the particular PL/SQL program being debugged). With these privileges, the HR user can debug PL/SQL code from within the Visual Studio environment.
To grant these privileges, use the DBA connection and the query window provided by ODT. To open the query window, right-click the SYSTEM.ORAMAG node under Data Connections in Oracle Explorer and select Query Window. To grant the first privilege, type GRANT DEBUG CONNECT SESSION TO HR in the query editor and click Execute. To grant the second privilege, delete the existing text in the query editor, type GRANT DEBUG ANY PROCEDURE TO HR, and click Execute.
Create the PL/SQL Package and Body
With a connection to the database created and the necessary privileges granted, you can now create the PL/SQL example code used in this article to demonstrate the features of the PL/SQL debugger. You create a PL/SQL package and a PL/SQL package body. The PL/SQL package describes and exposes (or publishes) the functionality in the package body, which is where the functionality described in the package is implemented. The PL/SQL code example in Listings 1 and 2 determines whether each number in a PL/SQL array is a prime number and creates a new row in the JOBS table using a PL/SQL record.
To create the PL/SQL code, use the query window within ODT. To display the query window, right-click the HR.ORAMAG connection node in Oracle Explorer and select Query Window from the context menu. Enter the text of Listing 1 in the Query Editor field, and click Execute to create the package. Next, replace the text in the Query Editor field with the text of Listing 2 and click Execute to create the package body. The Listing 1 and Listing 2 code is available in the Database.sql script included with this column's code download.
Code Listing 1: The HR.ORAMAG PL/SQL package code
CREATE OR REPLACE PACKAGE "HR"."ORAMAG" IS
-- types for associative arrays that client will pass as arguments
TYPE "T_IN_VALUES" IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE "T_OUT_VALUES" IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
-- procedure that accepts two associative arrays
-- determines if an element is likely prime and
-- sets value in output array
PROCEDURE "DETERMINE_PRIMES" ("P_IN_VALUES" IN T_IN_VALUES, "P_OUT_VALUES" OUT T_OUT_VALUES);
-- function that determines if a number is likely prime
FUNCTION "IS_PRIME" ("P_NUMBER" IN NUMBER) RETURN NUMBER;
-- constants used to return values from function
IS_NOT_A_PRIME CONSTANT NUMBER DEFAULT 0;
IS_A_PRIME CONSTANT NUMBER DEFAULT 1;
-- pl/sql record type for the jobs table
"JOBS_REC" jobs%rowtype;
-- pl/sql procedure to add new job to jobs table
PROCEDURE "ADD_NEW_JOB" ("P_JOB_ID" IN JOBS.JOB_ID%TYPE,
"P_JOB_TITLE" IN JOBS.JOB_TITLE%TYPE,
"P_MIN_SALARY" IN JOBS.MIN_SALARY%TYPE,
"P_MAX_SALARY" IN JOBS.MAX_SALARY%TYPE);
END "ORAMAG";
You must compile the PL/SQL code with debug information to enable debugging. To do this, go to Oracle Explorer and expand the Packages node. Find the ORAMAG package; right-click it; and from the menu, select Compile Debug. The icon next to the ORAMAG package and the individual procedures and functions underneath it will change to reflect that it has been compiled with debug information.
Create the .NET Project
To create a new .NET project to test and debug the PL/SQL code, select File -> New -> Project from the Visual Studio main menu. Select Visual C# Projects from the Project Types list. Select Console Application from the Templates list, and give the new project a name such as SepOct2006 in the Name field. You must add a reference to the ODP.NET assembly to your project to access the PL/SQL code. To add the reference, select Project -> Add Reference from the Visual Studio main menu. Select Oracle.DataAccess.dll from the Component Name list. Now that the reference to the ODP.NET assembly has been added, replace the template C# code generated by Visual Studio with the code in Listing 3.
When you create a new project, the Enable the Visual Studio hosting process debug option is enabled by default. Oracle recommends that you disable it, by selecting Project -> Properties... from the Visual Studio main menu, clicking the Debug tab, and unchecking Enable the Visual Studio hosting process in the Enable Debuggers list. Alternatively, you will need to start and then terminate the debugger once before the PL/SQL debugger can work properly.
Configuring the Debugger Options
To debug the PL/SQL code seamlessly from the Visual Studio environment, you need to configure ODT to locate the PL/SQL code to be debugged: Select Tools -> Options from the Visual Studio main menu, select Oracle Developer Tools from the Options list, and then click the check box next to HR.ORAMAG in the Available Database Connections list. The PL/SQL debugger may identify the code to be debugged by looking for any checked connections, so if you have other connections defined, ensure that they are not checked.
In the same location, you will see a TCP/IP port range. During PL/SQL debugging, Oracle Database will connect to Visual Studio via TCP/IP on a random port within this range. Make sure this range represents open ports on your machine and that they are not blocked by a firewall.
The last step in configuring ODT to debug PL/SQL alongside your .NET code is to enable application debugging. To do this, select Tools -> Oracle Application Debugging from the Visual Studio main menu and ensure that there is a check mark next to Oracle Application Debugging in the menu, indicating that it is enabled.
Debugging the PL/SQL Code
Now that you have created the PL/SQL code, granted the appropriate privileges to the HR user, and created a C# project, you can debug the code from within the Visual Studio environment.
Setting break points. You must set break points in both your C# and PL/SQL code to stop the debugger during execution. Set a break point in your C# code after the ExecuteNonQuery call, which executes the DETERMINE_PRIMES PL/SQL procedure. In addition, set a break point in your PL/SQL code after the BEGIN keyword in the DETERMINE_PRIMES procedure. If you don't set a break point in your PL/SQL code, the debugger will not stop inside the PL/SQL code body. If you don't set a break point in your C# code, the debugger will not stop inside the C# code when you return from the PL/SQL code. To compile and run the solution in debug mode, select Debug -> Start Debugging from the Visual Studio menu. The program will begin running, and the ODT debugger will stop execution on your break point in the PL/SQL code (see Figure 1). Press the F11 key to step through the PL/SQL code line by line.
figures 1
The PL/SQL debugger and array expansion during debugging
Viewing and modifying variable values. While debugging the example, you can watch the C# application set up and call the DETERMINE_PRIMES PL/SQL procedure, which accepts an array in its first parameter. When working with the PL/SQL debugger in Visual Studio, you can see
Next Steps
VISIT the .NET Developer Center
DOWNLOAD
sample code for this column
ODP.NET 10g
Oracle Developer Tools for Visual Studio .NET
each element in an array at debug time with the live data that was passed to the procedure from the .NET application. Figure 1 shows the debugger stopped on the break point in the DETERMINE_PRIMES PL/SQL procedure with the passed-in array P_IN_VALUES expanded in the Locals window, which displays local variables.
In addition to examining types such as arrays while debugging, you can examine other complex types, such as each element in a PL/SQL record type or a user-defined type.
To continue executing the program, press the F5 key until the Visual Studio debugger halts execution on the break point you previously set in the C# code. Set a break point in the ADD_NEW_JOB PL/SQL procedure. You can then press F5 once more to continue execution until you reach the break point in the ADD_NEW_JOB procedure. To step through this procedure line by line, press F11.
Figure 2 illustrates drilling down into the JOBS_REC PL/SQL record type in the ADD_NEW_JOB stored procedure. To view or drill down into a global PL/SQL package variable such as JOBS_REC, you must use the Watch window, a built-in window in Visual Studio that allows you to examine specific program variables. To add JOBS_REC to the Watch window, right-click JOBS_REC in the code and select Add Watch from the context menu.
figures 2
Examining a PL/SQL record type within the PL/SQL debugger
View the PL/SQL call stack. Another important PL/SQL debugging feature is the ability to view the PL/SQL call stack. By examining the call stack, you can determine the code path to the current point in the execution of your program.
To view the call stack, click Visual Studio's Call Stack tab, which is available by default in debug mode. Figure 3 illustrates the call stack within the IS_PRIME PL/SQL function in the package body.
figures 3
Viewing a PL/SQL call stack
You can double-click on any level of the stack to go to the corresponding line of code.
Summary
With the example code provided with this column, you can take your own tour of the PL/SQL debugger. It offers the same debugging features you are already familiar with from your experience in debugging C# or VB code, and the ability to seamlessly debug both .NET and PL/SQL code from a common environment is a welcome addition to the tools available for the .NET and Oracle programmer.
Debugging PL/SQL code in Microsoft Visual Studio is as easy as debugging native .NET code, thanks to the PL/SQL debugger in Oracle Developer Tools for Visual Studio .NET (ODT). This column demonstrates how to set up PL/SQL debugging in ODT and how to create and step through PL/SQL code from within your .NET solution.
To use PL/SQL debugging in ODT, you must have the following:
* Access to Oracle9i Database Release 2 (9.2) or Oracle Database 10g
* Oracle Data Provider for .NET (ODP .NET) release 10.2.0.2 or later installed
* ODT (10.2.0.2 or later) installed
* Microsoft Visual Studio 2005 (or Microsoft Visual Studio .NET 2003) installed
To step through the PL/SQL code that accompanies this column, you must also have access to the HR sample schema included with Oracle Database.
The Integrated PL/SQL Debugger
The new, integrated PL/SQL debugger in ODT 10.2.0.2 allows you to remain inside Visual Studio for end-to-end debugging of your .NET and Oracle solution. You can now debug PL/SQL code such as procedures and functions (both standalone and packaged), object methods, and triggers from within the Visual Studio environment in the same way you would debug your C# or VB code. With the ODT integrated PL/SQL debugger, you can do traditional debugging tasks such as setting break points, viewing and modifying variable values, and examining the call stack.
You use the PL/SQL debugger in one of three modes:
Direct database debugging. The direct database debugging mode allows you to debug PL/SQL code directly from the Visual Studio environment. When you use the direct database debugging mode, you do not need a Visual Studio solution or .NET code—you work directly with the PL/SQL code in the database.
External application debugging. If you need to debug the PL/SQL code that is called by a compiled binary or an application that is on a non-Windows platform, use the external application debugging mode. This mode allows you to debug PL/SQL programs that are called by any application (built with Oracle Database client libraries 9.2 or later) located on any platform without requiring you to modify the application.
Multitier application debugging. The most powerful option during the development of a .NET application is the multitier application debugging mode, which enables you to seamlessly debug both .NET and PL/SQL code from within your Visual Studio solution. You can step directly from your .NET code into the PL/SQL code and back out again.
This article shows how to use the multitier application debugging mode. Because this mode allows you to work with both .NET and PL/SQL code simultaneously during a debugging session, I've found it to be the most commonly used of the three modes.
Create a New Connection
The first step to PL/SQL debugging with ODT is to create a new connection in Oracle Explorer in Visual Studio. If Oracle Explorer is not visible, select View -> Oracle Explorer from the main menu. The top-level node of Oracle Explorer is Data Connections. To create a new connection in the Data Connections node, right-click Data Connections and select Add Connection.... In Data source name, select or type in the name of the datasource you want to use. The datasource is also commonly known as the tns-alias or tnsnames file entry. Click Use a specific user name and password. In the User name field, enter HR. In the Password field, enter the password for the HR user in your database. Ensure that the Role list has the value Default. Change the proposed Connection name to HR.ORAMAG. Click Test connection to make sure the connection details are all correctly specified. Click OK to complete the connection setup.
Repeat the steps above to establish an additional connection for the SYSTEM user with the SYSDBA role if you have the privileges to do so. Name the connection SYSTEM.ORAMAG. You will need this SYSTEM connection to grant the appropriate debugging privileges to HR.
Grant the Required Privileges
If you are using Oracle Database 10g (10.1 or later), you or your DBA must grant two necessary privileges to the HR user: DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE. If you are using Oracle9i Database (9.2), only the DEBUG ANY PROCEDURE privilege is required. (As an alternative to the DEBUG ANY PROCEDURE privilege, you can grant both DEBUG and EXECUTE permissions on the particular PL/SQL program being debugged). With these privileges, the HR user can debug PL/SQL code from within the Visual Studio environment.
To grant these privileges, use the DBA connection and the query window provided by ODT. To open the query window, right-click the SYSTEM.ORAMAG node under Data Connections in Oracle Explorer and select Query Window. To grant the first privilege, type GRANT DEBUG CONNECT SESSION TO HR in the query editor and click Execute. To grant the second privilege, delete the existing text in the query editor, type GRANT DEBUG ANY PROCEDURE TO HR, and click Execute.
Create the PL/SQL Package and Body
With a connection to the database created and the necessary privileges granted, you can now create the PL/SQL example code used in this article to demonstrate the features of the PL/SQL debugger. You create a PL/SQL package and a PL/SQL package body. The PL/SQL package describes and exposes (or publishes) the functionality in the package body, which is where the functionality described in the package is implemented. The PL/SQL code example in Listings 1 and 2 determines whether each number in a PL/SQL array is a prime number and creates a new row in the JOBS table using a PL/SQL record.
To create the PL/SQL code, use the query window within ODT. To display the query window, right-click the HR.ORAMAG connection node in Oracle Explorer and select Query Window from the context menu. Enter the text of Listing 1 in the Query Editor field, and click Execute to create the package. Next, replace the text in the Query Editor field with the text of Listing 2 and click Execute to create the package body. The Listing 1 and Listing 2 code is available in the Database.sql script included with this column's code download.
Code Listing 1: The HR.ORAMAG PL/SQL package code
CREATE OR REPLACE PACKAGE "HR"."ORAMAG" IS
-- types for associative arrays that client will pass as arguments
TYPE "T_IN_VALUES" IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE "T_OUT_VALUES" IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
-- procedure that accepts two associative arrays
-- determines if an element is likely prime and
-- sets value in output array
PROCEDURE "DETERMINE_PRIMES" ("P_IN_VALUES" IN T_IN_VALUES, "P_OUT_VALUES" OUT T_OUT_VALUES);
-- function that determines if a number is likely prime
FUNCTION "IS_PRIME" ("P_NUMBER" IN NUMBER) RETURN NUMBER;
-- constants used to return values from function
IS_NOT_A_PRIME CONSTANT NUMBER DEFAULT 0;
IS_A_PRIME CONSTANT NUMBER DEFAULT 1;
-- pl/sql record type for the jobs table
"JOBS_REC" jobs%rowtype;
-- pl/sql procedure to add new job to jobs table
PROCEDURE "ADD_NEW_JOB" ("P_JOB_ID" IN JOBS.JOB_ID%TYPE,
"P_JOB_TITLE" IN JOBS.JOB_TITLE%TYPE,
"P_MIN_SALARY" IN JOBS.MIN_SALARY%TYPE,
"P_MAX_SALARY" IN JOBS.MAX_SALARY%TYPE);
END "ORAMAG";
You must compile the PL/SQL code with debug information to enable debugging. To do this, go to Oracle Explorer and expand the Packages node. Find the ORAMAG package; right-click it; and from the menu, select Compile Debug. The icon next to the ORAMAG package and the individual procedures and functions underneath it will change to reflect that it has been compiled with debug information.
Create the .NET Project
To create a new .NET project to test and debug the PL/SQL code, select File -> New -> Project from the Visual Studio main menu. Select Visual C# Projects from the Project Types list. Select Console Application from the Templates list, and give the new project a name such as SepOct2006 in the Name field. You must add a reference to the ODP.NET assembly to your project to access the PL/SQL code. To add the reference, select Project -> Add Reference from the Visual Studio main menu. Select Oracle.DataAccess.dll from the Component Name list. Now that the reference to the ODP.NET assembly has been added, replace the template C# code generated by Visual Studio with the code in Listing 3.
When you create a new project, the Enable the Visual Studio hosting process debug option is enabled by default. Oracle recommends that you disable it, by selecting Project -> Properties... from the Visual Studio main menu, clicking the Debug tab, and unchecking Enable the Visual Studio hosting process in the Enable Debuggers list. Alternatively, you will need to start and then terminate the debugger once before the PL/SQL debugger can work properly.
Configuring the Debugger Options
To debug the PL/SQL code seamlessly from the Visual Studio environment, you need to configure ODT to locate the PL/SQL code to be debugged: Select Tools -> Options from the Visual Studio main menu, select Oracle Developer Tools from the Options list, and then click the check box next to HR.ORAMAG in the Available Database Connections list. The PL/SQL debugger may identify the code to be debugged by looking for any checked connections, so if you have other connections defined, ensure that they are not checked.
In the same location, you will see a TCP/IP port range. During PL/SQL debugging, Oracle Database will connect to Visual Studio via TCP/IP on a random port within this range. Make sure this range represents open ports on your machine and that they are not blocked by a firewall.
The last step in configuring ODT to debug PL/SQL alongside your .NET code is to enable application debugging. To do this, select Tools -> Oracle Application Debugging from the Visual Studio main menu and ensure that there is a check mark next to Oracle Application Debugging in the menu, indicating that it is enabled.
Debugging the PL/SQL Code
Now that you have created the PL/SQL code, granted the appropriate privileges to the HR user, and created a C# project, you can debug the code from within the Visual Studio environment.
Setting break points. You must set break points in both your C# and PL/SQL code to stop the debugger during execution. Set a break point in your C# code after the ExecuteNonQuery call, which executes the DETERMINE_PRIMES PL/SQL procedure. In addition, set a break point in your PL/SQL code after the BEGIN keyword in the DETERMINE_PRIMES procedure. If you don't set a break point in your PL/SQL code, the debugger will not stop inside the PL/SQL code body. If you don't set a break point in your C# code, the debugger will not stop inside the C# code when you return from the PL/SQL code. To compile and run the solution in debug mode, select Debug -> Start Debugging from the Visual Studio menu. The program will begin running, and the ODT debugger will stop execution on your break point in the PL/SQL code (see Figure 1). Press the F11 key to step through the PL/SQL code line by line.
figures 1
The PL/SQL debugger and array expansion during debugging
Viewing and modifying variable values. While debugging the example, you can watch the C# application set up and call the DETERMINE_PRIMES PL/SQL procedure, which accepts an array in its first parameter. When working with the PL/SQL debugger in Visual Studio, you can see
Next Steps
VISIT the .NET Developer Center
DOWNLOAD
sample code for this column
ODP.NET 10g
Oracle Developer Tools for Visual Studio .NET
each element in an array at debug time with the live data that was passed to the procedure from the .NET application. Figure 1 shows the debugger stopped on the break point in the DETERMINE_PRIMES PL/SQL procedure with the passed-in array P_IN_VALUES expanded in the Locals window, which displays local variables.
In addition to examining types such as arrays while debugging, you can examine other complex types, such as each element in a PL/SQL record type or a user-defined type.
To continue executing the program, press the F5 key until the Visual Studio debugger halts execution on the break point you previously set in the C# code. Set a break point in the ADD_NEW_JOB PL/SQL procedure. You can then press F5 once more to continue execution until you reach the break point in the ADD_NEW_JOB procedure. To step through this procedure line by line, press F11.
Figure 2 illustrates drilling down into the JOBS_REC PL/SQL record type in the ADD_NEW_JOB stored procedure. To view or drill down into a global PL/SQL package variable such as JOBS_REC, you must use the Watch window, a built-in window in Visual Studio that allows you to examine specific program variables. To add JOBS_REC to the Watch window, right-click JOBS_REC in the code and select Add Watch from the context menu.
figures 2
Examining a PL/SQL record type within the PL/SQL debugger
View the PL/SQL call stack. Another important PL/SQL debugging feature is the ability to view the PL/SQL call stack. By examining the call stack, you can determine the code path to the current point in the execution of your program.
To view the call stack, click Visual Studio's Call Stack tab, which is available by default in debug mode. Figure 3 illustrates the call stack within the IS_PRIME PL/SQL function in the package body.
figures 3
Viewing a PL/SQL call stack
You can double-click on any level of the stack to go to the corresponding line of code.
Summary
With the example code provided with this column, you can take your own tour of the PL/SQL debugger. It offers the same debugging features you are already familiar with from your experience in debugging C# or VB code, and the ability to seamlessly debug both .NET and PL/SQL code from a common environment is a welcome addition to the tools available for the .NET and Oracle programmer.
Improve ODP.NET Performance
Take advantage of connection pooling, fetch size controls, and statement caching.
For features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice for connecting .NET applications with Oracle Database.
Performance-related features that enable your applications to consume fewer database resources and therefore perform and scale better are built into the provider. Three of these features are connection pooling, controlling fetch size, and statement caching.
I ran some tests to confirm that these features could improve application performance. I used version 10.2.0.2.0 of both ODP.NET and Oracle Database for all tests in this article. Note that the statement caching feature was added in ODP.NET version 10.1.0.3.
Connection Pooling Overview
Creating a physical connection to a database can be an expensive operation in terms of time and system resources. During the busiest times, applications with precreated connections that can be allocated when needed perform better than when applications create the connections on demand. Using the connection pooling feature of ODP.NET, your application can create and maintain a ready pool of precreated connections.
By default, connection pooling is enabled in the provider. You can turn connection pooling on or off by setting Pooling—a Boolean connection string attribute—to either TRUE or FALSE, respectively. (Other connection string parameters that control various aspects of connection pooling are fully documented in the ODP.NET documentation.)
When your application uses connection pooling, the provider creates a pool of connections based on the connection string signature. The signature is based on the connection string parameter values. If any one of the connection string parameters is modified, ODP.NET will create a new connection pool for your application when the next connection is requested.
Subsequent connection requests can use a connection from the pool rather than go through the process of creating a new connection. When your application is finished using a connection, the Close or Dispose method returns the connection to the pool rather than physically closing or destroying it. Note that for nonpooled connections, it is best to call the Dispose method, because it explicitly frees system resources.
Connection Pooling in Action
The sample code for this column demonstrates one technique for measuring the performance benefit of using connection pooling. The code in JulyAug2006.cs—available for download and in Listing 1—creates two connection strings: one that uses connection pooling and one that does not. The code then opens and closes the connections in a loop. The number of iterations is controlled by a parameter passed into the test method.
The connection string with no connection pooling—where Pooling=FALSE—uses the sample HR user and the default password:
string no_pool =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=false"
The connection string with connection pooling—where Pooling=TRUE—also uses the sample HR user and the default password:
string with_pool =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true"
The main method calls each of the member methods to perform the tests. One member method—ConnectionPoolTest—captures the current time, executes the connection testing loop, and captures the current time again. The code then calculates the difference between the two time intervals.
On my test system, I achieved the following results with an iteration value of 100:
Beginning Connection Pool Test with 100 iterations...
No Pooling: 7.8312608 total seconds.
With Pooling: 0.1301872 total seconds.
As you can see, with no connection pooling, the loop executed in almost 8 seconds. With connection pooling, the loop executed in approximately .13 seconds—dramatically quicker.
Making specific connection pooling parameter recommendations is difficult, because workloads can vary greatly. One general recommendation is to have a sufficiently high minimum pool size. I have often seen connection pools drained to the minimum during a lull in activity. When activity picks up rapidly, the connection pool has to create connections quickly rather than use connections in the pool. In most cases, the application server reduces the number of connections and remains relatively idle. Thus, there is no reason to have a low minimum pool size.
Controlling Fetch Size
Retrieving data from the database tier to the middle tier (or client tier) is one of the most expensive operations with respect to performance. If the end user consumes a lot of data, you will want your application to minimize the number of round-trip data fetches.
By default, ODP.NET will read 64KB of data from a result set at a time. You change this value by setting the FetchSize attribute for an OracleDataReader object. However, rather than arbitrarily setting the fetch size, ODP.NET provides the ability to control the number of rows that are fetched from a result set per round trip. The two properties you use to do this are RowSize and FetchSize.
RowSize is a property of the OracleCommand object, and the value for this property is assigned by ODP.NET when the statement associated with the command object is executed. You explicitly set the value of the FetchSize property. For example, if you want to retrieve 100 rows at a time from the result set per round trip, you set the FetchSize property:
dr.FetchSize = cmd.RowSize * 100;
Here, dr represents an OracleDataReader and cmd represents an OracleCommand object.
Fetch Size Benefits
To illustrate the benefits of controlling fetch size, I used SQL*Plus to create a simple new table—fetch_test—in the HR sample schema:
create table fetch_test
(
id number primary key,
data varchar2(32)
);
and populated it with 100,000 rows:
begin
for i in 1..100000 loop
insert into fetch_test
values (i, to_char(i, '000000'));
end loop;
end;
/
You could also use a tool suite such as Oracle Developer Tools for Visual Studio .NET to accomplish this. Be sure to issue a COMMIT after populating the table if you are using a tool that does not do so by default.
After populating the table with data, I gathered statistics so the optimizer could determine the appropriate way to access the data. I used the dbms_stats package:
begin
dbms_stats.gather_table_stats(
ownname => 'HR',
tabname => 'FETCH_TEST',
estimate_percent => null,
method_opt => 'for all indexed columns size 1',
cascade => TRUE);
end;
/
Next I performed a test similar to that used for connection pooling: capture the current time, set the fetch size to an appropriate value, read the data from the result set, capture the current time again, and compute the difference. I created a test method called FetchTest to set the fetch size and read the data—it accepts an integer parameter that controls the number of rows to be fetched. To set the fetch size and read the data, I used the following:
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from fetch_test";
OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRows;
while (dr.Read())
{
rowsFetched++;
}
I then executed the test method, using five different row values. The results of the tests are summarized here:
ROWS TIME
________ __________________________
1 5.4378192 seconds
10 1.1115984 seconds
100 0.2703888 seconds
1000 0.200288 seconds
10000 0.1902736 seconds
As you can see, if you are tempted to fetch only a single row at a time to "save resources and increase performance" (I have sometimes heard this advice), you will actually be dramatically reducing, rather than increasing, runtime performance. In addition, for this set of data, there is not much performance increase when fetching more than 1,000 rows at a time. Of course, this is also a narrow data set (with only two small columns). Apply this technique to a result set representative of your environment to determine optimal values for setting your fetch size.
Of course, there is a cost if the fetch size is arbitrarily large. More client-side memory and processor cycles will be needed to store and manage a larger amount of data. The goal is to find a high-performing balance between the number of round trips and the amount of data retrieved per trip.
Statement Caching
Using bind variables in your application is a good practice. Bind variables can help ensure that Oracle Database can reuse SQL statements in your session and in other sessions that may use the same statement. (See the ODP.NET column in the September/October 2005 issue of Oracle Magazine, for more details on the benefits of bind variables.) For those applications about which you know, at design time, which statements will be reused, you can further increase the performance benefits of bind variables by using a feature exposed by ODP.NET known as statement caching.
When you use statement caching, you are telling Oracle Database to keep a copy of the statement cached in memory on the server, because you will be reusing that statement in your session. This allows Oracle Database to more quickly use that statement upon subsequent executions. Note that statement caching does not increase the runtime performance of the statement itself. Statement caching allows Oracle Database to find the statement in a memory area that is associated with your session, rather than all sessions, and avoid reparsing the statement. Keep in mind that the statement, not the data, is cached. Executing a cached statement always returns the latest database results.
You enable the statement caching feature by setting the Statement Cache Size connection string parameter. The default value of this parameter is 0, meaning that statement caching is disabled by default. You set this parameter to the number of statements you plan to keep cached during your application's lifetime. For example, if you plan to cache 16 statements, you will set this value to 16. ODP.NET will then cache the 16 most recently used statements. The 17th most recently used statement is aged out of the cache by the last 16 unique statements.
With statement caching enabled, ODP.NET will automatically cache any statement you execute. If you have enabled statement caching and you do not want to place a statement in the cache, set the AddToStatementCache property on OracleCommand to false:
OracleCommand cmd =
new OracleCommand();
cmd.Connection = con;
cmd.AddToStatementCache = false;
cmd.CommandText = "select data from fetch_test where id = :1";
SQL and PL/SQL statements can be stored in the statement cache.
Statement Caching Results
I employed the same pattern to test statement caching as I did for connection pooling and controlling the fetch size. I used the fetch_test table to repeatedly select data without using statement caching and then repeated the same test with statement caching.
Here is the connection string for the test without statement caching:
string constr =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true";
And here is the connection string for the test with statement caching:
string constr =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true;
Statement Cache Size=1";
Note that I set the cache size to only 1. This is because I knew that I would be caching only a single statement. The bind variable may change, but the statement will remain cached. If a literal value were used instead of a bind value, Oracle Database would need to reparse the statement every time the literal value changed, instead of using the cache. In addition, the new statement would replace the statement currently in the cache.
I executed the sample statement 1,000 times without statement caching and 1,000 times with statement caching, which produced these results on my system:
No Statement Caching:
0.6409216 total seconds.
With Statement Caching:
0.3905616 total seconds.
Next Steps
VISIT .NET Developer Center
DOWNLOAD
the sample code for this column
ODP.NET 10g
Oracle Developer Tools for Visual Studio .NET
The latest ODP.NET releases use a Windows Registry parameter that turns on statement caching by default and sets the cache size to 10. The registry setting applies to all applications using this ODP.NET instance. You can override the registry value for each connection pool by setting Statement Cache Size to another value.
Going Further
I performed all of these tests on a single laptop hosting both the database and the client application. Although it is convenient to test features in such an environment, for best results test your applications in an environment that accurately reflects your production environment. When the client and database server are on separate machines, the performance improvement should be even greater. Of course, your tests are almost certain to show different numbers in the results than those presented here.
This investigation limited performance observations to time only. However, other performance benefits include reduced network traffic and reduced parsing in the database. I encourage you to work with your system administrators to develop tests and measurement techniques applicable to your environment. By using the features offered by ODP.NET, you can increase performance in your applications with a minimum of effort.
For features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice for connecting .NET applications with Oracle Database.
Performance-related features that enable your applications to consume fewer database resources and therefore perform and scale better are built into the provider. Three of these features are connection pooling, controlling fetch size, and statement caching.
I ran some tests to confirm that these features could improve application performance. I used version 10.2.0.2.0 of both ODP.NET and Oracle Database for all tests in this article. Note that the statement caching feature was added in ODP.NET version 10.1.0.3.
Connection Pooling Overview
Creating a physical connection to a database can be an expensive operation in terms of time and system resources. During the busiest times, applications with precreated connections that can be allocated when needed perform better than when applications create the connections on demand. Using the connection pooling feature of ODP.NET, your application can create and maintain a ready pool of precreated connections.
By default, connection pooling is enabled in the provider. You can turn connection pooling on or off by setting Pooling—a Boolean connection string attribute—to either TRUE or FALSE, respectively. (Other connection string parameters that control various aspects of connection pooling are fully documented in the ODP.NET documentation.)
When your application uses connection pooling, the provider creates a pool of connections based on the connection string signature. The signature is based on the connection string parameter values. If any one of the connection string parameters is modified, ODP.NET will create a new connection pool for your application when the next connection is requested.
Subsequent connection requests can use a connection from the pool rather than go through the process of creating a new connection. When your application is finished using a connection, the Close or Dispose method returns the connection to the pool rather than physically closing or destroying it. Note that for nonpooled connections, it is best to call the Dispose method, because it explicitly frees system resources.
Connection Pooling in Action
The sample code for this column demonstrates one technique for measuring the performance benefit of using connection pooling. The code in JulyAug2006.cs—available for download and in Listing 1—creates two connection strings: one that uses connection pooling and one that does not. The code then opens and closes the connections in a loop. The number of iterations is controlled by a parameter passed into the test method.
The connection string with no connection pooling—where Pooling=FALSE—uses the sample HR user and the default password:
string no_pool =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=false"
The connection string with connection pooling—where Pooling=TRUE—also uses the sample HR user and the default password:
string with_pool =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true"
The main method calls each of the member methods to perform the tests. One member method—ConnectionPoolTest—captures the current time, executes the connection testing loop, and captures the current time again. The code then calculates the difference between the two time intervals.
On my test system, I achieved the following results with an iteration value of 100:
Beginning Connection Pool Test with 100 iterations...
No Pooling: 7.8312608 total seconds.
With Pooling: 0.1301872 total seconds.
As you can see, with no connection pooling, the loop executed in almost 8 seconds. With connection pooling, the loop executed in approximately .13 seconds—dramatically quicker.
Making specific connection pooling parameter recommendations is difficult, because workloads can vary greatly. One general recommendation is to have a sufficiently high minimum pool size. I have often seen connection pools drained to the minimum during a lull in activity. When activity picks up rapidly, the connection pool has to create connections quickly rather than use connections in the pool. In most cases, the application server reduces the number of connections and remains relatively idle. Thus, there is no reason to have a low minimum pool size.
Controlling Fetch Size
Retrieving data from the database tier to the middle tier (or client tier) is one of the most expensive operations with respect to performance. If the end user consumes a lot of data, you will want your application to minimize the number of round-trip data fetches.
By default, ODP.NET will read 64KB of data from a result set at a time. You change this value by setting the FetchSize attribute for an OracleDataReader object. However, rather than arbitrarily setting the fetch size, ODP.NET provides the ability to control the number of rows that are fetched from a result set per round trip. The two properties you use to do this are RowSize and FetchSize.
RowSize is a property of the OracleCommand object, and the value for this property is assigned by ODP.NET when the statement associated with the command object is executed. You explicitly set the value of the FetchSize property. For example, if you want to retrieve 100 rows at a time from the result set per round trip, you set the FetchSize property:
dr.FetchSize = cmd.RowSize * 100;
Here, dr represents an OracleDataReader and cmd represents an OracleCommand object.
Fetch Size Benefits
To illustrate the benefits of controlling fetch size, I used SQL*Plus to create a simple new table—fetch_test—in the HR sample schema:
create table fetch_test
(
id number primary key,
data varchar2(32)
);
and populated it with 100,000 rows:
begin
for i in 1..100000 loop
insert into fetch_test
values (i, to_char(i, '000000'));
end loop;
end;
/
You could also use a tool suite such as Oracle Developer Tools for Visual Studio .NET to accomplish this. Be sure to issue a COMMIT after populating the table if you are using a tool that does not do so by default.
After populating the table with data, I gathered statistics so the optimizer could determine the appropriate way to access the data. I used the dbms_stats package:
begin
dbms_stats.gather_table_stats(
ownname => 'HR',
tabname => 'FETCH_TEST',
estimate_percent => null,
method_opt => 'for all indexed columns size 1',
cascade => TRUE);
end;
/
Next I performed a test similar to that used for connection pooling: capture the current time, set the fetch size to an appropriate value, read the data from the result set, capture the current time again, and compute the difference. I created a test method called FetchTest to set the fetch size and read the data—it accepts an integer parameter that controls the number of rows to be fetched. To set the fetch size and read the data, I used the following:
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from fetch_test";
OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRows;
while (dr.Read())
{
rowsFetched++;
}
I then executed the test method, using five different row values. The results of the tests are summarized here:
ROWS TIME
________ __________________________
1 5.4378192 seconds
10 1.1115984 seconds
100 0.2703888 seconds
1000 0.200288 seconds
10000 0.1902736 seconds
As you can see, if you are tempted to fetch only a single row at a time to "save resources and increase performance" (I have sometimes heard this advice), you will actually be dramatically reducing, rather than increasing, runtime performance. In addition, for this set of data, there is not much performance increase when fetching more than 1,000 rows at a time. Of course, this is also a narrow data set (with only two small columns). Apply this technique to a result set representative of your environment to determine optimal values for setting your fetch size.
Of course, there is a cost if the fetch size is arbitrarily large. More client-side memory and processor cycles will be needed to store and manage a larger amount of data. The goal is to find a high-performing balance between the number of round trips and the amount of data retrieved per trip.
Statement Caching
Using bind variables in your application is a good practice. Bind variables can help ensure that Oracle Database can reuse SQL statements in your session and in other sessions that may use the same statement. (See the ODP.NET column in the September/October 2005 issue of Oracle Magazine, for more details on the benefits of bind variables.) For those applications about which you know, at design time, which statements will be reused, you can further increase the performance benefits of bind variables by using a feature exposed by ODP.NET known as statement caching.
When you use statement caching, you are telling Oracle Database to keep a copy of the statement cached in memory on the server, because you will be reusing that statement in your session. This allows Oracle Database to more quickly use that statement upon subsequent executions. Note that statement caching does not increase the runtime performance of the statement itself. Statement caching allows Oracle Database to find the statement in a memory area that is associated with your session, rather than all sessions, and avoid reparsing the statement. Keep in mind that the statement, not the data, is cached. Executing a cached statement always returns the latest database results.
You enable the statement caching feature by setting the Statement Cache Size connection string parameter. The default value of this parameter is 0, meaning that statement caching is disabled by default. You set this parameter to the number of statements you plan to keep cached during your application's lifetime. For example, if you plan to cache 16 statements, you will set this value to 16. ODP.NET will then cache the 16 most recently used statements. The 17th most recently used statement is aged out of the cache by the last 16 unique statements.
With statement caching enabled, ODP.NET will automatically cache any statement you execute. If you have enabled statement caching and you do not want to place a statement in the cache, set the AddToStatementCache property on OracleCommand to false:
OracleCommand cmd =
new OracleCommand();
cmd.Connection = con;
cmd.AddToStatementCache = false;
cmd.CommandText = "select data from fetch_test where id = :1";
SQL and PL/SQL statements can be stored in the statement cache.
Statement Caching Results
I employed the same pattern to test statement caching as I did for connection pooling and controlling the fetch size. I used the fetch_test table to repeatedly select data without using statement caching and then repeated the same test with statement caching.
Here is the connection string for the test without statement caching:
string constr =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true";
And here is the connection string for the test with statement caching:
string constr =
"User Id=hr;
Password=hr;
Data Source=oramag;
Enlist=false;
Pooling=true;
Statement Cache Size=1";
Note that I set the cache size to only 1. This is because I knew that I would be caching only a single statement. The bind variable may change, but the statement will remain cached. If a literal value were used instead of a bind value, Oracle Database would need to reparse the statement every time the literal value changed, instead of using the cache. In addition, the new statement would replace the statement currently in the cache.
I executed the sample statement 1,000 times without statement caching and 1,000 times with statement caching, which produced these results on my system:
No Statement Caching:
0.6409216 total seconds.
With Statement Caching:
0.3905616 total seconds.
Next Steps
VISIT .NET Developer Center
DOWNLOAD
the sample code for this column
ODP.NET 10g
Oracle Developer Tools for Visual Studio .NET
The latest ODP.NET releases use a Windows Registry parameter that turns on statement caching by default and sets the cache size to 10. The registry setting applies to all applications using this ODP.NET instance. You can override the registry value for each connection pool by setting Statement Cache Size to another value.
Going Further
I performed all of these tests on a single laptop hosting both the database and the client application. Although it is convenient to test features in such an environment, for best results test your applications in an environment that accurately reflects your production environment. When the client and database server are on separate machines, the performance improvement should be even greater. Of course, your tests are almost certain to show different numbers in the results than those presented here.
This investigation limited performance observations to time only. However, other performance benefits include reduced network traffic and reduced parsing in the database. I encourage you to work with your system administrators to develop tests and measurement techniques applicable to your environment. By using the features offered by ODP.NET, you can increase performance in your applications with a minimum of effort.
Following the Changes, Part Two
Use database change notification with ODP.NET and Oracle Database 10g Release 2.
You can access data from Oracle databases in .NET applications in different ways, but for features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice.
In the last issue ("Following the Changes, Part One," March/April 2006), I examined the new Database Change Notification feature introduced in Oracle Database 10g Release 2. In this issue, I examine in greater detail the classes that support change notification and show how to take advantage of additional ODP.NET features.
First, a Review
The Database Change Notification feature enables the database to automatically notify your application when a change occurs. This change can be in one or more of the values in a result set, in the underlying schema objects, or in the state of the database itself.
Using Database Change Notification is a three-step process: You register a query with the database, receive notification when a change occurs, and respond to the notification in your application. Any datatype can be included in the registered query, and all objects can be queried, with the exception of materialized views; fixed tables and views, such as V$ tables; and objects accessed through a database link.
The ODP.NET notification framework—in conjunction with Database Change Notification—supports the following activities
* Creating a notification registration
* Grouping multiple change notifications into one request
* Registering for database change notification
* Removing notification registration
* Ensuring change notification persistence—guaranteeing that change notification is sent
* Retrieving notification information, including the object name, schema name, database events, and ROWID
* Defining the listener port number
The sample code download in the last issue demonstrates the Database Change Notification process and uses the default values for the ODP.NET objects that support change notification. Two important limitations are inherent in this sample code: Only a single change notification event is generated by the database per registration, and the application does not retrieve the actual changed data. In this issue, I extend this sample code to generate and receive multiple change notifications in a single registration and provide the information needed so you can retrieve the changed data. To retrieve the changed rows, you need to include a pseudocolumn—ROWID—in the registered query.
The ROWID Pseudocolumn
The pseudocolumn construct allows you to include a column name in a query for a column that is not part of a table definition. However, you use a pseudocolumn in a query exactly as you would a real table column. Various pseudocolumns are available in Oracle Database; they are documented in chapter 3 of the Oracle Database 10g Release 2 SQL Reference manual.
A ROWID value represents the address of a particular database row. Selecting a ROWID as part of a query is trivial—you simply include the ROWID pseudocolumn in the query text. The ROWID pseudocolumn is of particular interest when your application uses Database Change Notification because it allows the application to retrieve only rows that have changed (rather than reissuing the original query on a change notification event to retrieve all the rows that make up a result set).
To retrieve only the changed rows, include the ROWID pseudocolumn as part of the query you register with the database. Alternatively, set the AddRowid property of the OracleCommand object to true, and ODP.NET will automatically add the ROWID pseudocolumn to your registered query.
Listing 1, also available with the complete code download for this article, contains the main class file from the sample code download and registers a Database Change Notification query:
select
rowid,
first_name,
last_name,
salary
from
employees
where
employee_id = :1
Code Listing 1: Main class file
using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
namespace DatabaseChangeNotification02
{
///
/// Summary description for Class1.
///
class ChangeNotificationSample
{
// connection object for the database
static OracleConnection con;
///
/// The main entry point for the application.
///
[STAThread]
static void Main(string[] args)
{
// sql statement to create result set that we will watch for changes
// include the rowid to use to retrieve data that has changed
string sql = "select rowid, first_name, last_name, salary from employees where employee_id = :1";
// ensure you change this to an appropriate setting for your environment
string constr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";
// create and open connection to database
con = new OracleConnection(constr);
con.Open();
// create the command object
OracleCommand cmd = new OracleCommand(sql, con);
// create parameter object for the employee id
OracleParameter p_id = new OracleParameter();
p_id.OracleDbType = OracleDbType.Decimal;
p_id.Value = 149;
// add parameter to the collection
cmd.Parameters.Add(p_id);
// create the dependency object
// note this does not perform the registration
// it only defines the relationship
OracleDependency dep = new OracleDependency(cmd);
// set notification to persist after first notification is received
// this will allow all notifications to be received instead of just
// a single notification
cmd.Notification.IsNotifiedOnce = false;
// define the event handler to invoke when the change notification
// is received
dep.OnChange += new OnChangeEventHandler(OnDatabaseNotification);
// execute the command (ignore the actual result set here)
// this performs the registration that was defined when
// the dependency object was created
cmd.ExecuteNonQuery();
// simply loop forever waiting for the notification from the database
// you need to ctrl+c from the console
// or Stop Debugging if running from within Visual Studio
while (true)
{
Console.WriteLine("Waiting for notification...");
Thread.Sleep(2000);
}
}
public static void OnDatabaseNotification(object src, OracleNotificationEventArgs args)
{
// this method is invoked each time a change notification
// is received from the database
// sql statement to retrieve changed data using the rowid
// including the rowid here is not required but is
// informational in nature
string sql = "select rowid, first_name, last_name, salary from employees where rowid = :1";
// create parameter object to hold the rowid
// get the rowid from the OracleNotificationEventArgs
// parameter to this method
// this assumes there is a single row updated which is the
// case in this sample
OracleParameter p_rowid = new OracleParameter();
p_rowid.Value = args.Details.Rows[0]["rowid"];
// command to retrieve new data
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.Add(p_rowid);
// execute the command
OracleDataReader dr = cmd.ExecuteReader();
// get the data
dr.Read();
// output a simple message with the resource name
Console.WriteLine();
Console.WriteLine("Database Change Notification received!");
DataTable changeDetails = args.Details;
Console.WriteLine("Resource {0} has changed.", changeDetails.Rows[0]["ResourceName"]);
// display the new data
Console.WriteLine();
Console.WriteLine(" New Data:");
Console.WriteLine(" Rowid: {0}", dr.GetString(0));
Console.WriteLine("First Name: {0}", dr.GetString(1));
Console.WriteLine(" Last Name: {0}", dr.GetString(2));
Console.WriteLine(" Salary: {0}", dr.GetDecimal(3).ToString());
Console.WriteLine();
// clean-up
dr.Dispose();
cmd.Dispose();
p_rowid.Dispose();
}
}
}
You can include ROWID in the query you register or set the OracleCommand object AddRowid property to true. In either case, the ROWID value is included as part of the notification data, which allows you to construct a new query to retrieve data based on that ROWID.
The database passes the ROWID value to your application as part of the OracleNotificationEventArgs parameter, and this code passes this parameter to the change notification event handler:
public static void
OnDatabaseNotification(
object src,
OracleNotificationEventArgs args)
You can then access the ROWID through the Details property of the args parameter. The ROWID is represented as a String object in your .NET code. This code accesses the ROWID and assigns the value to an OracleParameter object named p_rowid:
p_rowid.Value =
args.Details.Rows[0]["rowid"]
After you have retrieved the ROWID for a changed row, you construct a new query to get that row from the database. This code does just that:
select
rowid,
first_name,
last_name,
salary
from
employees
where
rowid = :1
As you can see in this example, the query for retrieving the changed row differs from the original query in only one way: The ROWID, rather than the EMPLOYEE_ID, is used in the WHERE clause.
Two Key Classes
The OracleNotificationRequest and OracleNotificationEventArgs classes form the backbone of the Database Change Notification support in ODP.NET. Each of these classes is documented in ODP.NET and available in the Microsoft Visual Studio online integrated help.
OracleNotificationRequest. One important property of the OracleNotificationRequest class is IsNotifiedOnce. This Boolean property has a default value of true and is used to inform the database about whether you wish to receive a notification for each change that occurs or only a single notification. When the property is set to true, the server's notification registration will be removed after the first change. When the property is set to false, the registration will persist, regardless of the number of changes. In the sample code, the property is set to false, allowing the notification registration to persist:
cmd.Notification.IsNotifiedOnce = false;
Next Steps
READ "Following the Changes, Part One"
VISIT .NET Developer Center
READ more about
ROWID
SQL Reference
Database Change Notification
Oracle Data Provider for .NET Developer's Guide
DOWNLOAD
the sample code for this column
Database Change Notification presentation and demonstration (from Oracle OpenWorld 2005)
ODP.NET
Oracle Developer Tools for Visual Studio .NET
If you want to limit the persistence of the registration, you can use the OracleNotificationRequest.Timeout property to specify how long the registration remains alive.
OracleNotificationEventArgs. The Oracle NotificationEventArgs class exposes the data (and metadata) associated with a change event to your application. Several properties exposed by the OracleNotificationEventArgs class may be useful in your applications:
* The Details property is a DataTable that contains information about the current change notification. You use this property to access the ROWID pseudocolumn when handling a change notification event. The ROWID is exposed as the Rowid column in the DataTable.
* The Info property exposes a value of the OracleNotificationInfo enumeration. You use this property to determine what change event occurred—for example, to determine if the driving change in the database was an insert, update, or delete operation.
* The ResourceNames property is a string that contains the name of the schema and the name of the object that was changed.
Sample Code and Beyond
The sample code download (revised from Part One) responds to multiple database change notifications (rather than a single notification) and retrieves the changed row from the database. I encourage you to download and experiment with it. For example, you can extend this code to retrieve multiple changed rows and loop through all the rows in the Details DataTable (accessed via the args parameter) and then build a query such as this:
select
rowid,
first_name,
last_name,
salary
from
employees
where
rowid = :1
or
rowid = :2
or
rowid = :3
Set breakpoints in the code, and run the sample, using the debugger. Examine the properties of the objects. Use this code as a starting point for implementing Database Change Notification in your applications, and experience its benefits for yourself.
You can access data from Oracle databases in .NET applications in different ways, but for features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice.
In the last issue ("Following the Changes, Part One," March/April 2006), I examined the new Database Change Notification feature introduced in Oracle Database 10g Release 2. In this issue, I examine in greater detail the classes that support change notification and show how to take advantage of additional ODP.NET features.
First, a Review
The Database Change Notification feature enables the database to automatically notify your application when a change occurs. This change can be in one or more of the values in a result set, in the underlying schema objects, or in the state of the database itself.
Using Database Change Notification is a three-step process: You register a query with the database, receive notification when a change occurs, and respond to the notification in your application. Any datatype can be included in the registered query, and all objects can be queried, with the exception of materialized views; fixed tables and views, such as V$ tables; and objects accessed through a database link.
The ODP.NET notification framework—in conjunction with Database Change Notification—supports the following activities
* Creating a notification registration
* Grouping multiple change notifications into one request
* Registering for database change notification
* Removing notification registration
* Ensuring change notification persistence—guaranteeing that change notification is sent
* Retrieving notification information, including the object name, schema name, database events, and ROWID
* Defining the listener port number
The sample code download in the last issue demonstrates the Database Change Notification process and uses the default values for the ODP.NET objects that support change notification. Two important limitations are inherent in this sample code: Only a single change notification event is generated by the database per registration, and the application does not retrieve the actual changed data. In this issue, I extend this sample code to generate and receive multiple change notifications in a single registration and provide the information needed so you can retrieve the changed data. To retrieve the changed rows, you need to include a pseudocolumn—ROWID—in the registered query.
The ROWID Pseudocolumn
The pseudocolumn construct allows you to include a column name in a query for a column that is not part of a table definition. However, you use a pseudocolumn in a query exactly as you would a real table column. Various pseudocolumns are available in Oracle Database; they are documented in chapter 3 of the Oracle Database 10g Release 2 SQL Reference manual.
A ROWID value represents the address of a particular database row. Selecting a ROWID as part of a query is trivial—you simply include the ROWID pseudocolumn in the query text. The ROWID pseudocolumn is of particular interest when your application uses Database Change Notification because it allows the application to retrieve only rows that have changed (rather than reissuing the original query on a change notification event to retrieve all the rows that make up a result set).
To retrieve only the changed rows, include the ROWID pseudocolumn as part of the query you register with the database. Alternatively, set the AddRowid property of the OracleCommand object to true, and ODP.NET will automatically add the ROWID pseudocolumn to your registered query.
Listing 1, also available with the complete code download for this article, contains the main class file from the sample code download and registers a Database Change Notification query:
select
rowid,
first_name,
last_name,
salary
from
employees
where
employee_id = :1
Code Listing 1: Main class file
using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
namespace DatabaseChangeNotification02
{
///
/// Summary description for Class1.
///
class ChangeNotificationSample
{
// connection object for the database
static OracleConnection con;
///
/// The main entry point for the application.
///
[STAThread]
static void Main(string[] args)
{
// sql statement to create result set that we will watch for changes
// include the rowid to use to retrieve data that has changed
string sql = "select rowid, first_name, last_name, salary from employees where employee_id = :1";
// ensure you change this to an appropriate setting for your environment
string constr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";
// create and open connection to database
con = new OracleConnection(constr);
con.Open();
// create the command object
OracleCommand cmd = new OracleCommand(sql, con);
// create parameter object for the employee id
OracleParameter p_id = new OracleParameter();
p_id.OracleDbType = OracleDbType.Decimal;
p_id.Value = 149;
// add parameter to the collection
cmd.Parameters.Add(p_id);
// create the dependency object
// note this does not perform the registration
// it only defines the relationship
OracleDependency dep = new OracleDependency(cmd);
// set notification to persist after first notification is received
// this will allow all notifications to be received instead of just
// a single notification
cmd.Notification.IsNotifiedOnce = false;
// define the event handler to invoke when the change notification
// is received
dep.OnChange += new OnChangeEventHandler(OnDatabaseNotification);
// execute the command (ignore the actual result set here)
// this performs the registration that was defined when
// the dependency object was created
cmd.ExecuteNonQuery();
// simply loop forever waiting for the notification from the database
// you need to ctrl+c from the console
// or Stop Debugging if running from within Visual Studio
while (true)
{
Console.WriteLine("Waiting for notification...");
Thread.Sleep(2000);
}
}
public static void OnDatabaseNotification(object src, OracleNotificationEventArgs args)
{
// this method is invoked each time a change notification
// is received from the database
// sql statement to retrieve changed data using the rowid
// including the rowid here is not required but is
// informational in nature
string sql = "select rowid, first_name, last_name, salary from employees where rowid = :1";
// create parameter object to hold the rowid
// get the rowid from the OracleNotificationEventArgs
// parameter to this method
// this assumes there is a single row updated which is the
// case in this sample
OracleParameter p_rowid = new OracleParameter();
p_rowid.Value = args.Details.Rows[0]["rowid"];
// command to retrieve new data
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.Add(p_rowid);
// execute the command
OracleDataReader dr = cmd.ExecuteReader();
// get the data
dr.Read();
// output a simple message with the resource name
Console.WriteLine();
Console.WriteLine("Database Change Notification received!");
DataTable changeDetails = args.Details;
Console.WriteLine("Resource {0} has changed.", changeDetails.Rows[0]["ResourceName"]);
// display the new data
Console.WriteLine();
Console.WriteLine(" New Data:");
Console.WriteLine(" Rowid: {0}", dr.GetString(0));
Console.WriteLine("First Name: {0}", dr.GetString(1));
Console.WriteLine(" Last Name: {0}", dr.GetString(2));
Console.WriteLine(" Salary: {0}", dr.GetDecimal(3).ToString());
Console.WriteLine();
// clean-up
dr.Dispose();
cmd.Dispose();
p_rowid.Dispose();
}
}
}
You can include ROWID in the query you register or set the OracleCommand object AddRowid property to true. In either case, the ROWID value is included as part of the notification data, which allows you to construct a new query to retrieve data based on that ROWID.
The database passes the ROWID value to your application as part of the OracleNotificationEventArgs parameter, and this code passes this parameter to the change notification event handler:
public static void
OnDatabaseNotification(
object src,
OracleNotificationEventArgs args)
You can then access the ROWID through the Details property of the args parameter. The ROWID is represented as a String object in your .NET code. This code accesses the ROWID and assigns the value to an OracleParameter object named p_rowid:
p_rowid.Value =
args.Details.Rows[0]["rowid"]
After you have retrieved the ROWID for a changed row, you construct a new query to get that row from the database. This code does just that:
select
rowid,
first_name,
last_name,
salary
from
employees
where
rowid = :1
As you can see in this example, the query for retrieving the changed row differs from the original query in only one way: The ROWID, rather than the EMPLOYEE_ID, is used in the WHERE clause.
Two Key Classes
The OracleNotificationRequest and OracleNotificationEventArgs classes form the backbone of the Database Change Notification support in ODP.NET. Each of these classes is documented in ODP.NET and available in the Microsoft Visual Studio online integrated help.
OracleNotificationRequest. One important property of the OracleNotificationRequest class is IsNotifiedOnce. This Boolean property has a default value of true and is used to inform the database about whether you wish to receive a notification for each change that occurs or only a single notification. When the property is set to true, the server's notification registration will be removed after the first change. When the property is set to false, the registration will persist, regardless of the number of changes. In the sample code, the property is set to false, allowing the notification registration to persist:
cmd.Notification.IsNotifiedOnce = false;
Next Steps
READ "Following the Changes, Part One"
VISIT .NET Developer Center
READ more about
ROWID
SQL Reference
Database Change Notification
Oracle Data Provider for .NET Developer's Guide
DOWNLOAD
the sample code for this column
Database Change Notification presentation and demonstration (from Oracle OpenWorld 2005)
ODP.NET
Oracle Developer Tools for Visual Studio .NET
If you want to limit the persistence of the registration, you can use the OracleNotificationRequest.Timeout property to specify how long the registration remains alive.
OracleNotificationEventArgs. The Oracle NotificationEventArgs class exposes the data (and metadata) associated with a change event to your application. Several properties exposed by the OracleNotificationEventArgs class may be useful in your applications:
* The Details property is a DataTable that contains information about the current change notification. You use this property to access the ROWID pseudocolumn when handling a change notification event. The ROWID is exposed as the Rowid column in the DataTable.
* The Info property exposes a value of the OracleNotificationInfo enumeration. You use this property to determine what change event occurred—for example, to determine if the driving change in the database was an insert, update, or delete operation.
* The ResourceNames property is a string that contains the name of the schema and the name of the object that was changed.
Sample Code and Beyond
The sample code download (revised from Part One) responds to multiple database change notifications (rather than a single notification) and retrieves the changed row from the database. I encourage you to download and experiment with it. For example, you can extend this code to retrieve multiple changed rows and loop through all the rows in the Details DataTable (accessed via the args parameter) and then build a query such as this:
select
rowid,
first_name,
last_name,
salary
from
employees
where
rowid = :1
or
rowid = :2
or
rowid = :3
Set breakpoints in the code, and run the sample, using the debugger. Examine the properties of the objects. Use this code as a starting point for implementing Database Change Notification in your applications, and experience its benefits for yourself.
Following the Changes, Part One
Use database change notification with ODP.NET and Oracle Database 10g Release 2.
There are different ways to access data from Oracle databases in .NET applications, but in terms of features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice.
This ODP.NET column is the first in a two-part examination of the new database change notification feature, introduced in Oracle Database 10g Release 2. This column introduces basic database change notification concepts and creates sample code to explore these concepts.
The Need for Database Change Notification
An important performance feature of modern applications—especially multitier, Web-based applications—is the use of a data cache. A data cache is typically used to remove or reduce excessive (and expensive) round-trip requests to the database server for data that has not changed. For example, when implementing a data sorting routine in your code, you sort the data in the cache rather than fetch the entire result set from the database.
However, when you're using a data cache, what does your application do when the data on the server does change? Two manual approaches are commonly used today. One approach is to give users a way to manually refresh the data in the cache. They click a Refresh button in your application, for example. Another approach is to poll the database at specific intervals or during each request to check whether the data has changed. Code that polls the database often retrieves a result set and compares it with the cached result set.
Both of these approaches have fairly significant limitations.
The limitation of the polling method is that if the polling interval is too small, it may unnecessarily produce too much database traffic. If the polling interval is too large, the end user will more likely be working with out-of-date data. Predicting the right balance is nearly impossible when workloads change over time.
Requiring users to click a Refresh button to refresh the data has the same limitations as the polling method, with the added limitation that the users must remember to refresh their data.
Database Change Notification Primer
With the introduction of Oracle Database 10g Release 2, you have a new option that addresses the limitations inherent in previous approaches to dealing with changing data: database change notification. When you use database change notification, the database server will notify you automatically when an event occurs that changes objects associated with a specific query.
Using the database change notification feature is a three-step process:
1. Registration: During the registration process, you specify a query that the database should watch for changes. ODP.NET automatically registers the events to watch for, based on the query. The database watches for Data Manipulation Language (DML) events, Data Definition Language (DDL) events, and global events. (A DML event occurs when the underlying data of a query is changed. A DDL event occurs when the structure of an object in the query is changed. A global event occurs when an action with a greater scope than the query alone takes place—the database is shut down, for example.)
2. Notification: Once a query has been registered with the database for change notification, you specify how you would like to receive that notification. You can receive the notification—automatically from the database—as an event in your application code, or you can poll the database. Most database change notification applications have the database automatically alert end users about changes, rather than using polling. (Note that ODP.NET needs to open a client network port to listen for the notification message from the database.)
3. Response: Your application responds to the change notification by taking some action, as appropriate. In most cases, you'll automatically update the cached data without requiring end user interaction. Alternatively, you can notify the user that the data has changed and ask if the user would like to update the cached data.
Database Change Notification in Action
Using the database change notification feature from an ODP.NET application is simple, and the data provider handles the underlying details of the process. To get started, you can use this month's sample code in Listing 1. This code uses the HR database user, which is included with the Oracle Database 10g Release 2 sample schemas and illustrates the minimum steps for producing a fully functional application that responds to a change notification event.
Code Listing 1: Database change notification code
static void Main(string[] args)
{
string sql = "select first_name, last_name, salary from employees where employee_id = 149";
string constr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";
OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDependency dep = new OracleDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDatabaseNotification);
cmd.ExecuteNonQuery();
while (notificationReceived == false)
{
Console.WriteLine("Waiting for notification...");
System.Threading.Thread.Sleep(2000);
}
cmd.Dispose();
con.Dispose();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
}
public static void OnDatabaseNotification(object src, OracleNotificationEventArgs args)
{
Console.WriteLine("Database Change Notification received!");
DataTable changeDetails = args.Details;
Console.WriteLine("Resource {0} has changed.", changeDetails.Rows[0]["ResourceName"]);
notificationReceived = true;
}
For your application to utilize change notification, the application's database user must have the CHANGE NOTIFICATION database privilege. Before running the code in Listing 1, run the following statement, using a DBA connection in a tool such as SQL*Plus or Oracle Developer Tools for Visual Studio .NET, to ensure that the HR user can use the change notification feature:
grant change notification to hr;
Now that the HR user has the appropriate privilege to use the change notification feature, let's consider the important parts of the C# sample code in Listing 1.
The following code creates the dependency object:
OracleDependency dep =
new OracleDependency(cmd);
The dependency object is used to register the query with the database. Note that the creation of the dependency object itself does not register the query; the command execution does.
The sample application receives the change notification as an event initiated by the database. The following code attaches the OnDatabaseNotification method in the sample code to the dependency object:
dep.OnChange +=
new OnChangeEventHandler(
OnDatabaseNotification);
The application will call the OnDatabaseNotification method when the ODP.NET client receives a change notification event.
The OnDatabaseNotification method in Listing 1 handles the notification event from the database. When a change notification event is received, this code outputs a message to the console to indicate that a notification message has been received, displays the name of the resource that has been changed, and sets the notificationReceived variable to true.
The following code checks the notificationReceived variable and loops while the value is false:
while (notificationReceived == false)
{
Console.WriteLine(
"Waiting for notification...");
System.Threading.Thread.Sleep(2000);
}
This simulates work taking place in a simple console window. While performing the loop, the code outputs a simple message to the console and sleeps for two seconds.
To test the sample code, create a new console application and add a reference to Oracle Data Provider for .NET to the project, by selecting Project -> Add Reference from the Microsoft Visual Studio menu bar and then choosing Oracle.DataAccess.dll from the Add Reference dialog box.
Next add the following using statements to the top of the code file, to include the namespaces used in the project:
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
Replace the system-generated Main method with this column's sample code. Add the OnDatabaseNotification method to the class after the Main method, and build the project. (The sample download for this article includes all the appropriate namespaces and assembly references.)
This sample application retrieves information about an employee in the EMPLOYEES table, registering the following query for a change notification:
select first_name, last_name, salary
from employees
where employee_id = 149
Another user will update that employee's salary in the database, and the database will send a change notification to the sample application. The application responds to the change notification by printing a simple message.
After building the project, run the resulting executable in a console window. While the application is running, log in as the HR user in SQL*Plus or Oracle Developer Tools for Visual Studio .NET to update the EMPLOYEES table. You can use the following sample UPDATE statement, which is included in the source code download in the increase_salary.sql file:
update employees set salary = salary+10
where employee_id = 149;
commit;
The output from your application should resemble the following:
Waiting for notification...
Waiting for notification...
Waiting for notification...
Waiting for notification...
Database Change Notification received!
Resource HR.EMPLOYEES has changed.
Implementation Guidelines
Next Steps
READ more about ODP.NET
Oracle Data Provider for .NET Developer's Guide
DOWNLOAD
the sample application for this column
Oracle Developer Tools for Visual Studio .NET
VISIT .NET Developer Center
As with any other feature, properly using Database Change Notification can greatly benefit the performance and usability of your application. The following guidelines will help you best use Database Change Notification in your ODP.NET application:
* Use Database Change Notification only with data that changes infrequently. The benefit of using Database Change Notification can be lost when the data changes frequently, such as with a stock ticker. A continuous stream of changes means the overhead of constant notification traffic and subsequent data updates. If the data changes almost as frequently as it is read, don't use change notification. Just requery the database whenever you do a read.
* Limit the number of Database Change Notification registrations for a particular query. If hundreds of users are monitoring for changes on the same query, hundreds of notifications will be sent to the client tier when a data change occurs. Rather than have each client in a system register for notification, move the code into a middle-tier element to consolidate the number of registrations and the workload.
Next Time
This column introduced the basics of using database change notification in your ODP.NET applications. The next column will examine in greater detail the classes that support change notification and show you how to optimize the use of database change notification.
There are different ways to access data from Oracle databases in .NET applications, but in terms of features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice.
This ODP.NET column is the first in a two-part examination of the new database change notification feature, introduced in Oracle Database 10g Release 2. This column introduces basic database change notification concepts and creates sample code to explore these concepts.
The Need for Database Change Notification
An important performance feature of modern applications—especially multitier, Web-based applications—is the use of a data cache. A data cache is typically used to remove or reduce excessive (and expensive) round-trip requests to the database server for data that has not changed. For example, when implementing a data sorting routine in your code, you sort the data in the cache rather than fetch the entire result set from the database.
However, when you're using a data cache, what does your application do when the data on the server does change? Two manual approaches are commonly used today. One approach is to give users a way to manually refresh the data in the cache. They click a Refresh button in your application, for example. Another approach is to poll the database at specific intervals or during each request to check whether the data has changed. Code that polls the database often retrieves a result set and compares it with the cached result set.
Both of these approaches have fairly significant limitations.
The limitation of the polling method is that if the polling interval is too small, it may unnecessarily produce too much database traffic. If the polling interval is too large, the end user will more likely be working with out-of-date data. Predicting the right balance is nearly impossible when workloads change over time.
Requiring users to click a Refresh button to refresh the data has the same limitations as the polling method, with the added limitation that the users must remember to refresh their data.
Database Change Notification Primer
With the introduction of Oracle Database 10g Release 2, you have a new option that addresses the limitations inherent in previous approaches to dealing with changing data: database change notification. When you use database change notification, the database server will notify you automatically when an event occurs that changes objects associated with a specific query.
Using the database change notification feature is a three-step process:
1. Registration: During the registration process, you specify a query that the database should watch for changes. ODP.NET automatically registers the events to watch for, based on the query. The database watches for Data Manipulation Language (DML) events, Data Definition Language (DDL) events, and global events. (A DML event occurs when the underlying data of a query is changed. A DDL event occurs when the structure of an object in the query is changed. A global event occurs when an action with a greater scope than the query alone takes place—the database is shut down, for example.)
2. Notification: Once a query has been registered with the database for change notification, you specify how you would like to receive that notification. You can receive the notification—automatically from the database—as an event in your application code, or you can poll the database. Most database change notification applications have the database automatically alert end users about changes, rather than using polling. (Note that ODP.NET needs to open a client network port to listen for the notification message from the database.)
3. Response: Your application responds to the change notification by taking some action, as appropriate. In most cases, you'll automatically update the cached data without requiring end user interaction. Alternatively, you can notify the user that the data has changed and ask if the user would like to update the cached data.
Database Change Notification in Action
Using the database change notification feature from an ODP.NET application is simple, and the data provider handles the underlying details of the process. To get started, you can use this month's sample code in Listing 1. This code uses the HR database user, which is included with the Oracle Database 10g Release 2 sample schemas and illustrates the minimum steps for producing a fully functional application that responds to a change notification event.
Code Listing 1: Database change notification code
static void Main(string[] args)
{
string sql = "select first_name, last_name, salary from employees where employee_id = 149";
string constr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";
OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
OracleDependency dep = new OracleDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDatabaseNotification);
cmd.ExecuteNonQuery();
while (notificationReceived == false)
{
Console.WriteLine("Waiting for notification...");
System.Threading.Thread.Sleep(2000);
}
cmd.Dispose();
con.Dispose();
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
}
public static void OnDatabaseNotification(object src, OracleNotificationEventArgs args)
{
Console.WriteLine("Database Change Notification received!");
DataTable changeDetails = args.Details;
Console.WriteLine("Resource {0} has changed.", changeDetails.Rows[0]["ResourceName"]);
notificationReceived = true;
}
For your application to utilize change notification, the application's database user must have the CHANGE NOTIFICATION database privilege. Before running the code in Listing 1, run the following statement, using a DBA connection in a tool such as SQL*Plus or Oracle Developer Tools for Visual Studio .NET, to ensure that the HR user can use the change notification feature:
grant change notification to hr;
Now that the HR user has the appropriate privilege to use the change notification feature, let's consider the important parts of the C# sample code in Listing 1.
The following code creates the dependency object:
OracleDependency dep =
new OracleDependency(cmd);
The dependency object is used to register the query with the database. Note that the creation of the dependency object itself does not register the query; the command execution does.
The sample application receives the change notification as an event initiated by the database. The following code attaches the OnDatabaseNotification method in the sample code to the dependency object:
dep.OnChange +=
new OnChangeEventHandler(
OnDatabaseNotification);
The application will call the OnDatabaseNotification method when the ODP.NET client receives a change notification event.
The OnDatabaseNotification method in Listing 1 handles the notification event from the database. When a change notification event is received, this code outputs a message to the console to indicate that a notification message has been received, displays the name of the resource that has been changed, and sets the notificationReceived variable to true.
The following code checks the notificationReceived variable and loops while the value is false:
while (notificationReceived == false)
{
Console.WriteLine(
"Waiting for notification...");
System.Threading.Thread.Sleep(2000);
}
This simulates work taking place in a simple console window. While performing the loop, the code outputs a simple message to the console and sleeps for two seconds.
To test the sample code, create a new console application and add a reference to Oracle Data Provider for .NET to the project, by selecting Project -> Add Reference from the Microsoft Visual Studio menu bar and then choosing Oracle.DataAccess.dll from the Add Reference dialog box.
Next add the following using statements to the top of the code file, to include the namespaces used in the project:
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
Replace the system-generated Main method with this column's sample code. Add the OnDatabaseNotification method to the class after the Main method, and build the project. (The sample download for this article includes all the appropriate namespaces and assembly references.)
This sample application retrieves information about an employee in the EMPLOYEES table, registering the following query for a change notification:
select first_name, last_name, salary
from employees
where employee_id = 149
Another user will update that employee's salary in the database, and the database will send a change notification to the sample application. The application responds to the change notification by printing a simple message.
After building the project, run the resulting executable in a console window. While the application is running, log in as the HR user in SQL*Plus or Oracle Developer Tools for Visual Studio .NET to update the EMPLOYEES table. You can use the following sample UPDATE statement, which is included in the source code download in the increase_salary.sql file:
update employees set salary = salary+10
where employee_id = 149;
commit;
The output from your application should resemble the following:
Waiting for notification...
Waiting for notification...
Waiting for notification...
Waiting for notification...
Database Change Notification received!
Resource HR.EMPLOYEES has changed.
Implementation Guidelines
Next Steps
READ more about ODP.NET
Oracle Data Provider for .NET Developer's Guide
DOWNLOAD
the sample application for this column
Oracle Developer Tools for Visual Studio .NET
VISIT .NET Developer Center
As with any other feature, properly using Database Change Notification can greatly benefit the performance and usability of your application. The following guidelines will help you best use Database Change Notification in your ODP.NET application:
* Use Database Change Notification only with data that changes infrequently. The benefit of using Database Change Notification can be lost when the data changes frequently, such as with a stock ticker. A continuous stream of changes means the overhead of constant notification traffic and subsequent data updates. If the data changes almost as frequently as it is read, don't use change notification. Just requery the database whenever you do a read.
* Limit the number of Database Change Notification registrations for a particular query. If hundreds of users are monitoring for changes on the same query, hundreds of notifications will be sent to the client tier when a data change occurs. Rather than have each client in a system register for notification, move the code into a middle-tier element to consolidate the number of registrations and the workload.
Next Time
This column introduced the basics of using database change notification in your ODP.NET applications. The next column will examine in greater detail the classes that support change notification and show you how to optimize the use of database change notification.
Cursor In, Cursor Out
The easy way to send a ref cursor to PL/SQL with ODP.NET and Oracle Database 10g Release 2
A ref cursor is a reference to a result set that resides in server memory. When a ref cursor is opened, no data is initially returned to the client. Instead, the address where the data resides is passed to the client and the client can choose how and when to process the data represented by the ref cursor.
With previous releases of ODP.NET, you could retrieve data from a ref cursor but you could not pass a ref cursor as an input parameter to a PL/SQL stored procedure or function. But with ODP.NET 10g Release 2, you are now able to easily pass a ref cursor as an input parameter in Oracle Database 10g Release 2.
This ODP.NET column focuses on how to send a ref cursor to a PL/SQL stored procedure in a .NET application that accesses Oracle Database 10g Release 2.
Sample Application Requirements
The sample application code that accompanies this column simulates a user selecting a subset of employees from an employees table. The subset of employees selected is represented by a ref cursor—the actual rows are never passed from the database to the client application, just the row addresses. After the employees have been selected, the ref cursor is passed to a stored procedure for processing. In this case, the processing simply inserts a message into a table.
This sample application uses the HR sample schema.
Implementing the Database Objects
Before you create the .NET code to pass a ref cursor to a stored procedure, you create the underlying database objects to support this activity. Although you can create the .NET code first, it is easiest to first create the database objects the .NET code will use. The three database objects to create are
* A table
* A PL/SQL package
* A PL/SQL package body
The table holds the results of the processing that occurs in the PL/SQL package body. The PL/SQL package defines the ref cursor and contains the definition of the stored procedure that processes it. The stored procedure logic is located in the PL/SQL package body and performs the bulk of the work, which, in this case, is simply to loop through the rows in the ref cursor and insert a simple message into the table. The code in database.sql, available with the sample application download at oracle.com/technology/oramag/oracle/06-jan/jf06_odpnet.zip, creates the database objects.
Creating the .NET Code
With the database objects created, you now create a new .NET console application and add a reference to the ODP.NET assembly to your project, by choosing Project->Add Reference from the Microsoft Visual Studio menu bar and then choosing Oracle.DataAccess.dll from the Add Reference dialog box.
As well as adding a reference to the ODP.NET assembly, I always add a standard set of namespaces to each of my projects, to reduce the amount of application code, by including the following code at the beginning of my class file:
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
The Oracle.DataAccess.Types namespaces contain Oracle-specific datatypes available in .NET, such as the ref cursor.
Code Listing 1: The .NET main method—creating and passing a ref cursor
static void Main(string[] args)
{
string constr = "User Id=hr; Password=hr;
Data Source=oramag; Pooling=false";
OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "begin open :1 for
select * from employees
where manager_id=101; end;";
OracleParameter p_rc = cmd.Parameters.Add(
"p_rc",
OracleDbType.RefCursor,
DBNull.Value,
ParameterDirection.Output);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "cursor_in_out.process_cursor";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_input = cmd.Parameters.Add(
"p_input",
OracleDbType.RefCursor,
p_rc.Value,
ParameterDirection.Input);
cmd.ExecuteNonQuery();
p_input.Dispose();
p_rc.Dispose();
cmd.Dispose();
con.Dispose();
}
The code in Listing 1 contains the main method to replace the main method that Microsoft Visual Studio creates when you build a new console application. This new code simulates a user selecting a subset of rows from a table.
The following are key points in the code in Listing 1:
cmd.CommandText = "begin open :1 for
select * from employees
where manager_id=101; end;";
—sets the CommandText property of the OracleCommand object to an anonymous PL/SQL block that creates a ref cursor. The anonymous block is delineated by the begin and end keywords (but no name), and it uses a bind variable (:1) for the ref cursor parameter. The ref cursor is created by the open keyword, and the contents of the cursor are determined by the simple SQL statement that retrieves all the employees whose manager_id is 101. I am using the literal value 101 in the code for this column; you can use a bind variable in your applications.
Next Steps
READ more about ODP.NET
Oracle Data Provider for .NET Developer's Guide
"Optimize Result Set Retrieval Using ODP.NET and Ref Cursors"
Pro .NET Oracle Programming
DOWNLOAD
sample application for this column
ODP.NET 10g
OracleParameter p_rc =
cmd.Parameters.Add(
"p_rc",
OracleDbType.RefCursor,
DBNull.Value,
ParameterDirection.Output);
—creates an OracleParameter object, p_rc, that represents the ref cursor in the .NET code. You need to declare the parameter to be of type OracleDbType.RefCursor. (See the Oracle Data Provider for .NET Developer's Guide for more details about the ref cursor ODP.NET datatype.) The initial value of the parameter is set to null (DBNull.Value), and the direction is set to output.
cmd.CommandText =
"cursor_in_out.process_cursor";
cmd.CommandType =
CommandType.StoredProcedure;
—assigns values to the CommandText and CommandType properties of the OracleCommand object, to invoke the PL/SQL stored procedure that processes the ref cursor. The CommandText property is set to the name of the stored procedure (including the package name as a prefix), and the CommandType property is set to indicate that a stored procedure, rather than a standalone SQL statement, is being used. Note that if you were to call a stored function rather than a procedure, you would still declare the command to be a StoredProcedure type.
OracleParameter p_input =
cmd.Parameters.Add(
"p_input",
OracleDbType.RefCursor,
p_rc.Value,
ParameterDirection.Input);
—creates an OracleParameter object, p_input, that passes the ref cursor into the PL/SQL stored procedure. Note that the parameter is an input parameter (ParameterDirection.Input).
Seeing the Code in Action
To see the results of the code, choose Build->Build Solution from the Microsoft Visual Studio .NET main menu and run the project by clicking on the Start button. The application produces no visible output, but you can easily see the results by using a query tool such as the SQL Query Window in Oracle Developer Tools for Visual Studio .NET or SQL*Plus. Whichever tool you use, you should see a result similar to the following:
SQL> select * from processing_result;
STATUS
----------------------------------------
Processed employee #108: Nancy Greenberg
Processed employee #200: Jennifer Whalen
Processed employee #203: Susan Mavris
Processed employee #204: Hermann Baer
Processed employee #205: Shelley Higgins
5 rows selected.
Summary
This column showed you how to create both the database objects and the .NET code to produce an output ref cursor and how to pass that ref cursor as an input parameter to a PL/SQL stored procedure. ODP.NET makes working with input ref cursors just as easy as working with any other ODP.NET and Oracle type. I encourage you to expand this simple example to leverage the power of ODP.NET, PL/SQL stored procedures, ref cursors, and Oracle Database 10g Release 2 in your applications today.
A ref cursor is a reference to a result set that resides in server memory. When a ref cursor is opened, no data is initially returned to the client. Instead, the address where the data resides is passed to the client and the client can choose how and when to process the data represented by the ref cursor.
With previous releases of ODP.NET, you could retrieve data from a ref cursor but you could not pass a ref cursor as an input parameter to a PL/SQL stored procedure or function. But with ODP.NET 10g Release 2, you are now able to easily pass a ref cursor as an input parameter in Oracle Database 10g Release 2.
This ODP.NET column focuses on how to send a ref cursor to a PL/SQL stored procedure in a .NET application that accesses Oracle Database 10g Release 2.
Sample Application Requirements
The sample application code that accompanies this column simulates a user selecting a subset of employees from an employees table. The subset of employees selected is represented by a ref cursor—the actual rows are never passed from the database to the client application, just the row addresses. After the employees have been selected, the ref cursor is passed to a stored procedure for processing. In this case, the processing simply inserts a message into a table.
This sample application uses the HR sample schema.
Implementing the Database Objects
Before you create the .NET code to pass a ref cursor to a stored procedure, you create the underlying database objects to support this activity. Although you can create the .NET code first, it is easiest to first create the database objects the .NET code will use. The three database objects to create are
* A table
* A PL/SQL package
* A PL/SQL package body
The table holds the results of the processing that occurs in the PL/SQL package body. The PL/SQL package defines the ref cursor and contains the definition of the stored procedure that processes it. The stored procedure logic is located in the PL/SQL package body and performs the bulk of the work, which, in this case, is simply to loop through the rows in the ref cursor and insert a simple message into the table. The code in database.sql, available with the sample application download at oracle.com/technology/oramag/oracle/06-jan/jf06_odpnet.zip, creates the database objects.
Creating the .NET Code
With the database objects created, you now create a new .NET console application and add a reference to the ODP.NET assembly to your project, by choosing Project->Add Reference from the Microsoft Visual Studio menu bar and then choosing Oracle.DataAccess.dll from the Add Reference dialog box.
As well as adding a reference to the ODP.NET assembly, I always add a standard set of namespaces to each of my projects, to reduce the amount of application code, by including the following code at the beginning of my class file:
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
The Oracle.DataAccess.Types namespaces contain Oracle-specific datatypes available in .NET, such as the ref cursor.
Code Listing 1: The .NET main method—creating and passing a ref cursor
static void Main(string[] args)
{
string constr = "User Id=hr; Password=hr;
Data Source=oramag; Pooling=false";
OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "begin open :1 for
select * from employees
where manager_id=101; end;";
OracleParameter p_rc = cmd.Parameters.Add(
"p_rc",
OracleDbType.RefCursor,
DBNull.Value,
ParameterDirection.Output);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "cursor_in_out.process_cursor";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p_input = cmd.Parameters.Add(
"p_input",
OracleDbType.RefCursor,
p_rc.Value,
ParameterDirection.Input);
cmd.ExecuteNonQuery();
p_input.Dispose();
p_rc.Dispose();
cmd.Dispose();
con.Dispose();
}
The code in Listing 1 contains the main method to replace the main method that Microsoft Visual Studio creates when you build a new console application. This new code simulates a user selecting a subset of rows from a table.
The following are key points in the code in Listing 1:
cmd.CommandText = "begin open :1 for
select * from employees
where manager_id=101; end;";
—sets the CommandText property of the OracleCommand object to an anonymous PL/SQL block that creates a ref cursor. The anonymous block is delineated by the begin and end keywords (but no name), and it uses a bind variable (:1) for the ref cursor parameter. The ref cursor is created by the open keyword, and the contents of the cursor are determined by the simple SQL statement that retrieves all the employees whose manager_id is 101. I am using the literal value 101 in the code for this column; you can use a bind variable in your applications.
Next Steps
READ more about ODP.NET
Oracle Data Provider for .NET Developer's Guide
"Optimize Result Set Retrieval Using ODP.NET and Ref Cursors"
Pro .NET Oracle Programming
DOWNLOAD
sample application for this column
ODP.NET 10g
OracleParameter p_rc =
cmd.Parameters.Add(
"p_rc",
OracleDbType.RefCursor,
DBNull.Value,
ParameterDirection.Output);
—creates an OracleParameter object, p_rc, that represents the ref cursor in the .NET code. You need to declare the parameter to be of type OracleDbType.RefCursor. (See the Oracle Data Provider for .NET Developer's Guide for more details about the ref cursor ODP.NET datatype.) The initial value of the parameter is set to null (DBNull.Value), and the direction is set to output.
cmd.CommandText =
"cursor_in_out.process_cursor";
cmd.CommandType =
CommandType.StoredProcedure;
—assigns values to the CommandText and CommandType properties of the OracleCommand object, to invoke the PL/SQL stored procedure that processes the ref cursor. The CommandText property is set to the name of the stored procedure (including the package name as a prefix), and the CommandType property is set to indicate that a stored procedure, rather than a standalone SQL statement, is being used. Note that if you were to call a stored function rather than a procedure, you would still declare the command to be a StoredProcedure type.
OracleParameter p_input =
cmd.Parameters.Add(
"p_input",
OracleDbType.RefCursor,
p_rc.Value,
ParameterDirection.Input);
—creates an OracleParameter object, p_input, that passes the ref cursor into the PL/SQL stored procedure. Note that the parameter is an input parameter (ParameterDirection.Input).
Seeing the Code in Action
To see the results of the code, choose Build->Build Solution from the Microsoft Visual Studio .NET main menu and run the project by clicking on the Start button. The application produces no visible output, but you can easily see the results by using a query tool such as the SQL Query Window in Oracle Developer Tools for Visual Studio .NET or SQL*Plus. Whichever tool you use, you should see a result similar to the following:
SQL> select * from processing_result;
STATUS
----------------------------------------
Processed employee #108: Nancy Greenberg
Processed employee #200: Jennifer Whalen
Processed employee #203: Susan Mavris
Processed employee #204: Hermann Baer
Processed employee #205: Shelley Higgins
5 rows selected.
Summary
This column showed you how to create both the database objects and the .NET code to produce an output ref cursor and how to pass that ref cursor as an input parameter to a PL/SQL stored procedure. ODP.NET makes working with input ref cursors just as easy as working with any other ODP.NET and Oracle type. I encourage you to expand this simple example to leverage the power of ODP.NET, PL/SQL stored procedures, ref cursors, and Oracle Database 10g Release 2 in your applications today.
Subscribe to:
Posts (Atom)