ADO.NET Oracle

Note

The types in System.Data.OracleClient are deprecated. The types remain supported in the current version of.NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.

This section describes features and behaviors that are specific to the .NET Framework Data Provider for Oracle.

The .NET Framework Data Provider for Oracle provides access to an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software. The functionality of the data provider is designed to be similar to that of the .NET Framework data providers for SQL Server, OLE DB, and ODBC.

To use the .NET Framework Data Provider for Oracle, an application must reference the System.Data.OracleClient namespace as follows:

using System.Data.OracleClient;

You also must include a reference to the DLL when you compile your code. For example, if you are compiling a C# program, your command line should include:

csc /r:System.Data.OracleClient.dll

In This Section

System Requirements
Describes requirements for using the .NET Framework Data Provider for Oracle, and describes a number of issues to be aware when using it.

Oracle BFILEs
Describes the OracleBFile class, which is used to work with the Oracle BFILE data type.

Oracle LOBs
Describes the OracleLob class, which is used to work with Oracle LOB data types.

Oracle REF CURSORs
Describes support for the Oracle REF CURSOR data type.

OracleTypes
Describes structures you can use to work with Oracle data types, including OracleNumber and OracleString.

Oracle Sequences
Describes support for retrieving the server-generated key Oracle Sequence values.

Oracle Data Type Mappings
Lists Oracle data types and their mappings to the OracleDataReader.

Oracle Distributed Transactions
Describes how the OracleConnection object automatically enlists in an existing distributed transaction if it determines that a transaction is active.

Related Sections

Securing ADO.NET Applications
Describes secure coding practices when using ADO.NET.

DataSets, DataTables, and DataViews
Describes how to create and use DataSets, typed DataSets, DataTables, and DataViews.

Retrieving and Modifying Data in ADO.NET
Describes how to work with data in ADO.NET.

SQL Server and ADO.NET
Describes how to work with features and functionality that are specific to SQL Server.

DbProviderFactories
Describes generic classes that allow you to write provider-independent code in ADO.NET.

System Requirements for the .NET Framework Data Provider for Oracle

The .NET Framework Data Provider for Oracle requires Microsoft Data Access Components (MDAC) version 2.6 or later. MDAC 2.8 SP1 is recommended.

You must also have Oracle 8i Release 3 (8.1.7) Client or later installed.

Oracle Client software prior to version Oracle 9i cannot access UTF16 databases because UTF16 is a new feature in Oracle 9i. To use this feature, you must upgrade your client software to Oracle 9i or later.

Working with the Data Provider for Oracle and Unicode Data

Following is a list of Unicode-related issues that you should consider when working with the .NET Framework Data Provider for Oracle and Oracle client libraries. For more information, see your Oracle documentation.

Setting the Unicode Value in a Connection String Attribute

When working with Oracle, you can use the connection string attribute

Unicode=True

to initialize the Oracle client libraries in UTF-16 mode. This causes the Oracle client libraries to accept UTF-16 (which is very similar to UCS-2) instead of multi-byte strings. This allows the Data Provider for Oracle to always work with any Oracle code page without additional translation work. This configuration only works if you are using Oracle 9i clients to communicate with an Oracle 9i database with the alternate character set of AL16UTF16. When an Oracle 9i client communicates with an Oracle 9i server, additional resources are required to convert the Unicode CommandText values to the appropriate multi-byte character set that the Oracle9i server uses. This can be avoided when you know that you have the safe configuration by adding Unicode=True to your connection string.

Mixing Versions of Oracle Client and Oracle Server

Oracle 8i clients cannot access NCHAR, NVARCHAR2, or NCLOB data in Oracle 9i databases when the server's national character set is specified as AL16UTF16 (the default setting for Oracle 9i). Because support for the UTF-16 character set was not introduced until Oracle 9i, Oracle 8i clients cannot read it.

Working with UTF-8 Data

To set the alternate character set, set the Registry Key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG to UTF8. See the Oracle Installation notes on your platform for more information. The default setting is the primary character set of the language from which you are installing the Oracle Client software. Not setting the language to match the national language character set of the database to which you are connecting will cause parameter and column bindings to send or receive data in your primary database character set, not the national character set.

OracleLob Can Only Update Full Characters.

For usability reasons, the OracleLob object inherits from the .NET Framework Stream class, and provides ReadByte and WriteByte methods. It also implements methods, such as CopyTo and Erase, that work on sections of Oracle LOB objects. In contrast, Oracle client software provides a number of APIs to work with character LOBs (CLOB and NCLOB). However, these APIs work on full characters only. Because of this difference, the Data Provider for Oracle implements support for Read and ReadByte to work with UTF-16 data in a byte-wise manner. However, the other methods of the OracleLob object only allow full-character operations.

Oracle BFILEs

The .NET Framework Data Provider for Oracle includes the OracleBFile class, which is used to work with the Oracle BFile data type.

The Oracle BFILE data type is an Oracle LOB data type that contains a reference to binary data with a maximum size of 4 gigabytes. An Oracle BFILE differs from other Oracle LOB data types in that its data is stored in a physical file in the operating system instead of on the server. Note that the BFILE data type provides read-only access to data.

Other characteristics of a BFILE data type that distinguish it from a LOB data type are that it:

Contains unstructured data.
Supports server-side chunking.
Uses reference copy semantics. For example, if you perform a copy operation on a BFILE, only the BFILE locator (which is a reference to the file) is copied. The data in the file is not copied.

The BFILE data type should be used for referencing LOBs that are large in size, and therefore, not practical to store in the database. More client, server, and communication overhead is involved when using a BFILE data type compared with the LOB data type. It is more efficient to access a BFILE if you only need to obtain a small amount of data. It is more efficient to access database-resident LOBs if you need to obtain the entire object.

Each non-NULL OracleBFile object is associated with two entities that define the location of the underlying physical file:

An Oracle DIRECTORY object, which is a database alias for a directory in the file system, and
The file name of the underlying physical file, which is located in the directory associated with the DIRECTORY object.

Example

The following C# example demonstrates how you can create a BFILE in an Oracle table and then retrieve it in the form of an OracleBFile object. The example demonstrates using the OracleDataReader object and the OracleBFile Seek and Read methods. Note that in order to use this sample, you must first create a directory named "c:\\bfiles" and file named "MyFile.jpg" on the Oracle server.

using System;  
using System.IO;  
using System.Data;  
using System.Data.OracleClient;  
  
public class Sample  
{  
   public static void Main(string[] args)  
   {  
      OracleConnection connection = new OracleConnection(  
        "Data Source=Oracle8i;Integrated Security=yes");  
      connection.Open();  
  
      OracleCommand command = connection.CreateCommand();  
      command.CommandText =   
        "CREATE or REPLACE DIRECTORY MyDir as 'c:\\bfiles'";  
      command.ExecuteNonQuery();  
      command.CommandText =   
        "DROP TABLE MyBFileTable";  
      try {  
        command.ExecuteNonQuery();  
      }  
      catch {  
      }  
      command.CommandText =   
        "CREATE TABLE MyBFileTable(col1 number, col2 BFILE)";  
      command.ExecuteNonQuery();  
      command.CommandText =   
        "INSERT INTO MyBFileTable values ('2', BFILENAME('MyDir', " +  
        "'MyFile.jpg'))";  
      command.ExecuteNonQuery();  
      command.CommandText = "SELECT * FROM MyBFileTable";  
  
        byte[] buffer = new byte[100];  
  
      OracleDataReader reader = command.ExecuteReader();  
      using (reader) {  
          if (reader.Read()) {  
                OracleBFile bFile = reader.GetOracleBFile(1);  
                using (bFile) {  
                  bFile.Seek(0, SeekOrigin.Begin);  
                  bFile.Read(buffer, 0, 100);  
              }  
          }  
      }  
  
      connection.Close();  
   }  
  
}

Oracle LOBs

The .NET Framework Data Provider for Oracle includes the OracleLob class, which is used to work with Oracle LOB data types.

An OracleLob may be one of these OracleType data types:

Data type	Description
Blob	An Oracle BLOB data type that contains binary data with a maximum size of 4 gigabytes. This maps to an Array of type Byte.
Clob	An Oracle CLOB data type that contains character data, based on the default character set on the server, with a maximum size of 4 gigabytes. This maps to String.
NClob	An Oracle NCLOB data type that contains character data, based on the national character set on the server with a maximum size of 4 gigabytes. This maps to String.

An OracleLob differs from an OracleBFile in that the data is stored on the server instead of in a physical file in the operating system. It can also be a read-write object, unlike an OracleBFile, which is always read-only.

Creating, Retrieving, and Writing to a LOB

The following C# example demonstrates how you can create LOBs in an Oracle table, and then retrieve and write to them in the form of OracleLob objects. The example demonstrates using the OracleDataReader object and the OracleLob Read and Write methods. The example uses Oracle BLOB, CLOB, and NCLOB data types.

using System;  
using System.IO;              
using System.Text;             
using System.Data;              
using System.Data.OracleClient;  
  
// LobExample  
public class LobExample  
{  
   public static int Main(string[] args)  
   {  
      //Create a connection.  
      OracleConnection conn = new OracleConnection(  
         "Data Source=Oracle8i;Integrated Security=yes");  
      using(conn)  
      {  
         //Open a connection.  
         conn.Open();  
         OracleCommand cmd = conn.CreateCommand();  
  
         //Create the table and schema.  
         CreateTable(cmd);  
  
         //Read example.  
         ReadLobExample(cmd);  
  
         //Write example  
         WriteLobExample(cmd);  
      }  
  
      return 1;  
   }  
  
   // ReadLobExample  
   public static void ReadLobExample(OracleCommand cmd)  
   {  
      int actual = 0;  
  
      // Table Schema:  
      // "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";  
      // "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";  
      // Select some data.  
      cmd.CommandText = "SELECT * FROM tablewithlobs";  
      OracleDataReader reader = cmd.ExecuteReader();  
      using(reader)  
      {  
         //Obtain the first row of data.  
         reader.Read();  
  
         //Obtain the LOBs (all 3 varieties).  
         OracleLob blob = reader.GetOracleLob(1);  
         OracleLob clob = reader.GetOracleLob(2);  
         OracleLob nclob = reader.GetOracleLob(3);  
  
         //Example - Reading binary data (in chunks).  
         byte[] buffer = new byte[100];  
         while((actual = blob.Read(buffer, 0, buffer.Length)) >0)  
            Console.WriteLine(blob.LobType + ".Read(" + buffer + ", " +   
              buffer.Length + ") => " + actual);  
  
         // Example - Reading CLOB/NCLOB data (in chunks).  
         // Note: You can read character data as raw Unicode bytes   
         // (using OracleLob.Read as in the above example).  
         // However, because the OracleLob object inherits directly   
         // from the .NET stream object,   
         // all the existing classes that manipluate streams can   
         // also be used. For example, the   
         // .NET StreamReader makes it easier to convert the raw bytes   
         // into actual characters.  
         StreamReader streamreader =   
           new StreamReader(clob, Encoding.Unicode);  
         char[] cbuffer = new char[100];  
         while((actual = streamreader.Read(cbuffer,   
           0, cbuffer.Length)) >0)  
            Console.WriteLine(clob.LobType + ".Read(  
              " + new string(cbuffer, 0, actual) + ", " +   
              cbuffer.Length + ") => " + actual);  
  
         // Example - Reading data (all at once).  
         // You could use StreamReader.ReadToEnd to obtain   
         // all the string data, or simply  
         // call OracleLob.Value to obtain a contiguous allocation   
         // of all the data.  
         Console.WriteLine(nclob.LobType + ".Value => " + nclob.Value);  
      }  
   }  
  
   // WriteLobExample  
   public static void WriteLobExample(OracleCommand cmd)  
   {  
      //Note: Updating LOB data requires a transaction.  
      cmd.Transaction = cmd.Connection.BeginTransaction();  
  
      // Select some data.  
      // Table Schema:  
      // "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";  
      // "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";  
      cmd.CommandText = "SELECT * FROM tablewithlobs FOR UPDATE";  
      OracleDataReader reader = cmd.ExecuteReader();  
      using(reader)  
      {  
         // Obtain the first row of data.  
         reader.Read();  
  
         // Obtain a LOB.  
         OracleLob blob = reader.GetOracleLob(1/*0:based ordinal*/);  
  
         // Perform any desired operations on the LOB   
         // (read, position, and so on).  
  
         // Example - Writing binary data (directly to the backend).  
         // To write, you can use any of the stream classes, or write  
         // raw binary data using   
         // the OracleLob write method. Writing character vs. binary   
         // is the same;  
         // however note that character is always in terms of   
         // Unicode byte counts  
         // (for example, even number of bytes - 2 bytes for every  
         // Unicode character).  
         byte[] buffer = new byte[100];  
         buffer[0] = 0xCC;  
         buffer[1] = 0xDD;  
         blob.Write(buffer, 0, 2);  
         blob.Position = 0;  
         Console.WriteLine(blob.LobType + ".Write(  
           " + buffer + ", 0, 2) => " + blob.Value);  
  
         // Example - Obtaining a temp LOB and copying data   
         // into it from another LOB.  
         OracleLob templob = CreateTempLob(cmd, blob.LobType);  
         long actual = blob.CopyTo(templob);  
         Console.WriteLine(blob.LobType + ".CopyTo(  
            " + templob.Value + ") => " + actual);  
  
         // Commit the transaction now that everything succeeded.  
         // Note: On error, Transaction.Dispose is called   
         // (from the using statement)  
         // and will automatically roll back the pending transaction.  
         cmd.Transaction.Commit();  
      }  
   }  
  
   // CreateTempLob  
   public static OracleLob CreateTempLob(  
     OracleCommand cmd, OracleType lobtype)  
   {  
      //Oracle server syntax to obtain a temporary LOB.  
      cmd.CommandText = "DECLARE A " + lobtype + "; "+  
                     "BEGIN "+  
                        "DBMS_LOB.CREATETEMPORARY(A, FALSE); "+  
                        ":LOC := A; "+  
                     "END;";  
  
      //Bind the LOB as an output parameter.  
      OracleParameter p = cmd.Parameters.Add("LOC", lobtype);  
      p.Direction = ParameterDirection.Output;  
  
      //Execute (to receive the output temporary LOB).  
      cmd.ExecuteNonQuery();  
  
      //Return the temporary LOB.  
      return (OracleLob)p.Value;  
   }  
  
   // CreateTable  
   public static void CreateTable(OracleCommand cmd)  
   {  
      // Table Schema:  
      // "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";  
      // "INSERT INTO tablewithlobs VALUES (1, 'AA', 'AAA', N'AAAA')";  
      try  
      {  
         cmd.CommandText   = "DROP TABLE tablewithlobs";  
         cmd.ExecuteNonQuery();  
      }  
      catch(Exception)  
      {  
      }  
  
      cmd.CommandText =   
        "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";  
      cmd.ExecuteNonQuery();  
      cmd.CommandText =   
        "INSERT INTO tablewithlobs VALUES (1, 'AA', 'AAA', N'AAAA')";  
      cmd.ExecuteNonQuery();  
   }  
}

Creating a Temporary LOB

The following C# example demonstrates how to create a temporary LOB.

OracleConnection conn = new OracleConnection(  
  "server=test8172; integrated security=yes;");  
conn.Open();  
  
OracleTransaction tx = conn.BeginTransaction();  
  
OracleCommand cmd = conn.CreateCommand();  
cmd.Transaction = tx;  
cmd.CommandText =   
  "declare xx blob; begin dbms_lob.createtemporary(  
  xx, false, 0); :tempblob := xx; end;";  
cmd.Parameters.Add(new OracleParameter("tempblob",  
  OracleType.Blob)).Direction = ParameterDirection.Output;  
cmd.ExecuteNonQuery();  
OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;  
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);  
tempLob.Write(tempbuff,0,tempbuff.Length);  
tempLob.EndBatch();  
cmd.Parameters.Clear();  
cmd.CommandText = "myTable.myProc";  
cmd.CommandType = CommandType.StoredProcedure;    
cmd.Parameters.Add(new OracleParameter(  
  "ImportDoc", OracleType.Blob)).Value = tempLob;  
cmd.ExecuteNonQuery();  
  
tx.Commit();

Oracle REF CURSORs

The .NET Framework Data Provider for Oracle supports the Oracle REF CURSOR data type. When using the data provider to work with Oracle REF CURSORs, you should consider the following behaviors.

Note

Some behaviors differ from those of the Microsoft OLE DB Provider for Oracle (MSDAORA).

For performance reasons, the Data Provider for Oracle does not automatically bind REF CURSOR data types, as MSDAORA does, unless you explicitly specify them.
The data provider does not support any ODBC escape sequences, including the {resultset} escape used to specify REF CURSOR parameters.
To execute a stored procedure that returns REF CURSORs, you must define the parameters in the OracleParameterCollection with an OracleType of Cursor and a Direction of Output. The data provider supports binding REF CURSORs as output parameters only. The provider does not support REF CURSORs as input parameters.
Obtaining an OracleDataReader from the parameter value is not supported. The values are of type DBNull after command execution.
The only CommandBehavior enumeration value that works with REF CURSORs (for example, when calling ExecuteReader) is CloseConnection; all others are ignored.
The order of REF CURSORs in the OracleDataReader depends on the order of the parameters in the OracleParameterCollection. The ParameterName property is ignored.
The PL/SQL TABLE data type is not supported. However, REF CURSORs are more efficient. If you must use a TABLE data type, use the OLE DB .NET Data Provider with MSDAORA.

In This Section

REF CURSOR Examples
Contains three examples that demonstrate using REF CURSORs.

REF CURSOR Parameters in an OracleDataReader
Demonstrates how to execute a PL/SQL stored procedure that returns a REF CURSOR parameter, and reads the value as an OracleDataReader.

Retrieving Data from Multiple REF CURSORs Using an OracleDataReader
Demonstrates how to execute a PL/SQL stored procedure that returns two REF CURSOR parameters, and reads the values using an OracleDataReader.

Filling a DataSet Using One or More REF CURSORs
Demonstrates how to execute a PL/SQL stored procedure that returns two REF CURSOR parameters, and fills a DataSet with the rows that are returned.

REF CURSOR Examples

The REF CURSOR examples are comprised of the following three Microsoft Visual Basic examples that demonstrate using REF CURSORs.

Sample	Description
REF CURSOR Parameters in an OracleDataReader	This example executes a PL/SQL stored procedure that returns a REF CURSOR parameter, and reads the value as an OracleDataReader.
Retrieving Data from Multiple REF CURSORs Using an OracleDataReader	This example executes a PL/SQL stored procedure that returns two REF CURSOR parameters, and reads the values using an OracleDataReader.
Filling a DataSet Using One or More REF CURSORs	This example executes a PL/SQL stored procedure that returns two REF CURSOR parameters, and fills a DataSet with the rows that are returned.

To use these examples, you may need to create the Oracle tables, and you must create a PL/SQL package and package body.

Creating the Oracle Tables

These examples use tables that are defined in the Oracle Scott/Tiger schema. The Oracle Scott/Tiger schema is included with most Oracle installations. If this schema does not exist, you can use the SQL commands file in {OracleHome}\rdbms\admin\scott.sql to create the tables and indexes used by these examples.

Creating the Oracle Package and Package Body

These examples require the following PL/SQL package and package body on your server. Create the following Oracle package on the Oracle server.

SQL

CREATE OR REPLACE PACKAGE CURSPKG AS   
    TYPE T_CURSOR IS REF CURSOR;   
    PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,   
                               IO_CURSOR IN OUT T_CURSOR);   
    PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,   
                                DEPTCURSOR OUT T_CURSOR);  
END CURSPKG;  
/

Create the following Oracle package body on the Oracle server.

SQL

CREATE OR REPLACE PACKAGE BODY CURSPKG AS  
    PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,  
                               IO_CURSOR IN OUT T_CURSOR)  
    IS   
        V_CURSOR T_CURSOR;   
    BEGIN   
        IF N_EMPNO <> 0   
        THEN  
             OPEN V_CURSOR FOR   
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME   
                  FROM EMP, DEPT   
                  WHERE EMP.DEPTNO = DEPT.DEPTNO   
                  AND EMP.EMPNO = N_EMPNO;  
  
        ELSE   
             OPEN V_CURSOR FOR   
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME   
                  FROM EMP, DEPT   
                  WHERE EMP.DEPTNO = DEPT.DEPTNO;  
  
        END IF;  
        IO_CURSOR := V_CURSOR;   
    END OPEN_ONE_CURSOR;   
  
    PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,  
                                DEPTCURSOR OUT T_CURSOR)  
    IS   
        V_CURSOR1 T_CURSOR;   
        V_CURSOR2 T_CURSOR;   
    BEGIN   
        OPEN V_CURSOR1 FOR SELECT * FROM EMP;  
        OPEN V_CURSOR2 FOR SELECT * FROM DEPT;  
        EMPCURSOR  := V_CURSOR1;   
        DEPTCURSOR := V_CURSOR2;   
    END OPEN_TWO_CURSORS;   
END CURSPKG;  
/

REF CURSOR Parameters in an OracleDataReader

This Microsoft Visual Basic example executes a PL/SQL stored procedure that returns a REF CURSOR parameter, and reads the value as an OracleDataReader.

Private Sub Button1_Click(ByVal sender As Object, _  
  ByVal e As System.EventArgs) Handles Button1.Click  
  
  Dim connString As New String(_  
      "Data Source=Oracle9i;User ID=scott;Password=tiger;")  
  Using conn As New OracleConnection(connString)  
    Dim cmd As New OracleCommand()  
    Dim rdr As OracleDataReader  
  
    conn.Open()  
    cmd.Connection = conn  
    cmd.CommandText = "CURSPKG.OPEN_ONE_CURSOR"  
    cmd.CommandType = CommandType.StoredProcedure  
    cmd.Parameters.Add(New OracleParameter(  
      "N_EMPNO", OracleType.Number)).Value = 7369  
    cmd.Parameters.Add(New OracleParameter(  
      "IO_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output  
  
    rdr = cmd.ExecuteReader()  
    While (rdr.Read())  
        REM do something with the values  
    End While  
  
    rdr.Close()  
  End Using  
End Sub

Retrieving Data from Multiple REF CURSORs Using an OracleDataReader

This Microsoft Visual Basic example executes a PL/SQL stored procedure that returns two REF CURSOR parameters, and reads the values using an OracleDataReader.

Private Sub Button1_Click( _  
  ByVal sender As Object, ByVal e As System.EventArgs)  _  
  Handles Button1.Click  
  
  Dim connString As New String( _  
      "Data Source=Oracle9i;User ID=scott;Password=tiger;")  
  Using conn As New OracleConnection(connString)  
    Dim cmd As New OracleCommand()  
    Dim rdr As OracleDataReader  
  
    conn.Open()  
    cmd.Connection = conn  
    cmd.CommandText = "CURSPKG.OPEN_TWO_CURSORS"  
    cmd.CommandType = CommandType.StoredProcedure  
    cmd.Parameters.Add(New OracleParameter( _  
      "EMPCURSOR", OracleType.Cursor)).Direction = _  
      ParameterDirection.Output  
    cmd.Parameters.Add(New OracleParameter(_  
      "DEPTCURSOR", OracleType.Cursor)).Direction = _  
      ParameterDirection.Output  
  
    rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
    While (rdr.Read())  
        REM do something with the values from the EMP table   
    End While  
  
    rdr.NextResult()  
    While (rdr.Read())  
        REM do something with the values from the DEPT table   
    End While  
    rdr.Close()  
  End Using  
End Sub

Filling a DataSet Using One or More REF CURSORs

This Microsoft Visual Basic example executes a PL/SQL stored procedure that returns two REF CURSOR parameters, and fills a DataSet with the rows that are returned.

Private Sub Button1_Click(ByVal sender As Object, _  
  ByVal e As System.EventArgs) Handles Button1.Click  
  
  Dim connString As New String(_  
    "Data Source=Oracle9i;User ID=scott;Password=tiger;")  
  Dim ds As New DataSet()  
    Using conn As New OracleConnection(connString)  
    Dim cmd As New OracleCommand()  
  
    cmd.Connection = conn  
    cmd.CommandText = "CURSPKG.OPEN_TWO_CURSORS"  
    cmd.CommandType = CommandType.StoredProcedure  
    cmd.Parameters.Add(New OracleParameter( _  
      "EMPCURSOR", OracleType.Cursor)).Direction = _  
      ParameterDirection.Output  
    cmd.Parameters.Add(New OracleParameter( _  
      "DEPTCURSOR", OracleType.Cursor)).Direction = _  
       ParameterDirection.Output  
  
    Dim da As New OracleDataAdapter(cmd)  
    da.TableMappings.Add("Table", "Emp")  
    da.TableMappings.Add("Table1", "Dept")  
    da.Fill(ds)  
  
    ds.Relations.Add("EmpDept", ds.Tables("Dept").Columns("Deptno"), _  
      ds.Tables("Emp").Columns("Deptno"), False)  
  
    DataGrid1.DataSource = ds.Tables("Dept")  
  End Using

OracleTypes

The .NET Framework Data Provider for Oracle includes several structures you can use to work with Oracle data types. These include OracleNumber and OracleString.

Note

For a complete list of these structures, see System.Data.OracleClient.

The following C# examples:

Create an Oracle table and load it with data.
Use an OracleDataReader to access the data, and use several OracleType structures to display the data.

Creating an Oracle Table

This example creates an Oracle table and loads it with data. You must run this example before running the next example.

public void Setup(string connectionString)  
   {  
   OracleConnection conn = new OracleConnection(connectionString);  
   try  
      {  
      conn.Open();  
      OracleCommand cmd = conn.CreateCommand();  
      cmd.CommandText ="CREATE TABLE OracleTypesTable " +  
        "(MyVarchar2 varchar2(3000),MyNumber number(28,4) " +  
        "PRIMARY KEY ,MyDate date, MyRaw raw(255))";  
      cmd.ExecuteNonQuery();  
      cmd.CommandText ="INSERT INTO OracleTypesTable VALUES " +  
        "( 'test', 2, to_date('2000-01-11 12:54:01','yyyy-mm-dd " +  
        "hh24:mi:ss'), '0001020304' )";  
      cmd.ExecuteNonQuery();  
      }  
   catch(Exception)  
   {  
   }  
   finally  
   {  
      conn.Close();  
   }  
}

Retrieving Data from the Oracle Table

This example uses an OracleDataReader to access the data, and uses several OracleType structures to display the data.

public void ReadOracleTypesExample(string connectionString)  
   {  
   OracleConnection myConnection =   
      new OracleConnection(connectionString);  
   myConnection.Open();  
   OracleCommand myCommand = myConnection.CreateCommand();  
  
   try  
      {  
      myCommand.CommandText = "SELECT * from OracleTypesTable";  
      OracleDataReader oracledatareader1 = myCommand.ExecuteReader();  
      oracledatareader1.Read();  
  
      //Using the oracle specific getters for each type is faster than  
      //using GetOracleValue.  
  
      //First column, MyVarchar2, is a VARCHAR2 data type in Oracle  
      //Server and maps to OracleString.  
      OracleString oraclestring1 =   
        oracledatareader1.GetOracleString(0);  
      Console.WriteLine("OracleString " + oraclestring1.ToString());  
  
      //Second column, MyNumber, is a NUMBER data type in Oracle Server  
      //and maps to OracleNumber.  
      OracleNumber oraclenumber1 =   
        oracledatareader1.GetOracleNumber(1);  
      Console.WriteLine("OracleNumber " + oraclenumber1.ToString());  
  
      //Third column, MyDate, is a DATA data type in Oracle Server  
      //and maps to OracleDateTime.  
      OracleDateTime oracledatetime1 =   
        oracledatareader1.GetOracleDateTime(2);  
      Console.WriteLine("OracleDateTime " + oracledatetime1.ToString());  
  
      //Fourth column, MyRaw, is a RAW data type in Oracle Server and  
      //maps to OracleBinary.  
      OracleBinary oraclebinary1 =   
        oracledatareader1.GetOracleBinary(3);  
      //Calling value on a null OracleBinary throws  
      //OracleNullValueException; therefore, check for a null value.  
      if (oraclebinary1.IsNull==false)  
      {  
         foreach(byte b in oraclebinary1.Value)  
         {  
            Console.WriteLine("byte " + b.ToString());  
         }  
      }  
      oracledatareader1.Close();  
   }  
   catch(Exception e)  
   {  
       Console.WriteLine(e.ToString());  
   }  
   finally  
   {  
       myConnection.Close();  
   }  
}

Oracle Sequences

The .NET Framework Data Provider for Oracle provides support for retrieving the server-generated key Oracle Sequence values after performing inserts by using the OracleDataAdapter.

SQL Server and Oracle support the creation of automatically incrementing columns that can be designated as primary keys. These values are generated by the server as rows are added to a table. In SQL Server, you set the Identity property of a column; in Oracle you create a Sequence. The difference between auto-increment columns in SQL Server and sequences in Oracle is that:

In SQL Server, you mark a column as an auto-increment column and SQL Server automatically generates new values for the column when you insert a new row.
In Oracle, you create a sequence to generate new values for a column in your table, but there is no direct link between the sequence and the table or column. An Oracle sequence is an object, like a table or a stored procedure.

When you create a sequence in an Oracle database, you can define its initial value and the increment between its values. You can also query the sequence for new values before submitting new rows. That means your code can recognize the key values for new rows before you insert them into the database.

For more information about creating auto-increment columns by using SQL Server and ADO.NET, see Retrieving Identity or Autonumber Values and Creating AutoIncrement Columns.

Example

The following C# example demonstrates how you can retrieve new sequence values from Oracle database. The example references the sequence in the INSERT INTO query used to submit the new rows, and then returns the sequence value generated using the RETURNING clause introduced in Oracle10g. The example adds a series of pending new rows in a DataTable by using ADO.NET’s auto-increment functionality to generate "placeholder" primary key values. Note that the increment value ADO.NET generated for the new row is just a "placeholder". That means the database might generate different values from the ones ADO.NET generates.

Before submitting the pending inserts to the database, the example displays the contents of the rows. Then, the code creates a new OracleDataAdapter object and sets its InsertCommand and the UpdateBatchSize properties. The example also supplies the logic to return the server-generated values by using output parameters. Then, the example executes the update to submit the pending rows and displays the contents of the DataTable.

public void OracleSequence(String connectionString)  
{  
   String insertString =   
      "INSERT INTO SequenceTest_Table (ID, OtherColumn)" +  
      "VALUES (SequenceTest_Sequence.NEXTVAL, :OtherColumn)" +  
      "RETURNING ID INTO :ID";  
  
   using (OracleConnection conn = new OracleConnection(connectionString))  
   {  
      //Open a connection.  
      conn.Open();  
      OracleCommand cmd = conn.CreateCommand();  
  
      // Prepare the database.  
      cmd.CommandText = "DROP SEQUENCE SequenceTest_Sequence";  
      try { cmd.ExecuteNonQuery(); } catch { }  
  
      cmd.CommandText = "DROP TABLE SequenceTest_Table";  
      try { cmd.ExecuteNonQuery(); } catch { }  
  
      cmd.CommandText = "CREATE TABLE SequenceTest_Table " +  
                     "(ID int PRIMARY KEY, OtherColumn varchar(255))";  
      cmd.ExecuteNonQuery();  
  
      cmd.CommandText = "CREATE SEQUENCE SequenceTest_Sequence " +  
                        "START WITH 100 INCREMENT BY 5";  
      cmd.ExecuteNonQuery();  
  
      DataTable testTable = new DataTable();  
      DataColumn column = testTable.Columns.Add("ID", typeof(int));  
      column.AutoIncrement = true;  
      column.AutoIncrementSeed = -1;  
      column.AutoIncrementStep = -1;  
      testTable.PrimaryKey = new DataColumn[] { column };  
      testTable.Columns.Add("OtherColumn", typeof(string));  
      for (int rowCounter = 1; rowCounter <= 15; rowCounter++)  
      {  
         testTable.Rows.Add(null, "Row #" + rowCounter.ToString());  
      }  
  
      Console.WriteLine("Before Update => ");  
      foreach (DataRow row in testTable.Rows)  
      {  
         Console.WriteLine("   {0} - {1}", row["ID"], row["OtherColumn"]);  
      }  
      Console.WriteLine();  
  
      cmd.CommandText =   
        "SELECT ID, OtherColumn FROM SequenceTest_Table";  
      OracleDataAdapter da = new OracleDataAdapter(cmd);  
      da.InsertCommand = new OracleCommand(insertString, conn);  
      da.InsertCommand.Parameters.Add(":ID", OracleType.Int32, 0, "ID");  
      da.InsertCommand.Parameters[0].Direction = ParameterDirection.Output;  
      da.InsertCommand.Parameters.Add(":OtherColumn", OracleType.VarChar, 255, "OtherColumn");  
      da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;  
      da.UpdateBatchSize = 10;  
  
      da.Update(testTable);  
  
      Console.WriteLine("After Update => ");  
      foreach (DataRow row in testTable.Rows)  
      {  
         Console.WriteLine("   {0} - {1}", row["ID"], row["OtherColumn"]);  
      }  
      // Close the connection.  
      conn.Close();  
   }  
}

Oracle Data Type Mappings

The following table lists Oracle data types and their mappings to the OracleDataReader.

Oracle data type	.NET Framework data type returned by OracleDataReader.GetValue	OracleClient data type returned by OracleDataReader.GetOracleValue	Remarks
BFILE	Byte[]	OracleBFile
BLOB	Byte[]	OracleLob
CHAR	String	OracleString
CLOB	String	OracleLob
DATE	DateTime	OracleDateTime
FLOAT	Decimal	OracleNumber	This data type is an alias for the NUMBER data type, and is designed so that the OracleDataReader returns a System.Decimal or OracleNumber instead of a floating-point value. Using the .NET Framework data type can cause an overflow.
INTEGER	Decimal	OracleNumber	This data type is an alias for the NUMBER(38) data type, and is designed so that the OracleDataReader returns a System.Decimal or OracleNumber instead of an integer value. Using the .NET Framework data type can cause an overflow.
INTERVAL YEAR TO MONTH	Int32	OracleMonthSpan
INTERVAL DAY TO SECOND	TimeSpan	OracleTimeSpan
LONG	String	OracleString
LONG RAW	Byte[]	OracleBinary
NCHAR	String	OracleString
NCLOB	String	OracleLob
NUMBER	Decimal	OracleNumber	Using the .NET Framework data type can cause an overflow.
NVARCHAR2	String	OracleString
RAW	Byte[]	OracleBinary
REF CURSOR	The Oracle REF CURSOR data type is not supported by the OracleDataReader object.
ROWID	String	OracleString
TIMESTAMP	DateTime	OracleDateTime
TIMESTAMP WITH LOCAL TIME ZONE	DateTime	OracleDateTime
TIMESTAMP WITH TIME ZONE	DateTime	OracleDateTime
UNSIGNED INTEGER	Number	OracleNumber	This data type is an alias for the NUMBER(38) data type, and is designed so that the OracleDataReader returns a System.Decimal or OracleNumber instead of an unsigned integer value. Using the .NET Framework data type can cause an overflow.
VARCHAR2	String	OracleString

The following table lists Oracle data types and the .NET Framework data types (System.Data.DbType and OracleType) to use when binding them as parameters.

Oracle data type	DbType enumeration to bind as a parameter	OracleType enumeration to bind as a parameter	Remarks
BFILE	BFile	Oracle only allows binding a BFILE as a BFILE parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-BFILE value, such as byte[] or OracleBinary.
BLOB	Blob	Oracle only allows binding a BLOB as a BLOB parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-BLOB value, such as byte[] or OracleBinary.
CHAR	AnsiStringFixedLength	Char
CLOB	Clob	Oracle only allows binding a CLOB as a CLOB parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-CLOB value, such as System.String or OracleString.
DATE	DateTime	DateTime
FLOAT	Single, Double, Decimal	Float, Double, Number	Size determines the System.Data.DBType and OracleType.
INTEGER	SByte, Int16, Int32, Int64, Decimal	SByte, Int16, Int32, Number	Size determines the System.Data.DBType and OracleType.
INTERVAL YEAR TO MONTH	Int32	IntervalYearToMonth	OracleType is only available when using both Oracle 9i client and server software.
INTERVAL DAY TO SECOND	Object	IntervalDayToSecond	OracleType is only available when using both Oracle 9i client and server software.
LONG	AnsiString	LongVarChar
LONG RAW	Binary	LongRaw
NCHAR	StringFixedLength	NChar
NCLOB	NClob	Oracle only allows binding a NCLOB as a NCLOB parameter. The .NET Data Provider for Oracle does not automatically construct one for you if you attempt to bind a non-NCLOB value, such as System.String or OracleString.
NUMBER	VarNumeric	Number
NVARCHAR2	String	NVarChar
RAW	Binary	Raw
REF CURSOR	Cursor	For more information, see Oracle REF CURSORs.
ROWID	AnsiString	Rowid
TIMESTAMP	DateTime	Timestamp	OracleType is only available when using both Oracle 9i client and server software.
TIMESTAMP WITH LOCAL TIME ZONE	DateTime	TimestampLocal	OracleType is only available when using both Oracle 9i client and server software.
TIMESTAMP WITH TIME ZONE	DateTime	TimestampWithTz	OracleType is only available when using both Oracle 9i client and server software.
UNSIGNED INTEGER	Byte, UInt16, UInt32, UInt64, Decimal	Byte, UInt16, Uint32, Number	Size determines the System.Data.DBType and OracleType.
VARCHAR2	AnsiString	VarChar

The InputOutput, Output, and ReturnValue ParameterDirection values used by the Value property of the OracleParameter object are .NET Framework data types, unless the input value is an Oracle data type (for example, OracleNumber or OracleString). This does not apply to REF CURSOR, BFILE, or LOB data types.

Oracle Distributed Transactions

The OracleConnection object automatically enlists in an existing distributed transaction if it determines that a transaction is active. Automatic transaction enlistment occurs when the connection is opened or retrieved from the connection pool. You can disable auto-enlistment in existing transactions by specifying

Enlist=false

as a connection string parameter for an OracleConnection.

Content

ADO.NET Oracle

In This Section

Related Sections

See also

System Requirements for the .NET Framework Data Provider for Oracle

Working with the Data Provider for Oracle and Unicode Data

Setting the Unicode Value in a Connection String Attribute

Mixing Versions of Oracle Client and Oracle Server

Working with UTF-8 Data

OracleLob Can Only Update Full Characters.

See also

Oracle BFILEs

Example

See also

Oracle LOBs

Creating, Retrieving, and Writing to a LOB

Creating a Temporary LOB

See also

Oracle REF CURSORs

In This Section

See also

REF CURSOR Examples

Creating the Oracle Tables

Creating the Oracle Package and Package Body

See also

REF CURSOR Parameters in an OracleDataReader

See also

Retrieving Data from Multiple REF CURSORs Using an OracleDataReader

See also

Filling a DataSet Using One or More REF CURSORs

See also

OracleTypes

Creating an Oracle Table

Retrieving Data from the Oracle Table

See also

Oracle Sequences

Example

See also

Oracle Data Type Mappings

See also

Oracle Distributed Transactions

See also

Source/Reference