An OracleDataReader object represents a forward-only, read-only, in-memory result set.
Unlike the DataSet, the OracleDataReader object stays connected and fetches one row at a time.
The following section contain related information:
System.Object
System.MarshalByRefObject
System.Data.Common.DataReader
System.Data.Common.DbDataReader (ADO.NET 2.0 only)
Oracle.DataAccess.Client.OracleDataReader
// 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
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
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.
To minimize the number of open database cursors, OracleDataReader objects should be explicitly disposed.
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();
}
}
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
Microsoft .NET Framework Version: 1.0 or later
See Also:
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 |
|---|---|
|
|
Inherited from |
OracleDataReader properties are listed in Table 5-39.
Table 5-39 OracleDataReader Properties
| Property | Description |
|---|---|
|
Gets a value indicating the depth of nesting for the current row |
|
|
Specifies the size of |
|
|
Gets the number of columns in the result set |
|
|
Indicates whether the |
|
|
Gets the number of fields in the Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Indicates whether or not the data reader is closed |
|
|
Gets the value of the column (Overloaded) |
|
|
Specifies the amount that the |
|
|
Specifies the amount that the |
|
|
Gets the number of rows changed, inserted, or deleted by execution of the SQL statement |
|
|
Gets the amount of memory the internal cache of the |
|
|
Gets the number of fields in the 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 |
|---|---|
|
Closes the |
|
|
|
Inherited from |
|
Releases any resources or memory allocated by the object |
|
|
|
Inherited from |
|
Not Supported |
|
|
Returns the byte value of the specified column |
|
|
Populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column |
|
|
Not Supported |
|
|
Populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column |
|
|
|
Not Supported |
|
Returns the ODP.NET type name of the specified column |
|
|
Returns the |
|
|
Returns the |
|
|
Returns the |
|
|
Returns an |
|
|
Returns the |
|
|
Returns the |
|
|
Not Supported |
|
|
|
Inherited from |
|
Returns the |
|
|
Returns the |
|
|
Returns the |
|
|
|
Inherited by System. |
|
Returns the name of the specified column |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an updatable |
|
|
Returns an |
|
|
Returns an updatable |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns the specified column value as a ODP.NET type |
|
|
Gets all the column values as ODP.NET types |
|
|
Returns the |
|
|
Returns the provider-specific type of the specified column Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Returns an object that represents the underlying provider-specific value of the specified ordinal Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Returns an array of objects that represent the underlying provider-specific values Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Returns a |
|
|
Returns the string value of the specified column |
|
|
Returns the |
|
|
|
Inherited from |
|
Returns the column value as a .NET type |
|
|
Gets all the column values as .NET types |
|
|
Returns the value of an |
|
|
Indicates whether or not the column value is null |
|
|
Advances the data reader to the next result set when reading the results |
|
|
Reads the next row in the result set |
|
|
|
Inherited from |
The OracleDataReader static method is listed in Table 5-41.
Table 5-41 OracleDataReader Static Method
| Method | Description |
|---|---|
|
|
Inherited from |
OracleDataReader properties are listed in Table 5-42.
Table 5-42 OracleDataReader Properties
| Property | Description |
|---|---|
|
Gets a value indicating the depth of nesting for the current row |
|
|
Specifies the size of |
|
|
Gets the number of columns in the result set |
|
|
Indicates whether the |
|
|
Gets the number of fields in the Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Indicates whether or not the data reader is closed |
|
|
Gets the value of the column (Overloaded) |
|
|
Specifies the amount that the |
|
|
Specifies the amount that the |
|
|
Gets the number of rows changed, inserted, or deleted by execution of the SQL statement |
|
|
Gets the amount of memory the internal cache of the |
|
|
Gets the number of fields in the Supported Only in ADO.NET 2.0-Compliant ODP.NET |
This property gets a value indicating the depth of nesting for the current row.
// ADO.NET 2.0: C#
public override int Depth {get;}
// ADO.NET 1.x: C#
public int Depth {get;}
The depth of nesting for the current row.
IDataReader
InvalidOperationException - The reader is closed.
Default = 0
This property always returns zero because Oracle does not support nesting.
This property specifies the size of OracleDataReader's internal cache.
// C#
public long FetchSize {get; set;}
A long that specifies the amount of memory (in bytes) that the OracleDataReader uses for its internal cache.
ArgumentException - The FetchSize value specified is invalid.
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.
The RowSize and FetchSize properties handle UDT and XMLType data differently than other scalar data types. Because only a reference to the UDT and XMLType data is stored in the ODP.NET's internal cache, the RowSize property accounts for only the memory needed for the reference (which is very small) and not the actual size of the UDT and XMLType data. Thus, applications can inadvertently fetch massive amount s of UDT or XMLType instances from the database in a single database round-trip because the actual size of UDT and XMLType data do not count against the FetchSize, and it would require numerous UDT and XMLType references to fill up the default cache size of 64K. Therefore, when fetching UDT or XMLType data, the FetchSize property must be appropriately configured to control the number of UDT and XMLType instances that are to be fetched, rather than the amount of the actual UDT and XMLType data to be fetched
NOTE: For LOB and LONG data types, only the sizes specified in the InitialLOBFetchSize and InitialLONGFetchSize properties are accounted for by the RowSize property in addition to the metadata and reference information that is maintained by the cache for each LOB in the select list.
See Also:
OracleDataReader "RowSize"
OracleCommand "ExecuteReader()"
OracleCommand "RowSize"
This property returns the number of columns in the result set.
// ADO.NET 2.0: C#
public override int FieldCount {get;}
// ADO.NET 1.x: C#
public int FieldCount {get;}
The number of columns in the result set if one exists, otherwise 0.
IDataRecord
InvalidOperationException - The reader is closed.
Default = 0
This property has a value of 0 for queries that do not return result sets.
This property indicates whether the OracleDataReader has one or more rows.
// ADO.NET 2.0: C#
public override bool HasRows {get;}
// ADO.NET 1.x: C#
public bool HasRows {get;}
bool
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.
// 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:
http://msdn.microsoft.com/library for detailed information about this Microsoft .NET Framework 1.1 feature
This property gets the number of fields in the OracleDataReader that are hidden.
Supported Only in ADO.NET 2.0-Compliant ODP.NET
// C#
public int HiddenFieldcount { get; }
The number of fields in the OracleDataReader that are hidden.
InvalidOperationException - The reader is closed.
OracleDataReader.FieldCount and OracleDataReader.VisibleFieldCount return the visible field count.
See Also:
This property indicates whether or not the data reader is closed.
// ADO.NET 2.0: C#
public override bool IsClosed {get;}
// ADO.NET 1.x: C#
public bool IsClosed {get;}
If the OracleDataReader is in a closed state, returns true; otherwise, returns false.
IDataReader
Default = true
IsClosed and RecordsAffected are the only two properties that are accessible after the OracleDataReader is closed.
This property gets the value of the column in .NET data type.
This property gets the .NET Value of the column specified by the column index.
This property gets the .NET Value of the column specified by the column name.
This property gets the .NET Value of the column specified by the column index.
// ADO.NET 2.0: C#
public override object this[int index] {get;}
// ADO.NET 1.x: C#
public object this[int index] {get;}
index
The zero-based index of the column.
The .NET value of the specified column.
IDataRecord
Default = Not Applicable
In C#, this property is the indexer for this class.
This property gets the .NET Value of the column specified by the column name.
// ADO.NET 2.0: C#
public override object this[string columnName] {get;}
// ADO.NET 1.x: C# public object this[string columnName] {get;}
columnName
The name of the column.
The .NET Value of the specified column.
IDataRecord
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.
This property specifies the amount that the OracleDataReader initially fetches for LOB columns.
// C#
public int InitialLOBFetchSize {get;}
The size of the chunk to retrieve.
InvalidOperationException - The reader is closed.
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.
See Also:
"InitialLOBFetchSize" for further information on OracleCommand.InitialLOBFetchSize
This property specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns.
// C#
public long InitialLONGFetchSize {get;}
The size of the chunk to retrieve. The default is 0.
InvalidOperationException - The reader is closed.
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.
See Also:
"InitialLONGFetchSize" for further information on OracleCommand.InitialLONGFetchSize
This property gets the number of rows changed, inserted, or deleted by execution of the SQL statement.
// C#
public int RecordsAffected {get;}
The number of rows affected by execution of the SQL statement.
IDataReader
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.
This property gets the amount of memory the internal cache of the OracleDataReader needs to store one row of data.
// C#
public long RowSize {get;}
A long that indicates the amount of memory (in bytes) that an OracleDataReader needs to store one row of data for the executed query.
The RowSize property is set to a nonzero value when the OracleDataReader object is created. This property can be used at design time or dynamically during run time, to set the FetchSize property, based on the number of rows. For example, to enable the OracleDataReader object to fetch N rows for each database round-trip, the OracleDataReader FetchSize property can be set dynamically to RowSize * N. Note that for the FetchSize property to take effect appropriately, it must be set before the first invocation of OracleDataReader.Read() for the particular result set.
See Also:
This property gets the number of fields in the OracleDataReader that are not hidden.
Supported Only in ADO.NET 2.0-Compliant ODP.NET
// C#
public override int VisibleFieldcount { get; }
The number of fields that are not hidden.
InvalidOperationException - The reader is closed.
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.
// 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);
}
}
}
}
See Also:
OracleDataReader public methods are listed in Table 5-43.
Table 5-43 OracleDataReader Public Methods
| Public Method | Description |
|---|---|
|
Closes the |
|
|
|
Inherited from |
|
Releases any resources or memory allocated by the object |
|
|
|
Inherited from |
|
Not Supported |
|
|
Returns the byte value of the specified column |
|
|
Populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column |
|
|
Not Supported |
|
|
Populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column |
|
|
|
Not Supported |
|
Returns the ODP.NET type name of the specified column |
|
|
Returns the |
|
|
Returns the |
|
|
Returns the |
|
|
Returns an |
|
|
Returns the |
|
|
Returns the |
|
|
Not Supported |
|
|
|
Inherited from |
|
Returns the |
|
|
Returns the |
|
|
Returns the |
|
|
|
Inherited by System. |
|
Returns the name of the specified column |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an updatable |
|
|
Returns an |
|
|
Returns an updatable |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns an |
|
|
Returns the specified column value as a ODP.NET type |
|
|
Gets all the column values as ODP.NET types |
|
|
Returns the |
|
|
Returns the provider-specific type of the specified column Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Returns an object that represents the underlying provider-specific value of the specified ordinal Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Returns an array of objects that represent the underlying provider-specific values Supported Only in ADO.NET 2.0-Compliant ODP.NET |
|
|
Returns a |
|
|
Returns the string value of the specified column |
|
|
Returns the |
|
|
|
Inherited from |
|
Returns the column value as a .NET type |
|
|
Gets all the column values as .NET types |
|
|
Returns the value of an |
|
|
Indicates whether or not the column value is null |
|
|
Advances the data reader to the next result set when reading the results |
|
|
Reads the next row in the result set |
|
|
|
Inherited from |
This method closes the OracleDataReader.
// ADO.NET 2.0: C# public override void Close();
// ADO.NET 1.x: C#
public void Close();
IDataReader
The Close method frees all resources associated with the OracleDataReader.
The code example for the OracleDataReader class includes the Close method. See OracleDataReader Overview "Example".
This method releases any resources or memory allocated by the object.
// C# public void Dispose();
IDisposable
The Dispose method also closes the OracleDataReader.
This method is not supported.
// ADO.NET 2.0: C#
public override bool GetBoolean(int index);
// ADO.NET 1.x: C# public bool GetBoolean(int index);
index
The zero-based column index.
IDataRecord
NotSupportedException - This property is not supported.
This method returns the byte value of the specified column.
// ADO.NET 2.0: C#
public override byte GetByte(int index);
// ADO.NET 1.x: C# public byte GetByte(int index);
index
The zero-based column index.
The value of the column as a byte.
IDataRecord
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.
IsDBNull should be called to check for NULL values before calling this method.
This method populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column.
// 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);
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).
The number of bytes read.
IDataRecord
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.
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.
This method is not supported.
// ADO.NET 2.0: C#
public override long GetChar(int index);
// ADO.NET 1.x: C#
public long GetChar(int index);
index
The zero based column index.
IDataRecord
NotSupportedException - This property is not supported.
This method populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column.
// 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);
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).
The number of characters read.
IDataRecord
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.
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.
This method returns the ODP.NET type name of the specified column.
// ADO.NET 2.0: C#
public override string GetDataTypeName(int index);
// ADO.NET 1.x: C# public string GetDataTypeName(int index);
index
The zero-based column index.
The name of the ODP.NET type of the column.
IDataRecord
InvalidOperationException - The reader is closed.
IndexOutOfRangeException - The column index is invalid.
This method returns the DateTime value of the specified column.
// ADO.NET 2.0: C#
public override DateTime GetDateTime(int index);
// ADO.NET 1.x: C# public DateTime GetDateTime(int index);
index
The zero-based column index.
The DateTime value of the column.
IDataRecord
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns the decimal value of the specified NUMBER column.
// ADO.NET 2.0: C#
public override decimal GetDecimal(int index);
// ADO.NET 1.x: C# public decimal GetDecimal(int index);
index
The zero-based column index.
The decimal value of the column.
IDataRecord
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns the double value of the specified NUMBER column or BINARY_DOUBLE column.
// ADO.NET 2.0: C#
public override double GetDouble(int index);
// ADO.NET 1.x: C# public double GetDouble(int index);
index
The zero-based column index.
The double value of the column.
IDataRecord
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.
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.
This method returns an IEnumerator that can be used to iterate through the collection (record set).
// ADO.NET 2.0: C# public override IEnumerator GetEnumerator();
// ADO.NET 1.x: C#
IEnumerator IEnumerable.GetEnumerator();
An IEnumerator that can be used to iterate through the collection (record set).
InvalidOperationException - The reader is closed.
This method returns the type of the specified column.
// ADO.NET 2.0: C#
public override Type GetFieldType(int index);
// ADO.NET 1.x: C# public Type GetFieldType(int index);
index
The zero-based column index.
The type of the default .NET type of the column.
IDataRecord
InvalidOperationException - The reader is closed, or the specified column is a UDT but no registered custom type mapping exists for the UDT.
IndexOutOfRangeException - The column index is invalid.
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.
If the attribute is a UDT, this method may return either of the following:
A .NET Type of the custom type if a custom type mapping exists for the Oracle object or collection.
A .NET Type of string if the column is an Oracle REF.
This method returns the float value of the specified NUMBER column or BINARY_FLOAT column.
// ADO.NET 2.0: C#
public override float GetFloat(int index);
// ADO.NET 1.x: C# public float GetFloat(int index);
index
The zero-based column index.
The float value of the column.
IDataRecord
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.
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.
This method is not supported.
// ADO.NET 2.0: C#
public override Guid GetGuid(int index);
// ADO.NET 1.x: C# public Guid GetGuid(int index);
index
The zero-based column index.
IDataRecord
NotSupportedException - This property is not supported.
This method returns the Int16 value of the specified NUMBER column.
Note:
short is equivalent to Int16.
// ADO.NET 2.0: C#
public override short GetInt16(int index);
// ADO.NET 1.x: C# public short GetInt16(int index);
index
The zero-based column index.
The Int16 value of the column.
IDataRecord
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns the Int32 value of the specified NUMBER column.
Note:
int is equivalent to Int32.
// ADO.NET 2.0: C#
public override int GetInt32(int index);
// ADO.NET 1.x: C# public int GetInt32(int index);
index
The zero-based column index.
The Int32 value of the column.
IDataRecord
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns the Int64 value of the specified NUMBER column.
Note:
long is equivalent to Int64.
// ADO.NET 2.0: C#
public override long GetInt64(int index);
// ADO.NET 1.x: C# public long GetInt64(int index);
index
The zero-based column index.
The Int64 value of the column.
IDataRecord
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns the name of the specified column.
// ADO.NET 2.0: C#
public override string GetName(int index);
// ADO.NET 1.x: C# public string GetName(int index);
index
The zero-based column index.
The name of the column.
IDataRecord
InvalidOperationException - The reader is closed.
IndexOutOfRangeException - The column index is invalid.
This method returns an OracleBFile object of the specified BFILE column.
// C#
public OracleBFile GetOracleBFile(int index);
index
The zero-based column index.
The OracleBFile value of the column.
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns an OracleBinary structure of the specified column.
// C#
public OracleBinary GetOracleBinary(int index);
index
The zero-based column index.
The OracleBinary value of the column.
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.
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
This method returns an OracleBlob object of the specified BLOB column.
// C#
public OracleBlob GetOracleBlob(int index);
index
The zero-based column index.
The OracleBlob value of the column.
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.
IsDBNull should be called to check for NULL values before calling this method.
GetOracleBlobForUpdate returns an updatable OracleBlob object of the specified BLOB column.
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.
This method returns an updatable OracleBlob object of the specified BLOB column.
// C#
public OracleBlob GetOracleBlobForUpdate(int index);
index
The zero-based column index.
An updatable OracleBlob object.
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.
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.
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();
}
}
See Also:
This method returns an updatable OracleBlob object of the specified BLOB column using a WAIT clause.
// C# public OracleBlob GetOracleBlobForUpdate(int index, int wait);
index
The zero-based column index.
wait
The number of seconds the method waits to acquire a lock.
An updatable OracleBlob object.
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.
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:
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.
-1
Nothing is appended at the end of the SELECT..FOR UPDATE. The statement execution waits indefinitely until a lock can be acquired.
The GetOracleBlobForUpdate methods are comparable. See "Example" for a code example demonstrating usage.
See Also:
This method returns an OracleClob object of the specified CLOB column.
// C#
public OracleClob GetOracleClob(int index);
index
The zero-based column index.
The OracleClob value of the column.
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.
IsDBNull should be called to check for NULL values before calling this method.
See Also:
GetOracleClobForUpdate returns an updatable OracleClob object of the specified CLOB column.
This method returns an updatable OracleClob object of the specified CLOB column.
GetOracleClobForUpdate(int, int)
This method returns an updatable OracleClob object of the specified CLOB column using a WAIT clause.
This method returns an updatable OracleClob object of the specified CLOB column.
// C#
public OracleClob GetOracleClobForUpdate(int index);
index
The zero-based column index.
An updatable OracleClob.
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.
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.
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();
}
}
See Also:
This method returns an updatable OracleClob object of the specified CLOB column using a WAIT clause.
// C# public OracleClob GetOracleClobForUpdate(int index, int wait);
index
The zero-based column index.
wait
The number of seconds the method waits to acquire a lock.
An updatable OracleClob.
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.
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:
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.
-1
Nothing is appended at the end of the SELECT..FOR UPDATE. The statement execution waits indefinitely until a lock can be acquired.
IsDBNull should be called to check for NULL values before calling this method.
The GetOracleClobForUpdate methods are comparable. See "Example" for a code example demonstrating usage.
See Also:
This method returns an OracleDate structure of the specified DATE column.
// C#
public OracleDate GetOracleDate(int index);
index
The zero-based column index.
The OracleDate value of the column.
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.
IsDBNull should be called to check for NULL values before calling this method.
See Also:
This method returns an OracleDecimal structure of the specified NUMBER column.
// C#
public OracleDecimal GetOracleDecimal(int index);
index
The zero-based column index.
The OracleDecimal value of the column.
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns an OracleIntervalDS structure of the specified INTERVAL DAY TO SECOND column.
// C#
public OracleIntervalDS GetOracleIntervalDS(int index);
index
The zero-based column index.
The OracleIntervalDS value of the column.
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns an OracleIntervalYM structure of the specified INTERVAL YEAR TO MONTH column.
// C#
public OracleIntervalYM GetOracleIntervalYM(int index);
index
The zero-based column index.
The OracleIntervalYM value of the column.
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns an OracleRef object of the specified REF column.
// C#
public OracleRef GetOracleRef(int index);
index
The zero-based column index.
The OracleRef object of the specified column.
InvalidOperationException - The connection is closed, the reader is closed, the Read method 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.
This method returns an OracleString structure of the specified column.
// C#
public OracleString GetOracleString(int index);
index
The zero-based column index.
The OracleString value of the column.
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.
If the column is an Oracle REF column, the string returned is a hexadecimal value that represents the REF in the database.
This method returns an OracleTimeStamp structure of the Oracle TimeStamp column.
// C#
public OracleTimeStamp GetOracleTimeStamp(int index);
index
The zero-based column index.
The OracleTimeStamp value of the column.
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.
GetOracleTimeStamp is used with the Oracle Type TimeStamp.
IsDBNull should be called to check for NULL values before calling this method.
This method returns an OracleTimeStampLTZ structure of the specified Oracle TimeStamp WITH LOCAL TIME ZONE column.
// C#
public OracleTimeStampLTZ GetOracleTimeStampLTZ(int index);
index
The zero-based column index.
The OracleTimeStampLTZ value of the column.
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.
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.
This method returns an OracleTimeStampTZ structure of the specified Oracle TimeStamp WITH TIME ZONE column.
// C#
public OracleTimeStampTZ GetOracleTimeStampTZ(int index);
index
The zero-based column index.
The OracleTimeStampTZ value of the column.
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.
Used with the Oracle Type TimeStamp with Local Time Zone columns
IsDBNull should be called to check for NULL values before calling this method.
This method returns an OracleXmlType object of the specified XMLType column.
// C#
public OracleXmlType GetOracleXmlType(int index);
index
The zero-based column index.
The OracleXmlType value of the column.
InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.
IsDBNull should be called to check for NULL values before calling this method.
This property can only be used with Oracle9i Release 2 (9.2) or later.
This method returns the specified column value as an ODP.NET type.
// C#
public object GetOracleValue(int index);
index
The zero-based column index.
The value of the column as an ODP.NET type.
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.
If the column is an Oracle object or Oracle collection column and a custom type mapping exists, then a custom type is returned.
If the column is an Oracle REF column, then an OracleRef is returned.
This method gets all the column values as ODP.NET types.
// C#
public int GetOracleValues(object[] values);
values
An array of objects to hold the ODP.NET types as the column values.
The number of ODP.NET types in the values array.
InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.
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.
See Also:
This method returns the 0-based ordinal (or index) of the specified column name.
// ADO.NET 2.0: C#
public override int GetOrdinal(string name);
// ADO.NET 1.x: C# public int GetOrdinal(string name);
name
The specified column name.
The index of the column.
IDataRecord
InvalidOperationException - The reader is closed.
IndexOutOfRangeException - The column index is invalid.
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.
This method returns the provider-specific type of the specified column.
// ADO.NET 2.0: C#public override Type GetProviderSpecificFieldType(int index);
index
A zero-based column index.
The provider-specific type of the specified column. This is a member of the Oracle.DataAccess.Types namespace.
IndexOutOfRangeException - The column index is invalid.
InvalidOperationException - The reader is closed, or the specified column is a UDT but no registered custom type mapping exists for the UDT.
GetProviderSpecficFieldType returns a type that corresponds to the value the application obtains after invoking the GetProviderSpecificValue accessor on the OracleDataReader. For example, if the column is a string, this method returns a .NET Type object for an OracleString.
If the attribute is a UDT, this method may return any of the following:
A .NET Type of the custom type, if the column is an Oracle object or Oracle collection column and a custom type mapping exists.
A .NET Type of OracleRef if the column is an Oracle REF.
This method returns an object that represents the underlying provider-specific value of the specified ordinal.
// ADO.NET 2.0: C#
public override object GetProviderSpecificValue (int index);
index
A zero-based column index.
An Object that is a representation of the underlying provider-specific field type.
IndexOutOfRangeException - The column index is invalid.
InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called or all rows have been read.
If the column is an Oracle object or collection column, and a custom type mapping exists, a custom type is returned.
If the column is an Oracle REF column, an OracleRef is returned.
This method returns an array of objects that represent the underlying provider-specific values.
// ADO.NET 2.0: C#
public override int GetProviderSpecificValues(object [ ] values);
values
An array of objects.
The number of Object instances in the array.
InvalidOperationException - The reader is closed.
This method returns a DataTable that describes the column metadata of the OracleDataReader.
// ADO.NET 2.0: C# public override DataTable GetSchemaTable();
// ADO.NET 1.x: C#
public DataTable GetSchemaTable();
A DataTable that contains the metadata of the result set.
IDataReader
InvalidOperationException - The connection is closed or the reader is closed.
The OracleDataReader.GetSchemaTable method returns the 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 |
|---|---|---|
|
|
|
The name of the column. |
|
|
|
The |
|
|
|
The maximum possible length of a value in the column.
See " |
|
|
|
The maximum precision of the column, if the column is a numeric data type. This column has valid values for Oracle |
|
|
|
The scale of the column. This column has valid values for Oracle |
|
|
|
Indicates whether or not the column is unique.
The default is The value of this property is the same for each occurrence of the base table column in the select list. |
|
|
|
Indicates whether or not the column is a key column.
This set of columns can be generated from one of the following in descending order of priority:
An explicitly selected |
|
|
|
|
|
|
|
The name of the column in the database if an alias is used for the column. |
|
|
|
The name of the schema in the database that contains the column. |
|
|
|
The name of the table or view in the database that contains the column. |
|
|
|
Maps to the common language runtime type. |
|
|
|
The database column type ( |
|
|
|
|
|
|
|
|
|
|
This value is always |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The type name of the UDT. |
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();
}
}
This method returns the string value of the specified column.
// ADO.NET 2.0: C#
public override string GetString(int index);
// ADO.NET 1.x: C# public string GetString(int index);
index
The zero-based column index.
The string value of the column.
IDataRecord
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.
Call the IsDBNull method to check for null values before calling this method.
If the column is an Oracle REF column, the string returned is a hexadecimal string that represents the REF in the database.
This method returns the TimeSpan value of the specified INTERVAL DAY TO SECOND column.
// C#
public TimeSpan GetTimeSpan(int index);
index
The zero-based column index.
The TimeSpan value of the column.
IDataRecord
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.
IsDBNull should be called to check for NULL values before calling this method.
This method returns the column value as a .NET type.
// ADO.NET 2.0: C#
public override object GetValue(int index);
// ADO.NET 1.x: C# public object GetValue(int index);
index
The zero-based column index.
The value of the column as a .NET type.
IDataRecord
InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, all rows have been read, or no valid custom type mapping has been specified for the Oracle Object or Oracle Collection column.
IndexOutOfRangeException - The column index is invalid.
If the column is an Oracle Object or an Oracle Collection column, the .NET custom type corresponding to the custom type mapping is returned.
If the column is an Oracle REF column, a hexidecimal value is returned as a .NET string that represents the REF in the database.
If the UDT is NULL, DBNull.Value is returned
This method gets all the column values as .NET types.
// ADO.NET 2.0: C#
public override int GetValues(object[ ] values);
// ADO.NET 1.x: C# public int GetValues(object[ ] values);
values
An array of objects to hold the .NET types as the column values.
The number of objects in the values array.
IDataRecord
InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.
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.
This method returns the contents of an XMLType column as an instance of an .NET XmlTextReader object.
// C#
public XmlReader GetXmlReader(int index);
index
The zero-based column index.
A .NET XmlTextReader.
InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.
IsDBNull should be called to check for NULL values before calling this method.
This method indicates whether or not the column value is NULL.
// ADO.NET 2.0: C#
public override bool IsDBNull(int index);
// ADO.NET 1.x: C# public bool IsDBNull(int index);
index
The zero-based column index.
Returns true if the column is a NULL value; otherwise, returns false.
IDataRecord
InvalidOperationException - The reader is closed, Read() has not been called, or all rows have been read.
IndexOutOfRangeException - The column index is invalid.
This method should be called to check for NULL values before calling the other accessor methods.
The code example for the OracleDataReader class includes the IsDBNull method. See "Example".
This method advances the data reader to the next result set.
// ADO.NET 2.0: C# public override bool NextResult();
// ADO.NET 1.x: C#
public bool NextResult();
Returns true if another result set exists; otherwise, returns false.
IDataReader
InvalidOperationException - The connection is closed or the reader is closed.
NextResult is used when reading results from stored procedure execution that return more than one result set.
This method reads the next row in the result set.
// ADO.NET 2.0: C# public override bool Read();
// ADO.NET 1.x: C#
public bool Read();
Returns true if another row exists; otherwise, returns false.
IDataReader
InvalidOperationException - The connection is closed or the reader is closed.
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.
The code example for the OracleDataReader class includes the Read method. See "Example".