OracleDataReader Class

An OracleDataReader object represents a forward-only, read-only, in-memory result set.

Unlike the DataSet, the OracleDataReader stays connected and fetches one row at a time.

The following section contain related information:

• "Obtaining LONG and LONG RAW Data".

• "Obtaining Data From an OracleDataReader".

Class Inheritance

Object

  MarshalByRefObject

    OracleDataReader

Declaration

// C#
public sealed class OracleDataReader : MarshalByRefObject, IEnumerable, 
   IDataReader, IDisposable, IDataRecord

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

An OracleDataReader instance is constructed by a call to the ExecuteReader method of the OracleCommand object. The only properties that can be accessed after the DataReader is closed or has been disposed, are IsClosed and RecordsAffected.

Example

The following OracleDataReader example retrieves the data from the EMP table:

/* Database Setup
 
CREATE TABLE empInfo (
empno NUMBER(4) PRIMARY KEY,
empName VARCHAR2(20) NOT NULL,
hiredate DATE,
salary NUMBER(7,2),
jobDescription Clob,
byteCodes BLOB
);
 
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION) values 
(1,'KING','SOFTWARE ENGR');
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION) values 
(2,'SCOTT','MANAGER');
commit;
 
*/
 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class OracleDataReaderSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    string cmdstr = "SELECT * FROM EMPINFO";
    OracleConnection connection = new OracleConnection(constr);
    OracleCommand cmd = new OracleCommand(cmdstr, con);
 
    OracleDataReader reader = cmd.ExecuteReader();
 
    // Declare the variables to retrieve the data in EmpInfo
    short empNo;
    string empName;
    DateTime hireDate;
    double salary;
    string jobDesc;
    byte[] byteCodes = new byte[10];
 
    // Read the next row until end of row
    while (reader.Read())
    {
      empNo = reader.GetInt16(0);
      Console.WriteLine("Employee number: " + empNo);
      empName = reader.GetString(1);
      Console.WriteLine("Employee name: " + empName);
 
      // The following columns can have NULL value, so it
      //   is important to call IsDBNull before getting the column data
      if (!reader.IsDBNull(2))
      {
        hireDate = reader.GetDateTime(2);
        Console.WriteLine("Hire date: " + hireDate);
      }
 
      if (!reader.IsDBNull(3))
      {
        salary = reader.GetDouble(3);
        Console.WriteLine("Salary: " + salary);
      }
 
      if (!reader.IsDBNull(4))
      {
        jobDesc = reader.GetString(4);
        Console.WriteLine("Job Description: " + jobDesc);
      }
 
      if (!reader.IsDBNull(5))
      {
        long len = reader.GetBytes(5, 0, byteCodes, 0, 10);
 
        Console.Write("Byte codes: ");
        for (int i = 0; i < len; i++)
          Console.Write(byteCodes[i].ToString("x"));
 
        Console.WriteLine();
      }
 
      Console.WriteLine();
    } 
 
    // Clean up
    reader.Dispose();
    con.Dispose();
  }
}

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

---

OracleDataReader Members

OracleDataReader members are listed in the following tables:

OracleDataReader Static Methods

OracleDataReader static methods are listed in Table 4-42.

OracleDataReader Properties

OracleDataReader properties are listed in Table 4-43.

OracleDataReader Public Methods

OracleDataReader public methods are listed in Table 4-44.

---

OracleDataReader Static Methods

OracleDataReader static methods are listed in Table 4-45.

---

OracleDataReader Properties

OracleDataReader properties are listed in Table 4-46.

Depth

This property gets a value indicating the depth of nesting for the current row.

Declaration

// C#
public int Depth {get;}

Property Value

The depth of nesting for the current row.

Implements

IDataReader

Exceptions

InvalidOperationException - The reader is closed.

Remarks

Default = 0

This property always returns zero because Oracle does not support nesting.

FetchSize

This property specifies the size of OracleDataReader's internal cache.

Declaration

// C#
public long FetchSize {get; set;} 

Property Value

A long that specifies the amount of memory (in bytes) that the OracleDataReader uses for its internal cache.

Exceptions

ArgumentException - The FetchSize value specified is invalid.

Remarks

Default = The OracleCommand's FetchSize property value.

The FetchSize property is inherited by the OracleDataReader that is created by a command execution returning a result set. The FetchSize property on the OracleDataReader object determines the amount of data fetched into its internal cache for each server round-trip.

FieldCount

This property gets the number of columns in the result set.

Declaration

// C#
public int FieldCount {get;}

Property Value

The number of columns in the result set if one exists, otherwise 0.

Implements

IDataRecord

Exceptions

InvalidOperationException - The reader is closed.

Remarks

Default = 0

This property has a value of 0 for queries that do not return result sets.

HasRows

This property indicates whether the OracleDataReader has one or more rows.

Declaration

// C#
public bool HasRows (get;);

Return Value

bool

Remarks

HasRows indicates whether the OracleDataReader has any rows.

The value of HasRows does not change based on the row position. For example, even if the application has read all the rows from the result set and the next Read method invocation will return false, the HasRows property still returns true since the result set was not empty to begin with.

Rows are fetched to determined the emptiness of the OracleDataReader when HasRows property is accessed for the first time after the creation of the OracleDataReader object.

Example

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class HasRowsSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = new OracleCommand(
      "select * from emp where empno = 9999", con);
 
    OracleDataReader reader = cmd.ExecuteReader();
 
    if (!reader.HasRows)
      Console.WriteLine("The result set is empty.");
    else
      Console.WriteLine("The result set is not empty.");
 
    con.Dispose();
  }
}

IsClosed

This property indicates whether the data reader is closed.

Declaration

// C#
public bool IsClosed {get;}

Property Value

If the OracleDataReader is in a closed state, returns true; otherwise, returns false.

Implements

IDataReader

Remarks

Default = true

IsClosed and RecordsAffected are the only two properties that are accessible after the OracleDataReader is closed.

Item

This property gets the value of the column in .NET datatype.

Overload List:

• Item [index]

  This property gets the .NET Value of the column specified by the column index.

• Item [string]

  This property gets the .NET Value of the column specified by the column name.

Item [index]

This property gets the .NET Value of the column specified by the column index.

Declaration

// C#
public object this[int index] {get;}

Parameters

• index

  The zero-based index of the column.

Property Value

The .NET value of the specified column.

Implements

IDataRecord

Remarks

Default = Not Applicable

In C#, this property is the indexer for this class.

Item [string]

This property gets the .NET Value of the column specified by the column name.

Declaration

// C#
public object this[string columnName] {get;}

Parameters

• columnName

  The name of the column.

Property Value

The .NET Value of the specified column.

Implements

IDataRecord

Remarks

Default = Not Applicable

A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.

In C#, this property is the indexer for this class.

InitialLOBFetchSize

This property specifies the amount that the OracleDataReader initially fetches for LOB columns.

Declaration

// C#
public int InitialLOBFetchSize {get;}

Property Value

The size of the chunk to retrieve.

Exceptions

InvalidOperationException - The reader is closed.

Remarks

The maximum value supported for InitialLOBFetchSize is 32767. If this property is set to a higher value, the provider resets it to 32767.

Default is the OracleCommand.InitialLOBFetchSize, from which this value is inherited.

InitialLONGFetchSize

This property specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns.

Declaration

// C#
public long InitialLONGFetchSize {get;}

Property Value

The size of the chunk to retrieve. The default is 0.

Exceptions

InvalidOperationException - The reader is closed.

Remarks

The maximum value supported for InitialLONGFetchSize is 32767. If this property is set to a higher value, the provider resets it to 32767.

Default is OracleCommand.InitialLONGFetchSize, from which this value is inherited.

This property is read-only for the OracleDataReader.

RecordsAffected

This property gets the number of rows changed, inserted, or deleted by execution of the SQL statement.

Declaration

// C#
public int RecordsAffected {get;}

Property Value

The number of rows affected by execution of the SQL statement.

Implements

IDataReader

Remarks

Default = 0

The value of -1 is returned for SELECT statements.

IsClosed and RecordsAffected are the only two properties that are accessible after the OracleDataReader is closed.

---

OracleDataReader Public Methods

OracleDataReader public methods are listed in Table 4-47.

Close

This method closes the OracleDataReader.

Declaration

// C#
public void Close();

Implements

IDataReader

Remarks

The Close method frees all resources associated with the OracleDataReader.

Example

The code example for the OracleDataReader class includes the Close method. See OracleDataReader Overview "Example".

Dispose

This method releases any resources or memory allocated by the object.

Declaration

// C#
public void Dispose();

Implements

IDisposable

Remarks

The Dispose method also closes the OracleDataReader.

GetByte

This method returns the byte value of the specified column.

Declaration

// C#
public byte GetByte(int index);

Parameters

• index

  The zero-based column index.

Return Value

The value of the column as a byte.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetBytes

This method populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column.

Declaration

// C#
public long GetBytes(int index, long fieldOffset, byte[] buffer, 
   int bufferOffset, int length);

Parameters

• index

  The zero-based column index.

• fieldOffset

  The offset within the column from which reading begins (in bytes).

• buffer

  The byte array that the data is read into.

• bufferOffset

  The offset within the buffer to begin reading data into (in bytes).

• length

  The maximum number of bytes to read (in bytes).

Return Value

The number of bytes read.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

This method returns the number of bytes read into the buffer. This may be less than the actual length of the field if the method has been called previously for the same column.

If a null reference is passed for buffer, the length of the field in bytes is returned.

IsDBNull should be called to check for NULL values before calling this method.

GetChars

This method populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column.

Declaration

// C#
public long GetChars(int index, long fieldOffset, char[] buffer,
    int bufferOffset, int length);

Parameters

• index

  The zero based column index.

• fieldOffset

  The index within the column from which to begin reading (in characters).

• buffer

  The character array that the data is read into.

• bufferOffset

  The index within the buffer to begin reading data into (in characters).

• length

  The maximum number of characters to read (in characters).

Return Value

The number of characters read.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

This method returns the number of characters read into the buffer. This may be less than the actual length of the field, if the method has been called previously for the same column.

If a null reference is passed for buffer, the length of the field in characters is returned.

IsDBNull should be called to check for NULL values before calling this method.

GetDataTypeName

This method returns the ODP.NET type name of the specified column.

Declaration

// C#
public string GetDataTypeName(int index);

Parameters

• index

  The zero-based column index.

Return Value

The name of the ODP.NET type of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The reader is closed.

IndexOutOfRangeException - The column index is invalid.

GetDateTime

This method returns the DateTime value of the specified column.

Declaration

// C#
public DateTime GetDateTime(int index);

Parameters

• index

  The zero-based column index.

Return Value

The DateTime value of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetDecimal

This method returns the decimal value of the specified NUMBER column.

Declaration

// C#
public decimal GetDecimal(int index);

Parameters

• index

  The zero-based column index.

Return Value

The decimal value of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetDouble

This method returns the double value of the specified NUMBER column or BINARY_DOUBLE column.

Declaration

// C#
public double GetDouble(int index);

Parameters

• index

  The zero-based column index.

Return Value

The double value of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

Starting with Oracle Database 10g, GetDouble now supports retrieval of data from BINARY_DOUBLE columns.

GetFieldType

This method returns the Type of the specified column.

Declaration

// C#
public Type GetFieldType(int index);

Parameters

• index

  The zero-based column index.

Return Value

The Type of the default .NET type of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The reader is closed.

IndexOutOfRangeException - The column index is invalid.

GetFloat

This method returns the float value of the specified NUMBER column or BINARY_FLOAT column.

Declaration

// C#
public float GetFloat(int index);

Parameters

• index

  The zero-based column index.

Return Value

The float value of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

Starting with Oracle Database 10g, GetFloat now supports retrieval of data from BINARY_FLOAT columns.

GetInt16

This method returns the Int16 value of the specified NUMBER column.

Declaration

// C#
public short GetInt16(int index);

Parameters

• index

  The zero-based column index.

Return Value

The Int16 value of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetInt32

This method returns the Int32 value of the specified NUMBER column.

Declaration

// C#
public int GetInt32(int index);

Parameters

• index

  The zero-based column index.

Return Value

The Int32 value of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetInt64

This method returns the Int64 value of the specified NUMBER column.

Declaration

// C#
public long GetInt64(int index);

Parameters

• index

  The zero-based column index.

Return Value

The Int64 value of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetName

This method returns the name of the specified column.

Declaration

// C#
public string GetName(int index);

Parameters

• index

  The zero-based column index.

Return Value

The name of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The reader is closed.

IndexOutOfRangeException - The column index is invalid.

GetOracleBFile

This method returns an OracleBFile object of the specified BFILE column.

Declaration

// C#
public OracleBFile GetOracleBFile(int index);

Parameters

• index

  The zero-based column index.

Return Value

The OracleBFile value of the column.

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetOracleBinary

This method returns an OracleBinary structure of the specified column.

Declaration

// C#
public OracleBinary GetOracleBinary(int index);

Parameters

• index

  The zero-based column index.

Return Value

The OracleBinary value of the column.

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetOracleBinary is used on the following Oracle types:

• BFILE

• BLOB

• LONG RAW

• RAW

GetOracleBlob

This method returns an OracleBlob object of the specified BLOB column.

Declaration

// C#
public OracleBlob GetOracleBlob(int index);

Parameters

• index

  The zero-based column index.

Return Value

The OracleBlob value of the column.

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

IsDBNull should be called to check for NULL values before calling this method.

GetOracleBlobForUpdate

GetOracleBlobForUpdate returns an updatable OracleBlob object of the specified BLOB column.

Overload List:

• GetOracleBlobForUpdate(int)

  This method returns an updatable OracleBlob object of the specified BLOB column.

• GetOracleBlobForUpdate(int, int)

  This method returns an updatable OracleBlob object of the specified BLOB column using a WAIT clause.

GetOracleBlobForUpdate(int)

This method returns an updatable OracleBlob object of the specified BLOB column.

Declaration

// C#
public OracleBlob GetOracleBlobForUpdate(int index);

Parameters

• index

  The zero-based column index.

Return Value

An updatable OracleBlob object.

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

When the OracleCommand's ExecuteReader() method is invoked, all the data fetched by the OracleDataReader is from a particular snapshot. Therefore, calling an accessor method on the same column always returns the same value. However, the GetOracleBlobForUpdate() method incurs a server round-trip to obtain a reference to the current BLOB data while also locking the row using the FOR UPDATE clause. This means that the OracleBlob obtained from GetOracleBlob() can have a different value than the OracleBlob obtained from GetOracleBlobForUpdate() since it is not obtained from the original snapshot.

The returned OracleBlob object can be used to safely update the BLOB because the BLOB column has been locked after a call to this method.

Invoking this method internally executes a SELECT..FOR UPDATE statement without a WAIT clause. Therefore, the statement can wait indefinitely until a lock is acquired for that row.

IsDBNull should be called to check for NULL values before calling this method.

Example

The following example gets the OracleBlob object for update from the reader, updates the OracleBlob object, and then commits the transaction.

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types;
 
class GetOracleBlobForUpdateSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Get the ByteCodes for empno = 1
    string cmdstr = "SELECT BYTECODES, EMPNO FROM EMPINFO where EMPNO = 1";
    OracleCommand cmd = new OracleCommand(cmdstr, con);
 
    // Since we are going to update the OracleBlob object, we will
    //have to create a transaction
    OracleTransaction txn = con.BeginTransaction();
 
    // Get the reader
    OracleDataReader reader = cmd.ExecuteReader();
 
    // Declare the variables to retrieve the data in EmpInfo
    OracleBlob byteCodesBlob;
 
    // Read the first row
    reader.Read();
    if (!reader.IsDBNull(0))
    {
      byteCodesBlob = reader.GetOracleBlobForUpdate(0);
 
      // Close the reader
      reader.Close();
 
      // Update the ByteCodes object
      byte[] addedBytes = new byte[2] {0, 0};
      byteCodesBlob.Append(addedBytes, 0, addedBytes.Length);
 
      // Now commit the transaction
      txn.Commit();
      Console.WriteLine("Blob Column successfully updated");
    }
    else
      reader.Dispose();
 
    // Close the connection
    con.Dispose();
  }
}

GetOracleBlobForUpdate(int, int)

This method returns an updatable OracleBlob object of the specified BLOB column using a WAIT clause.

Declaration

// C#
public OracleBlob GetOracleBlobForUpdate(int index, int wait);

Parameters

• index

  The zero-based column index.

• wait

  The number of seconds the method waits to acquire a lock.

Return Value

An updatable OracleBlob object.

Exceptions

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

When the OracleCommand's ExecuteReader() method is invoked, all the data fetched by the OracleDataReader is from a particular snapshot. Therefore, calling an accessor method on the same column always returns the same value. However, the GetOracleBlobForUpdate() method incurs a server round-trip to obtain a reference to the current BLOB data while also locking the row using the FOR UPDATE clause. This means that the OracleBlob obtained from GetOracleBlob() can have a different value than the OracleBlob obtained from GetOracleBlobForUpdate() since it is not obtained from the original snapshot.

IsDBNull should be called to check for NULL values before calling this method.

The returned OracleBlob object can be used to safely update the BLOB because the BLOB column has been locked after a call to this method.

Invoking this method internally executes a SELECT..FOR UPDATE statement which locks the row.

Different WAIT clauses are appended to the statement, depending on the wait value. If the wait value is:

• 0

  "NOWAIT" is appended at the end of a SELECT..FOR UPDATE statement. The statement executes immediately whether the lock is acquired or not. If the lock is not acquired, an exception is thrown.

• n

  "WAIT n" is appended at the end of a SELECT..FOR UPDATE statement. The statement executes as soon as the lock is acquired. However, if the lock cannot be acquired by n seconds, this method call throws an exception.

  The WAIT n" feature is only available for Oracle9i or later. For any version lower than Oracle9i, n is implicitly treated as -1 and nothing is appended at the end of a SELECT..FOR UPDATE statement.