Contents

Title and Copyright Information

Preface

Audience

Documentation Accessibility

Related Documents

Conventions

1 Introduction

About This Guide

What is the Microsoft .NET Framework

Overview of Oracle Data Provider for .NET

Overview of Oracle Developer Tools for Visual Studio

Overview of .NET Stored Procedures

Overview of Oracle Providers for ASP.NET

2 Installing .NET Products

What You Need

Oracle Database

Sample Data

Oracle Data Access Components

Oracle Database Extensions for .NET

Visual Studio Versions

Installing .NET Products

Configuring a NET Connect Alias

Setup for Oracle Providers for ASP.NET

Oracle Providers for ASP.NET Database User Setup

Creating the User and Granting Privileges

Configuring All Oracle Providers for ASP.NET

Configuring Oracle Providers for ASP.NET Individually

Uninstalling Schemas for Oracle Providers for ASP.NET

Setting the Connection String

Customizing Oracle Providers for ASP.NET for Different Setups

3 Building a Simple .NET Application Using ODP.NET

Creating a New Project

Adding a Reference

Adding Namespace Directives

Designing the User Interface

Writing the Connection Code

Compiling and Running the Application

Error Handling

Using Try-Catch-Finally Block Structure

Handling General Errors

Handling Common Oracle Errors

4 Retrieving and Updating with Oracle Data Provider for .NET

Using the Command Object

Retrieving Data: a Simple Query

Retrieving Data: Bind Variables

Retrieving Data: Multiple Values

Using the DataSet Class with Oracle Data Provider for .NET

Enabling Updates to the Database

Inserting, Deleting, and Updating Data

5 Using Oracle Developer Tools for Visual Studio

Using Oracle Developer Tools

Connecting to the Oracle Database

Creating a Table and Its Columns

Creating a Table Index

Adding Table Constraints

Adding Data to a Table

Generating Code Automatically to Display and Update Data

6 Using PL/SQL Stored Procedures and REF CURSORs

Introduction to PL/SQL Stored Procedures

Introduction to PL/SQL Packages and Package Bodies

Introduction to REF CURSORs

Creating a PL/SQL Stored Procedure that Uses REF CURSORs

Modifying an ODP.NET Application to Run Stored Procedures

Running a PL/SQL Stored Procedure Using an ODP.NET Application

7 Using ASP.NET with Oracle Database

Overview: Building an ASP.NET Application with Oracle Developer Tools

Before Beginning This Tutorial

Creating a Web Site and Connecting it to the Database

Creating an ASP.NET Web Site

Creating a Data Source

Enabling a Web Site for Authentication

Enabling Oracle Providers for ASP.NET and Creating a Lightweight Web User

Testing Web Site Authentication

8 Developing and Deploying .NET Stored Procedures

Overview of .NET Stored Procedures

Starting the Common Language Runtime Service

Creating a Connection as SYSDBA

Creating an Oracle Project

Creating .NET Stored Functions and Procedures

Deploying .NET Stored Functions and Procedures

Running .NET Stored Functions and Procedures

Running .NET Stored Procedure in a Query Window

9 Including Globalization Support

Introduction to Global Applications

Developing Global Applications with the .NET Framework

Presenting Data in the Correct User Local Convention

Connecting to SQL*Plus

Using Oracle Date Formats

Using Oracle Number Formats

Using Oracle Linguistic Sorts

Oracle Error Messages

Synchronizing the .NET and Oracle Database Locale Environments

Client Globalization Support in Oracle Data Provider for .NET

Client Globalization Settings

Using Session Globalization Settings

Thread-Based Globalization Settings

A Starting and Stopping an Oracle Database Instance

B Copying a Form

Index

Oracle® Database

2 Day + .NET Developer's Guide

11g Release 2 (11.2)

E10767-01

August 2009

Oracle Database 2 Day + .NET Developer's Guide, 11g Release 2 (11.2)

E10767-01

Copyright © 2006, 2009, Oracle and/or its affiliates. All rights reserved.

Primary Authors: Janis Greenberg, Roza Leyderman

Contributing Authors:  John Paul Cook, Mark Williams

Contributors: Alex Keh, Christian Shay

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Preface

This document is intended as an introduction to application development on Oracle Database with Oracle technologies for the Microsoft .NET Framework.

Audience

We assume that users of this book have already read the Oracle Database 2 Day DBA and the Oracle Database 2 Day Developer's Guide, are familiar with basics of SQL and PL/SQL, and know how to use Microsoft Visual Studio.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/.

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

Deaf/Hard of Hearing Access to Oracle Support Services

To reach Oracle Support Services, use a telecommunications relay service (TRS) to call Oracle Support at 1.800.223.1711. An Oracle Support Services engineer will handle technical issues and provide customer support according to the Oracle service request process. Information about TRS is available at http://www.fcc.gov/cgb/consumerfacts/trs.html, and a list of phone numbers is available at http://www.fcc.gov/cgb/dro/trsphonebk.html.

Related Documents

For more information, see the following documents in Oracle Database documentation set:

Oracle Data Provider for .NET Developer's Guide

Oracle Database Extensions for .NET Developer's Guide

Oracle Database 2 Day DBA

Oracle Database 2 Day Developer's Guide

Oracle Developer Tools for Visual Studio Dynamic Help

Oracle Net Services Administrator's Guide

Oracle Database Express Edition Installation Guide for Microsoft Windows

Conventions

The following text conventions are used in this document:

Convention | Meaning
boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.
italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

1 Introduction

This chapter contains:

About This Guide

What is the Microsoft .NET Framework

Overview of Oracle Data Provider for .NET

Overview of Oracle Developer Tools for Visual Studio

Overview of .NET Stored Procedures

Overview of Oracle Providers for ASP.NET

About This Guide

This guide serves as a quick start guide, which describes Oracle technologies for the Microsoft .NET Framework, including the key features of Oracle Data Provider for .NET and Oracle Developer Tools for Visual Studio. It leads you through installation and configuration, shows how to build basic applications using Oracle .NET products, and how to create and use both PL/SQL and .NET stored procedures.

Note:

This guide was created using Microsoft Visual Studio 2008. If you are using Microsoft Visual Studio 2005, you may notice differences in screen shots, shortcuts, menu options, and generated code, but generally the differences should be minor and not cause problems.

After working through this book, you will be ready to continue with more extensive information available in the Oracle Database documentation library.

See Also:

Visual Studio Dynamic help

Oracle Data Provider for .NET Developer's Guide

Oracle Database Extensions for .NET Developer's Guide

Oracle Database 2 Day DBA

Oracle Database 2 Day Developer's Guide

What is the Microsoft .NET Framework

The Microsoft .NET Framework is a multi-language environment for building, deploying, and running applications and XML Web services. Its main components are:

Common Language Runtime

The Common Language Runtime, or CLR, is a language-neutral development and run-time environment that provides services that help manage running applications

Framework Class Libraries

The Framework Class Libraries, or FCL, provide a consistent, object-oriented library of prepackaged functionality.

Overview of Oracle Data Provider for .NET

Oracle Data Provider for .NET (ODP.NET) provides fast and efficient ADO.NET data access from .NET client applications to Oracle databases and access to other Oracle Database features.

ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, XML DB, and advanced security.

Overview of Oracle Developer Tools for Visual Studio

Oracle Developer Tools for Visual Studio (ODT) is a set of application tools that integrate with the Visual Studio environment. These tools provide graphical user interface access to Oracle functionality, enable the user to perform a wide range of application development tasks, and improve development productivity and ease of use. Oracle Developer Tools supports the programming and implementation of .NET stored procedures using Visual Basic, C#, and other .NET languages.

These are some of the Oracle Developer Tools features:

Integration with Server Explorer for browsing the Oracle schema.

Designers and wizards to create and alter schema objects.

The ability to drag and drop schema objects onto a .NET form to automatically generate code.

A PL/SQL editor and debugger with integrated context-sensitive dynamic help.

An Oracle Data Window for performing routine database tasks such as inserting and updating data or testing stored procedures in the Visual Studio environment.

An Oracle Query Window for executing SQL statements or PL/SQL scripts.

An Oracle Deployment Wizard for .NET described "Integration with Microsoft Visual Studio".

Overview of .NET Stored Procedures

Oracle Database Extensions for .NET is a database option for Oracle Database on Windows. It makes it possible to build and run .NET stored procedures or functions with Oracle Database for Microsoft Windows using Visual Basic .NET or Visual C#.

See Also:

Oracle Database Extensions for .NET Developer's Guide

Integration with Microsoft Visual Studio

After building .NET procedures and functions into a .NET assembly, you can deploy them in Oracle Database using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio.

Overview of Oracle Providers for ASP.NET

Oracle Providers for ASP.NET offer ASP.NET developers an easy to use method to store application state common to web applications (such as web user information, shopping carts) within an Oracle database. These providers are modeled on existing Microsoft ASP.NET providers, sharing similar schema and programming interfaces to provide .NET developers a familiar interface.

Oracle supports the following providers:

Membership Provider

Role Provider

Site Map Provider

Session State Provider

Profile Provider

Web Events Provider

Web Parts Personalization Provider

Cache Dependency Provider

Each ASP.NET provider can be used individually or in combination with other Oracle ASP.NET providers for your web site. Each of them store a specific set of web site information.

Oracle Providers for ASP.NET classes, their use, installation, and requirements are described in Oracle Providers for ASP.NET Developer's Guide, which is also provided as dynamic help.

See Also:

Chapter 7, "Using ASP.NET with Oracle Database"

Oracle Providers for ASP.NET Developer's Guide

2 Installing .NET Products

This chapter contains:

What You Need

Installing .NET Products

Configuring a NET Connect Alias

Setup for Oracle Providers for ASP.NET

What You Need

This section lists the products and database schemas you need to run the examples provided in this guide.

Oracle Database

You must have Oracle Database installed, either locally or on a remote computer.

Note:

The samples in this guide all require Oracle Database 11g client. However, you may use any Oracle Database 9i Release 2 or higher as they are supported with this client.

If you plan to use Oracle Database Extensions for .NET, then the client also requires connecting to Oracle Database 11g.

You can administer the database with the user interface, Enterprise Manager, which can run scripts and queries, and more.

See Also:

Oracle Database Express Edition Installation Guide for Microsoft Windows if you do not have the Oracle Database installed and configured

Sample Data

The sample data used in this book is contained in the HR schema, one of the Oracle Sample Schemas. The Sample Schemas are included as part of the Oracle Database installation.

See Also:

Oracle Database Sample Schemas for the HR data model and table descriptions

Oracle Data Access Components

Oracle Data Access Components (ODAC) is a collection of tools that include:

Oracle Developer Tools for Visual Studio

Oracle Data Provider for .NET

Oracle Providers for ASP.NET

Oracle Provider for OLE DB

Oracle Objects for OLE

Oracle ODBC Driver

Oracle Services for Microsoft Transaction Server

Oracle SQL*Plus

Oracle Instant Client

Oracle Database Extensions for .NET

Oracle Database Extensions for .NET is installed as part of the Oracle Database 11g installation on Windows. After Oracle Database Extensions for .NET is installed, the ODAC installation provides an upgrade to Oracle Database Extensions for .NET. This upgrade is included as part of the Oracle Data Access Components for Oracle Server option, which is shown in the screen shot in Step #4 of the section "Installing .NET Products". You only need to perform this upgrade and install Oracle Database Extensions for .NET if you plan to complete Chapter 8 in this book.

Visual Studio Versions

If you are using Visual Studio 2008, you must install it before proceeding with instructions in this book.If you are using Microsoft Visual Studio 2005, you may notice differences in screen shots, shortcuts, menu options, and generated code, but generally the differences should be minor and not cause problems.

Installing .NET Products

These steps demonstrate how to install Oracle Developer Tools for Visual Studio (ODT) and Oracle Data Provider for .NET and other ODAC products once Visual Studio is installed.

Note:

Please note that as new versions of Oracle .NET products are released, the install process may change slightly from what is shown in this guide. The screenshots are based on Oracle Data Access Components (ODAC) version 11.1.0.6.21.

To install:

In your Internet browser, navigate to the following location, and download ODAC with Oracle Developer Tools for Visual Studio:

http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

Extract all the files from the zip file to a folder in your file system.

Double-click Setup.exe.

Oracle Installer launches. A screen appears briefly to detect required dependencies and then the Oracle Universal Installer (OUI) Welcome screen appears.

Description of install01.gif follows
Description of the illustration install01.gif

Click Next.

The Select a Product to Install screen appears

Description of install02.gif follows
Description of the illustration install02.gif

Select the first option.

This option, ODAC for Oracle Client, installs only products that are used in a client Oracle home. The second option, ODAC for Oracle Server, allows you to install directly into an Oracle home that contains an Oracle database.

Click Next.

The Install Location window appears, allowing you to chose the installation location. By default, a new client Oracle home is created. For the purposes of this guide, accept the default which will create a new Oracle home.

Description of install03.gif follows
Description of the illustration install03.gif

Click Next.

The installer performs prerequisite check. The status for each should be succeeded.

Description of install04.gif follows
Description of the illustration install04.gif

Click Next.

The Available Product Components screen appears.

Please be sure that the following are checked:

Oracle Data Provider for .NET 2.0

Oracle Providers for ASP.NET

Oracle Developer Tools for Visual Studio

Oracle Instant Client

Description of install05.gif follows
Description of the illustration install05.gif

Click Next.

A screen appears reminding you that you must run the SQL scripts located in ORACLE_BASE\\ORACLE_HOME\client_1\ASP.NET\SQL if you wish to use Oracle Providers for ASP.NET.

Note:

ORACLE_BASE\\ORACLE_HOME indicates the directory that represents your Oracle home.

Click Next.

The Summary window appears.

Description of install06.gif follows
Description of the illustration install06.gif

Click Install to complete the installation.

The end of the installation screen appears. It reminds you again to install the ASP.NET scripts. Do this if you plan to use the Oracle Providers for ASP.NET.

Click Exit.

Configuring a NET Connect Alias

The tnsnames.ora file defines database server addresses so that the Oracle client can use a short version of the name to connect to databases. Your DBA may have already provided you with a preconfigured tnsnames.ora file.

Otherwise, you need to navigate to the ORACLE_BASE\\ORACLE_HOME\network\admin\sample directory and copy the tnsnames.ora and sqlnet.ora files located there to the ORACLE_BASE\\ORACLE_HOME\network\admin directory.

You may use the following connect descriptor in your tnsnames.ora file and change the values shown in italics for your specific environment:

Example 2-1 tnsnames.ora connect descriptor

address name =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = hostname)(Port = port))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = sid)
 )
)

Where:

sid: Is the database service name

hostname: Is the database computer name

port: Is the port to use to communicate to the database

address name: Is a user-defined short name for the connect descriptor. This short name will be used in the connection string of your .NET application.

Example 2-2 shows a sample tnsnames.ora file.

Example 2-2 Sample tnsnames.ora File

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

See Also:

Oracle Net Services Administrator's Guide

Setup for Oracle Providers for ASP.NET

Oracle Providers for ASP.NET store web application state inside the Oracle database, under the context of a database user's schema. The administrator can create new database users to store application state.

This database user does not map to a single physical user, but acts as a repository to store ASP.NET information for all web site users. Thus, the application state of a single web user or multiple web users may be stored inside this new database user's schema.

Note:

In this tutorial, the database user is called ASPNET_DB_USER, to indicate that it is a Oracle Providers for ASP.NET database user.

At runtime, the ASP.NET application connects to the database using the database user's credentials, in the connection string.

To set up the Oracle database, database administrators must grant certain database privileges to the Oracle Providers for ASP.NET database user schema. With these privileges, the database user can create the tables, views, stored procedures, and other database objects that Oracle Providers for ASP.NET require.

After the privileges have been granted, the database user then can run the Oracle Provider for ASP.NET configuration scripts.

See Also:

Oracle Providers for ASP.NET Developer's Guide for a complete reference

This section walks you step by step through the Oracle Providers for ASP.NET setup for your database. You do not have to complete this section if you do not plan to complete the ASP.NET provider portion of this tutorial (second half of Chapter 7). The Oracle Providers for ASP.NET setup uses Oracle Developer Tools for Visual Studio, which should be installed before beginning setup.

This section contains these topics:

Oracle Providers for ASP.NET Database User Setup

Setting the Connection String

Customizing Oracle Providers for ASP.NET for Different Setups

Oracle Providers for ASP.NET Database User Setup

For this tutorial, you will create the new database user schema, ASPNET_DB_USER, to store the ASP.NET provider data in. You will grant user ASPNET_DB_USER specific database privileges and run the ASP.NET provider database scripts to setup the schema. This schema will contain the tables, stored procedures, and other database objects necessary for Oracle Providers for ASP.NET.

This section contains these topics:

Creating the User and Granting Privileges

Configuring All Oracle Providers for ASP.NET

Configuring Oracle Providers for ASP.NET Individually

Uninstalling Schemas for Oracle Providers for ASP.NET

Creating the User and Granting Privileges

To add the new user and grant the required privileges:

Login as SYS or another database administration user. This is described in "Creating a Connection as SYSDBA".

In the Server Explorer Query Window, create the new ASPNET_DB_USER user, as follows:.

In Server Explorer, right-click SYS.ORCL and right-click to the Query Window,

Description of aspnt_install01.gif follows
Description of the illustration aspnt_install01.gif

In the query window, enter the command:

create user ASPNET_DB_USER identified by your_password

This step creates the user ASPNET_DB_USER in the database, with the password that you enter.

Description of aspnt_install02.gif follows
Description of the illustration aspnt_install02.gif

Run the command by clicking Execute Query (the green arrow at the top left). The bottom window indicates that the command was successfully completed.

Return to the Server Explorer, select SYS.ORCL again, right-click and select Privileges...

Description of aspnt_install03.gif follows
Description of the illustration aspnt_install03.gif

This brings up the Grant/Revoke Privileges Wizard in ODT.

Grant privileges to the new database user so it can create the schema and store web site state for the ASP.NET providers:

Set Object type to USER and set User to ASPNET_DB_USER.

Note:

You may need to refresh in order to see ASPNET_DB_USER.

Use the right angle ( >) arrow in the middle to move privileges from the Available Privileges list to the Granted Privileges list.

The generally required privileges include:

Change notification

Create job

Create procedure

Create public synonym

Create role

Create session

Create table

Create view

Drop public synonym

Unlimited Tablespace - This example grants ASPNET_DB_USER unlimited tablespace. However, in most cases the administrator assigns the database user a specific tablespace quota.

Description of aspnt_install04.gif follows
Description of the illustration aspnt_install04.gif

Click Apply and the output window indicates success. Click OK.

Errors may occur during the setup script execution if the Oracle Providers for ASP.NET user is not granted the necessary privileges.

Configuring All Oracle Providers for ASP.NET

To configure all providers in the database at once, run InstallAllOracleASPNETProviders.sql.

To run this script in Oracle Developer Tools, do the following:

In Visual Studio, select Tools, then select Run SQL*Plus Script. When the screen comes up, select Browse.

Description of aspnt_install05a.gif follows
Description of the illustration aspnt_install05a.gif

Browse to the ORACLE_BASE\\ORACLE_HOME\ASP.NET\sql directory where ORACLE_BASE\\ORACLE_HOME represents your Oracle home, select InstallAllOracleASPNETProviders.sql, and click Open.

Description of aspnt_install05.gif follows
Description of the illustration aspnt_install05.gif

When the Run SQL*Plus Script screen reappears, select New Connection.

When the Connection Properties screen appears, be sure that the data source is Oracle Database (Oracle ODP.NET) and the data source name ORCL. Then enter the User name, ASPNET_DB_USER, and Password, with the Role as Default. Click OK.

Description of aspnt_install06.gif follows
Description of the illustration aspnt_install06.gif

In some situations, the Oracle Server Login dialog may appear. If it does, you can choose whether or not to Save Password.

When Run SQL*Plus Script reappears, select Run.

Description of aspnt_install07.gif follows
Description of the illustration aspnt_install07.gif

The SQL file runs, and in the background, the output window confirms the success of the scripts.

When the scripts finish running, select Cancel.

Configuring Oracle Providers for ASP.NET Individually

Applications may not require all Oracle Providers for ASP.NET. You can set up providers individually. In general, you must execute the InstallOracleASPNETCommon.sql install script before any other install scripts. It sets up a common infrastructure for the ASP.NET providers. Then, for each required Oracle Provider for ASP.NET, execute the specific SQL script (in any order).

These install scripts are located in the ORACLE_BASE\\ORACLE_HOME\ASP.NET\sql directory.

Table 2-1 Individual Install Scripts for Oracle Providers for ASP.NET

Provider
Required Installation Script

Oracle Membership Provider

InstallOracleMembership.sql

Oracle Personalization Provider

InstallOraclePersonalization.sql

Oracle Profile Provider

InstallOracleProfile.sql

Oracle Role Provider

InstallOracleRoles.sql

Oracle Session State Provider

For Oracle Database 10g Release 1 and later InstallOracleSessionState.sql

For Oracle Database 9i Release 2 InstallOracleSessionState92.sql

There are correspondingly named uninstall scripts for these install scripts.

Note: This provider does not require the execution of InstallOracleASPNETCommon.sql. It only requires the execution of the provider-specific .sql install script.

Oracle Site Map Provider

InstallOracleSiteMap.sql

Oracle Web Events Provider

InstallOracleWebEvents.sql

Oracle Cache Dependency Provider

No script execution needed

Uninstalling Schemas for Oracle Providers for ASP.NET

Use the corresponding uninstall scripts to remove database objects created by the install scripts. These scripts have the prefix Uninstall.

Setting the Connection String

With the database now configured to store and retrieve Oracle Providers for ASP.NET information, the middle-tier or client must be able to connect to the ASPNET_DB_USER user.

To configure the connection information on your computer:

Go to the machine.config file located here: drive:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG

With your text editor, search for <connectionStrings> and change the line that begins with <add name="OraAspNetConString".. to add the user id, password, data source entry, data source, and provider name as follows:

<connectionStrings>
<add name="OraAspNetConString" connectionString="User
     Id=aspnet_db_user;Password=your_password;Data Source=orcl;"
     providerName="Oracle.DataAccess.Client" />
</connectionStrings>

Note:

Be sure to change the password to the one that you have created. Also, be sure to remove any carriage returns that you might have copied into the connection string.

Customizing Oracle Providers for ASP.NET for Different Setups

Developers can customize the properties of each ASP.NET provider from within the <system.web> section of the machine.config file.

While Oracle Universal Installer automatically configures the machine.config file, developers can apply more fine-grained application-level control over the Oracle Providers for ASP.NET by using the web.config file. This file overrides entries from the machine.config file, but only for the specific web application it is associated with. Developers can set up their web.config file with the same XML syntax as the machine.config file.

3 Building a Simple .NET Application Using ODP.NET

This chapter contains:

Creating a New Project

Adding a Reference

Adding Namespace Directives

Designing the User Interface

Writing the Connection Code

Compiling and Running the Application

Error Handling

Creating a New Project

Visual Studio groups all development code that you create into containers known as projects. Simpler projects often contain only one file. In this section, you will learn how to create a new development project.

The application you build in this chapter serves as a starting point for work in subsequent chapters, so it is important to follow the order of this guide.

NOTE: When necessary, instructions specify Visual C# or Visual Basic.

To start a new project:

Start Visual Studio.

Open the Start menu, select All Programs, and then select Microsoft Visual Studio 2008.

Description of connect00.gif follows
Description of the illustration connect00.gif

The Microsoft Visual Studio IDE environment appears.

In the Start Page, under the Recent Projects heading, click Create: Project.

Alternatively, from the File menu, select New, and then select Project.

A New Project dialog box appears.

In Project Types, select the type of project you are creating:

Visual C#:

Visual C#: Windows

Visual Basic:

Other Languages: Visual Basic: Windows

In Templates, select Windows Forms Application.

In the Name field, enter the appropriate name.

Visual C#:

HR_Connect_CS

Visual Basic:

HR_Connect_VB

The abbreviation CS indicates C# projects and VB indicates Visual Basic projects.

In Location, enter the directory where you want to save the files.

For this guide, enter this directory C:\HR_Projects.

In Solution Name, the appropriate name, HR_Connect_CS or HR_Connect_VB should appear.

A solution can contain several projects; when it contains only one project, you can use the same name for both.

Check Create directory for solution.

Click OK.

Description of connect01.gif follows
Description of the illustration connect01.gif

The project is created.

The main window now displays a new title, either HR_Connect_CS - Microsoft Visual Studio or HR_Connect_CS - Microsoft Visual Studio, depending on the language, and contains Form1 shown below.

It is important to remember that many projects automatically name the first form Form1. This is the name of the form control. Do not confuse this with the actual name given to the code file, which is typically Form1.cs or Form1.vb.

Both Form1 and Form1.xx can be renamed. For the purposes of this guide, we will rename Form1.xx several times.

Description of connect2a.gif follows
Description of the illustration connect2a.gif

Adding a Reference

This section shows you how to add a reference to the Oracle.DataAccess.dll file, which contains the data provider, Oracle Data Provider for .NET.

To add a reference:

From the Project menu, select Add Reference.

Description of reference01.gif follows
Description of the illustration reference01.gif

The Add Reference windows appears.

In the Add Reference window, under the .NET tab, select Oracle.DataAccess. Click OK.

Description of reference02.gif follows
Description of the illustration reference02.gif

Note that the new reference appears in the Solution Explorer.

Description of reference03.gif follows
Description of the illustration reference03.gif

Adding Namespace Directives

You can add Oracle namespace directives that allow you to indicate an assembly's namespaces within the module. To do this, add C# using statements or Visual Basic Imports statements, at or near the top of a code file.

Note:

Adding a reference makes the namespace available within the application. Adding a namespace directive within the application code makes the namespace more visible and allows for additional scoping.

To add Oracle namespace directives:

With Form1 active, from the View menu select Code.

Alternatively, you can use the F7 keyboard shortcut.

Description of connect04.gif follows
Description of the illustration connect04.gif

Add the following statements to the list of declarations depending on the language you are using.

Visual C#:

Add with other using statements, before the namespace.

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

Description of connect05.gif follows
Description of the illustration connect05.gif

Visual Basic:.

Add to the top of the file, in the declarations section.

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Description of connect06.gif follows
Description of the illustration connect06.gif

Save the changes by selecting Save from the File menu, or using the Ctrl+S keyboard shortcut.

Designing the User Interface

You can create a user interface by adding the toolbox controls to the design form. This interface accepts connection information from the user.

To add toolbox controls:

From the View menu, select Designer.

This opens Form1, in design view, if it is not already open.

You will toggle between Code and Designer a lot. The keyboard shortcuts are F7 and shift- F7 respectively.

From the View menu, select Toolbox.

In the Toolbox, expand Common Controls.

Description of control01.gif follows
Description of the illustration control01.gif

In the Toolbox, select Label, and drag it onto the Form1.

Description of control02.gif follows
Description of the illustration control02.gif

On Form1, right-click label1.

Description of control03.gif follows
Description of the illustration control03.gif

From the menu, select Properties, if the Properties Window is not already visible.

The Properties Window appears.

In the Properties Window, change the Text property from label1 to User ID.

Description of control04.gif follows
Description of the illustration control04.gif

Repeat steps 4 through 7 twice, placing two more labels on Form 1 and changing their text properties to Password and Data Source.

Description of control05.gif follows
Description of the illustration control05.gif

In the Toolbox, select TextBox, and drag it onto the Form1, next to the User ID label.

Description of control06.gif follows
Description of the illustration control06.gif

In the Properties Window, change the Name property to userID.

Description of control07.gif follows
Description of the illustration control07.gif

Repeat steps 9 and 10 twice, positioning two more text boxes next to the existing labels, and setting the Name property to password and dataSource.

Description of control08.gif follows
Description of the illustration control08.gif

Select the text box next to the Password label. In the Properties Window, scroll to the PasswordChar property and set it to an asterisk (*).

This masks the password during entry.

Description of control09.gif follows
Description of the illustration control09.gif

From the Toolbox, select Button and drag it onto Form1.

In the Properties Window, change the Text property of the button from button1 to Connect, and change the Name property to connect.

Description of control10.gif follows
Description of the illustration control10.gif

Save.

Close the Toolbox.

Writing the Connection Code

Now we write the code that takes the information provided to the user interface and connects to the database.

To connect to the database, you must create a connection object.

To write code that connects to the database:

These steps enable your application to connect to the database based on data that the user enters into the Form1 control. See "Compiling and Running the Application".

From the View menu, select Code.

Add the code indicated to instantiate a database connection string.

Visual C#: Add the class variable conn to the Form1 class right after the public Form1() block with this code.

private OracleConnection conn = new OracleConnection();

Description of connect1.gif follows
Description of the illustration connect1.gif

Visual Basic: Add the conn class variable in the Form1 class declaration, using this code.

Public Class Form1
   Dim conn As New OracleConnection

Description of connect2.gif follows
Description of the illustration connect2.gif

Save your changes.

Change to Designer view by clicking on the View menu and selecting Designer.

Double-click the Connect button on Form1 to open the code window to the connect_Click() method.

Insert the code indicated into the connect_Click() method.

Visual C#:

conn.ConnectionString = "User Id=" + userID.Text +
   ";Password=" + password.Text +
   ";Data Source=" + dataSource.Text + ";";
conn.Open();

Visual Basic:

conn.ConnectionString = "User Id=" + userID.Text & _
  ";Password=" + password.Text & _
  ";Data Source=" + dataSource.Text
conn.Open()

Note: Before a connection can be opened, it must be built from user input for the User ID, Password, and Data Source. The Open() method makes the actual connection.

Set the Enabled attribute of the button to false by inserting the indicated code at the end of the connect_Click() method.

This disables the Connect button, which is a good practice once a connection is successfully made.

Visual C#:

connect.Enabled = false;

Description of connect_obj1.gif follows
Description of the illustration connect_obj1.gif

Visual Basic:

connect.Enabled = false

Description of connect_obj2.gif follows
Description of the illustration connect_obj2.gif

You have now finished writing an application that can connect to the Oracle database. The following sections show how to use it.

Compiling and Running the Application

This section shows how to compile and run the application you created in the previous sections.

To compile and run the application:

From the Build menu, select Build Solution.

Description of build1.gif follows
Description of the illustration build1.gif

Ensure that there are no errors reported in the output window, available from the View menu.

The following graphics shows a typical output result.

Description of build2.gif follows
Description of the illustration build2.gif

If there are any errors indicated, from the View menu, select Error List and fix the errors.

From the Debug menu, select Start Without Debugging to run the application.

Description of build3.gif follows
Description of the illustration build3.gif

In the Form1 application, enter the User ID, Password, and Data Source.

Click Connect.

This is where the application makes use of the tnsnames.ora file. See "Configuring a NET Connect Alias".

Once the connection is opened, the Connect button is disabled. You have succeeded in implementing a connection to an Oracle Database instance.

Description of build4.gif follows
Description of the illustration build4.gif

Error Handling

Applications must be able to handle run-time errors gracefully. For example, if you try to log in using an incorrect password, the application you developed so far cannot establish a connection to the database, and exits with the following unhandled exception error, ORA-1017: invalid username/password, logon denied.

Description of error1.gif follows
Description of the illustration error1.gif

You must reselect Start Without Debugging to try this with a different password.

Error handling manages occurrences of conditions that change the normal flow of program execution. Oracle Data Provider for .NET contains three classes for error handling and support:

The OracleError class represents a warning or an error reported by Oracle.

An OracleErrorCollection class represents a collection of all errors that are thrown by the Oracle Data Provider for .NET. It is a simple ArrayList that holds a list of OracleErrors.

The OracleException class represents an exception that is thrown when the Oracle Data Provider for .NET encounters an error. Each OracleException object contains at least one OracleError object in the Error property that describes the error or warning.

Using Try-Catch-Finally Block Structure

.NET languages use Try-Catch-Finally block structure for error handling. With this structure, the Try code is the main code, the goal that the application wants to accomplish. The Catch code catches errors of various types, as shown in the next two section. The Finally block comes last and always executes.

The Finally block frequently contains the Dispose method, which closes and disposes of the connection. Having the Dispose method in the Finally block ensures that the database connection is always closed after the Try-Catch-Finally block completes. Closing database connections after the application no longer requires database access is important for many reasons, especially data security.

Attempting to close a closed database connection does not cause an error. The attempt is irrelevant. Nonetheless, placing Dispose() in the Finally code block guarantees that the connection is closed.

The next section shows how to use Try-Catch-Finally block structure with general errors, and the section after that, with Oracle errors.

Handling General Errors

This section shows how to handle general errors using a Try-Catch-Finally block.

To handle general errors:

Change the code of the connect_Click() method in Form1 by adding an implementation of the Try-Catch-Finally syntax.

New code is in bold font.

Visual C#:

private void connect_Click(object sender, EventArgs e)
{
  conn.ConnectionString = "Data Source=ORCL;User Id="
     + userID.Text + ";Password=" + password.Text + ";";
  try
  {
    conn.Open();
    connect.Enabled = false;
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message.ToString());
  }
  finally
  {
    conn.Dispose();
  }
}

Alternatively, you can use C# syntax that disposes of a connection when it goes out of scope, with the using keyword, as follows:

using (OracleConnection conn = new OracleConnection())
{
  conn.Open();
  // application code
  ...
}

Visual Basic:

Try
  conn.Open()
  connect.Enabled = false

Catch ex As Exception
  MessageBox.Show(ex.Message.ToString())

Finally
  conn.Dispose()
End Try

From the Build menu, select Rebuild Solution.

Ensure that there are no errors.

From the Debug menu, select Start Without Debugging.

Run the application again, as described in section "Compiling and Running the Application", and attempt to connect using an incorrect password. This time, the application catches the error and displays it in a pop-up window, ORA-1017: invalid username/password; logon denied.

Description of error2.gif follows
Description of the illustration error2.gif

Handling Common Oracle Errors

In the completed Try-Catch-Finally block code shown below, the first Catch statement branch is skipped if there are no OracleExceptions. The second Catch statement branch catches all other Exceptions.

The first catch statement contains Case statements, which can be used to trap common database errors and display them in a user-friendly manner.

Note that the second Case statement catches a specific example of OracleException, when the database is not accessible.

To handle specific errors:

Stop the database instance. See Appendix A, "Starting and Stopping an Oracle Database Instance".

Add the Catch OracleException block shown below in bold, before the Catch Exception block previously added in the connect_Click() method.

Visual C#:

try
  {
    conn.Open();
    connect.Enabled = false;
  }
catch (OracleException ex)
{
  switch (ex.Number)
  {
    case 1:
      MessageBox.Show("Error attempting to insert duplicate data.");
      break;
    case 12560:
      MessageBox.Show("The database is unavailable.");
      break;
    default:
      MessageBox.Show("Database error: " + ex.Message.ToString());
      break;
  }
}
 catch (Exception ex)
   {
     MessageBox.Show(ex.Message.ToString());
   }
finally
  {
    conn.Dispose();
  }
}

Visual Basic:

Try
  conn.Open()
  connect.Enabled = false

Catch ex As OracleException ' catches only Oracle errors
  Select Case ex.Number
    Case 1
      MessageBox.Show("Error attempting to insert duplicate data.")
    Case 12560
      MessageBox.Show("The database is unavailable.")
    Case Else
      MessageBox.Show("Database error: " + ex.Message.ToString())
  End Select

Catch ex As Exception
  MessageBox.Show(ex.Message.ToString())

Finally
  conn.Dispose()
End Try

Compile and run the application again, as described in section "Compiling and Running the Application".

Note that the ORA-12560 error appears in the pop-up window as The database is unavailable with no error number provided.

Description of error04.gif follows
Description of the illustration error04.gif

Restart the database instance. See Appendix A, "Starting and Stopping an Oracle Database Instance".

4 Retrieving and Updating with Oracle Data Provider for .NET

This chapter contains:

Using the Command Object

Retrieving Data: a Simple Query

Retrieving Data: Bind Variables

Retrieving Data: Multiple Values

Using the DataSet Class with Oracle Data Provider for .NET

Enabling Updates to the Database

Inserting, Deleting, and Updating Data

Using the Command Object

To view, edit, insert or delete data in a database, you must encapsulate a request in an OracleCommand object specifying a SQL command, stored procedure, or table name. The OracleCommand object creates the request, sends it to the database, and returns the result.

To use the command object:

Make two copies of Form1.xx, from application HR_Connect_xx in Chapter 3, "Building a Simple .NET Application Using ODP.NET". To make copies, see the instructions in Appendix B, "Copying a Form".

Name the copies Form2.cs or Form2.vb and Form3.cs or Form3.vb. The first copy is for the first part of the chapter, and the second copy for the second part of the chapter

Open Form2.cs or Form2.vb.

Note that the actual form in the designer still says Form1, as you renamed code files but not the actual form controls within the project.

Create a string that represents the SQL query and add to the body of the try statement.

The new code is in bold typeface.

Visual C#:

try
{
    conn.Open();
    connect.Enabled = false;

    // SQL Statement
    string sql = "select department_name from departments"
       + " where department_id = 10";
}

Visual Basic:

Try
      conn.Open()
      connect.Enabled = False

     Dim sql As String = "select department_name from departments" & _
       "where department_id = 10"

Use the new sql variable to create the OracleCommand object, and set the CommandType property to run a text command.

Visual C#:

try
{
    conn.Open();
    connect.Enabled = false;

    // SQL Statement
    string sql = "select department_name from departments"
       + " where department_id = 10";

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
}

Visual Basic:

Try
      conn.Open()
      connect.Enabled = False

      Dim sql As String = "select department_name from departments" & _
         "where department_id = 10"

Dim cmd As New OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text

Save your work.

Retrieving Data: a Simple Query

This section demonstrates retrieving data from the database.

The ExecuteReader() method of an OracleCommand object returns an OracleDataReader object, which can be accessed to display the result on the form. The application uses a ListBox to display the results.

To retrieve data:

Create an OracleDataReader object, by adding the code indicated to the bottom of the Try block of the connect_Click() method.

This enables you to read the result of the query.

Visual C#:

OracleDataReader dr = cmd.ExecuteReader();
dr.Read();

Visual Basic:

Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()

Open Form1 in Design view. From the View menu, select Designer.

From the View menu, select Toolbox.

From the Toolbox, select a Label and drag it onto Form1.

From the View menu, select Properties Window.

In the Properties window, change the Text of the label to Department.

From the Toolbox, under Window forms, select a ListBox and drag it onto Form1.

In the Properties window, under Design, change the Name to departments.

Description of simple1.gif follows
Description of the illustration simple1.gif

Add accessor type methods for retrieving data from the query result.

Double-click the connect button to edit the connect_click() method, and add the code indicated to the bottom of the try block.

Visual C#:

departments.Items.Add(dr.GetString(0));

Visual Basic:

departments.Items.Add(dr.GetString(0))

Typed accessors, such as GetString, return native .NET data types and native Oracle data types. Zero-based ordinals passed to the accessors specify which column in the result set to return.

Build and save the application.

Run the application. Enter the login and data source.

After you connect, the departments list box shows Administration, the correct name for department number 10 in the HR schema, as requested by the SELECT statement.

Description of simple2.gif follows
Description of the illustration simple2.gif

Retrieving Data: Bind Variables

Bind variables are placeholders inside a SQL statement. When a database receives a SQL statement, it determines if the statement has already been executed and stored in memory. If the statement does exist in memory, Oracle Database can reuse it and skip the task of parsing and optimizing the statement. Using bind variables makes the statement reusable with different input values. Using bind variables also improves query performance in the database, eliminates the need for special handling of literal quotation marks in the input, and protects against SQL injection attacks.

The following code shows a typical SELECT statement that does not use bind variables, with the value 10 specified in the WHERE clause of the statement.

SELECT department_name FROM departments WHERE department_id = 10

The following code replaces the numerical value with a bind variable :department_id. A bind variable identifier always begins with a single colon ( :).

SELECT department_name FROM departments WHERE department_id = :department_id

Note that bind variables can also be used with UPDATE, INSERT, and DELETE statements, and also with stored procedures. The following code illustrates how to use bind variables in an UPDATE statement:

UPDATE departments SET department_name = :department_name
  WHERE departname_id = : department_id

See "Inserting, Deleting, and Updating Data" for more details.

You can use the OracleParameter class to represent each bind variable in your .NET code. The OracleParameterCollection class contains the OracleParameter objects associated with the OracleCommand object for each statement. The OracleCommand class passes your SQL statement to the database and returns the results to your application.

You can bind variables by position or by name by setting the OracleCommand property BindByName (which defaults to false).

Binding by position

You must use the Add() method to add the parameters to the OracleParameterCollection in the same order as they appear in the SQL statement or stored procedure.

Bind by name

You may add the parameters to the collection in any order; however, you must set the ParameterName property for the parameter object to the same name as the bind variable identifier in the stored procedure declaration.

In addition to the binding mode (by position or by name), the .NET developer sets the following properties for each parameter object: Direction, OracleDbType, Size, and Value.

Direction Bind variables may be used as output, input, or input/output parameters. The Direction property indicates the direction of each parameter. The default value of the Direction property is Input.

OracleDbType property indicates whether or not the parameter is a number, a date, a VARCHAR2, and so on.

Size indicates the maximum data size that parameters with a variable length data type, such as VARCHAR2, can hold.

Value contains the parameter value, either before statement execution (for input parameters), after execution (for output parameters), or both before and after (for input/output parameters).

To retrieve data using bind variables:

Move the ListBox named Departments to the right.

From the View menu, select Toolbox.

From the Toolbox, select a TextBox and drag it onto Form1, under the label that says Department.

From the View menu, select Properties Window.

In the Properties window, change Name to departmentID.

Description of bind0.gif follows
Description of the illustration bind0.gif

Change the SELECT statement to use the bind variable by adding the code indicated to the Try block of the connect_Click() method.

Changed or new code is in bold typeface.

Visual C#:

string sql = "select department_name from departments where department_id = " +
  ":department_id";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
OracleParameter p_department_id = new OracleParameter();
p_department_id.OracleDbType = OracleDbType.Decimal;
p_department_id.Value = departmentID.Text;
cmd.Parameters.Add(p_department_id);

OracleDataReader dr = cmd.ExecuteReader();
dr.Read();

departments.Items.Add(dr.GetString(0));

Visual Basic:

Dim sql As String = "select department_name from departments where" & _
  "department_id= ":department_id"
Dim cmd As OracleCommand = New OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text
Dim p_department_id as OracleParameter = new OracleParameter()
p_department_id.OracleDbType = OracleDbType.Decimal
p_department_id.Value = departmentID.Text
cmd.Parameters.Add(p_department_id)

Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()

departments.Items.Add(dr.GetString(0))

For this code, the parameter object sets the OracleDbType property, but there is no need to set the Direction property because it uses the default value, Input. There is no need to set the Size property because the object is an input parameter, and the data provider can determine the size from the value.

Save and run the application.

Enter the login information, and a typical department number, such as 50, from the HR schema.

Click Connect.

The application returns the name of the department that corresponds to the department ID.

Description of bind1.gif follows
Description of the illustration bind1.gif

Retrieving Data: Multiple Values

You frequently need to retrieve more than just one value from the database. A DataReader object can retrieve values for multiple columns and multiple rows. Consider the multiple column, multiple row query in the following example:

SELECT department_id, department_name, manager_id, location_id
  FROM departments
  WHERE department_id < 100

Processing multiple rows from the DataReader object requires a looping construct. Also, a control that can display multiple rows is useful. Because the OracleDataReader object is a forward-only, read-only cursor, it cannot be bound to an updatable or backward scrollable control such as Windows Forms DataGrid control. An OracleDataReader object is, however, compatible with a ListBox control.

To retrieve multiple values:

In the try block of the connect_Click() method, change the SQL query to return a multiple row result set and add a while loop to enclose the read method that displays the department names.

Visual C#:

try
{
  ...
string sql = "select department_name from departments where department_id" +
  "< :department_id";
...
  while (dr.Read())
  {
    departments.Items.Add(dr.GetString(0));
  }
}

Visual Basic:

Try
  ...
  Dim sql As String = "select department_name from departments " & _
        "where department_id < :department_id"
...
  While (dr.Read())
    departments.Items.Add(dr.GetString(0))
  End While

Save and run the application.

Enter the login information and enter 50 for the department.

Click Connect.

The application returns the name of the departments that correspond to the query.

Description of bind2.gif follows
Description of the illustration bind2.gif

Using the DataSet Class with Oracle Data Provider for .NET

The DataSet class provides a memory-resident copy of database data. It consists of one or more tables that store relational or XML data. Unlike an OracleDataReader object, a DataSet is updatable and backward scrollable.

To use the DataSet class:

If you have not done so before, make another copy of the Form1 that you completed in Chapter 3, and name it Form3.vb or .cs, as described in Appendix B, "Copying a Form". If Form1.xx does not appear in the Solution Explorer, from the Project menu, select Show All Files.

From the View menu, select Designer view.

From the View menu, select Toolbox.

From the Toolbox, select a DataGridView and drag it onto Form1.

From the View menu, select Properties Window.

In the Properties window, change the Name of the data grid view to departments.

Description of dataset1.gif follows
Description of the illustration dataset1.gif

From the View menu, select Code.

Immediately after the conn declaration in the code, add variable declarations to the class variables, as indicated.

Visual C#:

public partial class Form1 : Form
{
  public Form1()
  {
      InitializeComponent();
  }
  private OracleConnection conn = new OracleConnection();
  private OracleCommand cmd;
  private OracleDataAdapter da;
  private OracleCommandBuilder cb;
  private DataSet ds;
...

Visual Basic:

Public Class Form1    Dim conn As New OracleConnection     Private cmd As OracleCommand
    Private da As OracleDataAdapter
    Private cb As OracleCommandBuilder
    Private ds As DataSet

Within the connect_Click() method try block, add code to:

Query the database

Fill the DataSet with the result of the command query

Bind the DataSet to the data grid (departments)

Visual C#:

conn.Open();
connect.Enabled = false;

string sql = "select * from departments where department_id < 60";
cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;

da = new OracleDataAdapter(cmd);
cb = new OracleCommandBuilder(da);
ds = new DataSet();

da.Fill(ds);

departments.DataSource = ds.Tables[0];

Visual Basic:

conn.Open()
connect.Enabled = False

Dim sql As String = "select * from departments where department_id < 60"
cmd = New OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text

da = New OracleDataAdapter(cmd)
cb = New OracleCommandBuilder(da)
ds = New DataSet()

da.Fill(ds)

departments.DataSource = ds.Tables(0)

Build and save the application.

Run the application, entering the login and data source.

After you successfully connect to the database, the data grid is populated with the results of the query.

Description of dataset3.gif follows
Description of the illustration dataset3.gif

Enabling Updates to the Database

At this point, the DataSet contains a client copy of the database data. In this section, you will add a button that enables client data changes to be saved back to the database. The following section will show you how to test updating, inserting, and deleting the data.

To enable saving data from the DataSet to the database:

From the Toolbox, drag and drop a Button onto Form1.

In the Properties window, change the Name of the button to save.

Change the Text property to Save.

At the top of the Properties Window, click Events (the lightning bolt). In the list of events, select the click event. In the second column, enter the event name, save_Click.

Description of dataset2.gif follows
Description of the illustration dataset2.gif

From the View menu, select Code.

Add code that updates the data, to the body of the save_Click() method, as indicated.

Visual C#:

da.Update(ds.Tables[0]);

Visual Basic:

da.Update(ds.Tables(0))

You may see some errors show up in the Error List. These will disappear after you add the code in the next step.

Within the Form() method or Form1_Load method, add the code indicated.

Visual C#:

public Form1()
{
    InitializeComponent();
    save.Enabled = false;
}

Visual Basic:

Private Sub Form1_Load(ByVal sender As System.Object, & _
    ByVal e As System.EventArgs) Handles MyBase.Load
    save.Enabled = false

Within the connect_Click() method try block, add code to enable the Save button as indicated:

Visual C#:

conn.Open();
 ...
departments.DataSource = ds.Tables[0];

save.Enabled = true;

Visual Basic:

conn.Open()
...
departments.DataSource = ds.Tables(0)

save.Enabled = True

Remove the conn.Dispose() call from the finally block in the connect_Click() method.

Note: In the previous code used in this example, this method was necessary to dispose or close the connection. However, with these changes to the code, it is necessary to keep the connection open after the query result returns, so that data changes made by the end user are propagated to the database. A general override call, components.Dispose(), is already part of the definition of Form1.

Build and save the application.

Run the application, entering the login and data source.

After you successfully connect to the database, the data grid is populated with the results of the query.

Description of dataset3a.gif follows
Description of the illustration dataset3a.gif

Inserting, Deleting, and Updating Data

This section demonstrates how to use your new application to directly manipulate data in the database.

To insert, delete and update data:

Run the application you created in the last section, entering the login and data source, and connecting to the database.

At the bottom of the data grid, enter a new record at the * prompt:

For DEPARTMENT_ID, enter 5.

For DEPARTMENT_NAME, enter Community Outreach.

Leave MANAGER_ID without a value.

For LOCATION_ID, enter 1700.

Description of dataset4.gif follows
Description of the illustration dataset4.gif

Click Save.

Close the application to check if the new record is saved.

Run the application again, and connect to the database.

Note that the new department is at the top of the DEPARTMENTS table, in numerical order by DEPARTMENT_ID.

Change the name of the department to Community Volunteers, and click the Save button.

Description of dataset10.gif follows
Description of the illustration dataset10.gif

Repeat Step 4, run the application again, and connect to the database, and note that the name of the department is changed.

Select the entire record you just changed (click the cursor icon in the far left column), and delete it using the Delete key. Click the Save button. Description of dataset11.gif follows
Description of the illustration dataset11.gif

Repeat Step 4, run the application again, and connect to the database, and note that the new record is no longer part of the DEPARTMENTS table.

Close the application.

5 Using Oracle Developer Tools for Visual Studio

This chapter contains:

Using Oracle Developer Tools

Connecting to the Oracle Database

Creating a Table and Its Columns

Creating a Table Index

Adding Table Constraints

Adding Data to a Table

Generating Code Automatically to Display and Update Data

Using Oracle Developer Tools

Oracle Developer Tools for Visual Studio (ODT) is a tightly integrated Add-in for Visual Studio. Using enhancements that ODT brings to the Server Explorer, you can automatically create tables, indexes, constraints, data connections and other database schema objects. Additionally you can automatically generate application code.

See Also:

"Overview of Oracle Developer Tools for Visual Studio"

Connecting to the Oracle Database

This section shows you how to use the Server Explorer to connect to the Oracle Database for the purpose of automatically creating or modifying database schema objects.

To connect to the database:

From the View menu, select Server Explorer.

In Server Explorer, right-click Data Connections.

Select Add Connection.

Description of addconnection1.gif follows
Description of the illustration addconnection1.gif

When the Add Connection window appears, determine if the Data source says Oracle Database (Oracle ODP.NET).

If it does, skip to Step 6.

Description of addconnection1a.gif follows
Description of the illustration addconnection1a.gif

If Data source does not say Oracle Database (Oracle ODP.NET), select Change.

The Change Data Source window appears.

Description of addconnection2a.gif follows
Description of the illustration addconnection2a.gif

Choose Oracle Database and then select Oracle Data Provider for .NET.

On the Connection Details tab, in the Add Connection window, enter the following information:

Data source name: For this example, use the alias of the remote database instance, orcl.

If you are connecting to a database on the same computer, use the Local Database.

Select the Use a specific user name and password option.

For User name, enter HR.

For Password, enter the password created when the hr account was unlocked and set up.

To save the password for future sessions, check the Save password box.

Ensure that Role is set to Default. This refers to the default roles that have been granted to the user hr.

The Connection name should be generated automatically from the Data source name and the User name values. In this exercise, it will be HR.orcl.

Description of addconnection2.gif follows
Description of the illustration addconnection2.gif

Click the Apply Filters tab, and verify that the HR schema is in the Displayed schemas column. When you expand the schema category nodes in the data connection, only those schema objects (tables, views, and so on) selected in the Apply Filters tab appear.

Description of addconnection3.gif follows
Description of the illustration addconnection3.gif

Click Test connection.

Description of addconnection4.gif follows
Description of the illustration addconnection4.gif

The test should succeed. Click OK.

If the test fails, it may be due to one or more of the following issues that you must address before proceeding with further steps:

The database is not started.

The database listener is not started.

The database connectivity is not properly configured.

You do not have the correct user name, password, or role.

In the Add Connection window, click OK.

In the Server Explorer, expand the HR.ORCL connection to show the contents of the HR schema. You should see Tables, Views, Procedures, Functions, Packages, Synonyms, Sequences, and so on.

Description of addconnection5.gif follows
Description of the illustration addconnection5.gif

Creating a Table and Its Columns

Oracle Developer Tools includes a user interface for creating database objects. In this section, you will create a table named DEPENDENTS.

To create a table:

In Server Explorer, right-click Tables and select New Relational Table.

Description of table1.gif follows
Description of the illustration table1.gif

A table design window appears.

In design view, enter DEPENDENTS for Table name.

Description of table2.gif follows
Description of the illustration table2.gif

In the Column Properties tab, add the following six columns in this manner:

Click Add. Then enter the new column information. Keep clicking add until you have added all the new columns.

Fields may differ depending on the data type. You might have to close windows such as Server Explorer or Solution Explorer to access the entire tab.

Name LAST_NAME, Data Type VARCHAR2, and Size 30. Leave all other properties at their default values.

Name FIRST_NAME, Data Type VARCHAR2, and Size 20. Leave all other properties at their default values.

Name BIRTH_DATE, Data Type DATE. Leave all other properties at their default values.

Name RELATIONSHIP, Data Type VARCHAR2, and Size 20. Leave all other properties at their default values.

Name EMPLOYEE_ID, Data Type NUMBER, deselect Allow null, enter Precision 6 and Scale 0.

Name DEPENDENT_ID, Data Type NUMBER, deselect Allow null check box, enter Precision 6 and Scale 0.

Description of table3.gif follows
Description of the illustration table3.gif

Click Preview SQL.

The SQL statement for constructing the table appears in the Preview SQL window, similar to this.

Description of table4.gif follows
Description of the illustration table4.gif

Click OK to close the Preview SQL window.

In the table design view, click Save.

This action creates the new table DEPENDENTS in the HR schema. The new table is listed in the Server Explorer.

Description of table5.gif follows
Description of the illustration table5.gif

Creating a Table Index

Indexes are an optional but very powerful feature of relational databases. An index enables quick access to the rows (or records) in a table. In this section, you will create an index for the DEPENDENTS table.

To create an index:

In the DEPENDENTS Table Design view, click the Indexes tab.

Click Add under the Indexes area.

The Index Properties area becomes active.

Under Index Properties (to the right), enter the Name DEPENDENTS_INDEX, and leave all other properties in their default state.

At the bottom of the Index Properties area, click Add.

Under Index keys, click in the first cell of the Key column, and select DEPENDENT_ID from the list.

Description of index1.gif follows
Description of the illustration index1.gif

Click Preview SQL

A Preview SQL window appears, displaying SQL statement to construct the index.

Description of index2.gif follows
Description of the illustration index2.gif

Click OK to close the Preview SQL window.

In the table design view, click Save.

This creates the new index on the table DEPENDENTS in the HR schema. To see this in the Server Explorer, expand the DEPENDENTS table and related Indexes.

Description of index3.gif follows
Description of the illustration index3.gif

Adding Table Constraints

The database uses constraints to automatically enforce data integrity defining rules for permissible data values. Constraints also implement primary and foreign keys in the table. In this section, you will add such constraints to the new table DEPENDENTS.

How to add foreign and primary keys:

In the DEPENDENTS table design view, click the Constraints tab.

Note that depending on your configuration, there may already be default check constraints in the list.

Under the Constraints area, add the following constraints in this manner:.

Under Constraint Properties, Click Add. Then enter the new constraint information. Keep clicking add until you have added all the new constraints.

Name EMPLOYEES_FK, Type Foreign Key, Table EMPLOYEES, Constraint EMP_EMP_ID_PK. Under Association, select Referenced Column: EMPLOYEE_ID, and Local Column: EMPLOYEE_ID, set the On delete value to Cascade. Leave all other properties at their default values.

Description of constraint1.gif follows
Description of the illustration constraint1.gif

Name DEPENDENTS_PK, Type Primary Key.

Under the Primary key columns area, click Add (you may need to scroll down). Under Primary Key Columns, select Key: DEPENDENT_ID, set the Using index value to DEPENDENTS_INDEX. Leave all other properties at their default values.

Description of constraint2.gif follows
Description of the illustration constraint2.gif

Click Preview SQL.

The Preview SQL window displays the code generated for constraints on table DEPENDENTS. Note that adding constraints is an ALTER TABLE command because constraints change the definitions of the DEPENDENT_ID and EMPLOYEE_ID columns of the table.

Description of constraint3.gif follows
Description of the illustration constraint3.gif

Click OK to close the Preview SQL window.

In the table design view, click Save.

This action creates the two new constraints on the DEPENDENTS table in the HR schema. To see the Server Explorer, expand the hierarchy tree for the table DEPENDENTS and constraints.

Description of constraint4.gif follows
Description of the illustration constraint4.gif

Adding Data to a Table

You must now add data to the new DEPENDENTS table.

To populate a table:

In Server Explorer, right-click the DEPENDENTS table and select Retrieve Data.

Description of retrieve1.gif follows
Description of the illustration retrieve1.gif

A table grid for DEPENDENTS appears in design view.

Enter the four records listed in Table 5-1 into the table grid.

Table 5-1 New Data for the DEPENDENTS Table

LAST_NAME
FIRST_NAME
BIRTH_DATE
RELATIONSHIP
EMPLOYEE_ID
DEPENDENT_ID

Ernst

Mary

06-MAY-2000

daughter

104

1041

Atkinson

Sue

12-JUL-1998

daughter

130

1301

Ernst

David

02-APR-2007

son

104

1042

Sciarra

Aaron

31-JAN-2008

son

111

1111

The grid now looks as follows:

Description of retrieve2.gif follows
Description of the illustration retrieve2.gif

Note that the data is automatically saved as you move between rows.

Generating Code Automatically to Display and Update Data

To explore the content of the DEPENDENTS table, we will build a form that uses a simple table query. In this section you will use the Visual Studio integrated development environment (IDE), to automatically generate the code that corresponds to your actions.

To create a new Data Source:

Start a new project, as described in "Creating a New Project". Name the new project as indicated.

Visual C#:

HR_ODT_CS.

Visual Basic:

HR_ODT_VB.

Check Create Directory for Solution. Click OK.

Switch to the Form1 design view, if you are not already in it.

Note: All applications start with Form1, but this is not related to applications created in previous chapters.

Click on the Server Explorer window to enable the Show Data Sources window.

From the Visual Studio Data menu, select Show Data Sources.

The Data Source window appears.

Description of datasource1.gif follows
Description of the illustration datasource1.gif

In the Data Sources window, click Add New Data Source.

The Data Source Configuration Wizard opens.

In the Data Source Configuration Wizard, under Choose a Data Source Type, select Database.

Click Next.

Description of datasource2.gif follows
Description of the illustration datasource2.gif

Under Choose Your Data Connection, select HR.ORCL, or HR.(Local Database). For this example, we will use HR.ORCL.

Select Yes, include sensitive data in the connection string.

Click Next.

Description of datasource3.gif follows
Description of the illustration datasource3.gif

Under Save the Connection String to the Application Configuration File, select Yes, save the connection as: ConnectionString.

Click Next.

Description of datasource4.gif follows
Description of the illustration datasource4.gif

Under Choose Your Database Objects, expand Tables.

Check the DEPENDENTS(HR) table.

Change the DataSet name to tableDependents.

Click Finish.

Description of datasource5.gif follows
Description of the illustration datasource5.gif

See Also:

"Using the DataSet Class with Oracle Data Provider for .NET" for information about the DataSet Class

To automatically generate code using drag-and-drop:

Switch to the Form1 Design view.

In the Data Sources window, expand tableDependents.

Description of datasource6.gif follows
Description of the illustration datasource6.gif

Select the DEPENDENTS table, and drag it onto Form1.

You may need to resize both the form and the table grid.

Description of datasource7.gif follows
Description of the illustration datasource7.gif

Note that along with the table grid (which includes record navigation elements), the following components were added to the design view of your project. These objects represent automatically generated code for Form1.

Visual C#:

tableDependents, dEPENDENTSBindingSource, dEPENDENTSTableAdapter, tableAdapterManager, and dEPENDENTSBindingNavigator

Visual Basic:

TableDependents, DEPENDENTSBindingSource, DEPENDENTSTableAdapter, TableAdapterManager, and DEPENDENTSBiningNavigator

Double-click the Save icon (floppy disk) near the top of Form1.

This opens the code window for the Save icon for Form1.

In the private method, xxxSaveItem_Click(), encapsulate the existing code in a try...catch block. See the code listed for the complete Visual C# and Visual Basic names of this automatically generated method.

Also, add a MessageBox.show() call to both the try and catch sections. The updated method code follows, with new or changed code in bold font.

Visual C#:

private void dEPENDENTSBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
 try
 {
  this.Validate();
  this.dEPENDENTSBindingSource.EndEdit();
  this.tableAdapterManager.UpdateAll(this.tableDependents);

  MessageBox.Show("Update successful");
 }
 catch (System.Exception ex)
 {
  MessageBox.Show("Update failed: "+ ex.Message.ToString());
 }
}

Visual Basic#:

Private Sub DEPENDENTSBindingNavigatorSaveItem_Click(
 ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles DEPENDENTSBindingNavigatorSaveItem.Click

 Try
  Me.Validate()
  Me.DEPENDENTSBindingSource.EndEdit()
  Me.TableAdapterManager.UpdateAll(Me.TableDependents)
  MessageBox.Show("Update successful")

 Catch ex As Exception
  MessageBox.Show("Update failed: " + ex.Message.ToString())

 End Try

End Sub

To compile and run the application, follow the instructions in section "Compiling and Running the Application".

You can test the new application in the following manner. The floppy disk icon represents the Save command.

To test the application:

Change the DEPENDENT_ID value for Mary Ernst to 1110 and click the Save icon. The message box Update successful should appear. Click OK to dismiss the message box.

Change the EMPLOYEE_ID value for David Ernst to 99999 and click the Save icon. The following message should appear: Update failed: ORA-02291: integrity constraint (HR.EMPLOYEES_FK) violated - parent key not found. Click OK to dismiss the message box.

6 Using PL/SQL Stored Procedures and REF CURSORs

This chapter contains:

Introduction to PL/SQL Stored Procedures

Introduction to PL/SQL Packages and Package Bodies

Introduction to REF CURSORs

Creating a PL/SQL Stored Procedure that Uses REF CURSORs

Modifying an ODP.NET Application to Run Stored Procedures

Running a PL/SQL Stored Procedure Using an ODP.NET Application

Introduction to PL/SQL Stored Procedures

A stored procedure is a named set of PL/SQL statements designed to perform an action. Stored procedures are stored inside the database. They define a programming interface for the database rather than allowing the client application to interact with database objects directly. Stored procedures are typically used for data validation or to encapsulate large, complex processing instructions that combine several SQL queries.

Stored functions have a single return value parameter. Unlike functions, procedures may or may not return values.

Introduction to PL/SQL Packages and Package Bodies

A PL/SQL package stores related items as a single logical entity. A package is composed of two distinct pieces:

The package specification defines what is contained in the package; it is analogous to a header file in a language such as C++. The specification defines all public items. The specification is the published interface to a package.

The package body contains the code for the procedures and functions defined in the specification, and the code for private procedures and functions that are not declared in the specification. This private code is only visible within the package body.

The package specification and body are stored as separate objects in the data dictionary and can be seen in the user_source view. The specification is stored as the PACKAGE type, and the body is stored as the PACKAGE BODY type.

While it is possible to have a specification without a body, as when declaring a set of public constants, it is not possible to have a body with no specification.

Introduction to REF CURSORs

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs are represented through the OracleRefCursor ODP.NET class.

REF CURSORs have the following characteristics:

A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.

A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.

A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.

A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.

A REF CURSOR is a PL/SQL data type. You create and return a REF CURSOR inside a PL/SQL code block.

Creating a PL/SQL Stored Procedure that Uses REF CURSORs

This section demonstrates how to create a PL/SQL stored procedure.

To create a stored procedure:

Open Server Explorer and double-click HR to open the connection to the HR schema created in "Connecting to the Oracle Database".

Description of connect_se.gif follows
Description of the illustration connect_se.gif

If you have not previously saved the password, the Oracle Server Login opens and you can enter the password now. If you have saved the password, then the connection expands immediately.

In Server Explorer, right-click Packages and select New Package.

Description of procedure1.gif follows
Description of the illustration procedure1.gif

The New Package window appears.

In the New Package window, change the Package Name to HR_DATA.

Under the Methods area, click Add.

Description of procedure2.gif follows
Description of the illustration procedure2.gif

The Add Method window appears.

In the Add Method window, enter Method Name GETCURSORS, and change Method Type to Procedure.

Under Parameters, click Add.

This starts the process of adding parameters.

Under the Parameter Details group on the right, enter these three parameters. Click Add before each parameter that you need to add.

Name: DEP_ID, Direction: select IN , Data Type: select NUMBER.

Name: EMPLOYEES_C, Direction: select OUT, Data Type: select SYS_REFCURSOR.

Name: DEPENDENTS_C , Direction: OUT, Data Type: select SYS_REFCURSOR.

Description of procedure3.gif follows
Description of the illustration procedure3.gif

Click OK when you finish adding parameters.

The New Package window reappears.

In the New Package window, click Preview SQL to see the SQL code created.

A Preview SQL window appears, containing code similar to the following. Note that this code has been abbreviated by removing most of the comments.

CREATE PACKAGE "HR"."HR_DATA" IS  -- Declare types, variables, constants, exceptions, cursors,
  -- and subprograms that can be referenced from outside the package.

  PROCEDURE "GETCURSORS" (
    "DEP_ID" IN NUMBER,
    "EMPLOYEES_C" OUT SYS_REFCURSOR,
    "DEPENDENTS_C" OUT SYS_REFCURSOR);

END "HR_DATA";

CREATE PACKAGE BODY "HR"."HR_DATA" IS

  -- Implement subprograms, initialize variables declared in package
  -- specification.

  -- Make private declarations of types and items, that are not accessible
  -- outside the package
   PROCEDURE "GETCURSORS" (
    "DEP_ID" IN NUMBER,
    "EMPLOYEES_C" OUT SYS_REFCURSOR,
    "DEPENDENTS_C" OUT SYS_REFCURSOR) IS

  -- Declare constants and variables in this section.

     BEGIN -- executable part starts here

     NULL;

  -- EXCEPTION -- exception-handling part starts here

    END "GETCURSORS";

END "HR_DATA";

Click OK to close the Preview SQL window.

In the New Package window, click OK to save the new package.

The new package, HR_DATA, now appears in the Server Explorer.

In the Server Explorer, right-click the package HR_DATA, and select Edit Package Body.

Description of procedure4.gif follows
Description of the illustration procedure4.gif

The code for the package appears.

Scroll down to the body of the GETCURSORS procedure, and after BEGIN, replace the line NULL; with the following code:

OPEN EMPLOYEES_C FOR SELECT * FROM EMPLOYEES
   WHERE DEP_ID=DEPARTMENT_ID;
OPEN DEPENDENTS_C FOR SELECT * FROM DEPENDENTS;

Save the changes to the package.

To run the stored procedure, in Server Explorer, expand the HR_DATA package.

Right-click the GETCURSORS method, and select Run.

Description of procedure5.gif follows
Description of the illustration procedure5.gif

The Run Procedure window appears.

In the Run Procedure window, enter a Value of 60 for dep_id.

Description of procedure6.gif follows
Description of the illustration procedure6.gif

Click OK.

The Output window appears, showing that the run was successful.

In the result window, the following message appears:

Procedure <HR.HR_DATA.GETCURSORS@hr.database> was run successfully.

Under this message, note two output parameters (together with DEP_ID): EMPLOYEES_C and DEPENDENTS_C.

Select the Value column entry for EMPLOYEES_C.

The Parameter Details area appears, showing the employees in department 60. The value for DEP_ID is 60.

Description of procedure7.gif follows
Description of the illustration procedure7.gif

Select the Value column entry for DEPENDENTS_C.

The Parameter Details area appears, showing the value of the DEPENDENTS_C.

Description of procedure8.gif follows
Description of the illustration procedure8.gif

Modifying an ODP.NET Application to Run Stored Procedures

This section demonstrates how to modify your Oracle Data Provider for .NET application to run a PL/SQL stored procedure, using the GETCURSORS stored procedure as a sample.

To modify your application to run a stored procedure:

Open the application HR_Connect_CS or HR_Connect_VB.

Make a copy of Form3.xx, which you finished at the end of Chapter 4 and name it Form4.xx, following the instructions in Appendix B, "Copying a Form".

With Form1 selected, switch to code view.

In the try block of the connect_Click() method, replace the two command assignment lines, starting with cmd = New OracleCommand... with the code indicated.

Visual C#:

cmd = new OracleCommand("HR_DATA.GETCURSORS", conn);
cmd.CommandType = CommandType.StoredProcedure;

Visual Basic:

cmd = new OracleCommand("HR_DATA.GETCURSORS", conn)
cmd.CommandType = CommandType.StoredProcedure

Under the code added in Step 3, add definitions and bindings for the three parameters of the GETCURSORS stored procedure as OracleParameter objects, calling them dep_id, employees_c and dependents_c.

Visual C#:

OracleParameter dep_id = new OracleParameter();
dep_id.OracleDbType = OracleDbType.Decimal;
dep_id.Direction = ParameterDirection.Input;
dep_id.Value = 60;
cmd.Parameters.Add(dep_id);

OracleParameter employees_c = new OracleParameter();
employees_c.OracleDbType = OracleDbType.RefCursor;
employees_c.Direction = ParameterDirection.Output;
cmd.Parameters.Add(employees_c);

OracleParameter dependents_c = new OracleParameter();
dependents_c.OracleDbType = OracleDbType.RefCursor;
dependents_c.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dependents_c);

Visual Basic:

Dim dep_id As OracleParameter = New OracleParameter
dep_id.OracleDbType = OracleDbType.Decimal
dep_id.Direction = ParameterDirection.Input
dep_id.Value = 60
cmd.Parameters.Add(dep_id)

Dim employees_c As OracleParameter = New OracleParameter
employees_c.OracleDbType = OracleDbType.RefCursor
employees_c.Direction = ParameterDirection.Output
cmd.Parameters.Add(employees_c)

Dim dependents_c As OracleParameter = New OracleParameter
dependents_c.OracleDbType = OracleDbType.RefCursor
dependents_c.Direction = ParameterDirection.Output
cmd.Parameters.Add(dependents_c)

Build the application.

Running a PL/SQL Stored Procedure Using an ODP.NET Application

This section demonstrates how to run a PL/SQL stored procedure, such as the GETCURSORS stored procedure, from your ODP application.

To run a stored procedure:

Run the application.

A Form1 window appears.

In the Form1 window, enter the connection information, and click Connect.

In the DataGrid object, scroll horizontally to verify that the values in the last column, DEPARTMENT_ID are only 60.

Note that the DataGrid contains the first result set from the stored procedure, which matches the query of the EMPLOYEES table.

Description of procedure9.gif follows
Description of the illustration procedure9.gif

Close the application.

7 Using ASP.NET with Oracle Database

This chapter contains:

Overview: Building an ASP.NET Application with Oracle Developer Tools

Before Beginning This Tutorial

Creating a Web Site and Connecting it to the Database

Enabling a Web Site for Authentication

Enabling Oracle Providers for ASP.NET and Creating a Lightweight Web User

Testing Web Site Authentication

Overview: Building an ASP.NET Application with Oracle Developer Tools

Oracle integrates directly with Microsoft ASP.NET in a number of ways:

Oracle Developer Tools for Visual Studio provides an easy way to design data-driven web sites.

ODP.NET enables ASP.NET data access.

Oracle Providers for ASP.NET integrate directly with Microsoft ASP.NET controls and services to provide state management capabilities for web sites.

This tutorial demonstrates some of these features, including how to build a data-driven web application using Oracle Developer Tools and how to add security to that application in a simple manner using Oracle Providers for ASP.NET.

First, you will use the tools to build a web application that retrieves employee data from the Oracle database into a data grid. Then, you add a login control and secure the application by permitting only authorized web users access to this employee information. Finally, you create authorized web users using Oracle Providers for ASP.NET. These web users are stored within the Oracle database for authentication by this application.

Before Beginning This Tutorial

Before you can build an ASP.NET application with Oracle Developer Tools, you may need to perform the setups described in the following sections:

"Connecting to the Oracle Database".

If you are planning to use web site authentication, as described in "Enabling a Web Site for Authentication", you must perform the following setups:.

"Creating the User and Granting Privileges"

"Configuring All Oracle Providers for ASP.NET"

"Setting the Connection String"

Creating a Web Site and Connecting it to the Database

This section shows you how to create an ASP.NET web site that retrieves data from an Oracle database. The web site will display data in an ASP.NET GridView, allowing users to page through the results.

This section contains two topics:

Creating an ASP.NET Web Site

Creating a Data Source

Creating an ASP.NET Web Site

To create an ASP.NET web site with a grid:

Start Visual Studio.

From the File menu, select New, then Web Site...

Description of aspnt01.gif follows
Description of the illustration aspnt01.gif

From New Web Site, select ASP.NET Web Site and enter or browse to the directory location for the web site. Click OK.

Description of aspnt02.gif follows
Description of the illustration aspnt02.gif

In the Default.aspx tab, click the Design icon at the bottom of the screen.

Description of aspnt03.gif follows
Description of the illustration aspnt03.gif

In the <div> element, which appears as a dotted rectangle, enter a title, such as Employees List.

Description of aspnt04.gif follows
Description of the illustration aspnt04.gif

From the View menu, select Toolbox.

Description of aspnt05.gif follows
Description of the illustration aspnt05.gif

Expand the Data group and drag the GridView control into the dotted rectangle labeled <div> of the Designer

Description of aspnt06.gif follows
Description of the illustration aspnt06.gif

When a grid with dummy titles and contents appears, view the GridView Tasks list on the right.

Description of aspnt08.gif follows
Description of the illustration aspnt08.gif

If you do not see the task list, select the grid, and then click the > symbol on the right.

Description of asnpt07.gif follows
Description of the illustration asnpt07.gif

Creating a Data Source

To Create a Data Source:

Under the GridView Tasks, select <New data source...> from the Choose Data Source list, as shown in step 8 in the previous section.

A Data Source Configuration wizard starts.

Select Database. Enter HR as the ID for the data source. Click OK.

Description of aspnt09.gif follows
Description of the illustration aspnt09.gif

Click the down arrow to choose HR.ORCL from the list. Click Next.

Description of aspnt10.gif follows
Description of the illustration aspnt10.gif

Click Next to save the connection string in the application configuration file.

Description of aspnt11.gif follows
Description of the illustration aspnt11.gif

Select the EMPLOYEES table from the Name list. In the Columns list, check the box next to the asterisk ( *).

These selections tell Oracle to return all the rows from the EMPLOYEES table, just as if you had typed SELECT * FROM EMPLOYEES.

Click Next.

Description of aspnt12.gif follows
Description of the illustration aspnt12.gif

Click Test Query.

Description of aspnt13.gif follows
Description of the illustration aspnt13.gif

Click Finish when the Test Query results appear.

Description of aspnt14.gif follows
Description of the illustration aspnt14.gif

From the GridView Tasks, select Enable Paging. If you do not see the task list, select the grid, and then click the > symbol on the right. You may have to scroll right.

Description of aspnt15.gif follows
Description of the illustration aspnt15.gif

From the View menu, select Solution Explorer, and then select the web site. Right-click and select Build Web Site. The status bar will indicate success or failure.

From the View menu, select Debug, then Start Without Debugging.

A browser window, such as the following, appears, showing the data requested by the query. You can page through the results using the numbers at the lower left section of the page.

Description of aspnt16.gif follows
Description of the illustration aspnt16.gif

Close the browser.

Enabling a Web Site for Authentication

This section shows you how to add web site authentication to limit the users that can access the employee data. We will authenticate using an ASP.NET login control, verifying against users created and stored with the Oracle Providers for ASP.NET.

In the section following this, "Enabling Oracle Providers for ASP.NET and Creating a Lightweight Web User", you will take the ASP.NET application you have just built and secure the employee data so that an authorized user can access the information. You will create a web user for the application using Oracle Providers for ASP.NET. This web user starts the web application through the login control, and if the credentials are correct, can then access the employees information.

Reopen the web site you created in the previous section.

Select View, then Solution Explorer, and click the web site.

Description of aspnt17.gif follows
Description of the illustration aspnt17.gif

Right-click on the web site and Add New Item.

Description of aspnt18.gif follows
Description of the illustration aspnt18.gif

Select Web Form, enter the name login.aspx and click Add.

Description of aspnt19.gif follows
Description of the illustration aspnt19.gif

When the login.aspx page appears, switch to the Design tab.

From the View menu, open the Toolbox, expand the Login section, and drag and drop the login control onto the form, into the dotted rectangle labeled <div>.

Description of aspnt20.gif follows
Description of the illustration aspnt20.gif

This is a standard ASP.NET login control, which can retrieve and verify user login credentials stored in the Oracle database.

Right-click the login control and select Properties. For DestinationPageUrl, select or enter Default.aspx.

When a user successfully logs in, that user will be directed to the Default.aspx page, which contains the employee data. If a user does not successfully log in, they will be redirected back to the login page.

Description of aspnt21.gif follows
Description of the illustration aspnt21.gif

Enabling Oracle Providers for ASP.NET and Creating a Lightweight Web User

In this section, you will use the ASP.NET Web Site Administration Tool to do the following:

Direct the web site to use the Oracle ASP.NET providers.

Create a new web user specific to this web site, demonstrating the authentication features added to the site in "Enabling a Web Site for Authentication".

To direct the web site to use the Oracle ASP.NET providers and create new web site users, do the following:

In Visual Studio, select Website, then ASP.NET Configuration.

Description of aspnt23.gif follows
Description of the illustration aspnt23.gif

When the ASP.NET Web Site Administration Tool appears, select the Provider tab.

Description of aspnt24.gif follows
Description of the illustration aspnt24.gif

On the Provider page, select the second link: Select a different provider for each feature (advanced).

Description of aspnt25.gif follows
Description of the illustration aspnt25.gif

When the Provider page reappears, change the Membership Provider and Role Provider to the Oracle versions, if they are not selected.

Description of aspnt26.gif follows
Description of the illustration aspnt26.gif

Go to the Security tab and under Users, click Select authentication type.

By default, the ASP.NET site uses Windows authentication to identify users. You are building a web site that will identify users by their site-specific logins and passwords. Therefore, the site must be configured to expect to use logins and passwords.

Description of aspnt27.gif follows
Description of the illustration aspnt27.gif

When the Security page reappears, choose From the internet and click Done.

Description of aspnt28.gif follows
Description of the illustration aspnt28.gif

When the security tab reappears with new links under Users, choose Create user.

Description of aspnt29.gif follows
Description of the illustration aspnt29.gif

In the Create User section, enter the information for the user that you are allowing to enter the web site, as shown. Enter a password that contains at least 7 characters, including one non-alphanumeric character.

Click Create User.

Description of aspnt30.gif follows
Description of the illustration aspnt30.gif

When the Security page reappears, indicating that your account has been successfully completed, click the Security tab.

Description of aspnt31.gif follows
Description of the illustration aspnt31.gif

Note: Other options for this screen include continuing to create other users or going to a different tab.

When the main Security page reappears, under Access Rules, select Manage access rules.

Description of aspnt31a.gif follows
Description of the illustration aspnt31a.gif

NOTE: Under Users, there is now one existing user.

When the Manage Access Rules section on the Security tab appears, click on Add new access rule.

Description of aspnt32.gif follows
Description of the illustration aspnt32.gif

Select Anonymous users and Deny, then click OK.

By default, anonymous access to the web site is enabled. The above settings secure the web site by disabling anonymous access. Now, only authenticated users can view the employee data.

Description of aspnt33.gif follows
Description of the illustration aspnt33.gif

The Security page now indicates that the web site denies anonymous users access to the site.

Click Done.

Description of aspnt34.gif follows
Description of the illustration aspnt34.gif

Close the browser.

Testing Web Site Authentication

Now that you have created a web user specific to this web site, the web site will allow this user access to the employee data and deny access to all other users, including anonymous users.

In this section, you will attempt to access the employee data as an anonymous user, as an unauthorized user, as an authorized user with an incorrect password, and finally as an authorized user with the correct password. Only in the last scenario will the web site grant access to the employee data.

Note:

Five or more consecutive invalid passwords entered for an ASP.NET provider user within a ten minute period will lock the account to prevent unauthorized users from gaining access through password guessing. Oracle Membership Provider sets these security measures through the following properties, which you can modify in the machine.config file or web.config files: MaxInvalidPasswordAttempts (default: 5 attempts) and PasswordAttemptWindow (default: 10 minutes).

If the account is locked, then you can unlock the user by calling the UnlockUser method.

From the Debug menu, select Start Without Debugging, and when the login web page appears, change the URL to end with Default.aspx rather than login.aspx and press the enter key.

Description of aspnt39.gif follows
Description of the illustration aspnt39.gif

You are denied access and redirected back to the login page. This shows that anonymous users cannot browse the web site; only users with credentials have access.

Description of aspnt40.gif follows
Description of the illustration aspnt40.gif

If you are experimenting with the authentication mechanism, you will likely repeat this step or try variations. For each variation, either start a new browser or clear the browser cache. Because browsers cache web pages, if you access Default.aspx again, you may see the cached version of this web page. This is not the intended behavior, rather the web page should undergo the ASP.NET provider authentication process, which is accomplished by using a new browser instance or clearing the browser cache.

Remove the text of the URL after login.aspx. This returns the URL back to the original state when you first accessed the site.

Enter the User Name Bob and a password that contains at least 7 characters, one of which is non-alphanumeric.

Click Log In.

Description of aspnt35.gif follows
Description of the illustration aspnt35.gif

The page reappears with the message "Your login attempt was not successful. Please try again."

Bob is not an authorized user. The web site correctly denies access to the user.

Enter the user name Anne, but with an incorrect password for that web site user.

Click Log In.

Description of aspnt38.gif follows
Description of the illustration aspnt38.gif

As the screen shot indicates, the user is denied access, demonstrating that the control could not verify this user's credentials with those stored by the Oracle Membership Provider.

Enter the correct password for the web site user.

Click Log In.

The employee data appears. This demonstrates that only authorized users can access the data. Thus, Oracle Providers for ASP.NET provided web site security in a very simple manner.

You have now built a data-driven ASP.NET web application. It performs authentication and retrieves employee data from the database.

8 Developing and Deploying .NET Stored Procedures

This chapter contains:

Overview of .NET Stored Procedures

Starting the Common Language Runtime Service

Creating a Connection as SYSDBA

Creating an Oracle Project

Creating .NET Stored Functions and Procedures

Deploying .NET Stored Functions and Procedures

Running .NET Stored Functions and Procedures

Running .NET Stored Procedure in a Query Window

Overview of .NET Stored Procedures

.NET stored procedures are methods or procedures written in a .NET language which contains SQL or PL/SQL statements.

You can write custom stored procedures and functions using any .NET compliant language, such as C# and VB.NET, and use these .NET stored procedures in the database, in the same manner as other PL/SQL or Java stored procedures. .NET stored procedures can be called from PL/SQL packages, procedures, functions, and triggers; from SQL statements, or from anywhere a PL/SQL procedure or function can be called.

Oracle Database Extensions for .NET (a database option that allows you to write .NET stored procedures) must be installed and configured in the database to run the examples in this chapter.

This chapter discusses how to use and deploy .NET stored procedures in your application.

Starting the Common Language Runtime Service

To use .NET stored procedures, you must first start the common language runtime agent, represented by the OraClrAgent service. This service may not start by default. Note that it is located on the Oracle database, not on the client.

Note:

OraClrAgnt can be accessed through the Services Control Panel, as OracleOracleHomeNameClrAgnt, where OracleHomeName represents your Oracle home.

To start the common language runtime service:

From the Start menu, select All Programs, then select Administrative Tools, and finally, select Services.

In the Services window, click the Extended tab.

Scroll down the list of Services, and select OracleOracleHomeNameClrAgnt.

Click the Start hyperlink.

The Service Control window shows that the OracleClrAgent is starting.

When the Service Control window closes, note that the status of the OracleClrAgent is changed to Started.

Creating a Connection as SYSDBA

Next, you must create a database connection as SYSDBA which enables you to deploy your Oracle Project.

Note:

You must have administrative privileges as SYSDBA to perform this task.

Note:

To use the Enterprise Manager to set the sys account password, see About Administrative Accounts and Privileges in the Oracle Database 2 Day DBA.

To create a database connection in ODT:

From the View menu, select Server Explorer.

In Server Explorer, right-click Data Connections.

Select Add Connection.

When the Add Connection window appears, determine if the Data source says Oracle Database (Oracle ODP.NET).

If it does, skip to Step 6.

Description of addconnection1a.gif follows
Description of the illustration addconnection1a.gif

If Data source does not say Oracle Database (Oracle ODP.NET), select Change.

The Change Data Source window appears.

Description of addconnection2a.gif follows
Description of the illustration addconnection2a.gif

Choose Oracle Database and then select Oracle Data Provider for .NET.

In the Add Connection window, use the following:

For User name, enter sys.

For Password, enter the password set by the administrator who unlocked and set up the sys account.

To use the Enterprise Manager to set the sys account password, see About Administrative Accounts and Privileges in the Oracle Database 2 Day DBA.

Ensure that the Role is set to Sysdba.

The Connection name is generated automatically from the Data source name and the User name values.

Description of deploy03.gif follows
Description of the illustration deploy03.gif

In the Add Connection window, click OK

The Server Explorer window should now contain the SYS.ORCL connection.

Creating an Oracle Project

To use stored procedures in .NET, you must first create a new Oracle Project to hold the stored procedures.

To create a project for .NET stored procedures:

From the File menu, select New, and then select Project.

A New Project dialog box appears.

In Project Types, select the type of project you are creating:

Visual C#:

Visual C# , then select Database, and under Templates:Oracle Project

Enter Name: HR_DeployStored_CS.

Visual Basic:

Other Languages, then select Visual Basic and Database, then under Templates: Oracle Project

Enter Name: HR_DeployStored_VB.

Enter Location: C:\HR_Projects.

Click OK.

Description of deploy01.gif follows
Description of the illustration deploy01.gif

Creating .NET Stored Functions and Procedures

You are now ready to create a .NET stored procedure.

To create a .NET stored procedure:

In Solution View, select the Class1.cs or Class1.vb tab in your project.

Add these namespace directives for the specific language, as described in "Adding Namespace Directives".

Visual C#:

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

Visual Basic:

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Add Reference to Oracle.DataAccess.dll as described in "Adding a Reference".

Copy the getDepartmentno() method into the Class1 declaration, as indicated

Visual C#

public static int getDepartmentno(int employee_id)
{
  int department_id = 0;

  // Get a connection to the db
  OracleConnection conn = new OracleConnection();
  conn.ConnectionString = "context connection=true";
  conn.Open();

  // Create and execute a command
  OracleCommand cmd = conn.CreateCommand();
  cmd.CommandText = "select department_id from employees where employee_id = :1";
  cmd.Parameters.Add(":1", OracleDbType.Int32, employee_id,
    ParameterDirection.Input);
  OracleDataReader rdr = cmd.ExecuteReader();

  while(rdr.Read())
    department_id=rdr.GetInt32(0);

  rdr.Close();
  cmd.Dispose();

  // Return the employee's department number
  return department_id;
}

Visual Basic:

Public Shared Function getDepartmentno(ByVal employee_id As Integer) As Integer
  Dim department_id As Integer = 0

  ' Get a connection to the db
  Dim conn As OracleConnection = New OracleConnection()
  conn.ConnectionString = "context connection=true"
  conn.Open()

  ' Create and execute a command
  Dim cmd As OracleCommand = conn.CreateCommand()
  cmd.CommandText = "select department_id from employees where employee_id = :1"
  cmd.Parameters.Add(":1", OracleDbType.Int32, employee_id,     ParameterDirection.Input)
  Dim rdr As OracleDataReader = cmd.ExecuteReader()

  While rdr.Read()
    department_id = rdr.GetInt32(0)

  End While

  rdr.Close()
  cmd.Dispose()

  ' Return the employee's department number
  Return department_id

End Function

Description of deploy04.gif follows
Description of the illustration deploy04.gif

Save Class1.

From the Build menu, select Build Solution.

Description of deploy05.gif follows
Description of the illustration deploy05.gif

Check that the Output window indicates a successful build and close it.

Deploying .NET Stored Functions and Procedures

You can now deploy the .NET stored procedure that you created "Creating .NET Stored Functions and Procedures".

To deploy a .NET stored procedure:

From the Build menu, select Deploy Solution.

Description of deploy06.gif follows
Description of the illustration deploy06.gif

An Oracle Deployment Wizard for .NET window appears.

In the Oracle Deployment Wizard for .NET window, click Next.

Description of deploy07.gif follows
Description of the illustration deploy07.gif

On the Configure your OracleConnection window, click Next.

Description of deploy08.gif follows
Description of the illustration deploy08.gif

On the Specify your deployment option window, ensure that the first option, Copy assembly and generate stored procedures is selected, and click Next.

Description of deploy10.gif follows
Description of the illustration deploy10.gif

On the Specify an assembly and library name window, accept the defaults and click Next.

Description of deploy11.gif follows
Description of the illustration deploy11.gif

On the Specify copy options window, accept the defaults and click Next.

Visual Basic:

If you are using Visual Basic, the Microsoft.VisualBasic assembly also appears as a referenced assembly.

Description of deploy12.gif follows
Description of the illustration deploy12.gif

On the Specify methods and security details window, under Available methods, expand HR_DeployStored_CS or HR_DeployStored_VB, then expand Class1, and select the getDepartmentno() method.

Under Method Details, select HR from the Schema list.

Click Next.

Description of deploy13.gif follows
Description of the illustration deploy13.gif

On the Summary window, click Finish.

Description of deploy14.gif follows
Description of the illustration deploy14.gif

Running .NET Stored Functions and Procedures

You are now ready to run the .NET stored procedure you deployed earlier.

To run a .NET stored procedure:

In Server Explorer, open and expand the HR.ORCL connection. Expand Functions. Right-click GETDEPARTMENTNO and select Run.

Description of deploy15.gif follows
Description of the illustration deploy15.gif

The Run Function window appears.

In the Run Function window, enter a Value of 100 for EMPLOYEE_ID.

Click OK.

Description of deploy16.gif follows
Description of the illustration deploy16.gif

Note that the return value for department is 90, indicating that EMPLOYEE_ID 100 is in department 90.

Description of deploy17.gif follows
Description of the illustration deploy17.gif

Running .NET Stored Procedure in a Query Window

You can run the .NET stored procedure that you have just created using the ODT Query Window, in addition to running it from Server Explorer.

Open the Server Explorer in the HR.ORCL schema.

Expand Functions and select GETDEPARTMENTNO.

Right-click and select Query Window.

Enter Select getdepartmentno(100) from dual.

Click Execute from the toolbar.

Description of querywindow.gif follows
Description of the illustration querywindow.gif

9 Including Globalization Support

This chapter contains:

Introduction to Global Applications

Developing Global Applications with the .NET Framework

Presenting Data in the Correct User Local Convention

Synchronizing the .NET and Oracle Database Locale Environments

Client Globalization Support in Oracle Data Provider for .NET

See Also:

Chapter 8, "Oracle Data Provider for .NET Globalization Classes" in Oracle Data Provider for .NET Developer's Guide

"Working in a Global Environment" in the Oracle Database 2 Day Developer's Guide

Microsoft .NET Internationalization Internet site, http://msdn.microsoft.com/en-us/goglobal/bb688096.aspx

Introduction to Global Applications

This chapter discusses global application development with Oracle Database in .NET. It addresses the basic tasks associated with developing applications that are ready for global deployment, such as developing locale awareness and presenting data with cultural conventions of the user's locale. It also discusses globalization support features available in Oracle Data Provider for .NET.

Building a global-ready application that supports different locales requires good development practices.

A locale refers to a national language and the region in which the language is spoken. The application itself must be aware of the user's locale preference and be able to present content following the cultural convention expected by the user. It is important to present data with appropriate locale characteristics, such as the correct date and number formats. Oracle Database is fully internationalized to provide a global platform for developing and deploying global applications.

Developing Global Applications with the .NET Framework

When planning a global-ready application, you have to consider two main tasks:

Globalization is the process of designing applications that can adapt to different cultures.

Localization is the process of translating resources for a specific culture.

In the .NET Framework, the System.Globalization namespace contains classes that define information related to culture, such as language, country and region, calendars, format patterns for dates, currency, and numbers, and the sort order for strings. These classes simplify the process of developing a global-ready application, so that passing a CultureInfo object that represents the user's culture to methods in System.Globalization namespace initiates the correct set of rules and data.

The .NET Framework also supports the creation and localization of resources, and offers a model for packaging and deploying them. Localizing the application's resources for specific cultures supports development of translated versions of the application. The .NET Framework base class library provides several classes in the System.Resources namespace for building and manipulating application resources.

Presenting Data in the Correct User Local Convention

Data in the application must be presented in a way that meets the user's expectations, or its meaning can be misinterpreted. For example, 12/11/05 implies December 11, 2005 in the United States and November 12, 2005 in the United Kingdom. Similar confusion exists for number and monetary formats. For example, the period (.) is a decimal separator in the United States and a thousand separator throughout Europe.

Different languages have their own sorting rules: some languages are collated according to the letter sequence in the alphabet, others according to stroke count in the letter, still others are ordered by the pronunciation of the words. Presenting data that is not sorted according to the linguistic sequence that the user is accustomed to can make searching for information difficult and time-consuming.

Depending on the application logic and the volume of data retrieved from the database, it may be more appropriate to format the data at the database level rather than at the application level. Oracle Database offers many features that refine the presentation of data when the user locale preference is known.

Connecting to SQL*Plus

Several of the following examples require that you use SQL*Plus to connect as a user with database administrator privileges such as SYS or SYSTEM.

See Also:

"Locking and Unlocking User Accounts" in the Oracle Database 2 Day DBA for further information

Using Oracle Date Formats

There are three different date presentation formats in Oracle Database: standard, short, and long. The following steps illustrate the difference between the short and long date formats for United States and Germany.

To change the Oracle date format:

From a Windows command prompt, enter the following

  C:\>sqlplus "sys as sysdba"
Enter password:passwd

where passwd is the Sys password that was established when the database was installed. The password does not appear when you type the characters. Description of nls00.gif follows
Description of the illustration nls00.gif

Enter this command at the SQL prompt:

SQL> ALTER SESSION SET NLS_TERRITORY=america NLS_LANGUAGE=american;

This message appears: Session altered.

There is no problem with setting a parameter to its current setting. You may want to do this for security. To determine what your current settings are enter:

SQL> select * from v$nls_parameters;

or

select * from v$nls_parameters where parameter = 'NLS_LANGUAGE';

At the SQL prompt, enter the following query:

SQL> SELECT employee_id "ID",
  SUBSTR (first_name,1,1)||'. '||last_name "Name",
  TO_CHAR (hire_date, 'DS') "Short Hire",
  TO_CHAR (hire_date, 'DL') "Long Hire Date"
  FROM hr.employees
  WHERE employee_id < 105;

Note that you must use hr.employees in order to access the employees table in the hr schema because you are currently logged in as sys, not hr.

The result of the query returns in the American format specified in Step 1.

Description of nls01.gif follows
Description of the illustration nls01.gif

Enter the following command at the SQL prompt:

SQL> ALTER SESSION SET NLS_TERRITORY=germany NLS_LANGUAGE=german;

This message appears: Session altered.

At the SQL prompt, enter the query from Step 3.

The result of the query returns in the German format specified in Step 4.

Description of nls02.gif follows
Description of the illustration nls02.gif

Using Oracle Number Formats

There are also differences in the decimal character and group separator. The following steps illustrate these difference between United States and Germany.

To change the Oracle number format:

Enter the following command at the SQL prompt:

SQL> ALTER SESSION SET NLS_TERRITORY=america NLS_LANGUAGE=american;

This message appears: Session altered.

At the SQL prompt, enter the following query:

SQL> SELECT employee_id "ID",
  SUBSTR (first_name,1,1)||'. '||last_name "Name",
  TO_CHAR (salary, '99G999D99') "Salary"
  FROM hr.employees
  WHERE employee_id < 105;

The result of the query returns in the American format specified in Step 1.

Description of nls03.gif follows
Description of the illustration nls03.gif

Enter the following command at the SQL prompt:

SQL> ALTER SESSION SET NLS_TERRITORY=germany;

This message appears: Session altered.

At the SQL prompt, enter the query in Step 2.

The result of the query returns in the German format specified in Step 3.

Description of nls04.gif follows
Description of the illustration nls04.gif

Using Oracle Linguistic Sorts

Spain traditionally treats ch, ll, and ñ as letters of their own, ordered after c, l and n, respectively. The following steps illustrate the effect of using a Spanish sort against the employee names Chen, Chung, and Colmenares.

To change the Oracle linguistic sort:

Enter the following command at the SQL prompt.

SQL> ALTER SESSION SET NLS_SORT=binary;

This message appears: Session altered.

At the SQL prompt, enter the following query:

SQL> SELECT employee_id "ID",
          last_name "Name"
  FROM hr.employees
  WHERE last_name LIKE 'C%'
  ORDER BY last_name;

The result of the query returns in the binary sort specified in Step 1.

Description of nls05.gif follows
Description of the illustration nls05.gif

Enter the following command at the SQL prompt.

SQL> ALTER SESSION SET NLS_SORT=spanish_m;

This message appears: Session altered.

At the SQL prompt, enter the query in Step 2.

The result of the query returns in the Spanish sort specified in Step 3.

Description of nls06.gif follows
Description of the illustration nls06.gif

Oracle Error Messages

The NLS_LANGUAGE parameter also controls the language of the database error messages. Setting this parameter prior to submitting a SQL query ensures the return of local language-specific error messages, as shown in these steps:

To change the Oracle NLS language parameter:

Enter the following command at the SQL prompt.

SQL> ALTER SESSION SET NLS_LANGUAGE=american;

This message appears: Session altered.

At the SQL prompt, enter the following query.

SQL> SELECT * FROM managers;

The result of the query return the error message in the language specified in Step 1.

Description of nls07.gif follows
Description of the illustration nls07.gif

Enter the following command at the SQL prompt.

SQL> ALTER SESSION SET NLS_LANGUAGE=french;

This message appears: Session altered.

At the SQL prompt, enter the query in Step 2.

The result of the query returns the error message in the language specified in Step 3.

Description of nls08.gif follows
Description of the illustration nls08.gif

Reset your language, local, and sort settings back to their original values.

Synchronizing the .NET and Oracle Database Locale Environments

When you are developing global applications, always synchronize the user locale settings between the database and clients. Otherwise, the application may present conflicting culture-sensitive information. For example, a .NET application must map the Culture ID of the application user to the correct NLS_LANGUAGE and NLS_TERRITORY parameter values before performing SQL operations.

Table 9-1 shows some of the more common locales, as defined in .NET and Oracle environments.

Table 9-1 Common NLS_LANGUAGE and NLS_TERRITORY Parameters

Culture
Culture ID
NLS_LANGUAGE
NLS_TERRITORY

Chinese (P.R.C.)

zh-CN

SIMPLIFIED CHINESE

CHINA

Chinese (Taiwan)

zh-TW

TRADITIONAL CHINESE

TAIWAN

English (U.S.A.)

en-US

AMERICAN

AMERICA

English (U.K.)

en-GB

ENGLISH

UNITED KINGDOM

French (Canada)

fr-CA

CANADIAN FRENCH

CANADA

French (France)

fr-FR

FRENCH

FRANCE

German

de

GERMAN

GERMANY

Italian

it

ITALIAN

ITALY

Japanese

ja

JAPANESE

JAPAN

Korean

ko

KOREAN

KOREA

Portuguese (Brazil)

pt-BR

BRAZILIAN PORTUGUESE

BRAZIL

Portuguese

pt

PORTUGUESE

PORTUGAL

Spanish

es

SPANISH

SPAIN

Client Globalization Support in Oracle Data Provider for .NET

Oracle Data Provider for .NET enables applications to manipulate culture-sensitive data, such as ensuring proper string format, date, time, monetary, numeric, sort order, and calendar support using culture conventions defined in the Oracle Database. The default globalization settings are determined by the client's NLS_LANG parameter, which is defined in the Windows Registry of the local computer. When the OracleConnection Open method establishes a connection, it implicitly opens a session with globalization parameters specified by the value of the NLS_LANG parameter.

Client Globalization Settings

The client globalization parameter settings are read-only and remain constant throughout the lifetime of the application. Changing the OracleGlobalization object properties does not change the globalization settings of the session or the thread. The following sections describe how to modify the globalization settings at the session and thread level.

Your .NET application can obtain globalization settings by calling the OracleGlobalization.GetClientInfo() static method. The OracleGlobalization sample code below demonstrates how to obtain some of the values in .NET.

Visual C#:

using System;
using Oracle.DataAccess.Client;

class ClientGlobalizationSample
{
  static void Main()
  {
    OracleGlobalization ClientGlob = OracleGlobalization.GetClientInfo();
    Console.WriteLine("Client machine language: " + ClientGlob.Language);
    Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet);
  }
}

Visual Basic:

Imports System
Imports Oracle.DataAccess.Client

Class ClientGlobalizationSample
  Shared Sub Main()
    Dim ClientGlob As OracleGlobalization = OracleGlobalization.GetClientInfo()
    Console.WriteLine("Client machine language: " + ClientGlob.Language)
    Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet)
  End Sub
End Class

Using Session Globalization Settings

Session globalization parameters are initially identical to client globalization settings, but they can be modified. To modify the session parameters, you must establish a connection to the database, and then call the GetSessionInfo() method of an OracleConnection object to retrieve the session globalization settings. Next, you modify the globalization settings as needed, then save the settings back to the OracleConnection object through the SetSessionInfo(OracleGlobalization) method.

To specify the globalization session setting:

Open the application HR_Connect_CS or HR_Connect_VB.

Make a copy of Form3.xx, which you finished at the end of Chapter 4 and name it Form5.xx, following the instructions in Appendix B, "Copying a Form".

Open Form1 of the project, and switch to design view.

From the View menu, select Toolbox.

From the Toolbox, under Windows Forms, drag and drop a Button onto Form1.

Right-click the new Button, select Properties. The Properties window appears.

In the Properties window, set these properties:

Under Appearance, change Text to Change Date Format.

Under Design, change (Name) to date_change.

Form1 should look much like this:

Description of nls09.gif follows
Description of the illustration nls09.gif

In the properties window, if you click Events (lightning bolt icon), date_change_Click() now shows as the Event for the date button.

Open the new date_change_Click() method just created and add the following code to change the date format from the standard DD-MON-RR to YYYY-MM-DD and to update the DataSet.

Visual C#:

si.DateFormat = "YYYY-MM-DD";
conn.SetSessionInfo(si);

ds.Clear();
da.Fill(ds);
departments.DataSource = ds.Tables[0];

Visual Basic:

si.DateFormat = "YYYY-MM-DD"
conn.SetSessionInfo(si)

ds.Clear()
da.Fill(ds)
departments.DataSource = ds.Tables(0)

Note that the ds.Clear() call will clear the old results before posting the changed data.

Also, the si class variable will be declared and session globalization information retrieved in Step 10 and Step 11.

Within the appropriate method, add the code indicated.

Visual C#: In the Form1() method

date_change.Enabled = false;

Visual Basic: In the Form1_Load method

date_change.Enabled = false

Description of nls10.gif follows
Description of the illustration nls10.gif

Add the following class variable to the existing Form1 class declarations right after the public Form1() block with this code as indicated.

Visual C#:

private OracleGlobalization si;

Visual Basic:

private si As OracleGlobalization

Description of nls11.gif follows
Description of the illustration nls11.gif

Within the connect_Click() method try block, add the indicated code which does the following:

Retrieve the value of the OracleGlobalization object.

Retrieve data from the EMPLOYEES table (note the new query).

Enable the Change Date Format button.

The changed code is in bold typeface.

Visual C#:

conn.Open();
connect.Enabled = false;

si = conn.GetSessionInfo();

string sql = "select employee_id, first_name, last_name, TO_CHAR(hire_date)" +
   " \"Hire Date\" from employees where employee_id < 105";
cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;

da = new OracleDataAdapter(cmd);
cb = new OracleCommandBuilder(da);
ds = new DataSet();

da.Fill(ds);

departments.DataSource = ds.Tables[0];

save.Enabled = true;
date_change.Enabled = true;

Visual Basic:

conn.Open()
connect.Enabled = false

si = conn.GetSessionInfo()

Dim sql As String = "select employee_id, first_name, last_name, " & _
    "TO_CHAR(hire_date) ""Hire Date"" from employees where employee_id < 105"
cmd = new OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text

da = new OracleDataAdapter(cmd)
cb = new OracleCommandBuilder(da)
ds = new DataSet()

da.Fill(ds)

departments.DataSource = ds.Tables[0]

save.Enabled = true
date_change.Enabled = true

Save Form1.

Run the application using the F5 keyboard shortcut.

The application successfully connects to the database so the data grid is populated with the results of the query.

Description of nls12.gif follows
Description of the illustration nls12.gif

Click Change Date Format.

Description of nls13.gif follows
Description of the illustration nls13.gif

Note that the date format changed from the original DD-MON-RR to YYYY-MM-DD.

Close the application.

Thread-Based Globalization Settings

Thread-based globalization parameter settings are specific to each thread. Initially, these settings are identical to the client globalization parameters, but they can be changed programmatically. When converting ODP.NET Types to and from strings, use the thread-based globalization parameters, if applicable.

Thread-based globalization parameter settings are obtained by calling the GetThreadInfo() static method of the OracleGlobalization class. A call to SetThreadInfo() static method sets the globalization settings of the thread.

ODP.NET classes and structures rely solely on the OracleGlobalization settings when manipulating culture-sensitive data. They do not use .NET thread culture information. If the application uses only .NET types, OracleGlobalization settings have no effect. However, when conversions are made between ODP.NET Types and .NET Types, OracleGlobalization settings are used where applicable.

Note:

Changes to the System.Threading.Thread. CurrentThread.CurrentCulture property do not impact the OracleGlobalization settings of the thread or the session. The reverse is also true.

A Starting and Stopping an Oracle Database Instance

You may need to frequently stop and restart the database.

To start an Oracle Database Instance:

From the Start button, select Programs, then Administrative Tools, then Services, and select OracleServiceDatabaseName where DatabaseName is the service_name of the database as indicated in the tnsnames.ora file. See "Configuring a NET Connect Alias" for further details.

In the left panel, click the link to Start the service.

The database services begin and the Start Database window appears. Do not proceed until you see this message: "OracleService service was started successfully".

To stop an Oracle Database Instance:

From the Start button, select Programs, then Administrative Tools, then Services, and select OracleServiceDatabaseName.

In the left panel, click the link to Stop the service.

The database begins to shut down, showing the Stop Database window. Do not proceed until you see this message: "OracleService service was stopped successfully".

B Copying a Form

Because you will be using this application to learn about various aspects of application development with Oracle, you should make copies of your form for reuse.

To create a copy of an existing form:

In the Solution Explorer, right-click on Form1.xx or any other file you need to copy. Select Copy.

If Form1.xx does not appear in the Solution Explorer, from the Project menu, select Show All Files.

Description of copy1.gif follows
Description of the illustration copy1.gif

Right-click HR_Connect_CS or other project. Select Paste.

Description of copy2.gif follows
Description of the illustration copy2.gif

Right-click Copy of Form1.cs. Select Rename. Change the name of the form to Form2.cs.

Description of copy3.gif follows
Description of the illustration copy3.gif

Right-click on Form2.cs, and select Include In Project.

Description of copy4.gif follows
Description of the illustration copy4.gif

Right-click on Form1.cs, and select Exclude From Project.

You can include and exclude forms from the project just by reversing these steps.

Note:

This process generally works smoothly. If you encounter a problem, try running Rebuild Solution from the Build menu.

Index

A  B  C  D  E  F  G  H  I  L  M  N  O  P  Q  R  S  T  U  V  W 

A

accounts

unlocking, 5.2, 8.3

Add() method, 4.3

adding references, 3.2

alias

database, 5.2

ALTER TABLE, 5.5

anonymous users

denying, 7.6

apply filters, 5.2, 8.3

ASP.NET Configuration, 7.5

ASP.NET tutorial, 7.2

ASP.NET user schema, 2.4.1

ASP.NET Web Sit Administration Tool, 7.5

ASPNET_DB_USER, 2.4.1

authentication

web site, 7.4

automatic naming, 3.1

B

before beginning ASP.NET tutorial, 7.2

bind variables

name, 4.3

position, 4.3

binding data, 4.5

building an ASP.NET Application with ODT, 7.1

building connection, 3.5

button control, 3.4

C

C# statements

using, 3.3

case statements, 3.7.3

class variables, 4.5

click events, 4.5

client globalization settings, 9.5.1

CLR (Common Language Runtime), 1.2

Code and Designer toggle, 3.4

Code view, 3.3

commands

query, 4.1

using, 4.1

CommandType property, 4.1

Common Language Runtime (CLR)

agent, 8.2

definition, 1.2

Service

starting, 8.2

configuration scripts, 2.4

configuring an OracleConnection window, 8.6

configuring Oracle Providers for ASP.NET

all, 2.4.1.2

individually, 2.4.1.3

connect alias, 2.3

connect descriptor, 2.3

connecting, 5.2

as SYSDBA, 8.3, 8.6

connecting web site to database, 7.3

connection

add, 8.3

building, 3.5

data source names, 5.2, 5.2, 8.3

details, 8.3

dispose, 4.5

hr, 8.3

name, 5.2

new, 8.3

opening, 3.5

password, 5.2, 5.2

role, 5.2

specific user name and password, 5.2

user name, 5.2, 8.3

user name and password, 8.3

connection control, 3.4

connection strings

setting for ASP.NET, 2.4.2

constraints

add, 5.5

properties, 5.5

tab, 5.5

controls, 3.4

button, 3.4

DataGrid, 4.4

Label, 3.4, 3.4

Listbox, 4.2

Textbox, 3.4

toolbox, 3.4

copying a form, B

creating a user, 2.4.1

creating a web site, 7.3

creating a web user, 7.5

creating an Oracle Project, 8.4

cultural conventions, 9.1

Culture parameter (ID), 9.4

CultureInfo object, 9.2

culture-sensitive data, 9.5

CurrentCulture parameter, 9.5.3

customizing Oracle Providers for ASP.NET, 2.4.3

D

data entry control, 3.4

data grid, 6.6

data provider, 3.2

Oracle Data Provider for .NET, 1.3

Data Source Configuration wizard, 7.3.2

data source names, 5.2, 8.3

database error messages, 3.7.3

DataGrid class, 6.6

DataGrid control, 4.4

DataReader class, 4.4

DataSet class, 4.5

updating, 9.5.2

date formats, 9.3.2

change, 9.5.2

default roles, 5.2

Default.aspx, 7.4

deleting data, 4.7

Design view, 5.3

Designer, 3.4

Designer and Code toggle, 3.4

designing user interfaces, 3.4

dialog

new projects, 3.1

Direction property, 4.3

display schema, 5.2, 8.3

Dispose() method, 3.7.1

documentation library, 1.1

E

enabling a web site for authentication, 7.4

enabling Oracle Providers for ASP.NET, 7.5

Enterprise Manager, 2.1.1, 8.6

error handling

exceptions with ODP.NET, 3.7

ODP.NET, 3.7

Oracle, 3.7

Try-Catch-Finally, 3.7.1

error messages, 9.3.5

Error property, 3.7

events

click, 4.5

examples

names of, 3.1

Exception class, 3.7.3

ExecuteReader() method, 4.2

F

FCL (Framework Class Libraries), 1.2

File menu, 3.1, 3.1

finally block, 4.5

foreign key, 5.5

Form1, 3.1

form1.cs, 3.1

form1.vb, 3.1

forms, 3.4

Framework Class Libraries (FCL)

definition, 1.2

G

GetSessionInfo() method, 9.5.2

GetThreadInfo() method, 9.5.3

global applications

development, 9.1

introduction, 9.1

.NET framework, 9.2

globalization

definition, 9.2

session information, 9.5.2

globalization support

client, 9.5

ODP for .NET, 9.5

granting privileges, 2.4.1

Grant/Revoke Privileges Wizard in ODT, 2.4.1

GridView control, 7.3

H

HR schema, 2.1.2

I

Imports statement, 3.3

indexes

add, 5.4

creating, 5.4

properties, 5.4

Indexes tab, 5.4

inserting data, 4.7

InstallOracleASPNETCommon.sql, configuration, 2.4

L

Label control, 3.4, 3.4

linguistic sorts, 9.3.4

ListBox, 4.2

local user conventions, 9.3

locale

awareness, 9.1

characteristics, 9.1

definition, 9.1

synchronizing, 9.4

localization

resources, 9.2

lock, 5.2, 8.3

login.aspx, 7.4

M

machine.config, 2.4

memory location, 6.3

menus

File, 3.1, 3.1

View, 3.4

method parameters

binding, 6.6

definition, 6.6

methods

Add(), 4.3

Dispose(), 3.7.1

Open(), 3.5

Microsoft internationalization

URL, 9

Microsoft .NET Framework

definition, 1.2

Microsoft Visual Studio, 1.5

2005, 1.1

2008, 1.1

N

name of code files, 3.1

name of forms, 3.1

Name property, 3.4

namespace directives, 3.3

.NET assembly, 1.5

NET connect, 2.3

.NET languages, 1.4

.NET stored functions and procedures

creating, 8.5

deploying, 8.6

running, 8.7

.NET stored procedure, 1.5

.NET Stored Procedures, 2.1.1

.NET stored procedures, 1.4, 2.1.4

deployment, 8.2

.NET Types, 9.5.3

New Package Window, 6.4, 6.5

New Project dialog, 3.1

NLS error messages setting, 9.3.5

NLS number formats

settings, 9.3.3

NLS sort order, 9.3.4

NLS_LANG parameter, 9.5, 9.5.1

NLS_LANGUAGE parameter, 9.3.2, 9.3.5, 9.4

NLS_SORT parameter, 9.3.4

NLS_TERRITORY parameter, 9.3.2, 9.3.3, 9.4

O

ODAC (Oracle Data Access Components), 2.2

ODP.NET Types, 9.5.3

Open() method, 3.5, 9.5

opening connection, 3.5

Oracle Data Access Components (ODAC), 2.1.3

downloading, 2.2

Oracle Data Provider for .NET, 2.1.1

using, 4

Oracle Data Provider for .NET (ODP.NET)

definition, 1.3

globalization, 9.1

installation, 2.2

Oracle Database, 2.1.1

documentation library, 1.1

installation, 2.1.1

Oracle Database Extensions for .NET

installing, 2.1.4

upgrades, 2.1.4

Oracle date formats, 9.3.2

Oracle Deployment Wizard for .NET, 1.5, 8.6

Oracle Developer Tools

definition, 1.4

features

designer, 1.4

drag and drop, 1.4

dynamic help, 1.4

Oracle Data Window, 1.4

Oracle Query Window, 1.4

PL/SQL editor, 1.4

wizard, 1.4

installation, 2.2

using, 5.1

Oracle error messages, 9.3.5

Oracle linguistic sorts, 9.3.4

Oracle number formats, 9.3.3

Oracle Projects

creating, 8.4

Oracle Providers for ASP.NET, 2.4.1.2

configuring individually, 2.4.1.3

customizing, 2.4.3

enabling, 7.5

setup, 2.4

Oracle Universal Installer (OUI), 2.2

ORACLE_BASE\ORACLE_HOME, 2.2

OracleClrAgent service, 8.2

OracleCommand class, 4.1, 4.2, 4.3

using stored procedure, 6.6

OracleConnection class, 3.5, 9.5

GetSessionInfo() method, 9.5.2

Open() method, 9.5

OracleDataAccess.dll, 3.2

OracleDataReader class, 4.2, 4.2, 4.4, 4.5

OracleDbType property, 4.3, 4.3

OracleError class, 3.7

OracleErrorCollection class, 3.7

OracleException class, 3.7, 3.7.3

OracleGlobalization

class, 9.5.2

GetClientInfo() method, 9.5.1

OracleGlobalization class

GetThreadInfo() method, 9.5.3

SetThreadInfo() method, 9.5.3

OracleParameter class, 4.3, 6.6

OracleParameterCollection class, 4.3

OracleRefCursor class, 6.3

OracleService, A

OraProvCfg, 2.4

OUI (Oracle Universal Installer), 2.2

P

package bodies, 6.2

package interfaces, 6.2

PACKAGE types, 6.2

packages

new, 6.4, 6.5

ParameterName, 4.3

passwords

save, 5.2

PL/SQL packages

body, 6.2

definition, 6.2

interface, 6.2

introduction, 6.2

PL/SQL stored procedures

definition, 6.1

in ODP.NET, 6.6

introduction, 6.1

REF CURSORs, 6.4, 6.5

preview SQL, 5.3, 6.4, 6.5

primary key

column, 5.5

privileges

granting, 2.4.1

projects

add reference, 3.2

new, 3.1

solution, 3.1

type

Visual Basic, 3.1

Visual C#, 3.1

properties

Direction, 4.3

Error, 3.7

OracleDBType, 4.3

OracleDBType property, 4.3

ParameterName, 4.3

Size, 4.3

Value, 4.3

Properties window, 3.4

Q

query performance, 4.3

Query Window

running .NET procedures, 8.8

query work area

definition, 6.3

R

Rebuild Solution, 3.7.2

records, 4.7

add, 5.6

REF CURSORs

accessibility, 6.3

assigning, 6.4, 6.5

definition, 6.3

introduction, 6.3

PL/SQL data type, 6.3

PL/SQL stored procedures, 6.4, 6.5

references

adding, 3.2

result set, 6.3

retrieving data

accessor type, 4.2

bind variables, 4.3

from Oracle, 7.3

looping, 4.4

multiple columns, 4.4

multiple rows, 4.4

multiple values, 4.4

simple query, 4.2

value methods, 4.2

roles

user default, 5.2

Run Function window, 8.7

running .NET procedures in Query Window, 8.8

running .NET procedures in SQL, 8.8

S

sample data, 2.1.2

sample schemas, 2.1.2

Save command, 3.3

schema object, 1.4, 8.3

schemas

display, 5.2, 8.3

security, 7.1

SELECT statements

bind variables, 4.3

simple, 4.3

Server Explorer, 1.4, 6.5

using, 5.2

service_name, A

Services, A

session globalization setting, 9.5.2

SetThreadInfo() method, 9.5.3

setup for Oracle Providers for ASP.NET, 2.4

simple query, 4.2

Size property, 4.3

solution, 3.1

specify copy options window, 8.6

specify deployment option window, 8.6

specify methods and security details window, 8.6

SQL preview, 5.3

SQL query, 4.1

SQL statement string, 4.1

SQL*Plus, 9.3.2

connecting to, 9.3.1

sqlnet.ora, 2.3

start Oracle Database Instance, A

Start Without Debugging, 7.6

statements

case, 3.7.3

Imports, 3.3

optimizing, 4.3

parsing, 4.3

reusing, 4.3

using, 3.3

stop Oracle Database Instance, A

stored procedures

creating Oracle Project to hold, 8.4

definition, 6.1

run, 8.7

Summary

of deployment, 8.6

SYSDBA

connecting as, 8.6

System.Globalization, 9.2

System.Resources, 9.2

System.Threading.Thread.CurrentThread.CurrentCulture parameter, 9.5.3

T

table design views, 5.4

table design windows, 5.3

tables

add data, 5.6

constraint name, 5.5

constraint properties, 5.5

constraints, 5.5

add, 5.5

creating, 5.3

data, 5.6

grid, 5.6

new, 5.3

new relational, 5.3

query, 5.7

record, 5.6

relational, 5.3

retrieve data, 5.6

simple query, 5.7

testing

web site authentication, 7.6

Text property, 4.2

Textbox control, 3.4

thread-based globalization setting, 9.5.3

tnsnames.ora, A

configuring, 2.3

toolbox, 3.4

try code block, 4.1, 4.5

Try-Catch-Finally block, 3.7.1

Try-Catch-Finally error handling, 3.7.1

tutorial, 7.2

U

unlocking accounts, 5.2, 8.3

unlocking user account

Oracle Database interface, 8.6

updating data

bind variable, 4.3

user interfaces

designing, 3.4

user schema

ASPNET_DB_USER, 2.4.1

user_source view, 6.2

users

creating, 2.4.1

locale settings, 9.4

role, 5.2, 8.3

using statements, 3.3

V

Value property, 4.3

variable declarations, 4.5

View menu, 3.4

views

Design, 5.3

table design, 5.4

user_source, 6.2

Visual Basic (VB) statements

Imports, 3.3

Visual Studio, 1.5

versions, 2.1.5

W

warning

in error handling, 3.7

web site

connecting to database, 7.3

creating, 7.3

web site authentication, 7.4

testing, 7.6

web users

creating, 7.5

Windows Registry, 9.5, 9.5.1

This screen shot requests that you Choose Your Data Connection. There is a list and HR.ORCL is selection. There is also a button going to New Connection.

At the bottom, there are buttons for Previous, Next, Finish, and Cancel.

The graphic is described in the preceding text.

This screen shot is like the previous one, except there is a right arrow, and the GridView Tasks list is visible. There is a field to Choose Data Source: It shows (None) and underneath that, <New data source...>.

The graphic is described in the preceding text. It shows a tree of Tables, with DEPENDENTS(HR) checked. Below that there is a field named DataSet name: with the entry tableDependents. Below that are Previous, Next, Finish, and Cancel buttons.

This is the Welcome window for the Oracle Deployment Wizard.

There is a box to select Do not show this page again.There are buttons to go Back, Next, Finish, and Cancel.

This screen shot shows the above with C:\HR_Projects\WebSite1 highlighted and its menu showing, with Add New Item... highlighted.

The graphic displays the Summary window, indicating the Global Settings such as Oracle Base: E:\app\Oracle, Product Languages, Space Requirements, and New Installations. There are Help, Installed Products..., Back, Next, Install, and Cancel buttons.

The graphic shows the Toolbox with many of its controls. The first is All Windows Forms, which is collapsed, followed by Common Controls, which is expanded and shows the Pointer, Button, CheckBox, Label, ListBox and many other controls.

The graphic displays the fields described in the preceding text. The project types are on the left, templates on the right. There is a Browse button to determine the location of the project, check boxes to Create directory for solution and Add to Source Control There are OK and Cancel buttons.

The graphic is described in the preceding text. Under Choose a Data Source Type there is a field labeled Where will the application get data from? There are icons for selection, labeled, Database, Service, and Object. Below that field, is an area that describes each of the icons in turn when you select them. The database icon is currently highlighted with this description. "Lets you connect to a database and choose the database objects for your application. This option creates a dataset." Then there are Previous, Next, Finish, and Cancel buttons.

The graphic shows the Server Explorer tree, with the following expanded, HR.ORCL schema, then tables, then Indexes, then the DEPENDENTS_INDEX.

The graphic shows the Add Connection screen with the Apply Filters tab selected. On the left are the Available Schema such as Anonymous, and others. On the right are the Displayed Schemas. This contains the HR schema. There are arrows between the two lists of schema for moving a schema from one side to another. There is a display public synonyms check box. There are Advanced..., Test Connection, OK, and Cancel Buttons.

The graphic shows the Build menu, with the Build Solution command highlighted.

The graphic now shows the grid with the additional information described in the text.

The graphic shows a form with a Connect button and the following fields, User ID, Password, Data Source and Departments. There is one large unlabeled field and a Save button.

This screen shot shows the Address, which ends in Default.aspx (http://localhost:1451/WebSite1/Default.aspx). The actual page shows the login, with entry fields for User Name and Password, a check box to Remember me next time (unchecked) and the Log In button.

The graphic shows a list of names that begin with C and their ID. The name order is Cabrio, Cambrault, Cambrault, Chen, Chung, and Colmenares.

This screen shot shows the Add New Access Rule section. Under it are several parts. First there is a Select directory for this rule: This shows the tree view of WebSite1 expanded to show the App_Data folder. The second part is Rule applies to: with options to choose Role (includes a list) and User (includes an entry field). After that there is a link to Search for users. Below that are options to choose All users or Anonymous users. The last part is Permission: with option for All and Deny (selected). Last is the OK button.

The graphic shows a form with a Connect button and the following fields and entries, User ID:hr, Password:*********, Data Source orcl and a Department a list field showing Administration, Marketing, Purchasing, and Human Resources.

The graphic shows salaries for employees with ID below 105. The columns and first line of data are: ID:100, Name, S. King, Salary: 24.005,00.

The graphic shows the Server Explorer tree with HR.ORCL expanded. Under Packages HR.DATA is expanded and GETCURSORS is selected and the context menu displayed. It shows commands to Run, Run Debug, Step Into, and so on.

This screen shot shows the Default.aspx source code in the Visual Studio IDE.

There is a heading Client Objects and Events from the list. Below that is the code including an html section that has a title "Untitled Page". At the bottom, are tabs to switch to the Design or Spilt views instead of Source.

The graphic shows the Debug Menu, with commands such as Windows, Start Debugging, Start Without Debugging, Attach to Process., Exceptions..., Step Into and others.

The graphic shows the Solution Explorer for the HR_CONNECT_CS project with Copy of Form1.cs selected and expanded. The context menu is open showing commands such as Open, Open With..., View Class Diagram, Include in Project, and so on.

Screen shot shows a grid with dummy titles such as Column0, Column1, etc and dummy contents abc. A right arrow appears in line with the Column titles, which leads to the GridView Tasks list

The User Name is Anne, with a password. The login screen says "Your login attempt was not succesful. Please try again."

The graphic shows a grid with the same data as the New Data for the DEPENDENTS Table.

The graphic lists the current destination folder for .NET products. It contains a Browse button for changing the path, Help, Installed Products..., Back, Next, Install, and Cancel buttons.

The graphic indicates that Oracle Data Access Components for Oracle Client are being installed. The required components are listed in the text. There is a Components column with check boxes and an Install Status column. There are buttons to Expand All, Collapse All, Select All, Deselect all, Help, Installed Products, Back, Next, Install, and Cancel. There is a check box to Show all components, including required dependencies.

The graphic shows the Designer for Tables, as described. the Columns list has increased. It includes: Last_name, plus First_Name, Birth_date, Relationship, Employee_ID, and Dependent_ID. In the Column Properties, there is Name: Dependent_ID and Data Type Number, Default is blank, The Allow null box is unchecked, Size is 5 and Length semantic is selected as 0.

The graphic shows the Add Method window. It has these fields and data: Method name:GETCURSORS, Method type: Procedure, Return type: a disabled field that says NUMBER. The text describes the Parameters: area where you add and remove parameters, and the Parameter Details field where you add details about the parameters.

This screenshot shows the Connection Properties Dialog. The first field is for Data source and Oracle Database (Oracle ODP.NET) is entered. There is a button to change this.

Then there is the connection details tab with the selected Data source name: ORCL

There is the unselected option to use Windows integrated authentication or the selected option to use a specific user name and password, with the entries: User name: ASPNET_DB_USER and Password *******.

There is a check box to Save Password (unchecked), followed by Role: with Default selected from list. Then a Connection name with ASPNET_DB_USER.ORCL.

Last, there are button labeled Advanced, Test Connection, OK, and Cancel.

The graphic shows the Designer for Tables, as described. In the Columns list is the Name: Last_name and Data Type VARCHAR2. In the Column Properties, there is Name: Last_name and Data Type VARCHAR2, Default is blank, The Allow null box is check, Size is 30 and Length semantic is selected as Default.

This shows the Test Query screen again, this time the blank space is filled with EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and other employee related columns and the first five employees show up.

This screen shot shows the Visual Studio menu, with File, then New expanded to show Project..., Web Site..., File..., and Project from Existing Code...

The graphic shows the following: ERROR at line:

ORA-00946: table or view does not exist.

The graphic now shows the Table Designer with the Constraints tab selected. There are two unrelated system indexes, and one index for the example, Name: EMPLOYEES_FK and Type: Foreign Key. On the right are Constraints Properties, with EMPLOYEES_FK and Type: Foreign Key, there are check boxes for Deferrable, Enabled (checked), and Validate (checked). There is a disabled field for Execution. There is a section on called Select the foreign key constraint, with Table: EMPLOYEES, and Constraint: EMP_EMP_ID_PK. There is grid called Association: with Referenced Column: EMPLOYEE_ID and Local Column: EMPLOYEE_ID. There is a list called On delete with Cascade selected.

The graphic shows employees with ID below 105. The columns and first line of data are: ID:100, Name, S. King, Short Hire: 6/17/1987, Long Hire Date, Wednesday, June 17, 1987.

This screen shot requests the connection string to the Application configuration file. First this is a check box labeled Yes, save this connection as: ConnectionString is entered in this field. Below this are buttons for Previous, Next, Finish, and Cancel.

This shot shows the GridView again, only now instead of Column01 etc, it shows column names such as EMPLOYEE_ID, FIRST_NAME, etc from the EMPLOYEES table. The GridView Tasks page is shown listing some of the possible tasks. There ar check boxes for Enable Paging (checked), Enable Sorting (unchecked), and Enable Selection (unchecked).

The graphic shows the OUI Welcome screen. It has the following buttons: Deinstall Products, About Oracle Universal Installer..., Help, Installed Products..., Back, Next, Install, and Cancel.

This screen shot is named Add New Item - C:\HR_Projects\WebSite1|. There is a heading Templates: and a list of Visual Studio Installed templates, including Web Form, Web User Control, Master Page, and others/

Below these is the Name field with login.aspx entered. Then there's the Language field, with Visual C# selected from the list. There are two check boxes: Place code in separate file (checked) and Select master page (unchecked). Last are the Add and Cancel buttons.

This screen shot shows the Manage Access Rules section with a tree view of WebSite1 that is expanded to show the App_Data folder. Besides the tree view is a panel that shows the rule: Permission: allow, for User and Roles: all, for Delete.

Below that is a link to Add new access rule

The graphic shows the Build menu with commands such as Build Solution, Rebuild Solution, Clean Solution, and Batch Build.

The graphic shows the two options described in the text. It has the following buttons: Product Languages, Help, Back, Next, Install, and Cancel.

The graphic is described in the preceding text.

Figure shows the Server Explorer tree, with SYS.ORCL, and the menu open to highlight Privileges...

This shot shows the browser window described in the text. The first line of the page is Employees List. At the bottom, are numbers, 23456 etc for paging through the data.

The graphic shows a code window for HR_Connect_CS.Form1 with these lines:

public Form1()
{
     InitializeComponent();
     Save.Enabled = false;
     date_change.Enabled = false;
}
private OracleGlobalization si;
private OracleConnection conn = new OracleConnection();
private OracleCommand cmd;

The screenshot shows the query window with the SQL command "create user aspnet_db_user identified by your_password. A query results window below indicates: "The command(s) successfully completed.

This screen shots show the Visual Studio View Menu, including Code, Designer Markup and others. At the bottom of the menu, the Toolbox selection is highlighted.

This graphic shows the command indicated in the text, and the typical response that indicates SQL*Plus has started, release number and copyright information. Next there is the password input request as described. Then the response "Connected to: Oracle Database ......Edition Release. The line after that shows the SQL*Plus prompt: SQL>.

The graphic contains the exception error text described in the text, a large X, and buttons for Details, Continue, and Quit.

The graphic shows a grid labeled Parameters: The columns and data are: Name:EMPLOYEE_ID, Direction: IN, Data Type: BINARY_INTEGER, and Value:100. There are OK, Cancel and Help buttons.

This screenshot shows the Add Connection Dialog. The first field is for Data source and Oracle Database (Oracle ODP.NET) is entered. There is a button to change this.

Then there is the connection details tab with the selected Data source name: orcl

There is the unselected option to use Windows integrated authentication or the selected option to use a specific user name and password, with the entries: User name: HR and Password *******.

There is a check box to Save Password (checked), followed by Role: with Default selected from list. Then a Connection name with HR.orcl.

Last, there are button labeled Advanced, Test Connection, OK, and Cancel.

The graphic shows the project types on the left with Database under Visual C# highlighted. On the right, under Visual Studio installed templates, Oracle Project is highlighted. At the bottom, there are fields with Name, Location, Solution as described in text. Location has a browse box. There is a checked box for Create directory for solution, and an unchecked box for Add to Source Control. At the end are Ok and Cancel buttons.

The graphic is described in the preceding text.

The security page now shows the heading Create User with the text Complete. Your account has been successfully completed. There is a Continue button. There is also a Roles section which still indicates that Roles are not enabled.

The graphic shows the Server Explorer, the HR.ORCL connection expanded, and Tables highlighted to display the menu. The menu lists New Relational Table..., New Object Table..., New XML Table..., Import Table..., Query Window, Privileges..., Refresh, and Properties.

The graphic shows the Class1.cs code. It shows the beginning of public class Class1 and under that the first six line of the code listed above for the getDepartmentno function.

This screen shot shows the Security page which was previously shown., however, now the Users heading has the following under it: Existing Users: 0, and two links, Create user and Manage users.

This graphic shows the Data source: field which is blank, and the Change button.

The graphic is described in the preceding text.

The graphic shows employees with ID below 105. The column labels and first data line are: EMPLOYEE_ID, FIRST_NAME: Steven, LAST_NAME: KING, and Hire Date: 17-Jun-87.

The graphic shows a form with a Connect button and the following fields and entries, User ID:hr, Password:*********, Data Source orcl and Department: 50, and a list field showing Shipping.

The graphic contains the following code.

CREATE  INDEX "DEPENDENTS_INDEX"
 ON "DEPENDENTS" (
  "DEPENDENT_ID");

The graphic contains the following code.

ALTER TABLE "DEPENDENTS" ADD (
 CONSTRAINT "EMPLOYEES_FK"
  FOREIGN KEY ("EMPLOYEEID")
   REFERENCES "EMPLOYEES" (EMPLOYEE_ID)
   ON DELETE CASCADE
  ENABLE
  VALIDATE);
ALTER TABLE "DEPENDENTS" ADD (
 CONSTRAINT "DEPENDENTS_PK"
  PRIMARY KEY("DEPENDENTID")
  USING INDEX "DEPENDENTS_INDEX"
  ENABLE
  VALIDATE);

There are buttons to Add to Project (disabled) and OK.

The graphic shows a code window for HR_Connect_CS.Form1 with these lines:

public Form1()
{
     InitializeComonent();
     Save.Enabled = false;
     date_change.Enabled = false;
}

The graphic shows the Server Explorer tree with the HR.ORCL expanded, then Tables, then DEPENDENTS. The table menu show is open to show Design..., Retrieve Data..., Add Trigger..., Query Window, Generate Script..., Generate Create Script to Project..., Privileges..., Copy, Delete, Refresh, and Properties.

The graphic contains the following code.
CREATE TABLE "DEPENDENTS" (
 "LAST_NAME" VARCHAR2(30) NULL,
 "FIRST_NAME" VARCHAR2(20) NULL,
 "BIRTH_DATE" DATE NULL,
 "RELATIONSHIP" VARCHAR2(20) NULL,
 "EMPLOYEE_ID" NUMBER(6,0) NOT NULL,
 "DEPENDENT_ID" NUMBER(6,0) NOT NULL);

This screen shot contains two headings: The first is Membership provider with two choices, OracleMembershipProvider and AspNetSqlMembershipProvider. the second heading is Role Provider with three choices, OracleRoleProvider, AspNetSqlRoleProvider and AspNetWindowsTokenRoleProvider

The graphic illustrates this step of the procedure. It is described in the preceding text.

The graphic shows the Oracle Server Login, with connection name HR.ORCL and a masked password. There is a check box to Save password and OK, Cancel and Help buttons.

The graphic shows the Server Explorer, with Data Connections highlighted and the HR.ORCL connection expanded.

The graphic shows a grid titled Parameters. Under this are fields and Data as follows: Name:DEP_ID, Direction: IN, DATA TYP: NUMBER, and Value 60 from a list. The second data line has this EMPLOYEES_C , OUT, REF CURSOR, and for value it instructs you to <Click here for Details.>. The third line is highlighted and it has data: DEPENDENTS_C, OUT, REF CURSOR and the value Click here for Details.>

A separate grid below this shows the results of the query in the highlighted line, activated when you hit <Click here for Details.>

The graphic shows the following: ERROR at line 1: ORA-00946: Table ou vue inexistante.

This graphic illustrates the initial form of the new project C# project, as described in the preceding text.

This screen shot shows the Data Source Configuration wizard. You are requested to choose a Data Source type, of the following Access Database, Database, LINQ, Object, Site Map and XML file. Database is highlight. When this is chosen, under the list it says "Connect to any SQL database supported by ADO.NET, such as Microsoft SQL Server, Oracle, or OLEDB." After this is a field for entering an ID for the data source, which is HR. Finally, there are OK and Cancel buttons.

The graphic displays the declarations section for a C# application, with the two using statements described in the text, plus many others.

The graphic is described in the preceding text.

This graphic shows the same view as the previous shot, except that the mouse is poised over the far left column, highlighting the whole row for deletion.

This window asks which data connection you want the wizard to use. The current connection SYS.ORCL is displayed.

There is a New Connection button.

There are buttons to go Back, Next, Finish, and Cancel.

This screen shot is named New Web Site. There is a heading Templates: and a list of Visual Studio Installed templates, including ASP.NET Web Site, ASP.NET Web Service, Empty Web Site, and others, and My Templates including Search online Templates...

Below these lists are Location: with File System and C:\HR_Projects\WebSite selected from the lists. Then there's a Browse button, and after than the Language field, with Visual C# selected from the list. Last are the OK and Cancel buttons.

The window shows the Schema: SYS from a list, Object type: USER from a list, and ObjectName: ANONYMOUS, which is indicated to be disabled, also from a list.

There is a User/Role section. User is selected and ASPNET_DB_USER is selected from alist. This is followed by the Privileges sections. There are two lists, one is Available Privileges which contains all the avaiable privileges except those on the right which are the Granted Privileges. There are arrows between the lists for clicking from one list to another.

At the end, are the following buttons: Preview SQL, OK, Cancel, Apply, and Help.

The graphic shows the Solution Explorer for the HR_CONNECT_CS project with Form1.cs selected and expanded. The context menu is open showing commands such as Open, Open With..., View Class Diagram and so on.

This shot is described in text.

This screen shot shows the Test Query. It starts with "To preview the data returned by this data source, click Test Query. To complete this wizard, click Finish."

Below this, there is a blank field. After that, there is a Test Query button, followed by the SELECT statement described in the text.

There are buttons for Previous, Next, Finish, and Cancel.

This screen shot shows the ASP.NET Web Site Administration Tool, which has Home, Security, Application and Provider tabs. The front home tab lists links to the other tabs.

This window requests that you chose the dependent assemblies that should be copied over to the database. It lists the following assemblies: mscorlib, Oracle.DataAccess, System, and System.Data.

Then it requests the destination of the bin/clr directory, if it is not the default bin/clr directory. For this demonstration, the field is blank.

Then there is a check box where you can chose overwrite the file if it already exists.

There are buttons for Back, Next, Finish, or Cancel.

The graphic is described in the preceding text.

The graphic shows the Solution Explorer, which has a tree structure. The first branch is Properties, then References, which is expanded to show Oracle.DataAccess, System, and many other.

The graphic is described in the text. After the Data connection list, there is a browse button to find other possible connections. Below that is a paragraph asking if you want to permit sensitive data to appear in the connection string. There are No and Yes options to check. Below that is a plus sign (+) labeled Connection string, which expands to display the connection string.

This screen shot shows the Properties Windows, including DestinationPageUrl, which is selected. Below that is another window, Select URL. It shows the contents of the Project folder, including Default.aspx (which should be selected) as well as login.aspx and web.config.

This graphic shows a list of data sources including Oracle Database which is highlighted. Below this is a list for Data provider with Oracle Data Provider for .NET displayed. There is a check box to Always use this selection, and then OK and Cancel buttons.

The graphic shows a form with a Connect button and the following fields and entries, User ID:hr, Password:*********, Data Source orcl and a grid showing DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, AND LOCATION_ID. the first line is 10, Administration, 200, and 1700. The second line is 20, Marketing, and so on. The last line is 5, Community Outreach, blank and 1700. It is still being entered.

The graphic is described in the preceding text.

The graphic is described in the preceding text, a Form with fields to enter the requested items and a button labeled Connect.

This shot shows the Solution Explorer, with Solution WebSite1 expanded to show C:\HR_Projects\WebSite1, expanded to show App_Data, Default.aspx, and web.config.

The graphic shows employees with ID below 105. The columns and first line of data are: ID:100, Name, S. King, Short Hire: 17.06.1987, Long Hire Date, Mittwoch, 17 Juni, 1987.

The graphic shows a form with a Connect button and the following fields and entries, User ID:hr, Password:*********, Data Source orcl and a grid showing DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, AND LOCATION_ID. The first line is 5, Community Outreach, blank and 1700. the second line is 10, Administration, 200, and 1700. The third line is 20, Marketing, and so on.

The graphic shows a Data Sources window, with a tree expanded from tableDependents, with the DEPENDENTS table under it.

The graphic shows the menu that appears. It contains the commands: View Code, Bring to Front, Send to Back, Lock Controls, and others.

The graphic illustrates this step of the procedure, as described in the preceding text.

The graphic is described in the preceding text.

This graphic shows the Query Window with the select statement described in the text in the input section and the result 90 in the Query Result section.

The graphic is described in the preceding text.

The graphic shows salaries for employees with ID below 105. The columns and first line of data are: ID:100, Name, S. King, Salary: 24,005.00.

The graphic shows a form with a Connect button and the following fields and entries, User ID:hr, Password:*********, Data Source orcl and Departments: Administration.

The graphic is described in the preceding text.

The graphic shows the previous constraint, plus a new one, Name: DEPENDENTS_PK and Type: Primary Key. The constraint properties are Enabled: checked, Validate: checked, Primary key columns: DEPENDENT_ID, and Using index: DEPENDENTS_INDEX.

The graphic shows a form with a Connect button and the following fields, User ID, Password, Data Source and Departments. There is one large unlabeled field.

The graphic displays the declarations section for a Visual Basic application, with the two Imports statements described in the text, plus many others.

The graphic is described in the preceding text.

This window requests that you specify an assembly and a library name.

First, it requests the location of the source assembly. An option for Project is selected.

Second, it requests the name of the library database object to be used for the selected assembly. The drop-down lists shows HR_DEPLOYSTORED_CS_DLL.

There are buttons to go Back, Next, Finish, and Cancel.

The graphic says that the Test connection succeeded. There is an OK button.

The graphic says the following. "Your project currently has no data sources associated with it. Add a new data source, then data-bind items by dragging from this window on forms or existing controls." Then there is a link labeled Add New Data Source.

This screen shot shows the Address, which still ends in Default.aspx, but has changed to http://localhost:1451/WebSite1/login.aspx?ReturnUrl= %2fWebSite1%2fDefault.aspx. The actual page is the same as the previous one.

This window requests that you specify your deployment option. The options are to: Copy assembly and generate stored procedures (selected), copy assembly only, or generate stored procedures only.

There are the following buttons: Back, Next, Finish (which is grayed out), and Cancel.

The graphic shows a list of names that begin with C and their ID. The name order is Cabrio, Cambrault, Cambrault, Colmenares, Chen, and Chung.

The graphic shows the installer verifying that the environment meets the minimum requirements for the products you have chosen to install. It shows a grid with titles Check, Type and Status. The highlighted item is "Checking Oracle Home path for spaces...", with Type: Automatic and Status: Succeeded.

This screen shot shows the Create User section of the Security page. It has fields for User Name, Password, Confirm Password, E-mail, Security Question, and Security Answer. There is a check box for Active user, and a button to Create User. The shot also contains a second section besides the Create User section. This section is Roles. All it contains is the text "Roles are not enabled".

This graphic shows Form1 with fields and data: UserID HR, Password: XXXXX, Data Source ORCL and a Connect button that is disabled. Then there is a grid showing Salary, Commision_PC, Manager_ID, Department_ID and so on. Then there is a Save button.

The graphic is the same as at the end of the previous section, except that now it has a Save button.

This screen shot is described in the text. The login control contains the heading Login In and a field to enter the User Name and Password. There is a check box to Remember me next time, and then a Login In button.

Also, on the right side is an arrow which when clicked shows the Login Tasks: Auto Format... , Convert to Template, and Administer Website.

The graphic is described in the preceding text.

The graphic shows the Solution Explorer for the HR_CONNECT_CS project with Form2.cs selected and expanded. The context menu is open showing commands such as Open, Open With..., View Class Diagram, Include in Project, and so on

This screen shot shows the Website menu and at the bottom, the highlighted ASP.NET Configuration.

This screen shot is much the same as last time as the time, except that it now contains the message described in the text and the address is again http://localhost:1451/WebSite1/login.aspx.

This screen shot shows the Run SQL*Plus Script feature. It has groups for File Information, with a field for specifying the full path of the SQL*Plus script with a browse button, Connection Information where the database connection information is specified as SYS.ORCL, with a New Connection button provided for browsing, and finally the Connection String section currently showing DATA SOURCE=ORCL;DBAPRIVILEGE=SYSDBA;PERSIS SECURITY. This is followed by a read-only user input area.There is an executing script bar and Run and Cancel buttons.

The graphic shows a form with a Connect button and the following fields, User ID, Password, Data Source and Departments. The Departments field contains departments.

The graphic shows the Output Window from the build with this text:

Compile complete -- 0 errors, 0 warnings

HR_Connect_CS -> C:\HR_Projects\HR_Connect_CS\HR_Connect_CS\bin\Debug\

HR_Connect_CS.exe

===========Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ======

The graphic shows the Properties window with Text highlighted and the text property as User ID.

The graphic displays the View menu, with these available commands, Code, Designer, Server Explorer, Solution Explorer, and Class View.

This screen shot shows the Security page which was previously shown., however, now the Users heading has the following under it: Existing Users: 1 (where it previously said 0, and two links, Create user and Manage users.

The graphic shows the Server Explorer tree with HR.ORCL expanded. Under Packages HR.DATA is selected and the context menu displayed. It shows commands to Edit Package Specification, Edit Package Body..., Compile, Compile Debug and so on.

This screen shot starts with Configure the Select Statement. It asks "How would you like to retrieve data from your database?" and provides two options. One is Specify a custom SQL statement or store procedure, Second is Specify columns from a table or view with a list of the Names of tables or views. The name EMPLOYEES appears. Under this are columns to select. Currently the asterisk (*) is selected. Other columns include EMPLOYEE_ID, FIRST_NAME, LAST_NAME and etc.

Below this is the select statement as described in the text. There are buttons for Previous, Next, Finish, and Cancel.

The graphic is described in the preceding text.

The graphic shows a form with a Connect button and the following fields, User ID, Password, Data Source. There is one large unlabeled field.

This screen shot shows the Run SQL*Plus Script feature. It has groups for File Information, with a field for specifying the full path of the SQL*Plus script with a browse button, Connection Information where the database connection information is specified as ASPNET_DB_USER.ORCL, with a New Connection button provided for browsing, and finally the Connection String section currently showing DATA SOURCE=ORCL;PERSIS SECURITY INFO=False;USER. This is followed by a read-only user input area.There is an executing script bar and Run and Cancel buttons.

The graphic shows the Build menu, with the Deploy Solution command highlighted.

The graphic shows a form with a Connect button and the following fields and entries, User ID:hr, Password:*********, Data Source orcl and a grid showing DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, AND LOCATION_ID. the first line is 10, Administration, 200, and 1700. The second line is 20, Marketing, and so on to 50.

This screen shot shows the ORACLE_BASE\\ORACLE_HOME\ASP.NET\sql directory with the InstallAllOracleASPNETProviders.sql file highlighted to open.

The graphic shows the Run Procedure window. It has a grid titled Parameters. Under this are fields and Data as follows: Name:DEP_ID, Direction: IN, DATA TYP: NUMBER, and Value 60 from a list. There are buttons for OK, Cancel, and Help.

The graphic is described in the preceding text.

Screen shot shows the Toolbox with Data group expanded and GridView highlighted.

This screen shot shows the Security page, which has the headings Users with one link, Select authentication type, Roles with one link, Enable roles, and Access Roles with two links, Create access rules and Manage access rules.

The graphic shows Form1 with an empty grid, with these columns showing: LAST_NAME, FIRST_NAME, BIRTH_DATE, RELATIONSHIP, EMPLOYEE_ID, and DEPENDENT_ID. Under that are labeled icons for the components listed in the text.

The summary window states that the information needed for the wizard has been specified.

There is a summary that includes the entire assembly path to HR_DeployedStored_CS.dll and the Project Name: HR_DeployedStored_CS.

The screen shot states that the HR_DeployedStored_CS.dll will be copied to the $Oracle_ HOME\bin\clr directory of local database and 1 stored procedure will be deployed to local database.

There is a Show Script button, as well as Back, Next, Finish, and Cancel buttons.

The graphic illustrates this step of the procedure. It is described in the preceding text.

The graphic is described in the preceding text.

The graphic shows the Solution Explorer with the HR_CONNECT_CS project selected. The context menu is open showing commands such as Build, Rebuild, Clean, Publish, Add, and so on.

The graphic displays the Microsoft Visual Studio Start window. It contains in the upper left, a Recent Projects section, middle left, a Getting Started section, lower left Visual Studio Headlines section, middle a Visual Studio Developer News page, upper right, the Solution Explorer, and the Properties window.

This screen shot asks: "How will users access your site?" and there are two options: From the internet and From a local network. At the end, is the Done button.

The graphic shows employees with ID below 105. The column labels and first data line are: EMPLOYEE_ID, FIRST_NAME: Steven, LAST_NAME: KING, and Hire Date: 1987-06-17.

The graphic is described in the preceding text.

The graphic is described in the preceding text. At the bottom there are Advanced..., Test Connection, Ok, and Cancel buttons.

The graphic shows a grid titled Parameters. Under this are fields and Data as follows: Name:DEP_ID, Direction: IN, DATA TYP: NUMBER, and Value 60 from a list. The second data line is highlighted and has this EMPLOYEES_C , OUT, REF CURSOR, and for value it instructs you to <Click here for Details.>

A separate grid below this shows the results of the query in the highlighted line, activated when you hit <Click here for Details.>

Figure shows the Server Explorer tree, with SYS.ORCL, and the menu open to highlight the Query Window.

This windows requests that you specify method and security details.On the left, a text box shows available methods with the following checked: HR_DeployedStored_CS, and under that, Class1, and then Int32 GetDepartmentNo.On the right is the title Methods Details, followed by Schema: with a drop-down list showing HR as the selected schema. After that, is a text field for Database method that shows GETDEPARTMETNTNO.After that, there is a button labeled Parameter Type Mapping... which goes to that screen.

After that, there is a drop-down list to set Security. The default level, Safe is entered.There are buttons to go Back, Next, Finish, and Cancel.

The graphic shows the Designer for Tables. At the top, is Schema Name, with HR selected from the list, then Table type with Relational indicated. There are four tabs: Columns, Constraints, Indexes, and Storage. The columns tab is selected. It contains a column area with the heading Name and Data Type, and Add and Remove buttons below. there are up and down arrows to change the order of the columns. to the right of this is the Column Properties Area with fields to enter Name, Data Type and Default. The bottom contains Preview SQL and Save buttons.

This screen shot shows the Provider page, with two links: Select a single provider for all site management, and Select a different provider for each feature (advanced.

This is a screen shot of the Design.aspx from the design view. The main window shows a dotted rectangle containing the words "Employees List."

The graphic shows Form1 with labels and fields for UserId, Password, and Data Source. There are buttons for Connect, Save and Change Date Format. There is a large empty field that will contain results when the form is run.

The graphic shows the Server Explorer tree with HR.ORCL schema expanded and the Packages node context menu open. It shows commands to New Package..., Privileges..., Refresh, and Properties.

The graphic now shows the Table Designer with the Indexes tab selected. There is one index, Name: Dependents_Index and Type: B-tree. On the right are Index Properties, with Name: Dependents_Index and Type: B-tree, there are check boxes for Unique and Reverse. There is a section on Index Keys: with Key Dependent_ID showing and the Order: Ascending. There are Add and Remove buttons under the Index properties, enabling you to add more properties.

The graphic shows the New Package window, with these fields and data: Schema name: HR, Package name: HR_DATA, Authentication identifier: DEFINER. There is a checked box for Generate empty package body. Then there is a field for adding methods to the packages. Fields are added by clicking Add button below the field. There are also Edit and Remove section for the methods. Last, there are these buttons: Preview SQL, OK, Cancel, and Help.

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2012, Oracle and/or its affiliates. All rights reserved.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Alpha and Beta Draft Documentation Notice

If this document is in prerelease status:

This documentation is in prerelease status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.