Using Ado.net to access Oracle 9i stored procedures (top)

This article discusses how to use ado.net to access Oracle stored procedures (called SQL programming Blocks) and functions (a programming block that returns a single value).

You can connect to an Oracle database using the following managed data provider: Microsoft. NET Oracle provider, OLE DB. NET provider, ODBC. NET data provider, and Oracle's Odp.net provider. This article uses Microsoft for Oracle. NET Framework Data Provider. You can use different features when using an Oracle odp.net data provider or a Microsoft. NET Framework Data Provider for OLE DB.

Oracle. NET data providers are provided with. NET Framework 1.1. If you are using. NET Framework 1.0, you will need to download the. NET Managed Provider for Oracle. Regardless of the version, the data provider class is located in the System.Data.OracleClient namespace.

Overview

Pl/sql is the Oracle implementation of SQL. It with Microsoft? SQL Server? The T-SQL used is similar, but there are some differences, which are discussed in detail later in this article. Like T-SQL, Pl/sql extends standard SQL. Pl/sql is used to define named programming blocks, such as stored procedures, functions, and triggers.

Class

Oracle stored procedures and functions can be executed using a subset of the classes in the System.Data.OracleClient namespace. These classes are described in the following table:

Class description

OracleCommand

The SQL statement for the stored procedure executed against the Oracle database.

OracleConnection

The Open database connection.

OracleParameter

The OracleCommand parameter, or it may be the mapping to the DataColumn.

OracleParameterCollection

A collection of OracleParameter objects.

OracleType

An enumeration of Oracle data types and structs.

Executing stored procedures

Executing an Oracle stored procedure is similar to executing a SQL Server stored procedure. The following steps explain how to execute an Oracle stored procedure and retrieve the results it returns.