Skip Headers
Oracle® Data Provider for .NET Developer's Guide
11g Release 1 (11.1)

B28375-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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:

Class Inheritance

System.Object

  System.MarshalByRefObject

    System.Data.Common.DataReader

      System.Data.Common.DbDataReader (ADO.NET 2.0 only)

        Oracle.DataAccess.Client.OracleDataReader

Declaration

// ADO.NET 2.0: C#
public sealed class OracleDataReader : DbDataReader, IEnumerable, 
   IDataReader, IDisposable, IDataRecord

// ADO.NET 1.x: 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, if you have not done so yet.
connect scott/tiger@oracle 
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,byteCodes) values 
(1,'KING','SOFTWARE ENGR', '5657');
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(2,'SCOTT','MANAGER', '5960');
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

Microsoft .NET Framework Version: 1.x or 2.0


OracleDataReader Members

OracleDataReader members are listed in the following tables:

OracleDataReader Static Methods

The OracleDataReader static method is listed in Table 5-38.

Table 5-38 OracleDataReader Static Method

Method Description

Equals

Inherited from System.Object (Overloaded)


OracleDataReader Properties

OracleDataReader properties are listed in Table 5-39.

Table 5-39 OracleDataReader Properties

Property Description

Depth

Gets a value indicating the depth of nesting for the current row

FetchSize

Specifies the size of OracleDataReader's internal cache

FieldCount

Gets the number of columns in the result set

HasRows

Indicates whether the OracleDataReader has one or more rows

HiddenFieldCount

Gets the number of fields in the OracleDataReader that are hidden

Supported Only in ADO.NET 2.0-Compliant ODP.NET

IsClosed

Indicates whether or not the data reader is closed

Item

Gets the value of the column (Overloaded)

InitialLOBFetchSize

Specifies the amount that the OracleDataReader initially fetches for LOB columns

InitialLONGFetchSize

Specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns

RecordsAffected

Gets the number of rows changed, inserted, or deleted by execution of the SQL statement

VisibleFieldCount

Gets the number of fields in the OracleDataReader that are not hidden

Supported Only in ADO.NET 2.0-Compliant ODP.NET


OracleDataReader Public Methods

OracleDataReader public methods are listed in Table 5-40.

Table 5-40 OracleDataReader Public Methods

Public Method Description

Close

Closes the OracleDataReader

CreateObjRef

Inherited from System.MarshalByRefObject

Dispose

Releases any resources or memory allocated by the object

Equals

Inherited from System.Object (Overloaded)

GetBoolean

Not Supported

GetByte

Returns the byte value of the specified column

GetBytes

Populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column

GetChar

Not Supported

GetChars

Populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column

GetData

Not Supported

GetDataTypeName

Returns the ODP.NET type name of the specified column

GetDateTime

Returns the DateTime value of the specified column

GetDecimal

Returns the decimal value of the specified NUMBER column

GetDouble

Returns the double value of the specified NUMBER column or BINARY_DOUBLE column

GetEnumerator

Returns an IEnumerator that can be used to iterate through the collection

GetFieldType

Returns the Type of the specified column

GetFloat

Returns the float value of the specified NUMBER column or BINARY_FLOAT column

GetGuid

Not Supported

GetHashCode

Inherited from System.Object

GetInt16

Returns the Int16 value of the specified NUMBER column

GetInt32

Returns the Int32 value of the specified NUMBER column

GetInt64

Returns the Int64 value of the specified NUMBER column

GetLifetimeService

Inherited by System.MarshalByRefObject

GetName

Returns the name of the specified column

GetOracleBFile

Returns an OracleBFile object of the specified BFILE column

GetOracleBinary

Returns an OracleBinary structure of the specified column

GetOracleBlob

Returns an OracleBlob object of the specified BLOB column

GetOracleBlobForUpdate

Returns an updatable OracleBlob object of the specified BLOB column

GetOracleClob

Returns an OracleClob object of the specified CLOB column

GetOracleClobForUpdate

Returns an updatable OracleClob object of the specified CLOB column

GetOracleDate

Returns an OracleDate structure of the specified DATE column

GetOracleDecimal

Returns an OracleDecimal structure of the specified NUMBER column

GetOracleIntervalDS

Returns an OracleIntervalDS structure of the specified INTERVAL DAY TO SECOND column

GetOracleIntervalYM

Returns an OracleIntervalYM structure of the specified INTERVAL YEAR TO MONTH column

GetOracleString

Returns an OracleString structure of the specified column

GetOracleTimeStamp

Returns an OracleTimeStamp structure of the Oracle TimeStamp column

GetOracleTimeStampLTZ

Returns an OracleTimeStampLTZ structure of the specified Oracle TimeStamp WITH LOCAL TIME ZONE column

GetOracleTimeStampTZ

Returns an OracleTimeStampTZ structure of the specified Oracle TimeStamp WITH TIME ZONE column

GetOracleXmlType

Returns an OracleXmlType object of the specified XMLType column

GetOracleValue

Returns the specified column value as a ODP.NET type

GetOracleValues

Gets all the column values as ODP.NET types

GetOrdinal

Returns the 0-based ordinal (or index) of the specified column name

GetProviderSpecificFieldType

Returns an Object that represents the underlying provider-specific field type

Supported Only in ADO.NET 2.0-Compliant ODP.NET

GetProviderSpecificValue

Returns an object that represents the underlying provider-specific value of the specified ordinal

Supported Only in ADO.NET 2.0-Compliant ODP.NET

GetProviderSpecificValues

Returns an array of objects that represent the underlying provider-specific values

Supported Only in ADO.NET 2.0-Compliant ODP.NET

GetSchemaTable

Returns a DataTable that describes the column metadata of the OracleDataReader

GetString

Returns the string value of the specified column

GetTimeSpan

Returns the TimeSpan value of the specified INTERVAL DAY TO SECOND column

GetType

Inherited from System.Object class

GetValue

Returns the column value as a .NET type

GetValues

Gets all the column values as .NET types

GetXmlReader

Returns the value of an XMLType column as an instance of an .NET XmlTextReader

IsDBNull

Indicates whether or not the column value is null

NextResult

Advances the data reader to the next result set when reading the results

Read

Reads the next row in the result set

ToString

Inherited from System.Object



OracleDataReader Static Methods

The OracleDataReader static method is listed in Table 5-41.

Table 5-41 OracleDataReader Static Method

Method Description

Equals

Inherited from System.Object (Overloaded)



OracleDataReader Properties

OracleDataReader properties are listed in Table 5-42.

Table 5-42 OracleDataReader Properties

Property Description

Depth

Gets a value indicating the depth of nesting for the current row

FetchSize

Specifies the size of OracleDataReader's internal cache

FieldCount

Gets the number of columns in the result set

HasRows

Indicates whether the OracleDataReader has one or more rows

HiddenFieldCount

Gets the number of fields in the OracleDataReader that are hidden

Supported Only in ADO.NET 2.0-Compliant ODP.NET

IsClosed

Indicates whether or not the data reader is closed

Item

Gets the value of the column (Overloaded)

InitialLOBFetchSize

Specifies the amount that the OracleDataReader initially fetches for LOB columns

InitialLONGFetchSize

Specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns

RecordsAffected

Gets the number of rows changed, inserted, or deleted by execution of the SQL statement

VisibleFieldCount

Gets the number of fields in the OracleDataReader that are not hidden

Supported Only in ADO.NET 2.0-Compliant ODP.NET


Depth

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

Declaration

// ADO.NET 2.0: C#
public override int Depth {get;}

// ADO.NET 1.x: 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 database round-trip.

FieldCount

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

Declaration

// ADO.NET 2.0: C#
public override int FieldCount {get;}

// ADO.NET 1.x: 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

// ADO.NET 2.0: C#
public override bool HasRows {get;}

// ADO.NET 1.x: C#
public bool HasRows {get;}

Return Value

bool

Remarks

HasRows indicates whether or not 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 determine 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();
  }
}

See Also:

HiddenFieldCount

This property gets the number of fields in the OracleDataReader that are hidden.

Supported Only in ADO.NET 2.0-Compliant ODP.NET

Declaration

// C#
public int HiddenFieldcount { get; }

Property Value

The number of fields in the OracleDataReader that are hidden.

Exceptions

InvalidOperationException - The reader is closed.

Remarks

OracleDataReader.FieldCount and OracleDataReader.VisibleFieldCount return the visible field count.

IsClosed

This property indicates whether or not the data reader is closed.

Declaration

// ADO.NET 2.0: C#
public override bool IsClosed {get;}

// ADO.NET 1.x: 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 data type.

Overload List:

Item [index]

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

Declaration

// ADO.NET 2.0: C#
public override object this[int index] {get;}

// ADO.NET 1.x: C#
public object this[int index] {get;}

Parameters

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

// ADO.NET 2.0: C#
public override object this[string columnName] {get;}

// ADO.NET 1.x: C#
public object this[string columnName] {get;}

Parameters

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

For Oracle Database 10g release 2 (10.2) and later, the maximum value supported for InitialLOBFetchSize is 2 GB.

For releases prior to Oracle Database 10g release 2 (10.2), the maximum value supported for InitialLOBFetchSize is 32K.

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.

VisibleFieldCount

This property gets the number of fields in the OracleDataReader that are not hidden.

Supported Only in ADO.NET 2.0-Compliant ODP.NET

Declaration

// C#
public override int VisibleFieldcount { get; }

Property Value

The number of fields that are not hidden.

Exceptions

InvalidOperationException - The reader is closed.

Remarks

If an application sets the AddRowid property on an OracleCommand object to true, then the application can access the RowId but it is not a visible field. If RowId is added in the select statement list, then it is a visible field. OracleDataReader.VisibleFieldCount and OracleDataReader.FieldCount always have the same value.

Example

// C#
 
using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
 
class VisibleFieldCountSample
{
  static void Main(string[] args)
  {
    string constr = "User Id=scott; Password=tiger; Data Source=oracle;";
    DbProviderFactory factory =
            DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
 
    using (DbConnection conn = factory.CreateConnection())
    {
      conn.ConnectionString = constr;
      try
      {
        conn.Open();
        OracleCommand cmd = (OracleCommand)factory.CreateCommand();
        cmd.Connection = (OracleConnection)conn;
 
        //to gain access to ROWIDs of the table
        cmd.AddRowid = true;
        cmd.CommandText = "select empno, ename from emp;";
 
        OracleDataReader reader = cmd.ExecuteReader();
        
        int visFC = reader.VisibleFieldCount; //Results in 2
        int hidFC = reader.HiddenFieldCount;  // Results in 1
 
        Console.Write("Visible field count: " + visFC);
        Console.Write("Hidden field count: " + hidFC);
 
        reader.Dispose();
        cmd.Dispose();
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        Console.WriteLine(ex.StackTrace);
      }
    }
  }
}
 
 

OracleDataReader Public Methods

OracleDataReader public methods are listed in Table 5-43.

Table 5-43 OracleDataReader Public Methods

Public Method Description

Close

Closes the OracleDataReader

CreateObjRef

Inherited from System.MarshalByRefObject

Dispose

Releases any resources or memory allocated by the object

Equals

Inherited from System.Object (Overloaded)

GetBoolean

Not Supported

GetByte

Returns the byte value of the specified column

GetBytes

Populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column

GetChar

Not Supported

GetChars

Populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column

GetData

Not Supported

GetDataTypeName

Returns the ODP.NET type name of the specified column

GetDateTime

Returns the DateTime value of the specified column

GetDecimal

Returns the decimal value of the specified NUMBER column

GetDouble

Returns the double value of the specified NUMBER column or BINARY_DOUBLE column

GetEnumerator

Returns an IEnumerator that can be used to iterate through the collection

GetFieldType

Returns the Type of the specified column

GetFloat

Returns the float value of the specified NUMBER column or BINARY_FLOAT column

GetGuid

Not Supported

GetHashCode

Inherited from System.Object

GetInt16

Returns the Int16 value of the specified NUMBER column

GetInt32

Returns the Int32 value of the specified NUMBER column

GetInt64

Returns the Int64 value of the specified NUMBER column

GetLifetimeService

Inherited by System.MarshalByRefObject

GetName

Returns the name of the specified column

GetOracleBFile

Returns an OracleBFile object of the specified BFILE column

GetOracleBinary

Returns an OracleBinary structure of the specified column

GetOracleBlob

Returns an OracleBlob object of the specified BLOB column

GetOracleBlobForUpdate

Returns an updatable OracleBlob object of the specified BLOB column

GetOracleClob

Returns an OracleClob object of the specified CLOB column

GetOracleClobForUpdate

Returns an updatable OracleClob object of the specified CLOB column

GetOracleDate

Returns an OracleDate structure of the specified DATE column

GetOracleDecimal

Returns an OracleDecimal structure of the specified NUMBER column

GetOracleIntervalDS

Returns an OracleIntervalDS structure of the specified INTERVAL DAY TO SECOND column

GetOracleIntervalYM

Returns an OracleIntervalYM structure of the specified INTERVAL YEAR TO MONTH column

GetOracleString

Returns an OracleString structure of the specified column

GetOracleTimeStamp

Returns an OracleTimeStamp structure of the Oracle TimeStamp column

GetOracleTimeStampLTZ

Returns an OracleTimeStampLTZ structure of the specified Oracle TimeStamp WITH LOCAL TIME ZONE column

GetOracleTimeStampTZ

Returns an OracleTimeStampTZ structure of the specified Oracle TimeStamp WITH TIME ZONE column

GetOracleXmlType

Returns an OracleXmlType object of the specified XMLType column

GetOracleValue

Returns the specified column value as a ODP.NET type

GetOracleValues

Gets all the column values as ODP.NET types

GetOrdinal

Returns the 0-based ordinal (or index) of the specified column name

GetProviderSpecificFieldType

Returns an Object that represents the underlying provider-specific field type

Supported Only in ADO.NET 2.0-Compliant ODP.NET

GetProviderSpecificValue

Returns an object that represents the underlying provider-specific value of the specified ordinal

Supported Only in ADO.NET 2.0-Compliant ODP.NET

GetProviderSpecificValues

Returns an array of objects that represent the underlying provider-specific values

Supported Only in ADO.NET 2.0-Compliant ODP.NET

GetSchemaTable

Returns a DataTable that describes the column metadata of the OracleDataReader

GetString

Returns the string value of the specified column

GetTimeSpan

Returns the TimeSpan value of the specified INTERVAL DAY TO SECOND column

GetType

Inherited from System.Object class

GetValue

Returns the column value as a .NET type

GetValues

Gets all the column values as .NET types

GetXmlReader

Returns the value of an XMLType column as an instance of an .NET XmlTextReader

IsDBNull

Indicates whether or not the column value is null

NextResult

Advances the data reader to the next result set when reading the results

Read

Reads the next row in the result set

ToString

Inherited from System.Object


Close

This method closes the OracleDataReader.

Declaration

// ADO.NET 2.0: C#
public override void Close();

// ADO.NET 1.x: 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.

GetBoolean

This method is not supported.

Declaration

// ADO.NET 2.0: C#
public override bool GetBoolean(int index);

// ADO.NET 1.x: C#
public bool GetBoolean(int index);

Parameters

Implements

IDataRecord

Exceptions

NotSupportedException - This property is not supported.

GetByte

This method returns the byte value of the specified column.

Declaration

// ADO.NET 2.0: C#
public override byte GetByte(int index);

// ADO.NET 1.x: C#
public byte GetByte(int index);

Parameters

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

// ADO.NET 2.0: C#
public override long GetBytes(int index, long fieldOffset, byte[] buffer, 
   int bufferOffset, int length);

// ADO.NET 1.x: C#
public long GetBytes(int index, long fieldOffset, byte[] buffer, 
   int bufferOffset, int length);

Parameters

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.

GetChar

This method is not supported.

Declaration

// ADO.NET 2.0: C#
public override long GetChar(int index);

// ADO.NET 1.x: C#
public long GetChar(int index);

Parameters

Implements

IDataRecord

Exceptions

NotSupportedException - This property is not supported.

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

// ADO.NET 2.0: C#
public override long GetChars(int index, long fieldOffset, char[] buffer,
    int bufferOffset, int length);

// ADO.NET 1.x: C#
public long GetChars(int index, long fieldOffset, char[] buffer,
    int bufferOffset, int length);

Parameters

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

// ADO.NET 2.0: C#
public override string GetDataTypeName(int index);

// ADO.NET 1.x: C#
public string GetDataTypeName(int index);

Parameters

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

// ADO.NET 2.0: C#
public override DateTime GetDateTime(int index);

// ADO.NET 1.x: C#
public DateTime GetDateTime(int index);

Parameters

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

// ADO.NET 2.0: C#
public override decimal GetDecimal(int index);

// ADO.NET 1.x: C#
public decimal GetDecimal(int index);

Parameters

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

// ADO.NET 2.0: C#
public override double GetDouble(int index);

// ADO.NET 1.x: C#
public double GetDouble(int index);

Parameters

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.

GetEnumerator

This method returns an IEnumerator that can be used to iterate through the collection (record set).

Declaration

// ADO.NET 2.0: C#
public override IEnumerator GetEnumerator();

// ADO.NET 1.x: C#
IEnumerator IEnumerable.GetEnumerator();

Return Value

An IEnumerator that can be used to iterate through the collection (record set).

Exceptions

InvalidOperationException - The reader is closed.

GetFieldType

This method returns the type of the specified column.

Declaration

// ADO.NET 2.0: C#
public override Type GetFieldType(int index);

// ADO.NET 1.x: C#
public Type GetFieldType(int index);

Parameters

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.

Remarks

GetFieldType returns a type that corresponds to the value that the application obtains after invoking the GetValue accessor or Item property on the OracleDataReader. For example, if the column is a string, this method returns a .NET Type object for a .NET string.

GetFloat

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

Declaration

// ADO.NET 2.0: C#
public override float GetFloat(int index);

// ADO.NET 1.x: C#
public float GetFloat(int index);

Parameters

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.

GetGuid

This method is not supported.

Declaration

// ADO.NET 2.0: C#
public override Guid GetGuid(int index);

// ADO.NET 1.x: C#
public Guid GetGuid(int index);

Parameters

Implements

IDataRecord

Exceptions

NotSupportedException - This property is not supported.

GetInt16

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

Note:

short is equivalent to Int16.

Declaration

// ADO.NET 2.0: C#
public override short GetInt16(int index);

// ADO.NET 1.x: C#
public short GetInt16(int index);

Parameters

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.

Note:

int is equivalent to Int32.

Declaration

// ADO.NET 2.0: C#
public override int GetInt32(int index);

// ADO.NET 1.x: C#
public int GetInt32(int index);

Parameters

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.

Note:

long is equivalent to Int64.

Declaration

// ADO.NET 2.0: C#
public override long GetInt64(int index);

// ADO.NET 1.x: C#
public long GetInt64(int index);

Parameters

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

// ADO.NET 2.0: C#
public override string GetName(int index);

// ADO.NET 1.x: C#
public string GetName(int index);

Parameters

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

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

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:

GetOracleBlob

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

Declaration

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

Parameters

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.

Declaration

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

Parameters

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 database 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.

/* Database Setup, if you have not done so yet.
connect scott/tiger@oracle 
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,byteCodes) values 
(1,'KING','SOFTWARE ENGR', '5657');
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(2,'SCOTT','MANAGER', '5960');
commit;
 
*/
// 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

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 database 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:

Example

The GetOracleBlobForUpdate methods are comparable. See "Example" for a code example demonstrating usage.

GetOracleClob

This method returns an OracleClob object of the specified CLOB column.

Declaration

// C#
public OracleClob GetOracleClob(int index);

Parameters

Return Value

The OracleClob 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.

GetOracleClobForUpdate

GetOracleClobForUpdate returns an updatable OracleClob object of the specified CLOB column.

Overload List:

GetOracleClobForUpdate(int)

This method returns an updatable OracleClob object of the specified CLOB column.

Declaration

// C#
public OracleClob GetOracleClobForUpdate(int index);

Parameters

Return Value

An updatable OracleClob.

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 GetOracleClobForUpdate() method incurs a database round-trip to obtain a reference to the current CLOB data while also locking the row using the FOR UPDATE clause. This means that the OracleClob obtained from GetOracleClob() can have a different value than the OracleClob obtained from GetOracleClobForUpdate() since it is not obtained from the original snapshot.

The returned OracleClob object can be used to safely update the CLOB because the CLOB column is 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 OracleClob object for update from the reader, updates the OracleClob object, and then commits the transaction.

/* Database Setup, if you have not done so yet.
connect scott/tiger@oracle 
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,byteCodes) values 
(1,'KING','SOFTWARE ENGR', '5657');
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(2,'SCOTT','MANAGER', '5960');
commit;
 
*/
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types;
 
class GetOracleClobForUpdateSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Get the job description for empno = 1
    string cmdStr = "SELECT JOBDESCRIPTION, EMPNO FROM EMPINFO where EMPNO = 1";
    OracleCommand cmd = new OracleCommand(cmdStr, con);
 
    // Since we are going to update the OracleClob 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
    OracleClob jobDescClob;
 
    // Read the first row
    reader.Read();
 
    if (!reader.IsDBNull(0))
    {
      jobDescClob = reader.GetOracleClobForUpdate(0);
 
      // Close the reader
      reader.Close();
 
      // Update the job description Clob object
      char[] jobDesc = "-SALES".ToCharArray();
      jobDescClob.Append(jobDesc, 0, jobDesc.Length);
 
      // Now commit the transaction
      txn.Commit();
      Console.WriteLine("Clob Column successfully updated");
    }
    else
      reader.Close();
 
    // Close the connection
    con.Close();
  }
}

GetOracleClobForUpdate(int, int)

This method returns an updatable OracleClob object of the specified CLOB column using a WAIT clause.

Declaration

// C#
public OracleClob GetOracleClobForUpdate(int index, int wait);

Parameters

Return Value

An updatable OracleClob.

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 GetOracleClobForUpdate() method incurs a database round-trip to obtain a reference to the current CLOB data while also locking the row using the FOR UPDATE clause. This means that the OracleClob obtained from GetOracleClob() can have a different value than the OracleClob obtained from GetOracleClobForUpdate() since it is not obtained from the original snapshot.

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

The returned OracleClob object can be used to safely update the CLOB because the CLOB column is locked after a call to this method.

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

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

Example

The GetOracleClobForUpdate methods are comparable. See "Example" for a code example demonstrating usage.

GetOracleDate

This method returns an OracleDate structure of the specified DATE column.

Declaration

// C#
public OracleDate GetOracleDate(int index);

Parameters

Return Value

The OracleDate 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.

GetOracleDecimal

This method returns an OracleDecimal structure of the specified NUMBER column.

Declaration

// C#
public OracleDecimal GetOracleDecimal(int index);

Parameters

Return Value

The OracleDecimal 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.

GetOracleIntervalDS

This method returns an OracleIntervalDS structure of the specified INTERVAL DAY TO SECOND column.

Declaration

// C#
public OracleIntervalDS GetOracleIntervalDS(int index);

Parameters

Return Value

The OracleIntervalDS 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.

GetOracleIntervalYM

This method returns an OracleIntervalYM structure of the specified INTERVAL YEAR TO MONTH column.

Declaration

// C#
public OracleIntervalYM GetOracleIntervalYM(int index);

Parameters

Return Value

The OracleIntervalYM 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.

GetOracleString

This method returns an OracleString structure of the specified column. The string is stored as a Unicode string.

Declaration

// C#
public OracleString GetOracleString(int index);

Parameters

Return Value

The OracleString 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.

GetOracleString is used on the following Oracle column types:

GetOracleTimeStamp

This method returns an OracleTimeStamp structure of the Oracle TimeStamp column.

Declaration

// C#
public OracleTimeStamp GetOracleTimeStamp(int index);

Parameters

Return Value

The OracleTimeStamp 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

GetOracleTimeStamp is used with the Oracle Type TimeStamp.

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

GetOracleTimeStampLTZ

This method returns an OracleTimeStampLTZ structure of the specified Oracle TimeStamp WITH LOCAL TIME ZONE column.

Declaration

// C#
public OracleTimeStampLTZ GetOracleTimeStampLTZ(int index);

Parameters

Return Value

The OracleTimeStampLTZ 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

GetOracleTimeStampLTZ is used with the Oracle Type TimeStamp with Local Time Zone columns.

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

GetOracleTimeStampTZ

This method returns an OracleTimeStampTZ structure of the specified Oracle TimeStamp WITH TIME ZONE column.

Declaration

// C#
public OracleTimeStampTZ GetOracleTimeStampTZ(int index);

Parameters

Return Value

The OracleTimeStampTZ 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

Used with the Oracle Type TimeStamp with Local Time Zone columns

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

GetOracleXmlType

This method returns an OracleXmlType object of the specified XMLType column.

Declaration

// C#
public OracleXmlType GetOracleXmlType(int index);

Parameters

Return Value

The OracleXmlType value of the column.

Exceptions

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.

Requirements

This property can only be used with Oracle9i Release 2 (9.2) or later.

GetOracleValue

This method returns the specified column value as an ODP.NET type.

Declaration

// C#
public object GetOracleValue(int index);

Parameters

Return Value

The value of the column as an ODP.NET type.

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.

GetOracleValues

This method gets all the column values as ODP.NET types.

Declaration

// C#
public int GetOracleValues(object[] values);

Parameters

Return Value

The number of ODP.NET types in the values array.

Exceptions

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

Remarks

This method provides a way to retrieve all column values rather than retrieving each column value individually.

The number of column values retrieved is the minimum of the length of the values array and the number of columns in the result set.

GetOrdinal

This method returns the 0-based ordinal (or index) of the specified column name.

Declaration

// ADO.NET 2.0: C#
public override int GetOrdinal(string name);

// ADO.NET 1.x: C#
public int GetOrdinal(string name);

Parameters

Return Value

The index of the column.

Implements

IDataRecord

Exceptions

InvalidOperationException - The reader is closed.

IndexOutOfRangeException - The column index is invalid.

Remarks

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

GetProviderSpecificFieldType

This method returns an Object that represents the underlying provider-specific field type.

Declaration

// ADO.NET 2.0: C#public override Type GetProviderSpecificFieldType(int ordinal);

Parameters

Return Value

A System.Type that represents the underlying provider-specific field type.

Exceptions

IndexOutOfRangeException - The column index is invalid.

InvalidOperationException - The reader is closed.

Remarks

The return type is a member of the Oracle.DataAccess.Types namespace.

GetProviderSpecificValue

This method returns an object that represents the underlying provider-specific value of the specified ordinal.

Declaration

// ADO.NET 2.0: C#
public override object GetProviderSpecificValue (int ordinal); 

Parameters

ordinal

A zero-based column ordinal.

Return Value

An Object that is a representation of the underlying provider-specific field type.

Exceptions

IndexOutOfRangeException - The column index is invalid.

InvalidOperationException - The reader is closed.

GetProviderSpecificValues

This method returns an array of objects that represent the underlying provider-specific values.

Declaration

// ADO.NET 2.0: C#
public override int GetProviderSpecificValues( object [ ] values);

Parameters

Return Value

The number of Object instances in the array.

Exceptions

InvalidOperationException - The reader is closed.

GetSchemaTable

This method returns a DataTable that describes the column metadata of the OracleDataReader.

Declaration

// ADO.NET 2.0: C#
public override DataTable GetSchemaTable();

// ADO.NET 1.x: C#
public DataTable GetSchemaTable();

Return Value

A DataTable that contains the metadata of the result set.

Implements

IDataReader

Exceptions

InvalidOperationException - The connection is closed or the reader is closed.

Remarks

The OracleDataReader.GetSchemaTable method returns the SchemaTable.

OracleDataReader SchemaTable

The OracleDataReader SchemaTable is a DataTable that describes the column metadata of the OracleDataReader.

The columns of the SchemaTable are in the order shown.

Table 5-44 OracleDataReader SchemaTable

Name Name Type Description

ColumnName

System.String

The name of the column.

ColumnOrdinal

System.Int32

The 0-based ordinal of the column.

ColumnSize

System.Int64

The maximum possible length of a value in the column. ColumnSize value is determined as follows:

  • CHAR and VARCHAR2 types:

    in bytes - if IsByteSemantic boolean value is true

    in characters - if IsByteSemantic boolean value is false

  • All other types:

    in bytes

See "IsByteSemantic" for more information.

NumericPrecision

System.Int16

The maximum precision of the column, if the column is a numeric data type.

This column has valid values for Oracle NUMBER, Oracle INTERVAL YEAR TO MONTH, and Oracle INTERVAL DAY TO SECOND columns. For all other columns, the value is null.

NumericScale

System.Int16

The scale of the column.

This column has valid values for Oracle NUMBER, Oracle INTERVAL DAY TO SECOND, and the Oracle TIMESTAMP columns. For all other columns, the value is null.

IsUnique

System.Boolean

Indicates whether or not the column is unique.

true if no two rows in the base table can have the same value in this column, where the base table is the table returned in BaseTableName.

IsUnique is guaranteed to be true if one of the following applies:

  • the column constitutes a key by itself

  • there is a unique constraint or a unique index that applies only to this column and a NOT NULL constraint has been defined on the column

  • the column is an explicitly selected ROWID

IsUnique is false if the column can contain duplicate values in the base table.

The default is false.

The value of this property is the same for each occurrence of the base table column in the select list.

IsKey

System.Boolean

Indicates whether or not the column is a key column.

true if the column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The set of columns with IsKey set to true must uniquely identify a row in the rowset. There is no requirement that this set of columns is a minimal set of columns.

This set of columns can be generated from one of the following in descending order of priority:

  • A base table primary key.

  • Any of the unique constraints or unique indexes with the following condition: A NOT NULL constraint must be defined on the column or on all of the columns, in the case of a composite unique constraint or composite unique index.

  • Any of the composite unique constraints or composite unique indexes with the following condition: A NULL constraint must be defined on at least one, but not all, of the columns.

An explicitly selected ROWID. false if the column is not required to uniquely identify the row. The value of this property is the same for each occurrence of the base table column in the select list.

IsRowID

System.Boolean

true if the column is a ROWID, otherwise false.

BaseColumnName

System.String

The name of the column in the database if an alias is used for the column.

BaseSchemaName

System.String

The name of the schema in the database that contains the column.

BaseTableName

System.String

The name of the table or view in the database that contains the column.

DataType

System.RuntimeType

Maps to the common language runtime type.

ProviderType

Oracle.DataAccess. Client.OracleDbType

The database column type (OracleDbType) of the column.

AllowDBNull

System.Boolean

true if null values are allowed, otherwise false.

IsAliased

System.Boolean

true if the column is an alias; otherwise false.

IsByteSemantic

System.Boolean

IsByteSemantic is:

  • true if the ColumnSize value uses bytes semantics

  • false if ColumnSize uses character semantics

This value is always true when connected to a database version earlier than Oracle9i.

IsExpression

System.Boolean

true if the column is an expression; otherwise false.

IsHidden

System.Boolean

true if the column is hidden; otherwise false.

IsReadOnly

System.Boolean

true if the column is read-only; otherwise false.

IsLong

System.Boolean

true if the column is a LONG, LONG RAW, BLOB, CLOB, or BFILE; otherwise false.


Example

This example creates and uses the SchemaTable from the reader.

/* Database Setup, if you have not done so yet.
connect scott/tiger@oracle 
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,byteCodes) values 
(1,'KING','SOFTWARE ENGR', '5657');
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(2,'SCOTT','MANAGER', '5960');
commit;
 
*/
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types;
 
class GetSchemaTableSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    string cmdstr = "SELECT EMPNO,EMPNAME FROM EMPINFO where EMPNO = 1";
    OracleCommand cmd = new OracleCommand(cmdstr, con);
 
    //get the reader
    OracleDataReader reader = cmd.ExecuteReader();
 
    //get the schema table
    DataTable schemaTable = reader.GetSchemaTable();
 
    //retrieve the first column info.
    DataRow row = schemaTable.Rows[0];
 
    //print out the column info
    Console.WriteLine("Column name: " + row["COLUMNNAME"]);
    Console.WriteLine("Precision: " + row["NUMERICPRECISION"]);
    Console.WriteLine("Scale: " + row["NUMERICSCALE"]);
    reader.Close();
 
    // Close the connection
    con.Close();
  }
}

GetString

This method returns the string value of the specified column.

Declaration

// ADO.NET 2.0: C#
public override string GetString(int index);

// ADO.NET 1.x: C#
public string GetString(int index);

Parameters

Return Value

The string 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

Call the IsDBNull method to check for null values before calling this method.

GetTimeSpan

This method returns the TimeSpan value of the specified INTERVAL DAY TO SECOND column.

Declaration

// C#
public TimeSpan GetTimeSpan(int index);

Parameters

Return Value

The TimeSpan 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.

GetValue

This method returns the column value as a .NET type.

Declaration

// ADO.NET 2.0: C#
public override object GetValue(int index);

// ADO.NET 1.x: C#
public object GetValue(int index);

Parameters

Return Value

The value of the column as a .NET type.

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.

Remarks

When this method is invoked for a NUMBER column, the .NET type returned depends on the precision and scale of the column. For example, if a column is defined as NUMBER(4,0) then values in this column are retrieved as a System.Int16.If the precision and scale is such that no .NET type can represent all the possible values that could exist in that column, the value is returned as a System.Decimal, if possible. If the value cannot be represented by a System.Decimal, an exception is raised. For example, if a column is defined as NUMBER (20,10) then a value in this column is retrieved as a System.Decimal.

GetValues

This method gets all the column values as .NET types.

Declaration

// ADO.NET 2.0: C#
public override int GetValues(object[ ] values);

// ADO.NET 1.x: C#
public int GetValues(object[ ] values);

Parameters

Return Value

The number of objects in the values array.

Implements

IDataRecord

Exceptions

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

Remarks

This method provides a way to retrieve all column values rather than retrieving each column value individually.

The number of column values retrieved is the minimum of the length of the values array and the number of columns in the result set.

GetXmlReader

This method returns the contents of an XMLType column as an instance of an .NET XmlTextReader object.

Declaration

// C#
public XmlReader GetXmlReader(int index);

Parameters

Return Value

A .NET XmlTextReader.

Exceptions

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.

IsDBNull

This method indicates whether or not the column value is NULL.

Declaration

// ADO.NET 2.0: C#
public override bool IsDBNull(int index);

// ADO.NET 1.x: C#
public bool IsDBNull(int index);

Parameters

Return Value

Returns true if the column is a NULL value; otherwise, returns false.

Implements

IDataRecord

Exceptions

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

IndexOutOfRangeException - The column index is invalid.

Remarks

This method should be called to check for NULL values before calling the other accessor methods.

Example

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

NextResult

This method advances the data reader to the next result set.

Declaration

// ADO.NET 2.0: C#
public override bool NextResult();

// ADO.NET 1.x: C#
public bool NextResult();

Return Value

Returns true if another result set exists; otherwise, returns false.

Implements

IDataReader

Exceptions

InvalidOperationException - The connection is closed or the reader is closed.

Remarks

NextResult is used when reading results from stored procedure execution that return more than one result set.

Read

This method reads the next row in the result set.

Declaration

// ADO.NET 2.0: C#
public override  bool Read();

// ADO.NET 1.x: C#
public bool Read();

Return Value

Returns true if another row exists; otherwise, returns false.

Implements

IDataReader

Exceptions

InvalidOperationException - The connection is closed or the reader is closed.

Remarks

The initial position of the data reader is before the first row. Therefore, the Read method must be called to fetch the first row. The row that was just read is considered the current row. If the OracleDataReader has no more rows to read, it returns false.

Example

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