Skip Headers

Oracle® Data Provider for .NET Developer's Guide
10g Release 1 (10.1)

Part Number B10117-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
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

Oracle Data Provider Classes

This chapter describes the classes and public methods Oracle Data Provider for .NET exposes for ADO.NET programmers. They are:


OracleCommand Class

An OracleCommand object represents a SQL command, a stored procedure, or a table name. The OracleCommand object is responsible for formulating the request and passing it to the database. If results are returned, OracleCommand is responsible for returning results as an OracleDataReader, a .NET XmlReader, a .NET Stream, a scalar value, or as output parameters.


Class Inheritance

Object

  MarshalByRefObject

    Component

      OracleCommand


Declaration
// C#
public sealed class OracleCommand : Component, IDbCommand, ICloneable

Thread Safety

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


Remarks

The execution of any transaction-related statements from an OracleCommand is not recommended because it is not reflected in the state of the OracleTransaction object represents the current local transaction, if one exists.

ExecuteXmlReader, ExecuteStream, and ExecuteToStream methods are only supported for XML operations.

ExecuteReader and ExecuteScalar methods are not supported for XML operations.


Example
// C#
...
string conStr = "User Id=scott;Password=tiger;Data Source=oracle";

// Create the OracleConnection
OracleConnection con = new OracleConnection(conStr);
con.Open();

string cmdQuery = "select ename, empno from emp";

// Create the OracleCommand
OracleCommand cmd = new OracleCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;

// Execute command, create OracleDataReader object
OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    // output Employee Name and Number
    Console.WriteLine("Employee Name : " + reader.GetString(0) + " , " +
                      "Employee Number : " + reader.GetDecimal(1));
}

// Dispose OracleDataReader object
reader.Dispose();

// Dispose OracleCommand object
cmd.Dispose();

// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
...


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleCommand Members

OracleCommand members are listed in the following tables:


OracleCommand Constructors

OracleCommand constructors are listed in Table 4-1.

Table 4-1 OracleCommand Constructors

Constructor Description
OracleCommand Constructors Instantiates a new instance of OracleCommand class (Overloaded)


OracleCommand Static Methods

OracleCommand static methods are listed in Table 4-2.

Table 4-2 OracleCommand Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleCommand Properties

OracleCommand properties are listed in Table 4-3.

Table 4-3 OracleCommand Properties

Name Description
AddRowid Adds the ROWID as part of the select list
ArrayBindCount Specifies if the array binding feature is to be used and also specifies the maximum number of array elements to be bound in the Value property
BindByName Specifies the binding method in the collection
CommandText Specifies the SQL statement or stored procedure to run against the Oracle database or the XML data used to store changes to the Oracle database
CommandTimeout Not supported
CommandType Specifies the command type that indicates how the CommandText property is to be interpreted
Connection Specifies the OracleConnection object that is used to identify the connection to execute a command
Container Inherited from Component
FetchSize Specifies the size of OracleDataReader's internal cache to store result set data
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
Parameters Specifies the parameters for the SQL statement or stored procedure
RowSize Specifies the amount of memory needed by the OracleDataReader internal cache to store one row of data
XmlCommandType Specifies the type of XML operation on the OracleCommand
XmlQueryProperties Specifies the properties that are used when an XML document is created from the result set of a SQL query statement
XmlSaveProperties Specifies the properties that are used when an XML document is used to save changes to the database


OracleCommand Public Methods

OracleCommand public methods are listed in Table 4-4.

Table 4-4 OracleCommand Public Methods

Public Method Description
Cancel Not Supported
Clone Creates a copy of OracleCommand object
CreateObjRef Inherited from MarshalByRefObject
CreateParameter Creates a new instance of OracleParameter class
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
ExecuteNonQuery Executes a SQL statement or a command using the XmlCommandType and CommandText properties and returns the number of rows affected
ExecuteReader Executes a command (Overloaded)
ExecuteScalar Returns the first column of the first row in the result set returned by the query
ExecuteStream Executes a command using the XmlCommandType and CommandText properties and returns the results in a new Stream object
ExecuteToStream Executes a command using the XmlCommandType and CommandText properties and appends the results as an XML document to the existing Stream
ExecuteXmlReader Executes a command using the XmlCommandType and CommandText properties and returns the result as an XML document in a .NET XmlTextReader object
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
Prepare This method is a no-op
ToString Inherited from Object

OracleCommand Constructors

OracleCommand constructors instantiate new instances of OracleCommand class.


Overload List:

OracleCommand()

This constructor instantiates a new instance of OracleCommand class.


Declaration
// C#
public OracleCommand();

Remarks

Default constructor.


OracleCommand(string)

This constructor instantiates a new instance of OracleCommand class using the supplied SQL command or stored procedure, and connection to the Oracle database.


Declaration
// C#
public OracleCommand(string cmdText);

Parameters

OracleCommand(string, OracleConnection)

This constructor instantiates a new instance of OracleCommand class using the supplied SQL command or stored procedure, and connection to the Oracle database.


Declaration
// C#
public OracleCommand(string cmdText, OracleConnection OracleConnection);

Parameters

OracleCommand Static Methods

OracleCommand static methods are listed in Table 4-5.

Table 4-5 OracleCommand Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleCommand Properties

OracleCommand properties are listed in Table 4-6.

Table 4-6 OracleCommand Properties

Name Description
AddRowid Adds the ROWID as part of the select list
ArrayBindCount Specifies if the array binding feature is to be used and also specifies the maximum number of array elements to be bound in the Value property
BindByName Specifies the binding method in the collection
CommandText Specifies the SQL statement or stored procedure to run against the Oracle database or the XML data used to store changes to the Oracle database
CommandTimeout Not supported
CommandType Specifies the command type that indicates how the CommandText property is to be interpreted
Connection Specifies the OracleConnection object that is used to identify the connection to execute a command
Container Inherited from Component
FetchSize Specifies the size of OracleDataReader's internal cache to store result set data
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
Parameters Specifies the parameters for the SQL statement or stored procedure
RowSize Specifies the amount of memory needed by the OracleDataReader internal cache to store one row of data
Site Inherited from Component
Transaction Specifies the OracleTransaction object in which the OracleCommand executes
UpdatedRowSource Specifies how query command results are applied to the row being updated
XmlCommandType Specifies the type of XML operation on the OracleCommand
XmlQueryProperties Specifies the properties that are used when an XML document is created from the result set of a SQL query statement
XmlSaveProperties Specifies the properties that are used when an XML document is used to save changes to the database


AddRowid

This property adds the ROWID as part of the select list.


Declaration
// C#
public bool AddRowid {get; set;}

Property Value

bool


Remarks

Default is false.

This ROWID column is hidden and is not accessible by the application. To gain access to the ROWIDs of a table, the ROWID must explicitly be added to the select list without the use of this property.


See Also:



ArrayBindCount

This property specifies if the array binding feature is to be used and also specifies the number of array elements to be bound in the OracleParameter Value property.


Declaration
// C#
public int ArrayBindCount {get; set;}

Property Value

An int value that specifies number of array elements to be bound in the OracleParameter Value property.


Exceptions

ArgumentException - The ArrayBindCount value specified is invalid.


Remarks

Default = 0.

If ArrayBindCount is equal to 0, array binding is not used; otherwise, array binding is used and OracleParameter Value property is interpreted as an array of values. The value of ArrayBindCount must be specified to use the array binding feature.

If neither DbType nor OracleDbType is set, it is strongly recommended that you set ArrayBindCount before setting the OracleParameter Value property so that inference of DbType and OracleDbType from Value can be correctly done.

Array binding is not used by default.

If the XmlCommandType property is set to any value other than None, this property is ignored.


BindByName

This property specifies the binding method in the collection.


Declaration
// C#
public bool BindByName {get; set;}

Property Value

Returns true if the parameters are bound by name; returns false if the parameters are bound by position.


Remarks

Default = false.

BindByName is supported only for OracleCommand.CommandType = CommandType.Text, not for OracleCommand.CommandType = CommandType.StoredProcedure.

BindByName is ignored under the following conditions:

If the XmlCommandType property is OracleXmlCommandType.Query and any parameters are set on the OracleCommand, the BindByName property must be set to true. Otherwise, the following OracleCommand methods throw an InvalidOperationException.


CommandText

This property specifies the SQL statement or stored procedure to run against the Oracle database or the XML data used to store changes to the Oracle database.


Declaration
// C#
public string CommandText {get; set;}

Property Value

A string.


Implements

IDbCommand


Remarks

The default is an empty string.

When the CommandType property is set to StoredProcedure, the CommandText property is set to the name of the stored procedure. The command calls this stored procedure when an Execute method is called.

The effects of XmlCommandType values on CommandText are:


CommandType

This property specifies the command type that indicates how the CommandText property is to be interpreted.


Declaration
// C#
public System.Data.CommandType CommandType {final get; final set;}

Property Value

A CommandType.


Exceptions

ArgumentException - The value is not a valid CommandType such as: CommandType.Text, CommandType.StoredProcedure, CommandType.TableDirect.


Remarks

Default = CommandType.Text

If the value of the XmlCommandType property is not None, then the CommandType property is ignored.


Connection

This property specifies the OracleConnection object that is used to identify the connection to execute a command.


Declaration
// C#
public OracleConnection Connection {get; set;}

Property Value

An OracleConnection object.


Implements

IDbCommand


Remarks

Default = null


FetchSize

This property specifies the size of OracleDataReader's internal cache to store result set data.


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

Property Value

A long that specifies the size (in bytes) of the OracleDataReader's internal cache.


Exceptions

ArgumentException - The FetchSize value specified is invalid.


Remarks

Default = 65536.

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 the OracleDataReader fetches into its internal cache for each server round-trip.

If the XmlCommandType property is set to any value other than None, this property is ignored.


InitialLOBFetchSize

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


Declaration
// C#
public int InitialLOBFetchSize  {get; set;}

Property Value

An int specifying the amount.


Exceptions

ArgumentException - The InitialLOBFetchSize value specified is invalid.


Remarks

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

Default = 0.

The value of InitialLOBFetchSize specifies the initial amount of LOB data that is immediately fetched by the OracleDataReader. The property value specifies the number of characters for CLOB and NCLOB data and the number of bytes for BLOB data. To fetch more than the specified InitialLOBFetchSize amount, one of the following must be in the select list:

The InitialLOBFetchSize value is used to determine the length of the LOB column data to fetch if LOB column is in the select list. If the select list does not contain a LOB column, the InitialLOBFetchSize value is ignored.

A primary key, a ROWID, or unique columns are not required if this property is set to 0.

If the InitialLOBFetchSize is set to a nonzero value, GetOracleBlob() and GetOracleClob() methods are disabled. BLOB and CLOB data are fetched by using GetBytes() and GetChars(), respectively.


InitialLONGFetchSize

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


Declaration
// C#
public int InitialLONGFetchSize  {get; set;}

Property Value

An int specifying the amount.


Exceptions

ArgumentException - The InitialLONGFetchSize value specified is invalid.


Remarks

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

The value of InitialLONGFetchSize specifies the initial amount of LONG or LONG RAW data that is immediately fetched by the OracleDataReader. The property value specifies the number of characters for LONG data and the number of bytes for LONG RAW. To fetch more than the specified InitialLONGFetchSize amount, one of the following must be in the select list:

The InitialLONGFetchSize value is used to determine the length of the LONG and LONG RAW column data to fetch if one of the two is in the select list. If the select list does not contain a LONG or a LONG RAW column, the InitialLONGFetchSize value is ignored.

Default = 0.

Setting this property to 0 defers the LONG and LONG RAW data retrieval entirely until the application specifically requests it.


Parameters

This property specifies the parameters for the SQL statement or stored procedure.


Declaration
// C#
public OracleParameterCollection Parameters {get;}

Property Value

OracleParameterCollection


Implements

IDbCommand


Remarks

Default value = an empty collection

The number of the parameters in the collection must be equal to the number of parameter placeholders within the command text, or an error is raised.

If the command text does not contain any parameter tokens (such as,:1,:2), the values in the Parameters property are ignored.


RowSize

This property specifies the amount of memory needed by the OracleDataReader internal cache to store one row of data.


Declaration
// C#
public long RowSize {get;}

Property Value

A long that indicates the amount of memory (in bytes) that an OracleDataReader needs to store one row of data for the executed query.


Remarks

Default value = 0

The RowSize property is set to a nonzero value after the execution of a command that returns a result set. This property can be used at design time or dynamically during run-time, to set the FetchSize, based on number of rows. For example, to enable the OracleDataReader to fetch N rows for each server round-trip, the OracleDataReader's FetchSize property can be set dynamically to RowSize * N. Note that for the FetchSize to take effect appropriately, it must be set after OracleCommand.ExecuteReader() but before OracleDataReader.Read().


Transaction

This property specifies the OracleTransaction object in which the OracleCommand executes.


Declaration
// C#
public OracleTransaction Transaction {get;}

Property Value

OracleTransaction


Implements

IDbCommand


Remarks

Default value = null

Transaction returns a reference to the transaction object associated with the OracleCommand connection object. Thus the command is executed in whatever transaction context its connection is currently in.


Note:

When this property is accessed through an IDbCommand reference, its set accessor method is not operational.


UpdatedRowSource

This property specifies how query command results are applied to the row to be updated.


Declaration
// C#
public System.Data.UpdateRowSource UpdatedRowSource {final get; final set;}

Property Value

An UpdateRowSource.


Implements

IDbCommand


Exceptions

ArgumentException - The UpdateRowSource value specified is invalid.


Remarks

Default = UpdateRowSource.None if the command is automatically generated. Default = UpdateRowSource.Both if the command is not automatically generated.


XmlCommandType

This property specifies the type of XML operation on the OracleCommand.


Declaration
// C#
public OracleXmlCommandType XmlCommandType {get; set;}

Property Value

An OracleXmlCommandType.


Remarks

Default value is None.

XmlCommandType values and usage:


XmlQueryProperties

This property specifies the properties that are used when an XML document is created from the result set of a SQL query statement.


Declaration
// C#
public OracleXmlQueryProperties XmlQueryProperties {get; set;}

Property Value

OracleXmlQueryProperties.


Remarks

When a new instance of OracleCommand is created, an instance of OracleXmlQueryProperties is automatically available on the OracleCommand instance through the OracleCommand.XmlQueryProperties property.

A new instance of OracleXmlQueryProperties can be assigned to an OracleCommand instance. Assigning an instance of OracleXmlQueryProperties to the XmlQueryProperties of an OracleCommand instance creates a new instance of the given OracleXmlQueryProperties instance for the OracleCommand. This way each OracleCommand instance has its own OracleXmlQueryProperties instance.

Use the default constructor to get a new instance of OracleXmlQueryProperties.

Use the OracleXmlQueryProperties.Clone() method to get a copy of an OracleXmlQueryProperties instance.


XmlSaveProperties

This property specifies the properties that are used when an XML document is used to save changes to the database.


Declaration
// C#
public OracleXmlSaveProperties XmlSaveProperties {get; set;}

Property Value

OracleXmlSaveProperties.


Remarks

When a new instance of OracleCommand is created, an instance of OracleXmlSaveProperties is automatically available on the OracleCommand instance through the OracleCommand.XmlSaveProperties property.

A new instance of OracleXmlSaveProperties can be assigned to an OracleCommand instance. Assigning an instance of OracleXmlSaveProperties to the XmlSaveProperties of an OracleCommand instance creates a new instance of the given OracleXmlSaveProperties instance for the OracleCommand. This way each OracleCommand instance has its own OracleXmlSaveProperties instance.

Use the default constructor to get a new instance of OracleXmlSaveProperties.

Use the OracleXmlSaveProperties.Clone() method to get a copy of an OracleXmlSaveProperties instance.

OracleCommand Public Methods

OracleCommand public methods are listed in Table 4-7.

Table 4-7 OracleCommand Public Methods

Public Method Description
Cancel Not Supported
Clone Creates a copy of OracleCommand object
CreateObjRef Inherited from MarshalByRefObject
CreateParameter Creates a new instance of OracleParameter class
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
ExecuteNonQuery Executes a SQL statement or a command using the XmlCommandType and CommandText properties and returns the number of rows affected
ExecuteReader Executes a command (Overloaded)
ExecuteScalar Returns the first column of the first row in the result set returned by the query
ExecuteStream Executes a command using the XmlCommandType and CommandText properties and returns the results in a new Stream object
ExecuteToStream Executes a command using the XmlCommandType and CommandText properties and appends the results as an XML document to the existing Stream
ExecuteXmlReader Executes a command using the XmlCommandType and CommandText properties and returns the result as an XML document in a .NET XmlTextReader object
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
Prepare This method is a no-op
ToString Inherited from Object


Clone

This method creates a copy of an OracleCommand object.


Declaration
// C#
public object Clone();

Return Value

An OracleCommand object.


Implements

ICloneable


Remarks

The cloned object has the same property values as that of the object being cloned.


Example
// C#
...
//Need a proper casting for the return value when cloned
OracleCommand cmd_cloned = (OracleCommand) cmd.Clone();
...

CreateParameter

This method creates a new instance of OracleParameter class.


Declaration
// C#
public OracleParameter CreateParameter();

Return Value

A new OracleParameter with default values.


Implements

IDbCommand


ExecuteNonQuery

This method executes a SQL statement or a command using the XmlCommandType and CommandText properties and returns the number of rows affected.


Declaration
// C#
public int ExecuteNonQuery();

Return Value

The number of rows affected.


Implements

IDbCommand


Exceptions

InvalidOperationException - The command cannot be executed.


Remarks

ExecuteNonQuery returns the number of rows affected, for the following:

For all other types of statements, the return value is -1.

ExecuteNonQuery is used for either of the following:

Although ExecuteNonQuery does not return any rows, it populates any output parameters or return values mapped to parameters with data.

If the XmlCommandType property is set to OracleXmlCommandType.Query then ExecuteNonQuery executes the select statement in the CommandText property, and if successful, returns -1. The XML document that is generated is discarded. This is useful for determining if the operation completes successfully without getting the XML document back as a result.

If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, or OracleXmlCommandType.Delete, then the value of the CommandText property is an XML document. ExecuteNonQuery saves the changes in that XML document to the table or view that is specified in the XmlSaveProperties property. The return value is the number of rows that are processed in the XML document. Also, each row in the XML document could affect multiple rows in the database, but the return value is still the number of rows in the XML document.


Example
// C#
...
OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;"+
         "Data Source=oracle");
OracleCommand cmd = new OracleCommand("update emp set sal = 3000" +
         "where empno=7934", con);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
...


Requirements

For XML support, this method requires Oracle9i XML Developer's Kits (Oracle XDK) or higher, to be installed in the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).

ExecuteReader

ExecuteReader executes a command specified in the CommandText.


Overload List:

ExecuteReader()

This method executes a command specified in the CommandText and returns an OracleDataReader object.


Declaration
// C#
public OracleDataReader ExecuteReader();

Return Value

An OracleDataReader.


Implements

IDbCommand


Exceptions

InvalidOperationException - The command cannot be executed.


Remarks

When the CommandType property is set to CommandType.StoredProcedure, the CommandText property should be set to the name of the stored procedure.

The command executes this stored procedure when you call ExecuteReader(). If parameters for the stored procedure consists of REF CURSORs, behavior differs depending on whether ExecuteReader() or ExecuteNonQuery() is called.

The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For further information, see "Obtaining LONG and LONG RAW Data".

If the value of the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete, or OracleXmlCommandType.Query then the ExecuteReader method throws an InvalidOperationException.


Example
// C#
...
OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;" 
       + "Data Source=oracle");
OracleCommand cmd = new OracleCommand("select ename from emp", con);
cmd.Connection.Open();
OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine("Employee Name : " + reader.GetString(0));
}
      
reader.Dispose();
cmd.Dispose();
...

ExecuteReader(CommandBehavior)

This method executes a command specified in the CommandText and returns an OracleDataReader object, using the specified behavior.


Declaration
// C#
public OracleDataReader ExecuteReader(CommandBehavior behavior);

Parameters

Return Value

An OracleDataReader.


Implements

IDbCommand


Exceptions

InvalidOperationException - The command cannot be executed.


Remarks

A description of the results and the effect on the database of the query command is indicated by the supplied behavior that specifies command behavior.

For valid CommandBehavior values and for the expected behavior of each CommandBehavior enumerated type, read the .NET Framework documentation.

When the CommandType property is set to CommandType.StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when ExecuteReader() is called.

If the stored procedure returns stored REF CURSORs, read the section on OracleRefCursors for more details. See "OracleRefCursor Class".

The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For more information, see "Obtaining LONG and LONG RAW Data".

If the value of the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete, or OracleXmlCommandType.Query then the ExecuteReader method throws an InvalidOperationException.


ExecuteScalar

This method executes the query using the connection, and returns the first column of the first row in the result set returned by the query.


Declaration
// C#
public object ExecuteScalar();

Return Value

An object which represents the value of the first row, first column.


Implements

IDbCommand


Exceptions

InvalidOperationException - The command cannot be executed.


Remarks

Extra columns or rows are ignored. ExecuteScalar retrieves a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader() method, and then performing the operations necessary to generate the single value using the data returned by an OracleDataReader.

If the query does not return any row, it returns null.

The ExecuteScalar method throws an InvalidOperationException, if the value of the XmlCommandType property is set to one of the following OracleXmlCommandType values: Insert, Update, Delete, Query.


Example
// C#
...
CmdObj.CommandText = "select count(*) from emp";
decimal count = (decimal) CmdObj.ExecuteScalar();
...


ExecuteStream

This method executes a command using the XmlCommandType and CommandText properties and returns the result as an XML document in a new Stream object.


Declaration
// C#
public Stream ExecuteStream();

Return Value

A Stream.


Remarks

The behavior of ExecuteStream varies depending on the XmlCommandType property value:


Requirements

For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).


ExecuteToStream

This method executes a command using the XmlCommandType and CommandText properties and appends the result as an XML document to the existing Stream provided by the application.


Declaration
// C#
public void ExecuteToStream(Stream outputStream);

Parameters

Remarks

The behavior of ExecuteToStream varies depending on the XmlCommandType property value:


Requirements

For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).


ExecuteXmlReader

This method executes the command using the XmlCommandType and CommandText properties and returns the result as an XML document in a .NET XmlTextReader object.


Declaration
// C#
public XmlReader ExecuteXmlReader();

Return Value

An XmlReader.


Remarks

The behavior of ExecuteXmlReader varies depending on the XmlCommandType property value:


Requirements

For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).


OracleCommandBuilder Class

An OracleCommandBuilder object provides automatic SQL generation for the OracleDataAdapter when updates are made to the database.


Class Inheritance

Object

  MarshalByRefObject

    Component

      OracleCommandBuilder


Declaration
// C#
public sealed class OracleCommandBuilder : Component

Thread Safety

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


Remarks

OracleCommandBuilder automatically generates SQL statements for single-table updates when the SelectCommand property of the OracleDataAdapter is set. An exception is thrown if the DataSet contains multiple tables. The OracleCommandBuilder registers itself as a listener for RowUpdating events whenever its DataAdapter property is set. Only one OracleDataAdapter object and one OracleCommandBuilder object can be associated with each other at one time.

To generate INSERT, UPDATE, or DELETE statements, the OracleCommandBuilder uses ExtendedProperties within the DataSet to retrieve a required set of metadata. If the SelectCommand is changed after the metadata is retrieved (for example, after the first update), the RefreshSchema method should be called to update the metadata.

OracleCommandBuilder first looks for the metadata from the ExtendedProperties of the DataSet; if the metadata is not available, OracleCommandBuilder uses the SelectCommand property of the OracleDataAdapter to retrieve the metadata.


Example

The OracleCommandBuilder examples in this section are based on the EMPINFO table which is defined as follows:

CREATE TABLE empInfo (
 empno NUMBER(4) PRIMARY KEY,
 empName VARCHAR2(20) NOT NULL,
 hiredate DATE,
 salary NUMBER(7,2),
 jobDescription Clob,
 byteCodes BLOB
);

The EMPINFO table has the following values:

EMPNO    EMPNAME    HIREDATE    SALARY     JOBDESCRIPTION  BYTECODES 
                                                          (Hex Values)
=====    =======    ========    ======     ==============  ============

    1    KING       01-MAY-81   12345.67   SOFTWARE ENGR   {0x12, 0x34}
    2    SCOTT      01-SEP-75   34567.89   MANAGER         {0x56, 0x78}
    3    BLAKE      01-OCT-90   9999.12    TRANSPORT       {0x23, 0x45}
    4    SMITH      NULL        NULL       NULL             NULL

The following example uses the OracleCommandBuilder object to create the UpdateCommand for the OracleDataAdapter object when OracleDataAdapter.Update() is called.

// C#
public static void BuilderUpdate(string connStr)
{
  string cmdStr = "SELECT EMPNO, EMPNAME, JOBDESCRIPTION FROM EMPINFO";

  //create the adapter with the selectCommand txt and the
  //connection string
  OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr);

  //get the connection from the adapter
  OracleConnection connection = adapter.SelectCommand.Connection;

  //create the builder for the adapter to automatically generate
  //the Command when needed
  OracleCommandBuilder builder = new OracleCommandBuilder(adapter);

  //Create and fill the DataSet using the EMPINFO
  DataSet dataset = new DataSet();
  adapter.Fill(dataset, "EMPINFO");

  //Get the EMPINFO table from the dataset
  DataTable table = dataset.Tables["EMPINFO"];

  //Get the first row from the EMPINFO table
  DataRow row0 = table.Rows[0];

  //update the job description in the first row
  row0["JOBDESCRIPTION"] = "MANAGER";

  //Now update the EMPINFO using the adapter, the job description
  //of 'KING' is changed to 'MANAGER'
  //The OracleCommandBuilder will create the UpdateCommand for the
  //adapter to update the EMPINFO table
  adapter.Update(dataset, "EMPINFO");

}


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleCommandBuilder Members

OracleCommandBuilder members are listed in the following tables:


OracleCommandBuilder Constructors

OracleCommandBuilder constructors are listed in Table 4-8.

Table 4-8 OracleCommandBuilder Constructors

Constructor Description
OracleCommandBuilder Constructors Instantiates a new instance of OracleCommandBuilder class (Overloaded)


OracleCommandBuilder Static Methods

OracleCommandBuilder static methods are listed in Table 4-9.

Table 4-9 OracleCommandBuilder Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleCommandBuilder Properties

OracleCommandBuilder properties are listed in Table 4-10.

Table 4-10 OracleCommandBuilder Properties

Name Description
Container Inherited from Component
DataAdapter Indicates the OracleDataAdapter for which the SQL statements are generated
CaseSensitive Indicates whether or not double quotes are used around Oracle object names when generating SQL statements
Site Inherited from Component


OracleCommandBuilder Public Methods

OracleCommandBuilder public methods are listed in Table 4-11.

Table 4-11 OracleCommandBuilder Public Methods

Public Method Description
CreateObjRef Inherited from MarshalByRefObject
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
GetDeleteCommand Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform deletions on the database
GetHashCode Inherited from Object
GetInsertCommand Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform insertions on the database
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
GetUpdateCommand Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform updates on the database
InitializeLifetimeService Inherited from MarshalByRefObject
RefreshSchema Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements
ToString Inherited from Object


OracleCommandBuilder Events

OracleCommandBuilder events are listed in Table 4-12.

Table 4-12 OracleCommandBuilder Events

Event Name Description
Disposed Inherited from Component


OracleCommandBuilder Event Delegates

OracleCommandBuilder event delegates are listed in Table 4-13.

Table 4-13 OracleCommandBuilder Event Delegates

Event Delegate Name Description
EventHandler Inherited from Component

OracleCommandBuilder Constructors

OracleCommandBuilder constructors create new instances of the OracleCommandBuilder class.


Overload List:

OracleCommandBuilder()

This constructor creates an instance of the OracleCommandBuilder class.


Declaration
// C#
public OracleCommandBuilder();

Remarks

Default constructor.


OracleCommandBuilder(OracleDataAdapter)

This constructor creates an instance of the OracleCommandBuilder class and sets the DataAdapter property to the provided OracleDataAdapter object.


Declaration
// C#
public OracleCommandBuilder(OracleDataAdapter da);

Parameters

OracleCommandBuilder Static Methods

OracleCommandBuilder properties are listed in Table 4-14.

Table 4-14 OracleCommandBuilder Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleCommandBuilder Properties

OracleCommandBuilder properties are listed in Table 4-15.

Table 4-15 OracleCommandBuilder Properties

Name Description
Container Inherited from Component
DataAdapter Indicates the OracleDataAdapter for which the SQL statements are generated
CaseSensitive Indicates whether or not double quotes are used around Oracle object names when generating SQL statements
Site Inherited from Component


DataAdapter

This property indicates the OracleDataAdapter for which the SQL statements are generated.


Declaration
// C#
OracleDataAdapter DataAdapter{get; set;}

Property Value

OracleDataAdapter


Remarks

Default = null


CaseSensitive

This property indicates whether or not double quotes are used around Oracle object names (for example, tables or columns) when generating SQL statements.


Declaration
// C#
bool CaseSensitive {get; set;}

Property Value

A bool that indicates whether or not double quotes are used.


Remarks

Default = false

OracleCommandBuilder Public Methods

OracleCommandBuilder public methods are listed in Table 4-16.

Table 4-16 OracleCommandBuilder Public Methods

Public Method Description
CreateObjRef Inherited from MarshalByRefObject
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
GetDeleteCommand Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform deletions on the database
GetHashCode Inherited from Object
GetInsertCommand Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform insertions on the database
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
GetUpdateCommand Gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform updates on the database
InitializeLifetimeService Inherited from MarshalByRefObject
RefreshSchema Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements
ToString Inherited from Object


GetDeleteCommand

This method gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform deletions on the database when an application calls Update() on the OracleDataAdapter.


Declaration
// C#
public OracleCommand GetDeleteCommand();

Return Value

An OracleCommand.


Exceptions

ObjectDisposedException - The OracleCommandBuilder object is already disposed.

InvalidOperationException - Either the SelectCommand or the DataAdapter property is null, or the primary key cannot be retrieved from the SelectCommand property of the OracleDataAdapter.


GetInsertCommand

This method gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform insertions on the database when an application calls Update() on the OracleDataAdapter.


Declaration
// C#
public OracleCommand GetInsertCommand();

Return Value

An OracleCommand.


Exceptions

ObjectDisposedException - The OracleCommandBuilder object is already disposed.

InvalidOperationException - Either the SelectCommand or the DataAdapter property is null, or the primary key cannot be retrieved from the SelectCommand property of the OracleDataAdapter.


GetUpdateCommand

This method gets the automatically generated OracleCommand object that has the SQL statement (CommandText) perform updates on the database when an application calls Update() on the OracleDataAdapter.


Declaration
// C#
public OracleCommand GetUpdateCommand();

Return Value

An OracleCommand.


Exceptions

ObjectDisposedException - The OracleCommandBuilder object is already disposed.

InvalidOperationException - Either the SelectCommand or the DataAdapter property is null, or the primary key cannot be retrieved from the SelectCommand property of the OracleDataAdapter.


RefreshSchema

This method refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements.


Declaration
// C#
public void RefreshSchema();

Remarks

An application should call RefreshSchema whenever the SelectCommand value of the OracleDataAdapter changes.

OracleCommandBuilder Events

OracleCommandBuilder events are listed in Table 4-17.

Table 4-17 OracleCommandBuilder Events

Event Name Description
Disposed Inherited from Component

OracleCommandBuilder Event Delegates

OracleCommandBuilder event delegates are listed in Table 4-18.

Table 4-18 OracleCommandBuilder Event Delegates

Event Delegate Name Description
EventHandler Inherited from Component


OracleConnection Class

An OracleConnection object represents a connection to an Oracle database.


Class Inheritance

Object

  MarshalByRefObject

    Component

      OracleConnection


Declaration
// C#
public sealed class OracleConnection : Component,
   IDbConnection, ICloneable  

Thread Safety

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


Example
// C#
//  Uses connection to create and return an OracleCommand object.
...
string ConStr = "User Id=myschema;Password=mypassword;" +
      "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();
OracleCommand cmd = con.CreateCommand();

cmd.CommandText = "insert into mytable values (99, 'foo')";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
...


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleConnection Members

OracleConnection members are listed in the following tables:


OracleConnection Constructors

OracleConnection constructors are listed in Table 4-19.

Table 4-19 OracleConnection Constructors

Constructor Description
OracleConnection Constructors Instantiates a new instance of the OracleConnection class (Overloaded)


OracleConnection Static Methods

OracleConnection static methods are listed in Table 4-20.

Table 4-20 OracleConnection Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleConnection Properties

OracleConnection properties are listed in Table 4-21

Table 4-21 OracleConnection Properties

Name Description
ConnectionString Specifies connection information used to connect to an Oracle database
ConnectionTimeout Specifies the maximum amount of time that the Open() method can take to obtain a pooled connection before terminating the request
Container Inherited from Component
DataSource Specifies the Oracle Net Service Name (also known as TNS alias) that identifies an Oracle database instance
ServerVersion Specifies the version number of the Oracle database to which the OracleConnection has established a connection
Site Inherited from Component
State Specifies the current state of the connection


OracleConnection Public Methods

OracleConnection public methods are listed in Table 4-22.

Table 4-22 OracleConnection Public Methods

Public Method Description
BeginTransaction Begins a local transaction (Overloaded)
ChangeDatabase Not Supported
Clone Creates a copy of an OracleConnection object
Close Closes the database connection
CreateCommand Creates and returns an OracleCommand object associated with the OracleConnection object
CreateObjRef Inherited from MarshalByRefObject
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetSessionInfo Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded)
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
Open Opens a database connection with the property settings specified by the ConnectionString
OpenWithNewPassword Opens a new connection with the new password
SetSessionInfo Alters the session's globalization settings with the property values provided by the OracleGlobalization object
ToString Inherited from Object


OracleConnection Events

OracleConnection events are listed in Table 4-23.

Table 4-23 OracleConnection Events

Event Name Description
Disposed Inherited from Component
Failover An event that is triggered when an Oracle failover occurs
InfoMessage An event that is triggered for any message or warning sent by the database
StateChange An event that is triggered when the connection state changes


OracleConnection Event Delegates

OracleConnection event delegates are listed in Table 4-24.

Table 4-24 OracleConnection Event Delegates

Event Delegate Name Description
OracleFailoverEventHandler An event delegate that handles the Failover event
OracleInfoMessageEventHandler An event delegate that handles the InfoMessage event
StateChangeEventHandler An event delegate that handles the StateChange event

OracleConnection Constructors

OracleConnection constructors instantiate new instances of the OracleConnection class.


Overload List:

OracleConnection()

This constructor instantiates a new instance of the OracleConnection class using default property values.


Declaration
// C#
public OracleConnection();

Remarks

The properties for OracleConnection are set to the following default values:


OracleConnection(String)

This constructor instantiates a new instance of the OracleConnection class with the provided connection string.


Declaration
// C#
public OracleConnection(String connectionString);

Parameters

Remarks

The ConnectionString property is set to the supplied connectionString. The ConnectionString property is parsed and an exception is thrown if it contains invalid connection string attributes or attribute values.

The properties of the OracleConnection object default to the following values unless they are set by the connection string:

OracleConnection Static Methods

OracleConnection static methods are listed in Table 4-25.

Table 4-25 OracleConnection Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleConnection Properties

OracleConnection properties are listed in Table 4-26

Table 4-26 OracleConnection Properties

Name Description
ConnectionString Specifies connection information used to connect to an Oracle database
ConnectionTimeout Specifies the maximum amount of time that the Open() method can take to obtain a pooled connection before terminating the request
Container Inherited from Component
DataSource Specifies the Oracle Net Service Name (also known as TNS alias) that identifies an Oracle database instance
ServerVersion Specifies the version number of the Oracle database to which the OracleConnection has established a connection
Site Inherited from Component
State Specifies the current state of the connection


ConnectionString

This property specifies connection information used to connect to an Oracle database.


Declaration
// C#
public string ConnectionString{get; set;} 

Property Value

If the connection string is supplied through the constructor, this property is set to that string.


Implements

IDbConnection


Exceptions

ArgumentException - An invalid syntax is specified for the connection string.

InvalidOperationException - ConnectionString is being set while the connection is open.


Remarks

The default value is an empty string.

ConnectionString must be a string of attribute name and value pairings, separated by a semi-colon, for example:

// C#
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=MYSCHEMA;Password=MYPASSWORD;" + 
   "Data Source=Oracle";

If the ConnectionString is not in a proper format, an exception is thrown. All spaces are ignored unless they are within double quotes.

When the ConnectionString property is set, the OracleConnection object immediately parses the string for errors. An ArgumentException is thrown if the ConnectionString contains invalid attributes or invalid values. Attribute values for User Id, Password, Proxy User Id, Proxy Password, and Data Source (if provided) are not validated until the Open method is called.

The connection must be closed to set the ConnectionString property. When the ConnectionString property is reset, all previously set values are reinitialized to their default values before the new values are applied.

The Oracle database supports case-sensitive user names. To connect as a user whose name is of mixed case, for example, "MySchema", the User Id attribute value must be surrounded by double quotes, as follows:

// C#
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=\"MySchema\";Password=MYPASSWORD;" +
    "Data Source=Oracle";

However, if the Oracle user name is all upper case, the User Id connection string attribute can be set to that user name without the use of the double quotes since User Ids that are not doubled-quoted are converted to all upper case when connecting. Single quotes are not supported.


See Also:

" Example" for a complete example

If a connection string attribute is set more than once, the last setting takes effect and no exceptions are thrown.

Boolean connection string attributes can be set to either true, false, yes, or no.

Supported connection string attributes:

Table 4-27 lists the supported connection string attributes.

Table 4-27 Supported Connection String Attributes

Connection String Attribute Default value Description
Connection Lifetime 0 Maximum life time (in seconds) of the connection

This attribute specifies the lifetime of the connection in seconds. Before the Connection is placed back into the pool, the lifetime of the connection is checked. If the lifetime of the connection exceeds this property value, the connection is closed and disposed. If this property value is 0, the connection lifetime is never checked. Connections that have exceeded their lifetimes are not closed and disposed of, if doing so brings the number of connection in the pool below the Min Pool Size.

Connection Timeout 15 Maximum time (in seconds) to wait for a free connection from the pool

This attribute specifies the maximum amount of time (in seconds) that the Open() method can take to obtain a pooled connection before it terminates the request. This value comes into effect only if no free connection is available from the connection pool and the Max Pool Size is reached. If a free connection is not available within the specified time, an exception is thrown. Connection Timeout does not limit the time required to open new connections.

This attribute value takes effect for pooled connection requests and not for new connection requests.

Data Source empty string Oracle Net Service Name that identifies the database to connect to

This attribute specifies the Oracle Net Service Name (formerly known as TNS alias) that identifies an Oracle database instance. This attribute must be set to connect to a remote database.

DBA Privilege empty string Administrative privileges SYSDBA or SYSOPER

This connection string attribute only accepts SYSDBA or SYSOPER as the attribute value. It is case insensitive.

Decr Pool Size 1 Number of connections that are closed when an excessive amount of established connections are unused.
   
This connection string attribute controls the maximum number of unused connections that are closed when the pool regulator makes periodic checks. The regulator thread is spawned every 3 minutes and closes up to Decr Pool Size amount of pooled connections if they are not used. The pool regulator never takes the total number of connections below the Min Pool Size by closing pooled connections.
Enlist true Serviced Components automatically enlist in distributed transactions

If this attribute is set to true, the connection is automatically enlisted in the thread's transaction context. If this attribute is false, no enlistments are made. This attribute can be set to either true, false, yes, or no.

Incr Pool Size 5 Number of connections established when all connections in pool are used

This connection string attribute determines the number of new connections that are established when a pooled connection is requested, but no unused connections are available and Max Pool Size is not reached. If new connections have been created for a pool, the regulator thread skips a cycle and does not have an opportunity to close any connections for 6 minutes. Note, however, that some connections can be still be closed during this time if their lifetime has been exceeded.

Max Pool Size 100 Maximum number of connections in a pool

This attribute specifies the maximum number of connections allowed in the particular pool used by that OracleConnection. Simply changing this attribute in the connection string does not change the Max Pool Size restriction on a currently existing pool. Doing so simply creates a new pool with a different Max Pool Size restriction. This attribute must be set to a value greater than the Min Pool Size. This value is ignored unless Pooling is turned on.

Min Pool Size 1 Minimum number of connections in a pool

This attribute specifies the minimum number of connections to be maintained by the pool during its entire lifetime. Simply changing this attribute in the connection string does not change the Min Pool Size restriction on a currently existing pool. Doing so simply creates a new pool with a different Min Pool Size restriction. This value is ignored unless Pooling is turned on.

Password empty string Password for the user specified by User Id

This attribute specifies an Oracle user's password. Password is case insensitive.

Persist Security Info false Enables or disables the retrieval of password in the connection string

If this attribute is set to false, the Password value setting is not returned when the application requests the ConnectionString after the connection is successfully opened by the Open() method. This attribute can be set to either true, false, yes, or no.

Pooling true Enables or disables connection pooling

This attribute specifies whether connection pooling is to be used. Pools are created using an attribute value matching algorithm. This means that connection strings which only differ in the number of spaces in the connection string use the same pool. If two connection strings are identical except that one sets an attribute to a default value while the other does not set that attribute, both requests obtain connections from the same pool. This attribute can be set to either true, false, yes, or no.

Proxy User Id empty string User name of the proxy user

This connection string attribute specifies the middle-tier user, or the proxy user, who establishes a connection on behalf of a client user specified by the User Id attribute. ODP.NET attempts to establish a proxy connection if either the Proxy User Id or the Proxy Password attribute is set to a non-empty string.

   
For the proxy user to connect to an Oracle database using operating system authentication, the Proxy User Id must be set to "/". The Proxy Password is ignored in this case. The User Id cannot be set to "/" when establishing proxy connections. The case of this attribute value is preserved if it is surrounded by double quotes.
Proxy Password empty string Password of the proxy user

This connection string attribute specifies the password of the middle-tier user or the proxy user. This user establishes a connection on behalf of a client user specified by the User Id attribute. ODP.NET attempts to establish a proxy connection if either the Proxy User Id or the Proxy Password attribute is set to a non-empty string.

User Id empty string Oracle user name

This attribute specifies the Oracle user name. The case of this attribute value is preserved if it is surrounded by double quotes. For the user to connect to an Oracle database using operating system authentication, set the User Id to "/". Any Password attribute setting is ignored in this case.



Example

This code example shows that the case of the User Id attribute value is not preserved unless it is surrounded by double quotes. The example also demonstrates when connection pools are created and when connections are drawn from the connection pool.

// C#
// Assume users "MYSCHEMA"and "MySchema" exist in the database
...
OracleConnection con1 = new OracleConnection();
con1.ConnectionString = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
con1.Open();  // Attempts to connect as "MYSCHEMA/MYPASSWORD"
            // A new connection is created; A new Connection Pool X is created
con1.Dispose(); // Connection is placed back into Pool X

OracleConnection con2 = new OracleConnection();
con2.ConnectionString = "User Id=MySchema;Password=MyPassword;" +
     "Data Source=oracle;";
con2.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is
             // obtained from Pool X; A new connection is NOT created.
con2.Dispose(); // Connection is placed back into Pool X

OracleConnection con3 = new OracleConnection();
con3.ConnectionString = "User Id=\"MYSCHEMA\";Password=MYPASSWORD;" +
     "Data Source=oracle;";
con3.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is
             // obtained from Pool X; A new connection is NOT created.
con3.Dispose(); // Connection is placed back into Pool X

OracleConnection con4 = new OracleConnection();
con4.ConnectionString = "User Id=\"MySchema\";Password=mypassword;" +
     "Data Source=oracle;";
con4.Open(); // Attempts to connect as "MySchema/MYPASSWORD"
             // A new connection is created; A new Connection Pool Y is created
con4.Dispose(); // Connection is placed back into Pool Y

OracleConnection con5 = new OracleConnection();
con5.ConnectionString = "User Id=MySchema;Password=mypassword;" +
     "Data Source=oracle;    ";
con5.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD"
             // A connection is obtained from Connection Pool X
             // Extra spaces in the connection string do not force creation
             // of a new pool
con5.Dispose(); // Connection is placed back into Pool X

OracleConnection con6 = new OracleConnection();
con6.ConnectionString = "User Id=MySchema;Password=mypassword;" +
     "Data Source=oracle;Pooling=true;";
con6.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD"
             // A connection is obtained from Connection Pool X. "Pooling=true"
             // in the connection string does not force creation of a new pool
             // since the initial connection was established using the default
             // value of "Pooling=true".  Note that even if the connection
             // string had "POOLING=Yes", a new connection pool will not be
             // created since they both enable pooling.  The same rule applies
             // to other connection string attributes as well.
con6.Dispose(); // Connection is placed back into Pool X
...


ConnectionTimeout

This property specifies the maximum amount of time that the Open() method can take to obtain a pooled connection before terminating the request.


Declaration
// C#
public int ConnectionTimeout {get;}

Property Value

The maximum time allowed for a pooled connection request, in seconds.


Implements

IDbConnection


Remarks

The default value is 15.

Setting this property to 0 allows the pooled connection request to wait for a free connection without a time limit. The timeout takes effect only for pooled connection requests and not for new connection requests.


DataSource

This property specifies the Oracle Net Service Name (formerly known as TNS alias) that identifies an Oracle database instance.


Declaration
// C#
public string DataSource {get;}

Property Value

The Oracle Net Service Name.


Remarks

The default value of this property is an empty string


ServerVersion

This property specifies the version number of the Oracle database to which the OracleConnection has established a connection.


Declaration
// C#
public string ServerVersion {get;}

Property Value

The version of the Oracle database, for example "9.2.0.1.0."


Exceptions

InvalidOperationException - The connection is closed.


Remarks

The default is an empty string.


State

This property specifies the current state of the connection.


Declaration
// C#
public ConnectionState State {get;}

Property Value

The ConnectionState of the connection.


Implements

IDbConnection


Remarks

ODP.NET supports ConnectionState.Closed and ConnectionState.Open for this property. The default value is ConnectionState.Closed.

OracleConnection Public Methods

OracleConnection public methods are listed in Table 4-28.

Table 4-28 OracleConnection Public Methods

Public Method Description
BeginTransaction Begins a local transaction (Overloaded)
ChangeDatabase Not Supported
Clone Creates a copy of an OracleConnection object
Close Closes the database connection
CreateCommand Creates and returns an OracleCommand object associated with the OracleConnection object
CreateObjRef Inherited from MarshalByRefObject
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetSessionInfo Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded)
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
Open Opens a database connection with the property settings specified by the ConnectionString
OpenWithNewPassword Opens a new connection with the new password
SetSessionInfo Alters the session's globalization settings with the property values provided by the OracleGlobalization object
ToString Inherited from Object

BeginTransaction

BeginTransaction methods begin local transactions.


Overload List

BeginTransaction()

This method begins a local transaction.


Declaration
// C#
public OracleTransaction BeginTransaction();

Return Value

An OracleTransaction object representing the new transaction.


Implements

IDbConnection


Exceptions

InvalidOperationException - A transaction has already been started.


Remarks

The transaction is created with its isolation level set to its default value of IsolationLevel.ReadCommitted. All further operations related to the transaction must be performed on the returned OracleTransaction object.


BeginTransaction(IsolationLevel)

This method begins a local transaction with the specified isolation level.


Declaration
// C#
public OracleTransaction BeginTransaction(IsolationLevel isolationLevel);

Parameters

Return Value

An OracleTransaction object representing the new transaction.


Implements

IDbConnection


Exceptions

InvalidOperationException - A transaction has already been started.

ArgumentException - The isolationLevel specified is invalid.


Remarks

The following two isolation levels are supported:

Requesting other isolation levels causes an exception.


Example
// C#
// Starts a transaction and inserts one record. If insert fails, rolls back
// the transaction. Otherwise, commits the transaction.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
        "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Create an OracleCommand object using the connection object
OracleCommand cmd = new OracleCommand("", con);

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

try
{
   cmd.CommandText = "insert into mytable values (99, 'foo')";
   cmd.CommandType = CommandType.Text;
   cmd.ExecuteNonQuery();
   txn.Commit();
   Console.WriteLine("Both records are inserted into the database table.");
}
catch(Exception e)
{
   txn.Rollback();
   Console.WriteLine("Neither record was inserted into the database table.");
 }
...


Clone

This method creates a copy of an OracleConnection object.


Declaration
// C#
public object Clone();

Return Value

An OracleConnection object.


Implements

ICloneable


Remarks

The cloned object has the same property values as that of the object being cloned.


Example
// C#
...
OracleConnection con = new OracleConnection(ConStr);
con.Open();
...

//Need a proper casting for the return value when cloned
OracleConnection con_cloned = (OracleConnection) con.Clone();
...


Close

This method closes the connection to the database.


Declaration
// C#
public void Close();

Implements

IDbConnection


Remarks

Performs the following:

The connection can be reopened using Open().


CreateCommand

This method creates and returns an OracleCommand object associated with the OracleConnection object.


Declaration
// C#
public OracleCommand CreateCommand();

Return Value

The OracleCommand object.


Implements

IDbConnection


Example
// C#
//  Uses connection to create and return an OracleCommand object.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
      "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

OracleCommand cmd = Con.CreateCommand();

cmd.CommandText = "insert into mytable values (99, 'foo')";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
...

GetSessionInfo

GetSessionInfo returns or refreshes an OracleGlobalization object that represents the globalization settings of the session.


Overload List:

GetSessionInfo()

This method returns a new instance of the OracleGlobalization object that represents the globalization settings of the session.


Declaration
// C#
public OracleGlobalization GetSessionInfo();

Return Value

The newly created OracleGlobalization object.


Example
// C#
//  Retrieves the session globalization info and prints the language name.
//  Then sets new territory, language, and timestamp format into the session
//  globalization info in the connection object.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
  "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

...
//Get session info from connection object
OracleGlobalization ogi = con.GetSessionInfo();

//Print the language name
Console.WriteLine(ogi.Language);

//Update session info
oraGlob.Territory = "JAPAN";
ogi.Language = "JAPANESE";
ogi.TimeStampFormat = "HH.MI.SSXFF AM YYYY-MM-DD";

//Set session info into connection object
con.SetSessionInfo(ogi);
...


GetSessionInfo(OracleGlobalization)

This method refreshes the provided OracleGlobalization object with the globalization settings of the session.


Declaration
// C#
public void GetSessionInfo(OracleGlobalization oraGlob);

Parameters

Open

This method opens a connection to an Oracle database.


Declaration
// C#
public void Open();

Implements

IDbConnection


Exceptions

ObjectDisposedException - The object is already disposed.

InvalidOperationException - The connection is already opened or the connection string is null or empty.


Remarks

The connection is obtained from the pool if connection pooling is enabled. Otherwise, a new connection is established.

It is possible that the pool does not contain any unused connections when the Open() method is invoked. In this case, a new connection is established.

If no connections are available within the specified connection timeout value, when the Max Pool Size is reached, an OracleException is thrown.


OpenWithNewPassword

This method opens a new connection with the new password.


Declaration
// C#
public void OpenWithNewPassword(string newPassword);

Parameters

Remarks

This method uses the ConnectionString property settings to establish a new connection. The old password must be provided in the connection string as the Password attribute value.

This method can only be called on an OracleConnection in the closed state.


SetSessionInfo

This method alters the session's globalization settings with all the property values specified in the provided OracleGlobalization object.


Declaration
// C#
public void SetSessionInfo(OracleGlobalization oraGlob);

Parameters

An OracleGlobalization object.


Remarks

Calling this method is equivalent to calling an ALTER SESSION SQL on the session.


Example
// C#

//  Retrieves the session globalization info and prints the language name.
//  Then sets new territory, language, and timestamp format into the session
//  globalization info in the connection object.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Create an OracleGlobalization object
OracleGlobalization ogi;

//Get session info using the second overloaded method
con.GetSessionInfo(ogi);

//Print the language name
Console.WriteLine(ogi.Language);

//Update session globalization info
oraGlob.Territory = "JAPAN";
ogi.Language = "JAPANESE";
ogi.TimeStampFormat = "HH.MI.SSXFF AM YYYY-MM-DD";

//Set session globalization info into connection object
con.SetSessionInfo(ogi);

OracleConnection Events

OracleConnection events are listed in Table 4-29.

Table 4-29 OracleConnection Events

Event Name Description
Disposed Inherited from Component
Failover An event that is triggered when an Oracle failover occurs
InfoMessage An event that is triggered for any message or warning sent by the database
StateChange An event that is triggered when the connection state changes


Failover

This event is triggered when an Oracle failover occurs.


Declaration
// C#
public event OracleFailoverEventHandler Failover;

Event Data

The event handler receives an OracleFailoverEventArgs object which exposes the following properties containing information about the event.


InfoMessage

This event is triggered for any message or warning sent by the database.


Declaration
// C#
public event OracleInfoMessageEventHandler InfoMessage;

Event Data

The event handler receives an OracleInfoMessageEventArgs object which exposes the following properties containing information about the event.


Remarks

In order to respond to warnings and messages from the database, the client should create an OracleInfoMessageEventHandler delegate to listen to this event.


StateChange

This event is triggered when the connection state changes.


Declaration
// C#
public event StateChangeEventHandler StateChange;

Event Data

The event handler receives a StateChangeEventArgs object which exposes the following properties containing information about the event.


Remarks

The StateChange event is raised after a connection changes state, whenever an explicit call is made to Open, Close or Dispose.

OracleConnection Event Delegates

OracleConnection event delegates are listed in Table 4-30.

Table 4-30 OracleConnection Event Delegates

Event Delegate Name Description
OracleFailoverEventHandler An event delegate that handles the Failover event
OracleInfoMessageEventHandler An event delegate that handles the InfoMessage event
StateChangeEventHandler An event delegate that handles the StateChange event


OracleFailoverEventHandler

This event delegate handles the Failover event.


OracleInfoMessageEventHandler

This event delegate handles the InfoMessage event.


StateChangeEventHandler

This event delegate handles the StateChange event.


See Also:


OracleDataAdapter Class

An OracleDataAdapter object represents a data provider object that populates the DataSet and updates changes in the DataSet to the Oracle database.


Class Inheritance

Object

  MarshalByRefObject

    Component

      DataAdapter

        DbDataAdapter

          OracleDataAdapter


Declaration
// C#
public sealed class OracleDataAdapter : DbDataAdapter, IDbDataAdapter

Thread Safety

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


Example

The OracleDataAdapter examples in this section are based on the EMPINFO table which is defined as follows:

CREATE TABLE empInfo (
    empno NUMBER(4) PRIMARY KEY,
    empName VARCHAR2(20) NOT NULL,
    hiredate DATE,
    salary NUMBER(7,2),
    jobDescription Clob,
    byteCodes BLOB
);

The EMPINFO table has the following values:

EMPNO    EMPNAME    HIREDATE    SALARY     JOBDESCRIPTION  BYTECODES 
                                                           (Hex Values)   
=====    =======    ========    ======     ==============  ============

    1    KING       01-MAY-81   12345.67   SOFTWARE ENGR   {0x12, 0x34}
    2    SCOTT      01-SEP-75   34567.89   MANAGER         {0x56, 0x78}
    3    BLAKE      01-OCT-90   9999.12    TRANSPORT       {0x23, 0x45}
    4    SMITH      NULL        NULL       NULL             NULL

The following example uses the OracleDataAdapter and the dataset to update the EMPINFO table:

// C# 
public static void AdapterUpdate(string connStr)
{
  string cmdStr = "SELECT EMPNO, EMPNAME, SALARY FROM EMPINFO";

  //create the adapter with the selectCommand txt and the
  //connection string
  OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr);

  //get the connection from the adapter
  OracleConnection connection = adapter.SelectCommand.Connection;

  //create the UpdateCommand object for updating the EMPINFO table
  //from the dataset
  adapter.UpdateCommand = new OracleCommand("UPDATE EMPINFO SET SALARY = "+
    " :iSALARY where EMPNO = :iEMPNO", connection);
  adapter.UpdateCommand.Parameters.Add(":iSALARY", OracleDbType.Double,    
    0, "SALARY");
  adapter.UpdateCommand.Parameters.Add(":iEMPNO", OracleDbType.Int16, 
        0, "EMPNO");

  //Create and fill the DataSet using the EMPINFO
  DataSet dataset = new DataSet();
  adapter.Fill(dataset, "EMPINFO");

  //Get the EMPINFO table from the dataset
  DataTable table = dataset.Tables["EMPINFO"];

  //Get the first row from the EMPINFO table
  DataRow row0 = table.Rows[0];

  //update the salary in the first row
  row0["SALARY"] = 99999.99;

  //Now update the EMPINFO using the adapter, the salary
  //of 'KING' is changed to 99999.99
  adapter.Update(dataset, "EMPINFO");

}


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleDataAdapter Members

OracleDataAdapter members are listed in the following tables:


OracleDataAdapter Constructors

OracleDataAdapter constructors are listed in Table 4-31.

Table 4-31 OracleDataAdapter Constructors

Constructor Description
OracleDataAdapter Constructors Instantiates a new instance of OracleDataAdapter class (Overloaded)


OracleDataAdapter Static Methods

OracleDataAdapter static methods are listed in Table 4-32.

Table 4-32 OracleDataAdapter Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleDataAdapter Properties

OracleDataAdapter properties are listed in Table 4-33.

Table 4-33 OracleDataAdapter Properties

Name Description
AcceptChangesDuringFill Inherited from DataAdapter
Container Inherited from Component
ContinueUpdateOnError Inherited from DataAdapter
DeleteCommand A SQL statement or stored procedure to delete rows from an Oracle database
InsertCommand A SQL statement or stored procedure to insert new rows into an Oracle database
MissingMappingAction Inherited from DataAdapter
MissingSchemaAction Inherited from DataAdapter
Requery Determines whether the SelectCommand is reexecuted on the next call to Fill
SafeMapping Creates a mapping between column names in the result set to .NET types, to preserve the data
SelectCommand A SQL statement or stored procedure that returns a single or multiple result set
Site Inherited from Component
TableMappings Inherited from DataAdapter
UpdateCommand A SQL statement or stored procedure to update rows from the DataSet to an Oracle database


OracleDataAdapter Public Methods

OracleDataAdapter public methods are listed in Table 4-34.

Table 4-34 OracleDataAdapter Public Methods

Public Method Description
CreateObjRef Inherited from MarshalByRefObject
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
Fill Adds or refreshes rows in the DataSet to match the data in the Oracle database (Overloaded)
FillSchema Inherited from DbDataAdapter
GetFillParameters Inherited from DbDataAdapter
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
ToString Inherited from Object
Update Inherited from DbDataAdapter


OracleDataAdapter Events

OracleDataAdapter events are listed in Table 4-35.

Table 4-35 OracleDataAdapter Events

Event Delegate Name Description
EventHandler Inherited from Component
FillErrorEventHandler Inherited from DbDataAdapter
OracleRowUpdatedEventHandler Event Delegate for the RowUpdated Event
OracleRowUpdatingEventHandler Event Delegate for the RowUpdating Event


OracleDataAdapter Event Delegates

OracleDataAdapter event delegates are listed in Table 4-36.

Table 4-36 OracleDataAdapter Event Delegates

Event Delegate Name Description
EventHandler Inherited from Component
FillErrorEventHandler Inherited from DbDataAdapter
OracleRowUpdatedEventHandler Event Delegate for the RowUpdated Event
OracleRowUpdatingEventHandler Event Delegate for the RowUpdating Event

OracleDataAdapter Constructors

OracleDataAdapter constructors create new instances of an OracleDataAdapter class.


Overload List:

OracleDataAdapter()

This constructor creates an instance of an OracleDataAdapter class with no arguments.


Declaration
// C#
public OracleDataAdapter();

Remarks

Initial values are set for the following OracleDataAdapter properties as indicated:


OracleDataAdapter(OracleCommand)

This constructor creates an instance of an OracleDataAdapter class with the provided OracleCommand as the SelectCommand.


Declaration
// C#
public OracleDataAdapter(OracleCommand selectCommand);

Parameters

Remarks

Initial values are set for the following OracleDataAdapter properties as indicated:


OracleDataAdapter(string, OracleConnection)

This constructor creates an instance of an OracleDataAdapter class with the provided OracleConnection object and the command text for the SelectCommand.


Declaration
// C#
public OracleDataAdapter(string selectCommandText, OracleConnection selectConnection);

Parameters

Remarks

The OracleDataAdapter opens and closes the connection, if it is not already open. If the connection is open, it must be explicitly closed.

Initial values are set for the following OracleDataAdapter properties as indicated:


OracleDataAdapter(string, string)

This constructor creates an instance of an OracleDataAdapter class with the provided connection string and the command text for the SelectCommand.


Declaration
// C#
public OracleDataAdapter(string selectCommandText, string selectConnectionString);

Parameters

Remarks

Initial values are set for the following OracleDataAdapter properties as indicated:

OracleDataAdapter Static Methods

OracleDataAdapter static methods are listed in Table 4-37.

Table 4-37 OracleDataAdapter Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleDataAdapter Properties

OracleDataAdapter properties are listed in Table 4-38.

Table 4-38 OracleDataAdapter Properties

Name Description
AcceptChangesDuringFill Inherited from DataAdapter
Container Inherited from Component
ContinueUpdateOnError Inherited from DataAdapter
DeleteCommand A SQL statement or stored procedure to delete rows from an Oracle database
InsertCommand A SQL statement or stored procedure to insert new rows into an Oracle database
MissingMappingAction Inherited from DataAdapter
MissingSchemaAction Inherited from DataAdapter
Requery Determines whether the SelectCommand is reexecuted on the next call to Fill
SafeMapping Creates a mapping between column names in the result set to .NET types, to preserve the data
SelectCommand A SQL statement or stored procedure that returns a single or multiple result set
Site Inherited from Component
TableMappings Inherited from DataAdapter
UpdateCommand A SQL statement or stored procedure to update rows from the DataSet to an Oracle database


DeleteCommand

This property is a SQL statement or stored procedure to delete rows from an Oracle database.


Declaration
// C#
public OracleCommand DeleteCommand {get; set;}

Property Value

An OracleCommand used during the Update call to delete rows from tables in the Oracle database, corresponding to the deleted rows in the DataSet.


Remarks

Default = null

If there is primary key information in the DataSet, the DeleteCommand can be automatically generated using the OracleCommandBuilder, if no command is provided for this.


InsertCommand

This property is a SQL statement or stored procedure to insert new rows into an Oracle database.


Declaration
// C#
public OracleCommand InsertCommand {get; set;}

Property Value

An OracleCommand used during the Update call to insert rows into a table, corresponding to the inserted rows in the DataSet.


Remarks

Default = null

If there is primary key information in the DataSet, the InsertCommand can be automatically generated using the OracleCommandBuilder, if no command is provided for this property.


Requery

This property determines whether the SelectCommand is reexecuted on the next call to Fill.


Declaration
// C#
public Boolean Requery {get; set;}

Property Value

Returns true if the SelectCommand is reexecuted on the next call to Fill; otherwise, returns false.


SafeMapping

This property creates a mapping between column names in the result set to .NET types that represent column values in the DataSet, to preserve the data.


Declaration
// C#
public Hashtable SafeMapping {get; set;}

Property Value

A hashtable.


Remarks

Default = null

The SafeMapping property is used, when necessary, to preserve data in the following types:


Example
// C#
// The following example shows how to use the SafeMapping property to fill the 
// dataset 
public static void UseSafeMapping( 
string connStr) 
  { 

//In this SELECT statement, EMPNO, HIREDATE and SALARY must be 
    //preserved using safe type mapping. 
    string cmdStr = "SELECT EMPNO, EMPNAME, HIREDATE, SALARY FROM EMPINFO"; 

    //create the adapter with the selectCommand txt and the connection string 
    OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); 

    //get the connection from the adapter 
    OracleConnection connection = adapter.SelectCommand.Connection; 
  
     //create the safe type mapping for the adapter 
    //which can safely map column data to byte arrays, where
    // applicable. By executing the following statement, EMPNO, HIREDATE AND 
    //SALARY columns will be mapped to byte[] 
    adapter.SafeMapping.Add("*", typeof(byte[])); 

    //Map HIREDATE to a string 
    //If the column name in the EMPINFO table is case-sensitive, 
    //the safe type mapping column name must be case-sensitive. 
    adapter.SafeMapping.Add("HIREDATE", typeof(string)); 
  
    //Map EMPNO to a string 
    //If the column name in the EMPINFO table is case-sensitive, 
    //the safe type mapping column name must also be case-sensitive. 
    adapter.SafeMapping.Add("EMPNO", typeof(string)); 

    //Create and fill the DataSet using the EMPINFO 
    DataSet dataset = new DataSet(); 

    adapter.Fill(dataset, "EMPINFO"); 

    //Get the EMPINFO table from the dataset 
    DataTable table = dataset.Tables["EMPINFO"]; 

    //Get the first row from the EMPINFO table 
    DataRow row0 = table.Rows[0]; 

    //Print out the row info 
    Console.WriteLine("EMPNO Column: type = " + row0["EMPNO"].GetType() + 
        "; value = " + row0["EMPNO"]); 
    Console.WriteLine("EMPNAME Column: type = " + row0["EMPNAME"].GetType() + 
        "; value = " + row0["EMPNAME"]); 
    Console.WriteLine("HIREDATE Column: type = " + row0["HIREDATE"].GetType()+ 
        "; value = " + row0["HIREDATE"]); 
    Console.WriteLine("SALARY Column: type = " + row0["SALARY"].GetType() + 
        "; value = " + row0["SALARY"]); 

 } 


Output:

EMPNO Column: type = System.String; value = 1 
EMPNAME Column: type = System.String; value = KING 
HIREDATE Column: type = System.String; value = 01-MAY-81 
SALARY Column: type = System.Byte[]; value = System.Byte[] 
  

SelectCommand

This property is a SQL statement or stored procedure that returns single or multiple result sets.


Declaration
// C#
public OracleCommand SelectCommand {get; set;}

Property Value

An OracleCommand used during the Fill call to populate the selected rows to the DataSet.


Remarks

Default = null

If the SelectCommand does not return any rows, no tables are added to the dataset and no exception is raised.

If the SELECT statement selects from a VIEW, no key information is retrieved when a FillSchema() or a Fill() with MissingSchemaAction.AddWithKey is invoked.


UpdateCommand

This property is a SQL statement or stored procedure to update rows from the DataSet to an Oracle database.


Declaration
// C#
public OracleCommand UpdateCommand {get; set;}

Property Value

An OracleCommand used during the Update call to update rows in the Oracle database, corresponding to the updated rows in the DataSet.


Remarks

Default = null

If there is primary key information in the DataSet, the UpdateCommand can be automatically generated using the OracleCommandBuilder, if no command is provided for this property.

OracleDataAdapter Public Methods

OracleDataAdapter public methods are listed in Table 4-39.

Table 4-39 OracleDataAdapter Public Methods

Public Method Description
CreateObjRef Inherited from MarshalByRefObject
Dispose Inherited from Component
Equals Inherited from Object (Overloaded)
Fill Adds or refreshes rows in the DataSet to match the data in the Oracle database (Overloaded)
FillSchema Inherited from DbDataAdapter
GetFillParameters Inherited from DbDataAdapter
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
ToString Inherited from Object
Update Inherited from DbDataAdapter

Fill

Fill populates or refreshes the specified DataTable or DataSet.


Overload List:

Fill(DataTable, OracleRefCursor)

This method adds or refreshes rows in the specified DataTable to match those in the provided OracleRefCursor object.


Declaration
// C#
public int Fill(DataTable dataTable, OracleRefCursor refCursor);   

Parameters

Return Value

The number of rows added to or refreshed in the DataTable.


Exceptions

ArgumentNullException - The dataTable or refCursor parameter is null.

InvalidOperationException - The OracleRefCursor is already being used to fetch data.

NotSupportedException - The SafeMapping type is not supported.


Remarks

No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.


Fill(DataSet, OracleRefCursor)

This method adds or refreshes rows in the DataSet to match those in the provided OracleRefCursor object.


Declaration
// C#
public int Fill(DataSet dataSet, OracleRefCursor refCursor);      

Parameters

Return Value

Returns the number of rows added or refreshed in the DataSet.


Exceptions

ArgumentNullException - The dataSet or refCursor parameter is null.

InvalidOperationException - The OracleRefCursor is already being used to fetch data.

InvalidOperationException - The OracleRefCursor is ready to fetch data.

NotSupportedException - The SafeMapping type is not supported.


Remarks

If there is no DataTable to refresh, a new DataTable named Table is created and populated using the provided OracleRefCursor object.

No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.


Fill(DataSet, string, OracleRefCursor)

This method adds or refreshes rows in the specified source table of the DataSet to match those in the provided OracleRefCursor object.


Declaration
// C#
public int Fill(DataSet dataSet, string srcTable, OracleRefCursor refCursor); 

Parameters

Return Value

Returns the number of rows added or refreshed into the DataSet.


Exceptions

ArgumentNullException - The dataSet or refCursor parameter is null.

InvalidOperationException - The OracleRefCursor is already being used to fetch data or the source table name is invalid.

NotSupportedException - The SafeMapping type is not supported.


Remarks

No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.


Fill(DataSet, int, int, string, OracleRefCursor)

This method adds or refreshes rows in a specified range in the DataSet to match rows in the provided OracleRefCursor object.


Declaration
// C#
public int Fill(DataSet dataSet, int startRecord, int maxRecords, 
  string srcTable, OracleRefCursor refCursor);

Parameters

Return Value

This method returns the number of rows added or refreshed in the DataSet. This does not include rows affected by statements that do not return rows.


Exceptions

ArgumentNullException - The dataSet or refCursor parameter is null.

InvalidOperationException - The OracleRefCursor is already being used to fetch data or the source table name is invalid.

NotSupportedException - The SafeMapping type is not supported.


Remarks

No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.

OracleDataAdapter Events

OracleDataAdapter events are listed in Table 4-40.

Table 4-40 OracleDataAdapter Events

Event Name Description
Disposed Inherited from Component
FillError Inherited from DbDataAdapter
RowUpdated This event is raised when row(s) have been updated by the Update() method
RowUpdating This event is raised when row data are about to be updated to the database


RowUpdated

This event is raised when row(s) have been updated by the Update() method.


Declaration
// C#
public event OracleRowUpdatedEventHandler RowUpdated;

Event Data

The event handler receives an OracleRowUpdatedEventArgs object which exposes the following properties containing information about the event.


Example

The following example shows how to use the RowUpdating and RowUpdated events.

// C#
// create the event handler for RowUpdating event

protected static void OnRowUpdating(object sender, OracleRowUpdatingEventArgs e)
{
  Console.WriteLine("Row updating.....");
  Console.WriteLine("Event arguments:");
  Console.WriteLine("Command Text: " + e.Command.CommandText);
  Console.WriteLine("Command Type: " + e.StatementType);
  Console.WriteLine("Status: " + e.Status);
}

// create the event handler for RowUpdated event
protected static void OnRowUpdated(object sender, OracleRowUpdatedEventArgs e)
{
  Console.WriteLine("Row updated.....");
  Console.WriteLine("Event arguments:");
  Console.WriteLine("Command Text: " + e.Command.CommandText);
  Console.WriteLine("Command Type: " + e.StatementType);
  Console.WriteLine("Status: " + e.Status);
}

public static void AdapterEvents(string connStr)
{
  string cmdStr = "SELECT EMPNO, EMPNAME, SALARY FROM EMPINFO";

  //create the adapter with the selectCommand txt and the
  //connection string
  OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr);

  //get the connection from the adapter
  OracleConnection connection = adapter.SelectCommand.Connection;

  //create the UpdateCommand object for updating the EMPINFO table
  //from the dataset
  adapter.UpdateCommand = new OracleCommand("UPDATE EMPINFO SET SALARY = "+
      " :iSALARY where EMPNO = :iEMPNO", connection);
  adapter.UpdateCommand.Parameters.Add(":iSALARY", OracleDbType.Double, 
      0, "SALARY");
  adapter.UpdateCommand.Parameters.Add(":iEMPNO", OracleDbType.Int16, 
      0, "EMPNO");

  //Create and fill the DataSet using the EMPINFO
  DataSet dataset = new DataSet();
  adapter.Fill(dataset, "EMPINFO");

  //Get the EMPINFO table from the dataset
  DataTable table = dataset.Tables["EMPINFO"];

  //Get the first row from the EMPINFO table
  DataRow row0 = table.Rows[0];

  //update the salary in the first row
  row0["SALARY"] = 99999.99;

  //set the event handlers for the RowUpdated and the RowUpdating event
  //the OnRowUpdating() method will be triggered before the update, and
  //the OnRowUpdated() method will be triggered after the update
  adapter.RowUpdating += new OracleRowUpdatingEventHandler(OnRowUpdating);
  adapter.RowUpdated += new OracleRowUpdatedEventHandler(OnRowUpdated);

  //Now update the EMPINFO using the adapter, the salary
  //of 'KING' is changed to 99999.99
  //The OnRowUpdating() and the OnRowUpdated() methods will be triggered
  adapter.Update(dataset, "EMPINFO");
}


RowUpdating

This event is raised when row data are about to be updated to the database.


Declaration
// C#
public event OracleRowUpdatingEventHandler RowUpdating;

Event Data

The event handler receives an OracleRowUpdatingEventArgs object which exposes the following properties containing information about the event.


Example

The example for the RowUpdated event also shows how to use the RowUpdating event. See RowUpdated event "Example".

OracleDataAdapter Event Delegates

OracleDataAdapter event delegates are listed in Table 4-41.

Table 4-41 OracleDataAdapter Event Delegates

Event Delegate Name Description
EventHandler Inherited from Component
FillErrorEventHandler Inherited from DbDataAdapter
OracleRowUpdatedEventHandler Event Delegate for the RowUpdated Event
OracleRowUpdatingEventHandler Event Delegate for the RowUpdating Event


OracleRowUpdatedEventHandler

This event delegate handles the RowUpdated Event.


OracleRowUpdatingEventHandler

This event delegate handles the RowUpdating Event.

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.


Class Inheritance

Object

  MarshalByRefObject

    OracleDataReader


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

Thread Safety

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


Remarks

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


Example

The OracleDataReader examples in this section are based on the EMPINFO table which is defined as follows:

CREATE TABLE empInfo (
    empno NUMBER(4) PRIMARY KEY,
    empName VARCHAR2(20) NOT NULL,
    hiredate DATE,
    salary NUMBER(7,2),
    jobDescription Clob,
    byteCodes BLOB
);

The EMPINFO table has the following values:

EMPNO    EMPNAME    HIREDATE    SALARY     JOBDESCRIPTION  BYTECODES 
                                                           (Hex Values)
=====    =======    ========    ======     ==============  ============

    1    KING       01-MAY-81   12345.67   SOFTWARE ENGR   {0x12, 0x34}
    2    SCOTT      01-SEP-75   34567.89   MANAGER         {0x56, 0x78}
    3    BLAKE      01-OCT-90   9999.12    TRANSPORT       {0x23, 0x45}
    4    SMITH      NULL        NULL       NULL             NULL

The following example retrieves the data from the EMPINFO table:

//C #
//This method retrieves all the data from EMPINFO table

public void ReadEmpInfo(string connStr)
{
   string cmdStr = "SELECT * FROM EMPINFO";
   OracleConnection connection = new OracleConnection(connStr);
   OracleCommand cmd = new OracleCommand(cmdStr, connection);
   connection.Open();

   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();
      //done reading one row
     } //Done Reading EMPINFO table

     //Close the reader
     reader.Close();

     // Close the connection
     connection.Close();
    }


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleDataReader Members

OracleDataReader members are listed in the following tables:


OracleDataReader Static Methods

OracleDataReader static methods are listed in Table 4-42.

Table 4-42 OracleDataReader Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleDataReader Properties

OracleDataReader properties are listed in Table 4-43.

Table 4-43 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
IsClosed Indicates whether 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


OracleDataReader Public Methods

OracleDataReader public methods are listed in Table 4-44.

Table 4-44 OracleDataReader Public Methods

Public Method Description
Close Closes the OracleDataReader
CreateObjRef Inherited from MarshalByRefObject
Dispose Releases any resources or memory allocated by the object
Equals Inherited from 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
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 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 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
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 Object class
GetValue Returns the column value as a .NET type
GetValues Gets all the column values as .NET types
GetXmlReader Returns the contents of an XMLType column as an instance of an .NET XmlTextReader object
IsDBNull Indicates whether 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 Object

OracleDataReader Static Methods

OracleDataReader static methods are listed in Table 4-45.

Table 4-45 OracleDataReader Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleDataReader Properties

OracleDataReader properties are listed in Table 4-46.

Table 4-46 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
IsClosed Indicates whether 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


Depth

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


Declaration
// C#
public int Depth {get;}

Property Value

The depth of nesting for the current row.


Implements

IDataReader


Exceptions

InvalidOperationException - The reader is closed.


Remarks

Default = 0

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


FetchSize

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


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

Property Value

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


Exceptions

ArgumentException - The FetchSize value specified is invalid.


Remarks

Default = The OracleCommand's FetchSize property value.

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


FieldCount

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


Declaration
// C#
public int FieldCount {get;}

Property Value

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


Implements

IDataRecord


Exceptions

InvalidOperationException - The reader is closed.


Remarks

Default = 0

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


IsClosed

This property indicates whether the data reader is closed.


Declaration
// C#
public bool IsClosed {get;}

Property Value

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


Implements

IDataReader


Remarks

Default = true

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

Item

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


Overload List:

Item [index]

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


Declaration
// 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
// 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

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

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


InitialLONGFetchSize

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


Declaration
// C#
public long InitialLONGFetchSize {get;}

Property Value

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


Exceptions

InvalidOperationException - The reader is closed.


Remarks

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

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

This property is read-only for the OracleDataReader.


RecordsAffected

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


Declaration
// C#
public int RecordsAffected {get;}

Property Value

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


Implements

IDataReader


Remarks

Default = 0

The value of -1 is returned for SELECT statements.

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

OracleDataReader Public Methods

OracleDataReader public methods are listed in Table 4-47.

Table 4-47 OracleDataReader Public Methods

Public Method Description
Close Closes the OracleDataReader
CreateObjRef Inherited from MarshalByRefObject
Dispose Releases any resources or memory allocated by the object
Equals Inherited from 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
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 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 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
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 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 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 Object


Close

This method closes the OracleDataReader.


Declaration
// C#
public void Close();

Implements

IDataReader


Remarks

The Close method frees all resources associated with the OracleDataReader.


Example

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


Dispose

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


Declaration
// C#
public void Dispose();

Implements

IDisposable


Remarks

The Dispose method also closes the OracleDataReader.


GetByte

This method returns the byte value of the specified column.


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

Parameters

Return Value

The value of the column as a byte.


Implements

IDataRecord


Exceptions

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

IndexOutOfRangeException - The column index is invalid.

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


Remarks

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


GetBytes

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


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

Parameters

Return Value

The number of bytes read.


Implements

IDataRecord


Exceptions

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

IndexOutOfRangeException - The column index is invalid.

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


Remarks

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

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

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


GetChars

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


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

Parameters

Return Value

The number of characters read.


Implements

IDataRecord


Exceptions

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

IndexOutOfRangeException - The column index is invalid.

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


Remarks

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

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

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


GetDataTypeName

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


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

Parameters

Return Value

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


Implements

IDataRecord


Exceptions

InvalidOperationException - The reader is closed.

IndexOutOfRangeException - The column index is invalid.


GetDateTime

This method returns the DateTime value of the specified column.


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

Parameters

Return Value

The DateTime value of the column.


Implements

IDataRecord


Exceptions

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

IndexOutOfRangeException - The column index is invalid.

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


Remarks

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


GetDecimal

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


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

Parameters

Return Value

The decimal value of the column.


Implements

IDataRecord


Exceptions

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

IndexOutOfRangeException - The column index is invalid.

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


Remarks

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


GetDouble

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


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

Parameters

Return Value

The double value of the column.


Implements

IDataRecord


Exceptions

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

IndexOutOfRangeException - The column index is invalid.

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


Remarks

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

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


GetFieldType

This method returns the Type of the specified column.


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

Parameters

Return Value

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


Implements

IDataRecord


Exceptions

InvalidOperationException - The reader is closed.

IndexOutOfRangeException - The column index is invalid.


GetFloat

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


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

Parameters

Return Value

The float value of the column.


Implements

IDataRecord


Exceptions

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

IndexOutOfRangeException - The column index is invalid.

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


Remarks

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

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


GetInt16

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


Note:

short is equivalent to Int16.


Declaration
// 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
// 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
// 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
// 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 server round-trip to obtain a reference to the current BLOB data while also locking the row using the FOR UPDATE clause. This means that the OracleBlob obtained from GetOracleBlob() can have a different value than the OracleBlob obtained from GetOracleBlobForUpdate() since it is not obtained from the original snapshot.

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

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

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


Example

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

// C#
public static void ReadOracleBlobForUpdate(string connStr)
  {
   //get the job description for empno = 1
   string cmdStr = "SELECT BYTECODES, EMPNO FROM EMPINFO where EMPNO = 1";

   OracleConnection connection = new OracleConnection(connStr);
   OracleCommand cmd = new OracleCommand(cmdStr, connection);
   connection.Open();

   //Since we are going to update the OracleBlob object, we will
   //have to create a transaction
   OracleTransaction txn = connection.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 job description Clob object
     byte[] addedBytes = new byte[2] {0, 0};
     byteCodesBlob.Append(addedBytes, 0, addedBytes.Length);

     //Now commit the transaction
     txn.Commit();
    }
    else
      reader.Close();

    // Close the connection
    connection.Close();
  }


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 server round-trip to obtain a reference to the current BLOB data while also locking the row using the FOR UPDATE clause. This means that the OracleBlob obtained from GetOracleBlob() can have a different value than the OracleBlob obtained from GetOracleBlobForUpdate() since it is not obtained from the original snapshot.

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

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

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

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


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

// C#
public static void ReadOracleClobForUpdate(string connStr)
  {
    //get the job description for empno = 1
    string cmdStr = "SELECT JOBDESCRIPTION, EMPNO FROM EMPINFO where EMPNO = 1";

    OracleConnection connection = new OracleConnection(connStr);
    OracleCommand cmd = new OracleCommand(cmdStr, connection);
    connection.Open();

    //Since we are going to update the OracleClob object, we will
    //have to create a transaction
    OracleTransaction txn = connection.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();
    }
    else
      reader.Close();

    // Close the connection
    connection.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 server 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 higher.


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


GetSchemaTable

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


Declaration
// 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

OracleDataReader.GetSchemaTable()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 4-48 OracleDataReader SchemaTable

Name Name Type Description
ColumnNameTB 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 datatype.

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

// C#
public static void ReadSchemaTable(string connStr)
 {
   .....

 //get the reader
 OracleDataReader reader = cmd.ExecuteReader();

 //get the schema table
 DataTable schemaTable = reader.GetSchemaTable();

 //retrieve the first column info.
 DataRow col0 = schemaTable.Rows[0];

 //print out the column info
 Console.WriteLine("Column name: " + col0["COLUMNNAME"]);
 Console.WriteLine("Precision: " + col0["NUMERICPRECISION"]);
 Console.WriteLine("Scale: " + col0["NUMERICSCALE"]);
 .....

 }


GetString

This method returns the string value of the specified column.


Declaration
// 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

IsDBNull should be called 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
// 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
// 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 the column value is NULL.


Declaration
// 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
// 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
// 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".


OracleError Class

The OracleError class represents an error reported by Oracle.


Class Inheritance

Object

  OracleError


Declaration
// C#
public sealed class OracleError

Thread Safety

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


Remarks

The OracleError class represents a warning or an error reported by Oracle.


Example
// C#
...
try {
    cmd.ExecuteNonQuery()
}
catch ( OracleException e ){
   OracleError err1 = e.Errors[0];
   OracleError err2 = e.Errors[1];

    Console.WriteLine("Error 1 Message:", err1.Message);
    Console.WriteLine("Error 2 Source:", err2.Source);
}

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleError Members

OracleError members are listed in the following tables:


OracleError Static Methods

OracleError static methods are listed in Table 4-49.

Table 4-49 OracleError Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleError Properties

OracleError properties are listed in Table 4-50.

Table 4-50 OracleError Properties

Properties Description
ArrayBindIndex
Specifies the row number of errors that occurred during the Array Bind execution
DataSource Specifies the Oracle service name (TNS name) that identifies the Oracle database
Message Specifies the message describing the error
Number Specifies the Oracle error number
Procedure Specifies the stored procedure that causes the error
Source Specifies the name of the data provider that generates the error


OracleError Methods

OracleError methods are listed in Table 4-51.

Table 4-51 OracleError Methods

Methods Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Returns a string representation of the OracleError

OracleError Static Methods

OracleError static methods are listed in Table 4-52.

Table 4-52 OracleError Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleError Properties

OracleError properties are listed in Table 4-53.

Table 4-53 OracleError Properties

Properties Description
ArrayBindIndex
Specifies the row number of errors that occurred during the Array Bind execution
DataSource Specifies the Oracle service name (TNS name) that identifies the Oracle database
Message Specifies the message describing the error
Number Specifies the Oracle error number
Procedure Specifies the stored procedure that causes the error
Source Specifies the name of the data provider that generates the error


ArrayBindIndex

This property specifies the row number of errors that occurred during the Array Bind execution.


Declaration
// C#
public int ArrayBindIndex {get;}

Property Value

An int value that specifies the row number for errors that occurred during the Array Bind execution.


Remarks

Default = 0.

This property is used for Array Bind operations only.

ArrayBindIndex represents the zero-based row number at which the error occurred during an Array Bind operation. For example, if an array bind execution causes two errors on the 2nd and 4th operations, two OracleError objects appear in the OracleErrorCollection with the ArrayBindIndex property values 2 and 4 respectively.


DataSource

This property specifies the Oracle service name (TNS name) that identifies the Oracle database.


Declaration
// C#
public string DataSource {get;}

Property Value

A string.


Message

This property specifies the message describing the error.


Declaration
// C#
public string Message {get;}

Property Value

A string.


Number

This property specifies the Oracle error number.


Declaration
// C#
public int Number {get;}

Property Value

An int.


Procedure

This property specifies the stored procedure that causes the error.


Declaration
// C#
public string Procedure {get;}

Property Value

The stored procedure name.


Remarks

Represents the stored procedure which creates this OracleError object.


Source

This property specifies the name of the data provider that generates the error.


Declaration
// C#
public string Source {get;}

Property Value

A string.

OracleError Methods

OracleError methods are listed in Table 4-54.

Table 4-54 OracleError Methods

Methods Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Returns a string representation of the OracleError


ToString

Overrides Object

This method returns a string representation of the OracleError.


Declaration
// C#
public override string ToString();

Return Value

Returns a string with the format Ora- error number: Class.Method name error message stack trace information.


Example

ORA-24333: zero iteration count


OracleErrorCollection Class

An OracleErrorCollection class represents a collection of all errors that are thrown by the Oracle Data Provider for .NET.


Class Inheritance

Object

  ArrayList

    OracleErrorCollection


Declaration
// C#
public sealed class OracleErrorCollection : ArrayList

Thread Safety

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


Remarks

A simple ArrayList that holds a list of OracleErrors.


Example
// C#
// The following example demonstrates how to access an individual OracleError
// from an OracleException
...
public void DisplayErrors(OracleException myException)
{
  for (int i=0; i < myException.Errors.Count; i++;)
  {
    Console.WriteLine("Index #" + i + "\n" +
      "Error: " + myException.Errors[i].ToString() + "\n");
  }
}
...



Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleErrorCollection Members

OracleErrorCollection members are listed in the following tables:


OracleErrorCollection Static Methods

OracleErrorCollection static methods are listed in Table 4-55.

Table 4-55 OracleErrorCollection Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleErrorCollection Properties

OracleErrorCollection properties are listed in Table 4-56.

Table 4-56 OracleErrorCollection Properties

Name Description
Capacity Inherited from ArrayList
Count Inherited from ArrayList
IsReadOnly Inherited from ArrayList
IsSynchronized Inherited from ArrayList
Item Inherited from ArrayList


OracleErrorCollection Public Methods

OracleErrorCollection public methods are listed in Table 4-57.

Table 4-57 OracleErrorCollection Public Methods

Public Method Description
CopyTo Inherited from ArrayList
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object

OracleErrorCollection Static Methods

OracleErrorCollection static methods are listed in Table 4-58.

Table 4-58 OracleErrorCollection Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleErrorCollection Properties

OracleErrorCollection properties are listed in Table 4-59.

Table 4-59 OracleErrorCollection Properties

Name Description
Capacity Inherited from ArrayList
Count Inherited from ArrayList
IsReadOnly Inherited from ArrayList
IsSynchronized Inherited from ArrayList
Item Inherited from ArrayList

OracleErrorCollection Public Methods

OracleErrorCollection public methods are listed in Table 4-60.

Table 4-60 OracleErrorCollection Public Methods

Public Method Description
CopyTo Inherited from ArrayList
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object


OracleException Class

The OracleException class represents an exception that is thrown when the Oracle Data Provider for .NET encounters an error. Each OracleException object contains at least one OracleError object in the Error property that describes the error or warning.


Class Inheritance

Object

  Exception

    SystemException

      OracleException


Declaration
// C#
public sealed class OracleException : SystemException

Thread Safety

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


Example
// C#
//  The following example generates an OracleException due to bad SQL syntax,
//  (that is the missing keyword "from") and then displays the exception message //  and source property.
..
try
{
  ...
  // select * emp will cause ORA-00923
  OracleCommand cmd = new OracleCommand("select * emp", con);
}
catch ( OracleException e )
{
  Console.WriteLine("{0} throws {1}",e.Source, e.Message);
}
..


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleException Members

OracleException members are listed in the following tables:


OracleException Static Methods

OracleException static methods are listed in Table 4-61.

Table 4-61 OracleException Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleException Properties

OracleException properties are listed in Table 4-62.

Table 4-62 OracleException Properties

Properties Description
DataSource Specifies the TNS name that contains the information for connecting to an Oracle instance
Errors Specifies a collection of one or more OracleError objects that contain information about exceptions generated by the Oracle database
HelpLink Inherited from Exception
InnerException Inherited from Exception
Message Specifies the error messages that occur in the exception
Number Specifies the Oracle error number
Procedure Specifies the stored procedure that cause the exception
Source Specifies the name of the data provider that generates the error
StackTrace Inherited from Exception
TargetSite Inherited from Exception


OracleException Methods

OracleException methods are listed in Table 4-63.

Table 4-63 OracleException Methods

Methods Description
Equals Inherited from Object (Overloaded)
GetBaseException Inherited from Exception
GetHashCode Inherited from Object
GetObjectData Sets the serializable info object with information about the exception
GetType Inherited from Object
ToString Returns the fully qualified name of this exception

OracleException Static Methods

OracleException static methods are listed in Table 4-64.

Table 4-64 OracleException Static Methods

Method Description
Equals Inherited from Object (Overloaded)

OracleException Properties

OracleException properties are listed in Table 4-65.

Table 4-65 OracleException Properties

Properties Description
DataSource Specifies the TNS name that contains the information for connecting to an Oracle instance
Errors Specifies a collection of one or more OracleError objects that contain information about exceptions generated by the Oracle database
HelpLink Inherited from Exception
InnerException Inherited from Exception
Message Specifies the error messages that occur in the exception
Number Specifies the Oracle error number
Procedure Specifies the stored procedure that cause the exception
Source Specifies the name of the data provider that generates the error
StackTrace Inherited from Exception
TargetSite Inherited from Exception


DataSource

This property specifies the TNS name that contains the information for connecting to an Oracle instance.


Declaration
// C#
public string DataSource {get;}

Property Value

The TNS name containing the connect information.


Errors

This property specifies a collection of one or more OracleError objects that contain information about exceptions generated by the Oracle database.


Declaration
// C#
public OracleErrorCollection Errors {get;}

Property Value

An OracleErrorCollection.


Remarks

The Errors property contains at least one instance of OracleError objects.


Message

Overrides Exception

This property specifies the error messages that occur in the exception.


Declaration
// C#
public override string Message {get;}

Property Value

A string.


Remarks

Message is a concatenation of all errors in the Errors collection. Each error message is concatenated and is followed by a carriage return, except the last one.


Number

This property specifies the Oracle error number.


Declaration
// C#
public int Number {get;}

Property Value

The error number.


Remarks

This error number can be the topmost level of error generated by Oracle and can be a provider-specific error number.


Procedure

This property specifies the stored procedure that caused the exception.


Declaration
// C#
public string Procedure {get;}

Property Value

The stored procedure name.


Source

Overrides Exception

This property specifies the name of the data provider that generates the error.


Declaration
// C#
public override string Source {get;}

Property Value

The name of the data provider.

OracleException Methods

OracleException methods are listed in Table 4-66.

Table 4-66 OracleException Methods

Methods Description
Equals Inherited from Object (Overloaded)
GetBaseException Inherited from Exception
GetHashCode Inherited from Object
GetObjectData Sets the serializable info object with information about the exception
GetType Inherited from Object
ToString Returns the fully qualified name of this exception


GetObjectData

Overrides Exception

This method sets the serializable info object with information about the exception.


Declaration
// C#
public override void GetObjectData(SerializationInfo info, StreamingContext context);

Parameters

Remarks

The information includes DataSource, Message, Number, Procedure, Source, and StackTrace.


ToString

Overrides Exception

This method returns the fully qualified name of this exception, the error message in the Message property, the InnerException.ToString() message, and the stack trace.


Declaration
// C#
public override string ToString();

Return Value

The string representation of the exception.


Example
// C#
...
try
{
  ...
  // select * from emp will cause ORA-00923
  OracleCommand cmd = new OracleCommand("select * from emp", con);
}
catch ( OracleException e )
{
  Console.WriteLine("{0}",e.ToString());
}
...

OracleFailoverEventArgs Class

The OracleFailoverEventArgs class provides event data for the OracleConnection.Failover event. When database failover occurs, the OracleConnection.Failover event is triggered along with the OracleFailoverEventArgs object that stores the event data.


Class Inheritance

Object

  EventArgs

    OracleFailoverEventArgs


Declaration
// C#
public sealed class OracleFailoverEventArgs

Thread Safety

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


Example
// C#
// Receiving Failover notifications
switch (eventArgs.FailoverEvent)
  {
    case FailoverEvent.Begin:
      {
       Console.WriteLine(" \nFailover Begin - Failing Over ..." +
          "Please stand by \n");
       Console.WriteLine(" \nFailover type was found to be " +
          eventArgs.FailoverType);
       break;
      }

    case FailoverEvent.End:
      {
       Console.WriteLine(" \nFailover ended ...resuming services\n");
       break;
      }

     case FailoverEvent.Error:
      {
       Console.WriteLine(" Failover error gotten. Sleeping...\n");
       Thread.Sleep(3000);
       return FailoverReturnCode.Retry;
      }

     default:
      {
       Console.WriteLine("\nBad Failover Event: " + eventArgs.FailoverEvent);
       break;
      }
  }


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleFailoverEventArgs Members

OracleFailoverEventArgs members are listed in the following tables:


OracleFailoverEventArgs Static Methods

The OracleFailoverEventArgs static methods are listed in Table 4-67.

Table 4-67 OracleFailoverEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleFailoverEventArgs Properties

The OracleFailoverEventArgs properties are listed in Table 4-68.

Table 4-68 OracleFailoverEventArgs Properties

Name Description
FailoverType Specifies the type of failover the client has requested
FailoverEvent Indicates the state of the failover


OracleFailoverEventArgs Public Methods

The OracleFailoverEventArgs public methods are listed in Table 4-69.

Table 4-69 OracleFailoverEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object

OracleFailoverEventArgs Static Methods

The OracleFailoverEventArgs static methods are listed in Table 4-70.

Table 4-70 OracleFailoverEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleFailoverEventArgs Properties

The OracleFailoverEventArgs properties are listed in Table 4-71.

Table 4-71 OracleFailoverEventArgs Properties

Name Description
FailoverType Specifies the type of failover the client has requested
FailoverEvent Indicates the state of the failover


FailoverType

This property indicates the state of the failover.


Declaration
// C#
public FailoverType FailoverType {get;} 

Property Value

A FailoverType enumeration value.


FailoverEvent

This property indicates the state of the failover.


Declaration
// C#
public FailoverEvent FailoverEvent {get;} 

Property Value

A FailoverEvent enumerated value.

OracleFailoverEventArgs Public Methods

The OracleFailoverEventArgs public methods are listed in Table 4-72.

Table 4-72 OracleFailoverEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object


OracleFailoverEventHandler Delegate

The OracleFailoverEventHandler represents the signature of the method that handles the OracleConnection.Failover event.


Declaration
// C#
public delegate FailoverReturnCode OracleFailoverEventHandler(object sender,          OracleFailoverEventArgs eventArgs);

Parameter

Return Type

An int.


Remarks

To receive failover notifications, a callback function can be registered as follows:

ConObj.Failover += new OracleFailoverEventHandler(OnFailover); 

The definition of the callback function OnFailover can be as follows:

public FailoverReturnCode OnFailover(object sender, OracleFailoverEventArgs eventArgs) 
  

Example
void Main(string[] args) 
{ 
... 
// register callback function OnFailOver 
ConObj.Failover += new OracleFailoverEventHandler(OnFailOver); 
... 
} 

//Failover Callback Function 
public FailoverReturnCode OnFailOver(object sender, OracleFailoverEventArgs eventArgs) 
{ 

      switch (eventArgs.FailoverEvent) 
      { 
        case FailoverEvent.Begin: 
        { 
          Console.WriteLine(" \nFailover Begin - Failing Over ... Please stand
                           by \n"); 
          Console.WriteLine(" Failover type was found to be " + 
                           eventArgs.FailoverType); 
          break; 
        } 

        case FailoverEvent.Abort: 
        { 
          Console.WriteLine(" Failover aborted. Failover will not take
                            place.\n"); 
          break; 
        } 

        case FailoverEvent.End: 
        { 
          Console.WriteLine(" Failover ended ...resuming services\n"); 
          break; 
        } 

        case FailoverEvent.Reauth: 
        { 
          Console.WriteLine(" Failed over user. Resuming services\n"); 
          break; 
        } 

        case FailoverEvent.Error: 
        { 
          Console.WriteLine(" Failover error gotten. Sleeping...\n"); 
          Thread.Sleep(3000); 
          return FailoverReturnCode.Retry; 
        } 

        default: 
        { 
          Console.WriteLine("Bad Failover Event: %d.\n",
                             eventArgs.FailoverEvent); 
          break; 
        } 
      } 
  
      return FailoverReturnCode.Success; 

} /* OnFailover */ 
  

OracleGlobalization Class

The OracleGlobalization class is used to obtain and set the Oracle globalization settings of the session, thread, and local computer (read-only).

Class Inheritance

Object

  OracleGlobalization


Declaration
public sealed class OracleGlobalization : ICloneable, IDisposable

Thread Safety

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


Remarks

An exception is thrown for invalid property values. All newly set property values are validated, except the TimeZone property.

Changing the OracleGlobalization object properties does not change the globalization settings of the session or the thread. Either the SetSessionInfo method of the OracleConnection object or the SetThreadInfo method of the OracleGlobalization object must be called to alter the session's and thread's globalization settings, respectively.


Example
// C#
//  Sets thread globalization info.
...
string ConStr = "User Id=myschema;Password=mypassword;" +
   "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Retrieves thread globalization info
OracleGlobalization ogi = OracleGlobalization.GetThreadInfo();

//Print the language name in thread globalization info
Console.WriteLine("Thread language: " + ogi.Language);

//Set thread's language
ogi.Language = "FRENCH";
OracleGlobalization.SetThreadInfo(ogi);

OracleGlobalization ogi2;
OracleGlobalization.GetThreadInfo(ogi2);

//Print the language name in thread globalization info
Console.WriteLine("Thread language: " + ogi2.Language);


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleGlobalization Members

OracleGlobalization members are listed in the following tables:


OracleGlobalization Static Methods

The OracleGlobalization static methods are listed in Table 4-73.

Table 4-73 OracleGlobalization Static Methods

Name Description
GetClientInfo Returns an OracleGlobalization object that represents the Oracle globalization settings of the local computer (Overloaded)
GetThreadInfo Returns or refreshes an OracleGlobalization instance that represents Oracle globalization settings of the current thread (Overloaded)
SetThreadInfo Sets Oracle globalization parameters to the current thread


OracleGlobalization Properties

The OracleGlobalization properties are listed in Table 4-74.

Table 4-74 OracleGlobalization Properties

Name Description
Calendar Specifies the calendar system
ClientCharacterSet Specifies a client character set
Comparison Specifies a method of comparison for WHERE clauses and comparison in PL/SQL blocks
Currency Specifies the string to use as a local currency symbol for the L number format element
DateFormat Specifies the date format for Oracle Date type as a string
DateLanguage Specifies the language used to spell day and month names and date abbreviations
DualCurrency Specifies the dual currency symbol, such as Euro, for the U number format element
ISOCurrency Specifies the string to use as an international currency symbol for the C number format element
Language Specifies the default language of the database
LengthSemantics Enables creation of CHAR and VARCHAR2 columns using either byte or character (default) length semantics
NCharConversionException Determines whether data loss during an implicit or explicit character type conversion reports an error
NumericCharacters Specifies the characters used for the decimal character and the group separator character for numeric values in strings
Sort Specifies the collating sequence for ORDER by clause
Territory Specifies the name of the territory
TimeStampFormat Specifies the string format for TimeStamp types
TimeStampTZFormat Specifies the string format for TimeStampTZ types
TimeZone Specifies the time zone region name


OracleGlobalization Public Methods

OracleGlobalization public methods are listed in Table 4-75.

Table 4-75 OracleGlobalization Public Methods

Public Method Description
Clone Creates a copy of an OracleGlobalization object
Dispose Inherited from Component

OracleGlobalization Static Methods

The OracleGlobalization static methods are listed in Table 4-76.

Table 4-76 OracleGlobalization Static Methods

Name Description
GetClientInfo Returns an OracleGlobalization object that represents the Oracle globalization settings of the local computer (Overloaded)
GetThreadInfo Returns or refreshes an OracleGlobalization instance that represents Oracle globalization settings of the current thread (Overloaded)
SetThreadInfo Sets Oracle globalization parameters to the current thread

GetClientInfo

GetClientInfo returns an OracleGlobalization object instance that represents the Oracle globalization settings of the local computer.


Overload List:

GetClientInfo()

This method returns an OracleGlobalization instance that represents the globalization settings of the local computer.


Declaration
// C#
public static OracleGlobalization GetClientInfo();

Return Value

An OracleGlobalization instance.


Example
// C#
//  Retrieves the client globalization info.
...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Retrieves the client globalization info
OracleGlobalization ogi = OracleGlobalization.GetClientInfo();

//Retrieves the client globalization info using overloaded method
OracleGlobalization ogi2;
OracleGlobalization.GetClientInfo(ogi2);

//Print the language name in client globalization info
Console.WriteLine("Client machine language: " + ogi.Language);
Console.WriteLine("Client machine language: " + ogi2.Language);


GetClientInfo(OracleGlobalization)

This method refreshes the provided OracleGlobalization object with the globalization settings of the local computer.


Declaration
// C#
public static void GetClientInfo(OracleGlobalization oraGlob);

Parameters

Example
// C#
//  Retrieves the client globalization info.
...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Retrieves the client globalization info
OracleGlobalization ogi = OracleGlobalization.GetClientInfo();

//Retrieves the client globalization info using overloaded method
OracleGlobalization ogi2;
OracleGlobalization.GetClientInfo(ogi2);

//Print the language name in client globalization info
Console.WriteLine("Client machine language: " + ogi.Language);
Console.WriteLine("Client machine language: " + ogi2.Language);

GetThreadInfo

GetThreadInfo returns or refreshes an OracleGlobalization instance.


Overload List:

GetThreadInfo()

This method returns an OracleGlobalization instance of the current thread.


Declaration
// C#
public static OracleGlobalization GetThreadInfo();

Return Value

An OracleGlobalization instance.


Remarks

Initially, GetThreadInfo() returns an OracleGlobalization object that has the same property values as that returned by GetClientInfo(), unless the application changes it by invoking SetThreadInfo().


Example
// C#
Retrieves the thread globalization info.
...
string ConStr = "User Id=myschema;Password=mypassword;" +
       "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Retrieves the thread globalization info
OracleGlobalization ogi = OracleGlobalization.GetThreadInfo();

//Retrieves the thread globalization info using overloaded method
OracleGlobalization ogi2;
OracleGlobalization.GetThreadInfo(ogi2);

//Print the language name in thread globalization info
Console.WriteLine("Thread language: " + ogi.Language);
Console.WriteLine("Thread language: " + ogi2.Language);


GetThreadInfo(OracleGlobalization)

This method refreshes the OracleGlobalization object with the globalization settings of the current thread.


Declaration
// C#
public static void GetThreadInfo(OracleGlobalization oraGlob);

Parameters

Remarks

Initially GetThreadInfo() returns an OracleGlobalization object that has the same property values as that returned by GetClientInfo(), unless the application changes it by invoking SetThreadInfo().


Example
// C#
Retrieves the thread globalization info.
...
string ConStr = "User Id=myschema;Password=mypassword;" +
       "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Retrieves the thread globalization info
OracleGlobalization ogi = OracleGlobalization.GetThreadInfo();

//Retrieves the thread globalization info using overloaded method
OracleGlobalization ogi2;
OracleGlobalization.GetThreadInfo(ogi2);

//Print the language name in thread globalization info
Console.WriteLine("Thread language: " + ogi.Language);
Console.WriteLine("Thread language: " + ogi2.Language);


SetThreadInfo

This method sets Oracle globalization parameters to the current thread.


Declaration
// C#
public static void SetThreadInfo(OracleGlobalization oraGlob);

Parameters

Remarks

Any .NET string conversions to and from ODP.NET Types, as well as ODP.NET Type constructors, use the globalization property values where applicable. For example, when constructing an OracleDate structure from a .NET string, that string is expected to be in the format specified by the OracleGlobalization.DateFormat property of the thread.


Example
// C#
//  Sets thread globalization info.
...
string ConStr = "User Id=myschema;Password=mypassword;" +
   "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Retrieves thread globalization info
OracleGlobalization ogi = OracleGlobalization.GetThreadInfo();

//Print the language name in thread globalization info
Console.WriteLine("Thread language: " + ogi.Language);

//Set thread's language
ogi.Language = "FRENCH";
OracleGlobalization.SetThreadInfo(ogi);

OracleGlobalization ogi2;
OracleGlobalization.GetThreadInfo(ogi2);

//Print the language name in thread globalization info
Console.WriteLine("Thread language: " + ogi2.Language);

OracleGlobalization Properties

The OracleGlobalization properties are listed in Table 4-77.

Table 4-77 OracleGlobalization Properties

Name Description
Calendar Specifies the calendar system
ClientCharacterSet Specifies a client character set
Comparison Specifies a method of comparison for WHERE clauses and comparison in PL/SQL blocks
Currency Specifies the string to use as a local currency symbol for the L number format element
DateFormat Specifies the date format for Oracle Date type as a string
DateLanguage Specifies the language used to spell day and month names and date abbreviations
DualCurrency Specifies the dual currency symbol, such as Euro, for the U number format element
ISOCurrency Specifies the string to use as an international currency symbol for the C number format element
Language Specifies the default language of the database
LengthSemantics Enables creation of CHAR and VARCHAR2 columns using either byte or character (default) length semantics
NCharConversionException Determines whether data loss during an implicit or explicit character type conversion reports an error
NumericCharacters Specifies the characters used for the decimal character and the group separator character for numeric values in strings
Sort Specifies the collating sequence for ORDER by clause
Territory Specifies the name of the territory
TimeStampFormat Specifies the string format for TimeStamp types
TimeStampTZFormat Specifies the string format for TimeStampTZ types
TimeZone Specifies the time zone region name


Calendar

This property specifies the calendar system.


Declaration
// C#
public string Calendar {get; set;}

Property Value

A string representing the Calendar.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_CALENDAR setting of the local computer. This value is the same regardless of whether the OracleGlobalization object represents the settings of the client, thread, or session.


ClientCharacterSet

This property specifies a client character set.


Declaration
// C#
public string ClientCharacterSet {get;}

Property Value

A string that the provides the name of the character set of the local computer.


Remarks

The default value is the character set of the local computer.


Comparison

This property represents a method of comparison for WHERE clauses and comparison in PL/SQL blocks.


Declaration
// C#
public string Comparison {get; set;}

Property Value

A string that provides the name of the method of comparison.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_COMP setting of the local computer.


Currency

This property specifies the string to use as a local currency symbol for the L number format element.


Declaration
// C#
public string Currency {get; set;}

Property Value

The string to use as a local currency symbol for the L number format element.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_CURRENCY setting of the local computer.


DateFormat

This property specifies the date format for Oracle Date type as a string.


Declaration
// C#
public string DateFormat {get; set;}

Property Value

The date format for Oracle Date type as a string


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_DATE_FORMAT setting of the local computer.


DateLanguage

This property specifies the language used to spell names of days and months, and date abbreviations (for example: a.m., p.m., AD, BC).


Declaration
// C#
public string DateLanguage {get; set;}

Property Value

A string specifying the language.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_DATE_LANGUAGE setting of the local computer.


DualCurrency

This property specifies the dual currency symbol, such as Euro, for the U number format element.


Declaration
// C#
public string DualCurrency {get; set;}

Property Value

A string that provides the dual currency symbol.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_DUAL_CURRENCY setting of the local computer.


ISOCurrency

This property specifies the string to use as an international currency symbol for the C number format element.


Declaration
// C#
public string ISOCurrency {get; set;}

Property Value

The string used as an international currency symbol.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_ISO_CURRENCY setting of the local computer.


Language

This property specifies the default language of the database.


Declaration
// C#
public string Language {get; set;}

Property Value

The default language of the database.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_LANGUAGE setting of the local computer.

Language is used for messages, day and month names, and sorting algorithms. It also determines NLS_DATE_LANGUAGE and NLS_SORT parameter values.


LengthSemantics

This property indicates whether CHAR and VARCHAR2 columns use byte or character (default) length semantics.


Declaration
// C#
public string LengthSemantics {get; set;}

Property Value

A string that indicates either byte or character length semantics.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_LENGTH_SEMANTICS setting of the local computer.


NCharConversionException

This property determines whether data loss during an implicit or explicit character type conversion reports an error.


Declaration
// C#
public bool NCharConversionException {get; set;}

Property Value

A string that indicates whether or not a character type conversion causes an error message.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_NCHAR_CONV_EXCP setting of the local computer.


NumericCharacters

This property specifies the characters used for the decimal character and the group separator character for numeric values in strings.


Declaration
// C#
public string NumericCharacters {get; set;}

Property Value

A string that represents the characters used.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_NUMERIC_CHARACTERS setting of the local computer.


Sort

This property specifies the collating sequence for ORDER by clause.


Declaration
// C#
public string Sort {get; set;}

Property Value

A string that indicates the collating sequence.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_SORT setting of the local computer.


Territory

This property specifies the name of the territory.


Declaration
// C#
public string Territory {get; set;}

Property Value

A string that provides the name of the territory.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_TERRITORY setting of the local computer.

Changing this property changes other globalization properties.


TimeStampFormat

This property specifies the string format for TimeStamp types.


Declaration
// C#
public string TimeStampFormat {get; set;}

Property Value

The string format for TimeStamp types.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_TIMESTAMP_FORMAT setting of the local computer.


TimeStampTZFormat

This property specifies the string format for TimeStampTZ types.


Declaration
// C#
public string TimeStampTZFormat {get; set;}

Property Value

The string format for TimeStampTZ types.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the NLS_TIMESTAMP_TZ_FORMAT setting of the local computer.


TimeZone

This property specifies the time zone region name or hour offset.


Declaration
// C#
public string TimeZone {get; set;}

Property Value

The string represents the time zone region name or the time zone offset.


Exceptions

ObjectDisposedException - The object is already disposed.


Remarks

The default value is the time zone region name of the local computer

TimeZone is only used when the thread constructs one of the TimeStamp structures. TimeZone has no effect on the session.

TimeZone can be either an hour offset, for example, 7:00, or a valid time zone region name that is provided in V$TIMEZONE_NAMES, such as US/Pacific. Time zone abbreviations are not supported.


Note:

PST is a time zone region name as well as a time zone abbreviation; therefore it is accepted by OracleGlobalization.

This property returns an empty string if the OracleGlobalization object is obtained using GetSessionInfo() or GetSessionInfo(OracleGlobalization). Initially, by default, the time zone of the session is identical to the time zone of the thread. Therefore, given that the session time zone is not changed by invoking ALTER SESSION calls, the session time zone can be fetched from the client's globalization settings.

OracleGlobalization Public Methods

OracleGlobalization public methods are listed in Table 4-78.

Table 4-78 OracleGlobalization Public Methods

Public Method Description
Clone Creates a copy of an OracleGlobalization object
Dispose Inherited from Component


Clone

This method creates a copy of an OracleGlobalization object.


Declaration
// C#
public object Clone();

Return Value

An OracleGlobalization object.


Implements

ICloneable


Remarks

The cloned object has the same property values as that of the object being cloned.


Example
// C#
...
//Need a proper casting for the return value when cloned
OracleGlobalization ogi_cloned = (OracleGlobalization) ogi.Clone();
...

OracleInfoMessageEventArgs Class

The OracleInfoMessageEventArgs class provides event data for the OracleConnection.InfoMessage event. When any warning occurs in the database, the OracleConnection.InfoMessage event is triggered along with the OracleInfoMessageEventArgs object that stores the event data.


Class Inheritance

Object

  EventArgs

    OracleInfoMessageEventArgs


Declaration
// C#
public sealed class OracleInfoMessageEventArgs

Thread Safety

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


Example
// C#
public void WarningHandler(object src, OracleInfoMessageEventArgs args)
    {
      LogOutput("Source object is: " + src.GetType().Name);
      LogOutput("InfoMessageArgs.Message is " + args.Message);
      LogOutput("InfoMessageArgs.Errors is " + args.Errors);
      LogOutput("InfoMessageArgs.Source is " + args.Source);
    }

    public bool MyFunc()
    {
      ...
      con.Open();
      OracleCommand cmd = Con.CreateCommand();

      //Register to the InfoMessageHandler
      cmd.Connection.InfoMessage +=
        new OracleInfoMessageEventHandler(WarningHandler);

      cmd.CommandText = CmdStr;
      cmd.CommandType = CommandType.Text;
      //If CmdStr causes warning(s), it will be handled.
      cmd.ExecuteNonQuery();
      ...
    }


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleInfoMessageEventArgs Members

OracleInfoMessageEventArgs members are listed in the following tables:


OracleInfoMessageEventArgs Static Methods

The OracleInfoMessageEventArgs static methods are listed in Table 4-79.

Table 4-79 OracleInfoMessageEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleInfoMessageEventArgs Properties

The OracleInfoMessageEventArgs properties are listed in Table 4-80.

Table 4-80 OracleInfoMessageEventArgs Properties

Name Description
Errors Specifies the collection of errors generated by the data source
Message Specifies the error text generated by the data source
Source Specifies the name of the object that generated the error


OracleInfoMessageEventArgs Public Methods

The OracleInfoMessageEventArgs methods are listed in Table 4-81.

Table 4-81 OracleInfoMessageEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object

OracleInfoMessageEventArgs Static Methods

The OracleInfoMessageEventArgs static methods are listed in Table 4-82.

Table 4-82 OracleInfoMessageEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleInfoMessageEventArgs Properties

The OracleInfoMessageEventArgs properties are listed in Table 4-83.

Table 4-83 OracleInfoMessageEventArgs Properties

Name Description
Errors Specifies the collection of errors generated by the data source
Message Specifies the error text generated by the data source
Source Specifies the name of the object that generated the error


Errors

This property specifies the collection of errors generated by the data source.


Declaration
// C#
public OracleErrorCollection Errors {get;}

Property Value

The collection of errors.


Message

This property specifies the error text generated by the data source.


Declaration
// C#
public string Message {get;}

Property Value

The error text.


Source

This property specifies the name of the object that generated the error.


Declaration
// C#
public string Source {get;}

Property Value

The object that generated the error.

OracleInfoMessageEventArgs Public Methods

The OracleInfoMessageEventArgs methods are listed in Table 4-84.

Table 4-84 OracleInfoMessageEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object

OracleInfoMessageEventHandler Delegate

The OracleInfoMessageEventHandler represents the signature of the method that handles the OracleConnection.InfoMessage event.


Declaration
// C#
public delegate void OracleInfoMessageEventHandler(object sender,  
   OracleInfoMessageEventArgs eventArgs);

Parameter

OracleParameter Class

An OracleParameter object represents a parameter for an OracleCommand or a DataSet column.


Class Inheritance

Object

  MarshalByRefObject

    OracleParameter


Declaration
// C#
public sealed class OracleParameter : MarshalByRefObject, IDBDataParameter,  
   IDataParameter, IDisposable, ICloneable

Thread Safety

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


Exceptions

ArgumentException - The type binding is invalid.


Example
// C#
...
OracleParameter [] prm = new OracleParameter[3];

// Create OracleParameter objects through OracleParameterCollection
prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 1234, 
    ParameterDirection.Input);
prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2, 
    "Client", ParameterDirection.Input);
prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal, 
    10, ParameterDirection.Input);

cmd.CommandText = "insert into emp(empno, ename, deptno) values(:1, :2, :3)";
cmd.CommandType = CommandType.CommandText;
cmd.ExecuteNonQuery();
...


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleParameter Members

OracleParameter members are listed in the following tables:


OracleParameter Constructors

OracleParameter constructors are listed in Table 4-85.

Table 4-85 OracleParameter Constructors

Constructor Description
OracleParameter Constructors Instantiates a new instance of OracleParameter class (Overloaded)


OracleParameter Static Methods

OracleParameter static methods are listed in Table 4-86.

Table 4-86 OracleParameter Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleParameter Properties

OracleParameter properties are listed in Table 4-87.

Table 4-87 OracleParameter Properties

Name Description
ArrayBindSize Specifies the input or output size of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
ArrayBindStatus Specifies the input or output status of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
CollectionType
Specifies whether the OracleParameter represents a collection, and if so, specifies the collection type
DbType Specifies the datatype of the parameter using the Data.DbType enumeration type
Direction Specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter
IsNullable This method is a no-op
Offset Specifies the offset to the Value property or offset to the elements in the Value property
OracleDbType Specifies the Oracle datatype
ParameterName Specifies the name of the parameter
Precision Specifies the maximum number of digits used to represent the Value property
Scale Specifies the number of decimal places to which Value property is resolved
Size Specifies the maximum size, in bytes or characters, of the data transmitted to or from the server. For PL/SQL Associative Array Bind, Size specifies the maximum number of elements in PL/SQL Associative Array.
SourceColumn Specifies the name of the DataTable Column of the DataSet
SourceVersion Specifies the DataRowVersion value to use when loading the Value property of the parameter
Status Indicates the status of the execution related to the data in the Value property
Value Specifies the value of the Parameter


OracleParameter Public Methods

OracleParameter public methods are listed in Table 4-88.

Table 4-88 OracleParameter Public Methods

Public Method Description
Clone Creates a shallow copy of an OracleParameter object
CreateObjRef Inherited from MarshalByRefObject
Dispose Releases allocated resources
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
ToString Inherited from Object (Overloaded)

OracleParameter Constructors

OracleParameter constructors instantiate new instances of the OracleParameter class.


Overload List:

OracleParameter()

This constructor instantiates a new instance of OracleParameter class.


Declaration
// C#
public OracleParameter();

Remarks

Default Values:


OracleParameter (string, OracleDbType)

This constructor instantiates a new instance of OracleParameter class using the supplied parameter name and Oracle datatype.


Declaration
// C#
public OracleParameter(string parameterName, OracleDbType oraType);

Parameters

Remarks

Changing the DbType implicitly changes the OracleDbType.

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:


OracleParameter(string, object)

This constructor instantiates a new instance of the OracleParameter class using the supplied parameter name and parameter value.


Declaration
// C#
public OracleParameter(string parameterName, object obj);

Parameters

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:


OracleParameter(string, OracleDbType, ParameterDirection)

This constructor instantiates a new instance of the OracleParameter class using the supplied parameter name, datatype, and parameter direction.


Declaration
// C#
public OracleParameter(string parameterName, OracleDbType type, ParameterDirection direction);

Parameters

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:


OracleParameter(string, OracleDbType, object, ParameterDirection)

This constructor instantiates a new instance of the OracleParameter class using the supplied parameter name, datatype, value, and direction.


Declaration
// C#
public OracleParameter(string parameterName, OracleDbType type, object obj, ParameterDirection direction);

Parameters

Remarks

Changing the DbType implicitly changes the OracleDbType.

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:


OracleParameter(string, OracleDbType, int)

This constructor instantiates a new instance of the OracleParameter class using the supplied parameter name, datatype, and size.


Declaration
// C#
public OracleParameter(string parameterName, OracleDbType type, int size);

Parameters

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:


OracleParameter(string, OracleDbType, int, string)

This constructor instantiates a new instance of the OracleParameter class using the supplied parameter name, datatype, size, and source column.


Declaration
// C#
public OracleParameter(string parameterName, OracleDbType type, int size,  
 string srcColumn);

Parameters

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:


OracleParameter(string, OracleDbType, int, ParameterDirection, bool, byte, byte, string, DataRowVersion, object)

This constructor instantiates a new instance of the OracleParameter class using the supplied parameter name, datatype, size, direction, null indicator, precision, scale, source column, source version and parameter value.


Declaration
// C#
public OracleParameter(string parameterName, OracleDbType oraType, int size,
 ParameterDirection direction, bool isNullable, byte precision, byte scale,
 string srcColumn, DataRowVersion srcVersion, object obj);

Parameters

Exceptions

ArgumentException - The supplied value does not belong to the type of Value property in any of the OracleTypes.


Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:


OracleParameter(string, OracleDbType, int, object, ParameterDirection)

This constructor instantiates a new instance of the OracleParameter class using the supplied parameter name, datatype, size, value, and direction.


Declaration
// C#
public OracleParameter(string parameterName, OracleDbType type, int size, object obj, ParameterDirection direction);

Parameters

Remarks

Changing the DbType implicitly changes the OracleDbType.

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

OracleParameter Static Methods

OracleParameter static methods are listed in Table 4-89.

Table 4-89 OracleParameter Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleParameter Properties

OracleParameter properties are listed in Table 4-90.

Table 4-90 OracleParameter Properties

Name Description
ArrayBindSize Specifies the input or output size of elements in Value property of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
ArrayBindStatus Specifies the input or output status of elements in Value property of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
DbType Specifies the datatype of the parameter using the Data.DbType enumeration type
Direction Specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter
IsNullable This method is a no-op
Offset Specifies the offset to the Value property or offset to the elements in the Value property
OracleDbType Specifies the Oracle datatype
ParameterName Specifies the name of the parameter
Precision Specifies the maximum number of digits used to represent the Value property
Scale Specifies the number of decimal places to which Value property is resolved
Size Specifies the maximum size, in bytes or characters, of the data transmitted to or from the server. For PL/SQL Associative Array Bind, Size specifies the maximum number of elements in PL/SQL Associative Array
SourceColumn Specifies the name of the DataTable Column of the DataSet
SourceVersion Specifies the DataRowVersion value to use when loading the Value property of the parameter
Status Indicates the status of the execution related to the data in the Value property
Value Specifies the value of the Parameter


ArrayBindSize

This property specifies the input or output size of elements of Value property before or after an Array Bind or PL/SQL Associative Array execution.


Declaration
// C#
public int[] ArrayBindSize {get; set; }

Property Value

An array of int values specifying the size.


Remarks

Default = null.

This property is only used for variable size element types for an Array Bind or PL/SQL Associative Array. For fixed size element types, this property is ignored.

Each element in the ArrayBindSize corresponds to the bind size of an element in the Value property. Before execution, ArrayBindSize specifies the maximum size of each element to be bound in the Value property. After execution, it contains the size of each element returned in the Value property.

For binding a PL/SQL Associative Array, whose elements are of a variable-length element type, as an InputOutput , Out, or ReturnValue parameter, this property must be set, and the number of elements in ArrayBindSize must be equal to or greater than the number of elements in Value property.


Example
// C#
...
OracleParameter Param = new OracleParameter("name", OracleDbType.Varchar2);
Param.ArrayBindSize = new Int32[3];

// These sizes indicate the maximum size of the elements in parameter Value 
// property.
Param.ArrayBindSize[0] = 100;
Param.ArrayBindSize[1] = 300;
Param.ArrayBindSize[2] = 200;
...


ArrayBindStatus

This property specifies the input or output status of each element in the Value property before or after an Array Bind or PL/SQL Associative Array execution.


Declaration
// C#
public OracleParameterStatus[] ArrayBindStatus { get; set; }

Property Value

An array of OracleParameterStatus enumerated values.


Exceptions

ArgumentOutofRangeException - The Status value specified is invalid.


Remarks

Default = null.

ArrayBindStatus is used for Array Bind and PL/SQL Associative Array execution only.

Before execution, ArrayBindStatus indicates the bind status of each element in the Value property. After execution, it contains the execution status of each element in the Value property.


CollectionType

This property specifies whether the OracleParameter represents a collection, and if so, specifies the collection type.


Declaration
// C#
public OracleCollectionType CollectionType { get; set; }

Property Value

An OracleCollectionType enumerated value.


Exceptions

ArgumentException - The OracleCollectionType value specified is invalid.


Remarks

Default = OracleCollectionType.None. If OracleParameter is used to bind a PL/SQL Associative Array, then CollectionType must be set to OracleCollectionType.PLSQLAssociativeArray.


DbType

This property specifies the datatype of the parameter using the Data.DbType enumeration type.


Declaration
// C#
public DbType DbType {get; set; }

Property Value

A DbType enumerated value.


Implements

IDataParameter


Exceptions

ArgumentException - The DbType value specified is invalid.


Remarks

Default = DbType.String

DbType is the datatype of each element in the array if the OracleParameter object is used for Array Bind or PL/SQL Associative Array Bind execution.

Due to the link between DbType and OracleDbType properties, if the DbType property is set, the OracleDbType property is inferred from DbType.


Direction

This property specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter.


Declaration
// C#
public ParameterDirection Direction { get; set; }

Property Value

A ParameterDirection enumerated value.


Implements

IDataParameter


Exceptions

ArgumentOutOfRangeException - The ParameterDirection value specified is invalid.


Remarks

Default = ParameterDirection.Input

Possible values: Input, InputOutput, Output, and ReturnValue.


Offset

This property specifies the offset to the Value property.


Declaration
// C#
public int Offset { get; set; }

Property Value

An int that specifies the offset.


Exceptions

ArgumentOutOfRangeException - The Offset value specified is invalid.


Remarks

Default = 0

For Array Bind and PL/SQL Associative Array Bind, Offset applies to every element in the Value property.

The Offset property is used for binary and string data types. The Offset property represents the number of bytes for binary types and the number of characters for strings. The count for strings does not include the terminating character if a null is referenced. The Offset property is used by parameters of the following types:


OracleDbType

This property specifies the Oracle datatype.


Declaration
// C#
public OracleDbType OracleDbType { get; set; }

Property Value

An OracleDbType enumerated value.


Remarks

Default = OracleDbType.Varchar2

If the OracleParameter object is used for Array Bind or PL/SQL Associative Array Bind execution, OracleDbType is the datatype of each element in the array.

The OracleDbType property and DbType property are linked. Therefore, setting the OracleDbType property changes the DbType property to a supporting DbType.


ParameterName

This property specifies the name of the parameter.


Declaration
// C#
public string ParameterName { get; set; }

Property Value

String


Implements

IDataParameter


Remarks

Default = null

Oracle supports ParameterName up to 30 characters.


Precision

This property specifies the maximum number of digits used to represent the Value property.


Declaration
// C#
Public byte Precision { get; set; }

Property Value

byte


Remarks

Default = 0

The Precision property is used by parameters of type OracleDbType.Decimal.

Oracle supports Precision range from 0 to 38.

For Array Bind and PL/SQL Associative Array Bind, Precision applies to each element in the Value property.


Scale

This property specifies the number of decimal places to which Value property is resolved.


Declaration
// C#
public byte Scale { get; set; }

Property Value

byte


Remarks

Default = 0.

Scale is used by parameters of type OracleDbType.Decimal.

Oracle supports Scale between -84 and 127.

For Array Bind and PL/SQL Associative Array Bind, Scale applies to each element in the Value property.


Size

This property specifies the maximum size, in bytes or characters, of the data transmitted to or from the server.

For PL/SQL Associative Array Bind, Size specifies the maximum number of elements in PL/SQL Associative Array.


Declaration
// C#
public int Size { get; set;}

Property Value

int


Exceptions

ArgumentOutOfRangeException - The Size value specified is invalid.

InvalidOperationException - The Size = 0 when the OracleParameter object is used to bind a PL/SQL Associative Array.


Remarks

The default value is 0.

Before execution, this property specifies the maximum size to be bound in the Value property. After execution, it contains the size of the type in the Value property.

Size is used for parameters of the following types:

The value of Size is handled as follows:

If the Size is not explicitly set, it is inferred from the actual size of the specified parameter value when binding.


Note:

Size does not include the null terminating character for the string data.

If the OracleParameter object is used to bind a PL/SQL Associative Array, Size specifies the maximum number of elements in the PL/SQL Associative Array. Before the execution, this property specifies the maximum number of elements in the PL/SQL Associative Array. After the execution, it specifies the current number of elements returned in the PL/SQL Associative Array. For Output and InputOutput parameters and return values, Size specifies the maximum number of elements in the PL/SQL Associative Array.

ODP.NET does not support binding an empty PL/SQL Associative Array. Therefore, Size cannot be set to 0 when the OracleParameter object is used to bind a PL/SQL Associative Array.


SourceColumn

This property specifies the name of the DataTable Column of the DataSet.


Declaration
// C#
public string SourceColumn { get; set; }

Property Value

A string.


Implements

IDataParameter


Remarks

Default = empty string


SourceVersion

This property specifies the DataRowVersion value to use when loading the Value property of the parameter.


Declaration
// C#
public DataRowVersion SourceVersion { get; set; }

Property Value

DataRowVersion


Implements

IDataParameter


Exceptions

ArgumentOutOfRangeException - The DataRowVersion value specified is invalid.


Remarks

Default = DataRowVersion.Current

SourceVersion is used by the OracleDataAdapter.UpdateCommand() during the OracleDataAdapter.Update to determine whether the original or current value is used for a parameter value. This allows primary keys to be updated. This property is ignored by the OracleDataAdapter.InsertCommand() and the OracleDataAdapter.DeleteCommand().


Status

This property indicates the status of the execution related to the data in the Value property.


Declaration
// C#
public OracleParameterStatus Status { get; set; }

Property Value

An OracleParameterStatus enumerated value.


Exceptions

ArgumentOutOfRangeException - The Status value specified is invalid.


Remarks

Default = OracleParameterStatus.Success

Before execution, this property indicates the bind status related to the Value property. After execution, it returns the status of the execution.

Status indicates whether:


Value

This property specifies the value of the Parameter.


Declaration
// C#
public object Value { get; set; }

Property Value

An object.


Implements

IDataParameter


Exceptions

ArgumentException - The Value property specified is invalid.

InvalidArgumentException- The Value property specified is invalid.


Remarks

Default = null

If the OracleParameter object is used for Array Bind or PL/SQL Associative Array, Value is an array of parameter values.

The Value property can be overwritten by OracleDataAdapter.Update().

The provider attempts to convert any type of value if it supports the IConvertible interface. Conversion errors occur if the specified type is not compatible with the value.

When sending a null parameter value to the database, the user must specify DBNull, not null. The null value in the system is an empty object that has no value. DBNull is used to represent null values. The user can also specify a null value by setting Status to OracleParameterStatus.NullValue. In this case, the provider sends a null value to the database.

If neither OracleDbType nor DbType are set, their values can be inferred by Value.

For input parameters the value is:

For output parameters the value is:

When array binding is used with:

When PL/SQL Associative Array binding is used with:

Each parameter should have a value. To bind a parameter with a null value, set Value to DBNull.Value, or set Status to OracleParameterStatus. NullInsert.

OracleParameter Public Methods

OracleParameter public methods are listed in Table 4-91.

Table 4-91 OracleParameter Public Methods

Public Method Description
Clone Creates a shallow copy of an OracleParameter object
CreateObjRef Inherited from MarshalByRefObject
Dispose Releases allocated resources
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
ToString Inherited from Object (Overloaded)


Clone

This method creates a shallow copy of an OracleParameter object.


Declaration
// C#
public object Clone();

Return Value

An OracleParameter object.


Implements

ICloneable


Remarks

The cloned object has the same property values as that of the object being cloned.


Example
// C#
...
//Need a proper casting for the return value when cloned
OracleParameter param_cloned = (OracleParameter) param.Clone();
...

Dispose

This method releases resources allocated for an OracleParameter object.


Declaration
// C#
public void Dispose();

Implements

IDisposable


OracleParameterCollection Class

An OracleParameterCollection class represents a collection of all parameters relevant to an OracleCommand object and their mappings to DataSet columns.


Class Inheritance

Object

  MarshalByRefObject

    OracleParameterCollection


Declaration
// C#
public sealed class OracleParameterCollection : MarshalByRefObject, 
  IDataParameterCollection, IList, ICollection, IEnumerable

Thread Safety

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


Remarks

The position of an OracleParameter added into the OracleParameterCollection is the binding position in the SQL statement. Position is 0-based and is used only for positional binding. If named binding is used, the position of an OracleParameter in the OracleParameterCollection is ignored.


Example
// C#
string conStr = "User Id=scott;Password=tiger;Data Source=oracle";

// Create the OracleConnection
OracleConnection con = new OracleConnection(conStr);
con.Open();

// Create the OracleCommand
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;

// Create OracleParameter
OracleParameter [] prm = new OracleParameter[3];

// Bind parameters
prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 1234, 
    ParameterDirection.Input);
prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2, 
    "Client", ParameterDirection.Input);
prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal, 
    10, ParameterDirection.Input);

cmd.CommandText = "insert into emp(empno, ename, deptno) values(:1, :2, :3)";
cmd.ExecuteNonQuery();

// Remove OracleParameter objects from the collection
cmd.Parameters.Clear();

// Dispose OracleCommand object
cmd.Dispose();

// Close and Dispose OracleConnection object
con.Close();
con.Dispose();


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleParameterCollection Members

OracleParameterCollection members are listed in the following tables:


OracleParameterCollection Static Methods

OracleParameterCollection static methods are listed in Table 4-92.

Table 4-92 OracleParameterCollection Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleParameterCollection Properties

OracleParameterCollection properties are listed in Table 4-93.

Table 4-93 OracleParameterCollection Properties

Name Description
Count Specifies the number of OracleParameters in the collection
Item Gets and sets the OracleParameter object (Overloaded)


OracleParameterCollection Public Methods

OracleParameterCollection public methods are listed in Table 4-94.

Table 4-94 OracleParameterCollection Public Methods

Public Method Description
Add Adds objects to the collection (Overloaded)
Clear Removes all the OracleParameter objects from the collection
Contains Indicates whether objects exist in the collection (Overloaded)
CopyTo Copies OracleParameter objects from the collection, starting with the supplied index to the supplied array
CreateObjRef Inherited from MarshalByRefObject
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
IndexOf Returns the index of the objects in the collection (Overloaded)
Insert Inserts the supplied OracleParameter to the collection at the specified index
Remove Removes objects from the collection
RemoveAt Removes objects from the collection by location (Overloaded)
ToString Inherited from Object

OracleParameterCollection Static Methods

OracleParameterCollection static methods are listed in Table 4-95.

Table 4-95 OracleParameterCollection Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleParameterCollection Properties

OracleParameterCollection properties are listed in Table 4-96.

Table 4-96 OracleParameterCollection Properties

Name Description
Count Specifies the number of OracleParameters in the collection
Item Gets and sets the OracleParameter object (Overloaded)


Count

This property specifies the number of OracleParameter objects in the collection.


Declaration
// C#
public int Count {get;}

Property Value

The number of OracleParameter objects.


Implements

ICollection


Remarks

Default = 0

Item

Item gets and sets the OracleParameter object.


Overload List:

Item[int]

This property gets and sets the OracleParameter object at the index specified by the supplied parameterIndex.


Declaration
// C#
public object Item[int parameterIndex] {get; set;}

Property Value

An object.


Implements

IList


Exceptions

IndexOutOfRangeException - The supplied index does not exist.


Remarks

The OracleParameterCollection class is a zero-based index.


Item[string]

This property gets and sets the OracleParameter object using the parameter name specified by the supplied parameterName.


Declaration
// C#
public OracleParameter Item[string parameterName] {get; set;};

Property Value

An OracleParameter.


Implements

IDataParameterCollection


Exceptions

IndexOutOfRangeException - The supplied parameter name does not exist.

OracleParameterCollection Public Methods

OracleParameterCollection public methods are listed in Table 4-97.

Table 4-97 OracleParameterCollection Public Methods

Public Method Description
Add Adds objects to the collection (Overloaded)
Clear Removes all the OracleParameter objects from the collection
Contains Indicates whether objects exist in the collection (Overloaded)
CopyTo Copies OracleParameter objects from the collection, starting with the supplied index to the supplied array
CreateObjRef Inherited from MarshalByRefObject
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
IndexOf Returns the index of the objects in the collection (Overloaded)
Insert Inserts the supplied OracleParameter to the collection at the specified index
Remove Removes objects from the collection
RemoveAt Removes objects from the collection by location (Overloaded)
ToString Inherited from Object

Add

Add adds objects to the collection.


Overload List:

Add(object)

This method adds the supplied object to the collection.


Declaration
// C#
public int Add(object obj);

Parameters

Return Value

The index at which the new OracleParameter is added.


Implements

IList


Remarks

InvalidCastException - The supplied obj cannot be cast to an OracleParameter object.


Add(OracleParameter)

This method adds the supplied OracleParameter object to the collection.


Declaration
// C#
public OracleParameter Add(OracleParameter paramObj);

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Add(string, object)

This method adds an OracleParameter object to the collection using the supplied name and object value


Declaration
// C#
public OracleParameter Add(string name, object val);

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Add(string, OracleDbType)

This method adds an OracleParameter object to the collection using the supplied name and database type.


Declaration
// C#
public OracleParameter Add(string name, OracleDbType dbType);

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Add(string, OracleDbType, ParameterDirection)

This method adds an OracleParameter object to the collection using the supplied name, database type, and direction.


Declaration
// C#
public OracleParameter Add(string name, OracleDbType dbType, ParameterDirection direction);

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Add(string, OracleDbType, object, ParameterDirection)

This method adds an OracleParameter object to the collection using the supplied name, database type, parameter value, and direction.


Declaration
// C#
public OracleParameter Add(string name, OracleDbType dbType, object val,
 ParameterDirection dir);

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Example
// C#
...
OracleParameter prm = new OracleParameter();
prm = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 1234, 
   ParameterDirection.Input);

cmd.CommandText = "insert into NumTable(numcol) values(:1)";
cmd.ExecuteNonQuery();
...


Add(string, OracleDbType, int, object, ParameterDirection)

This method adds an OracleParameter object to the collection using the supplied name, database type, size, parameter value, and direction.


Declaration
// C#
public OracleParameter Add(string name, OracleDbType dbType, int size, 
   object val, ParameterDirection dir;

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Add(string, OracleDbType, int)

This method adds an OracleParameter object to the collection using the supplied name, database type, and size.


Declaration
// C#
public OracleParameter Add(string name, OracleDbType dbType, int size);

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Example
// C#
...
OracleParameter prm = new OracleParameter();
prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 10);
prm.Direction = ParameterDirection.Input;
prm.Value = 1111;

cmd.CommandText = "insert into NumTable(numcol) values(:1)";
cmd.ExecuteNonQuery();
...


Add (string, OracleDbType, int, string)

This method adds an OracleParameter object to the collection using the supplied name, database type, size, and source column.


Declaration
// C#
public OracleParameter Add(string name, OracleDbType dbType, int size, 
  string srcColumn);

Parameters

Return Value

An OracleParameter.


Add(string, OracleDbType, int, ParameterDirection, bool, byte, byte, string, DataRowVersion, object)

This method adds an OracleParameter object to the collection using the supplied name, database type, size, direction, null indicator, precision, scale, source column, source version, and parameter value.


Declaration
// C#
public OracleParameter Add(string name, OracleDbType dbType, int size,
  ParameterDirection dir, bool isNullable, byte precision, 
  byte scale, string srcColumn, DataRowVersion version, object val);

Parameters

Return Value

The newly created OracleParameter object which was added to the collection.


Exceptions

ArgumentException - The type of supplied val does not belong to the type of Value property in any of the ODP.NET Types.


Clear

This method removes all the OracleParameter objects from the collection.


Declaration
// C#
public void Clear();

Implements

IList


Example
// C#
...
OracleParameter [] prm = new OracleParameter[3];

prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 
   1234, ParameterDirection.Input);
prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2, 
   "Client", ParameterDirection.Input);
prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal, 10,
    ParameterDirection.Input);

cmd.CommandText = "insert into emp(empno, ename, deptno) values(:1,:2, :3)";
cmd.ExecuteNonQuery();

// This method removes all the parameters from the parameter collection.
cmd.Parameters.Clear();
...

Contains

Contains indicates whether the supplied object exists in the collection.


Overload List:

Contains(object)

This method indicates whether the supplied object exists in the collection.


Declaration
// C#
public bool Contains(object obj)

Parameters

Return Value

A bool that indicates whether or not the OracleParameter specified is inside the collection.


Implements

IList


Exceptions

InvalidCastException - The supplied obj is not an OracleParameter object.


Remarks

Returns true if the collection contains the OracleParameter object; otherwise, returns false.


Example
...
prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 1234, 
     ParameterDirection.Input);
if (cmd.Parameters.Contains((Object)prm))
// This method removes a particular parameter from the parameter collection.
cmd.Parameters.Remove((Object) prm);
...


Contains(string)

This method indicates whether an OracleParameter object exists in the collection using the supplied string.


Declaration
// C#
public bool Contains(string name);

Parameters

Return Value

Returns true if the collection contains the OracleParameter object with the specified parameter name; otherwise, returns false.


Implements

IDataParameterCollection


Example
...
prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 1234, +
     ParameterDirection.Input);
if (cmd.Parameters.Contains((Object)prm))
// This method removes a particular parameter from the parameter collection.
cmd.Parameters.Remove((Object) prm);
...


CopyTo

This method copies OracleParameter objects from the collection, starting with the supplied index to the supplied array.


Declaration
// C#
public void CopyTo(Array array, int index);

Parameters

Implements

ICollection

IndexOf

IndexOf returns the index of the OracleParameter object in the collection.


Overload List:

IndexOf(object)

This method returns the index of the OracleParameter object in the collection.


Declaration
// C#
public int IndexOf(object obj);

Parameters

Return Value

Returns the index of the OracleParameter object in the collection.


Implements

IList


Exceptions

InvalidCastException - The supplied obj cannot be cast to an OracleParameter object.


Remarks

Returns the index of the supplied OracleParameter obj in the collection.


IndexOf(String)

This method returns the index of the OracleParameter object with the specified name in the collection.


Declaration
// C#
public int IndexOf(String name);

Parameters

Return Value

Returns the index of the supplied OracleParameter in the collection.


Implements

IDataParameterCollection


Insert

This method inserts the supplied OracleParameter object to the collection at the specified index.


Declaration
// C#
public void Insert(int index, object obj);

Parameters

Implements

IList


Remarks

An InvalidCastException is thrown if the supplied obj cannot be cast to an OracleParameter object.


Remove

This method removes the supplied OracleParameter from the collection.


Declaration
// C#
public void Remove(object obj);

Parameters

Implements

IList


Exceptions

InvalidCastException - The supplied obj cannot be cast to an OracleParameter object.


Example
...
prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 1234, 
     ParameterDirection.Input);
if (cmd.Parameters.Contains((Object)prm))
// This method removes a particular parameter from the parameter collection.
cmd.Parameters.Remove((Object) prm);
...

RemoveAt

RemoveAt removes the OracleParameter object from the collection by location.


Overload List:

RemoveAt(int)

This method removes from the collection the OracleParameter object located at the index specified by the supplied index.


Declaration
// C#
public void RemoveAt(int index);

Parameters

Implements

IList


RemoveAt(String)

This method removes from the collection the OracleParameter object specified by the supplied name.


Declaration
// C#
public void RemoveAt(String name);

Parameters

Implements

IDataParameterCollection

OracleRowUpdatedEventHandler Delegate

The OracleRowUpdatedEventHandler delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdated event.


Declaration
// C#
public delegate void OracleRowUpdatedEventHandler(object sender,
    OracleRowUpdatedEventArgs eventArgs);

Parameters

Remarks

Event callbacks can be registered through this event delegate for applications that wish to be notified after a row is updated.

In the .NET framework, the convention of an event delegate requires two parameters: the object that raises the event and the event data.


OracleRowUpdatedEventArgs Class

The OracleRowUpdatedEventArgs class provides event data for the OracleDataAdapter.RowUpdated event.


Class Inheritance

Object

  EventArgs

    RowUpdatedEventArgs

      OracleRowUpdatedEventArgs


Declaration
// C#
public sealed class OracleRowUpdatedEventArgs : RowUpdatedEventArgs

Thread Safety

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


Example

The example for the RowUpdated event shows how to use OracleRowUpdatedEventArgs. See "Example".


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleRowUpdatedEventArgs Members

OracleRowUpdatedEventArgs members are listed in the following tables:


OracleRowUpdatedEventArgs Constructors

OracleRowUpdatedEventArgs constructors are listed in Table 4-98.

Table 4-98 OracleRowUpdatedEventArgs Constructors

Constructor Description
OracleRowUpdatedEventArgs Constructor Instantiates a new instance of OracleRowUpdatedEventArgs class


OracleRowUpdatedEventArgs Static Methods

The OracleRowUpdatedEventArgs static methods are listed in Table 4-99.

Table 4-99 OracleRowUpdatedEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleRowUpdatedEventArgs Properties

The OracleRowUpdatedEventArgs properties are listed in Table 4-100.

Table 4-100 OracleRowUpdatedEventArgs Properties

Name Description
Command Specifies the OracleCommand that is used when OracleDataAdapter.Update() is called
Errors Inherited from RowUpdatedEventArgs
RecordsAffected Inherited from RowUpdatedEventArgs
Row Inherited from RowUpdatedEventArgs
StatementType Inherited from RowUpdatedEventArgs
Status Inherited from RowUpdatedEventArgs
TableMapping Inherited from RowUpdatedEventArgs


OracleRowUpdatedEventArgs Public Methods

The OracleRowUpdatedEventArgs properties are listed in Table 4-101.

Table 4-101 OracleRowUpdatedEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object

OracleRowUpdatedEventArgs Constructor

The OracleRowUpdatedEventArgs constructor creates a new OracleRowUpdatedEventArgs instance.


Declaration
// C#
public OracleRowUpdatedEventArgs(DataRow row,IDbCommand command,  
   StatementType statementType, DataTableMapping tableMapping);

Parameters

OracleRowUpdatedEventArgs Static Methods

The OracleRowUpdatedEventArgs static methods are listed in Table 4-102.

Table 4-102 OracleRowUpdatedEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleRowUpdatedEventArgs Properties

The OracleRowUpdatedEventArgs properties are listed in Table 4-103.

Table 4-103 OracleRowUpdatedEventArgs Properties

Name Description
Command Specifies the OracleCommand that is used when OracleDataAdapter.Update() is called
Errors Inherited from RowUpdatedEventArgs
RecordsAffected Inherited from RowUpdatedEventArgs
Row Inherited from RowUpdatedEventArgs
StatementType Inherited from RowUpdatedEventArgs
Status Inherited from RowUpdatedEventArgs
TableMapping Inherited from RowUpdatedEventArgs


Command

This property specifies the OracleCommand that is used when OracleDataAdapter.Update() is called.


Declaration
// C#
public new OracleCommand Command {get;}

Property Value

The OracleCommand executed when Update is called.

OracleRowUpdatedEventArgs Public Methods

The OracleRowUpdatedEventArgs properties are listed in Table 4-104.

Table 4-104 OracleRowUpdatedEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object


OracleRowUpdatingEventArgs Class

The OracleRowUpdatingEventArgs class provides event data for the OracleDataAdapter.RowUpdating event.


Class Inheritance

Object

  EventArgs

    RowUpdatingEventArgs

      OracleRowUpdatingEventArgs


Declaration
// C#
public sealed class OracleRowUpdatingEventArgs : RowUpdatingEventArgs

Thread Safety

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


Example

The example for the RowUpdated event shows how to use OracleRowUpdatingEventArgs. See "Example".


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleRowUpdatingEventArgs Members

OracleRowUpdatingEventArgs members are listed in the following tables:


OracleRowUpdatingEventArgs Constructors

OracleRowUpdatingEventArgs constructors are listed in Table 4-105.

Table 4-105 OracleRowUpdatingEventArgs Constructors

Constructor Description
OracleRowUpdatingEventArgs Constructor Instantiates a new instance of OracleRowUpdatingEventArgs class (Overloaded)


OracleRowUpdatingEventArgs Static Methods

The OracleRowUpdatingEventArgs static methods are listed in Table 4-106.

Table 4-106 OracleRowUpdatingEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleRowUpdatingEventArgs Properties

The OracleRowUpdatingEventArgs properties are listed in Table 4-107.

Table 4-107 OracleRowUpdatingEventArgs Properties

Name Description
Command
Specifies the OracleCommand that is used when the OracleDataAdapter.Update() is called
Errors Inherited from RowUpdatingEventArgs
Row Inherited from RowUpdatingEventArgs
StatementType Inherited from RowUpdatingEventArgs
Status Inherited from RowUpdatingEventArgs
TableMapping Inherited from RowUpdatingEventArgs


OracleRowUpdatingEventArgs Public Methods

The OracleRowUpdatingEventArgs public methods are listed in Table 4-108.

Table 4-108 OracleRowUpdatingEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object

OracleRowUpdatingEventArgs Constructor

The OracleRowUpdatingEventArgs constructor creates a new instance of the OracleRowUpdatingEventArgs class using the supplied data row, IDbCommand, type of SQL statement, and table mapping.


Declaration
// C#
public OracleRowUpdatingEventArgs(DataRow row, IDbCommand command,  
   StatementType statementType, DataTableMapping tableMapping);

Parameters

OracleRowUpdatingEventArgs Static Methods

The OracleRowUpdatingEventArgs static methods are listed in Table 4-109.

Table 4-109 OracleRowUpdatingEventArgs Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleRowUpdatingEventArgs Properties

The OracleRowUpdatingEventArgs properties are listed in Table 4-110.

Table 4-110 OracleRowUpdatingEventArgs Properties

Name Description
Command
Specifies the OracleCommand that is used when the OracleDataAdapter.Update() is called
Errors Inherited from RowUpdatingEventArgs
Row Inherited from RowUpdatingEventArgs
StatementType Inherited from RowUpdatingEventArgs
Status Inherited from RowUpdatingEventArgs
TableMapping Inherited from RowUpdatingEventArgs


Command

This property specifies the OracleCommand that is used when the OracleDataAdapter.Update() is called.


Declaration
// C#
public new OracleCommand Command {get; set;}

Property Value

The OracleCommand executed when Update is called.

OracleRowUpdatingEventArgs Public Methods

The OracleRowUpdatingEventArgs public methods are listed in Table 4-111.

Table 4-111 OracleRowUpdatingEventArgs Public Methods

Name Description
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetType Inherited from Object
ToString Inherited from Object

OracleRowUpdatingEventHandler Delegate

The OracleRowUpdatingEventHandler delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdating event.


Declaration
// C#
public delegate void OracleRowUpdatingEventHandler (object sender,
 OracleRowUpdatingEventArgs eventArgs);

Parameters

Remarks

Event callbacks can be registered through this event delegate for applications that wish to be notified after a row is updated.

In the .NET framework, the convention of an event delegate requires two parameters: the object that raises the event and the event data.

OracleTransaction Class

An OracleTransaction object represents a local transaction.


Class Inheritance

Object

  MarshalByRefObject

    OracleTransaction


Declaration
// C#
public sealed class OracleTransaction : MarshalByRefObject, 
    IDbTransaction, IDisposable

Thread Safety

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


Remarks

The application calls BeginTransaction on the OracleConnection object to create an OracleTransaction object. The OracleTransaction object can be created in one of the following two modes:

Any other mode results in an exception.

The execution of a DDL statement in the context of a transaction is not recommended since it results in an implicit commit that is not reflected in the state of the OracleTransaction object.

All operations related to savepoints pertain to the current local transaction. Operations like commit and rollback performed on the transaction have no effect on data in any existing DataSet.


Example
// C#
//  Starts a transaction and inserts one record. If insert fails, rolls back
//  the transaction. Otherwise, commits the transaction.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Create an OracleCommand object using the connection object
OracleCommand cmd = new OracleCommand("", con);

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

try
  {
    cmd.CommandText = "insert into mytable values (99, 'foo')";
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    txn.Commit();
    Console.WriteLine("One record is inserted into the database table.");
  }
    catch(Exception e)
  {
    txn.Rollback();
    Console.WriteLine("No record was inserted into the database table.");
  }
...


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleTransaction Members

OracleTransaction members are listed in the following tables:


OracleTransaction Static Methods

OracleTransaction static methods are listed in Table 4-112.

Table 4-112 OracleTransaction Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleTransaction Properties

OracleTransaction properties are listed in Table 4-113.

Table 4-113 OracleTransaction Properties

Name Description
IsolationLevel
Specifies the isolation level for the transaction
Connection
Specifies the connection that is associated with the transaction


OracleTransaction Public Methods

OracleTransaction public methods are listed in Table 4-114.

Table 4-114 OracleTransaction Public Methods

Public Method Description
Commit
Commits the database transaction
CreateObjRef Inherited from MarshalByRefObject
Dispose
Frees the resources used by the OracleTransaction object
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
Rollback
Rolls back a database transaction (Overloaded)
Save Creates a savepoint within the current transaction
ToString Inherited from Object

OracleTransaction Static Methods

OracleTransaction static methods are listed in Table 4-115.

Table 4-115 OracleTransaction Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleTransaction Properties

OracleTransaction properties are listed in Table 4-116.

Table 4-116 OracleTransaction Properties

Name Description
IsolationLevel
Specifies the isolation level for the transaction
Connection
Specifies the connection that is associated with the transaction


IsolationLevel

This property specifies the isolation level for the transaction.


Declaration
// C#
public IsolationLevel IsolationLevel {get;}

Property Value

IsolationLevel


Implements

IDbTransaction


Exceptions

InvalidOperationException - The transaction has already completed.


Remarks

Default = IsolationLevel.ReadCommitted


Connection

This property specifies the connection that is associated with the transaction.


Declaration
// C#
public OracleConnection Connection {get;}

Property Value

Connection


Implements

IDbTransaction


Remarks

This property indicates the OracleConnection object that is associated with the transaction.

OracleTransaction Public Methods

OracleTransaction public methods are listed in Table 4-117.

Table 4-117 OracleTransaction Public Methods

Public Method Description
Commit
Commits the database transaction
CreateObjRef Inherited from MarshalByRefObject
Dispose
Frees the resources used by the OracleTransaction object
Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetType Inherited from Object
InitializeLifetimeService Inherited from MarshalByRefObject
Rollback
Rolls back a database transaction (Overloaded)
Save Creates a savepoint within the current transaction
ToString Inherited from Object


Commit

This method commits the database transaction.


Declaration
// C#
public void Commit();

Implements

IDbTransaction


Exceptions

InvalidOperationException - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.


Remarks

Upon a successful commit, the transaction enters a completed state.


Example
// C#
//  Starts a transaction and inserts one record. If insert fails, rolls back
//  the transaction. Otherwise, commits the transaction.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Create an OracleCommand object using the connection object
OracleCommand cmd = new OracleCommand("", con);

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

try
  {
    cmd.CommandText = "insert into mytable values (99, 'foo')";
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    txn.Commit();
    Console.WriteLine("One record was inserted into the database table.");
  }
    catch(Exception e)
  {
    txn.Rollback();
    Console.WriteLine("No record was inserted into the database table.");
  }
...


Dispose

This method frees the resources used by the OracleTransaction object.


Declaration
// C#
public void Dispose();

Implements

IDisposable


Remarks

This method releases both the managed and unmanaged resources held by the OracleTransaction object. If the transaction is not in a completed state, an attempt to rollback the transaction is made.

Rollback

Rollback rolls back a database transaction.


Overload List:

Rollback()

This method rolls back a database transaction.


Declaration
// C#
public void Rollback();

Implements

IDbTransaction


Exceptions

InvalidOperationException - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.


Remarks

After a Rollback(), the OracleTransaction object can no longer be used because the Rollback ends the transaction.


Example
// C#
//  Starts a transaction and inserts one record. Then rolls back the
//  transaction.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
   "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

OracleCommand cmd = Con.CreateCommand();

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

cmd.CommandText = "insert into mytable values (99, 'foo')";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
txn.Rollback();
Console.WriteLine("Nothing was inserted into the database table.");
...


Rollback(string)

This method rolls back a database transaction to a savepoint within the current transaction.


Declaration
// C#
public void Rollback(string savepointName);

Parameters

Exceptions

InvalidOperationException - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.


Remarks

After a rollback to a savepoint, the current transaction remains active and can be used for further operations.

The savepointName specified does not have to match the case of the savepointName created using the Save method, since savepoints are created in the database in a case-insensitive manner.


Save

This method creates a savepoint within the current transaction.


Declaration
// C#
public void Save(string savepointName);

Parameters

Exceptions

InvalidOperationException - The transaction has already been completed.


Remarks

After creating a savepoint, the transaction does not enter a completed state and can be used for further operations.

The savepointName specified is created in the database in a case-insensitive manner. Calling the Rollback method rolls back to savepointName. This allows portions of a transaction to be rolled back, instead of the entire transaction.


Example
// C#
//  Starts a transaction and inserts two records. Creates a savepoint
//  within the current transaction for the first insert. Then rolls back to 
//  the savepoint to commit the first record.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

OracleCommand cmd = Con.CreateCommand();

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

cmd.CommandText = "insert into mytable values (99, 'foo')";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

//Create a savepoint
txn.Save("MySavePoint");

cmd.CommandText = "insert into mytable values (100, 'bar')";
cmd.ExecuteNonQuery();

//Rollback to the savepoint
txn.Rollback("MySavePoint");

//Commit the first insert
txn.Commit();


OracleXmlQueryProperties Class

An OracleXmlQueryProperties object represents the XML properties used by the OracleCommand class when the XmlCommandType property is Query.


Class Inheritance

Object

  OracleXmlQueryProperties


Declaration
public sealed class OracleXmlQueryProperties : ICloneable

Thread Safety

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


Remarks

OracleXmlQueryProperties can be accessed, and modified using the XmlQueryProperties property of the OracleCommand class. Each OracleCommand object has its own instance of the OracleXmlQueryProperties class in the XmlQueryProperties property.

Use the default constructor to get a new instance of the OracleXmlQueryProperties. Use the OracleXmlQueryProperties.Clone() method to get a copy of an OracleXmlQueryProperties instance.


Example

This example retrieves relational data as XML.

// C#
 StreamReader sr = null;

// Create the connection.
string constr = "User Id=hr;Password=hr;Data Source=orcl";
OracleConnection conn = new OracleConnection(constr);
conn.Open();

// Create the command.
OracleCommand cmd = new OracleCommand("", conn);

// Set the XML command type to query.
cmd.XmlCommandType =  OracleXmlCommandType.Query;

// Set the SQL query.
cmd.CommandText = "select * from employees e where e.employee_id = :empno";

// Set command properties that affect XML query behaviour.
cmd.BindByName = true;
cmd.AddRowid = true;

// Bind values to the parameters in the SQL query.
Int32 empNum = 205;
cmd.Parameters.Add(":empno", OracleDbType.Int32, empNum, 
        ParameterDirection.Input);

// Set the XML query properties.
cmd.XmlQueryProperties.MaxRows =  -1;
cmd.XmlQueryProperties.RootTag =  "MYROWSET";
cmd.XmlQueryProperties.RowTag =  "MYROW";
cmd.XmlQueryProperties.Xslt =  null;
cmd.XmlQueryProperties.XsltParams =  null;

// Test query execution without returning a result.
int rows = cmd.ExecuteNonQuery();
Console.WriteLine("rows: " + rows);

// Get the XML document as an XmlReader.
XmlReader xmlReader =  cmd.ExecuteXmlReader();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.PreserveWhitespace = true;
xmlDocument.Load(xmlReader);
Console.WriteLine(xmlDocument.OuterXml);

// Change the SQL query, and set the maximum number of rows to 2.
cmd.CommandText = "select * from employees e";
cmd.Parameters.Clear();
cmd.XmlQueryProperties.MaxRows =  2;

// Get the XML document as a Stream.
Stream stream = cmd.ExecuteStream();
sr = new StreamReader(stream, Encoding.Unicode);
Console.WriteLine(sr.ReadToEnd());

// Get all the rows.
cmd.XmlQueryProperties.MaxRows =  -1;

// Append the XML document to an existing Stream.
MemoryStream mstream = new MemoryStream(32);
cmd.ExecuteToStream(mstream);
mstream.Seek(0, SeekOrigin.Begin);
sr = new StreamReader(mstream, Encoding.Unicode);
Console.WriteLine(sr.ReadToEnd());

// Clean up.
cmd.Dispose();
conn.Close();
conn.Dispose();


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleXmlQueryProperties Members

OracleXmlQueryProperties members are listed in the following tables:


OracleXmlQueryProperties Constructors

The OracleXmlQueryProperties constructors are listed in Table 4-118.

Table 4-118 OracleXmlQueryProperties Constructors

Constructor Description
OracleXmlQueryProperties Constructor Instantiates a new instance of the OracleXmlQueryProperties class


OracleXmlQueryProperties Properties

The OracleXmlQueryProperties properties are listed in Table 4-119.

Table 4-119 OracleXmlQueryProperties Properties

Name Description
MaxRows Specifies the maximum number of rows from the result set of the query that can be represented in the result XML document
RootTag Specifies the root element of the result XML document
RowTag Specifies the value of the XML element which identifies a row of data from the result set in an XML document
Xslt Specifies the XSL document used for XML transformation using XSLT
XsltParams Specifies parameters for the XSL document


OracleXmlQueryProperties Public Methods

The OracleXmlQueryProperties public methods are listed in Table 4-120.

Table 4-120 OracleXmlQueryProperties Public Methods

Name Description
Clone Creates a copy of an OracleXmlQueryProperties object

OracleXmlQueryProperties Constructor

The OracleXmlQueryProperties constructor instantiates a new instance of the OracleXmlQueryProperties class.


Declaration
// C#
public OracleXmlQueryProperties();

OracleXmlQueryProperties Properties

The OracleXmlQueryProperties properties are listed in Table 4-121.

Table 4-121 OracleXmlQueryProperties Properties

Name Description
MaxRows Specifies the maximum number of rows from the result set of the query that can be represented in the result XML document
RootTag Specifies the root element of the result XML document
RowTag Specifies the value of the XML element which identifies a row of data from the result set in an XML document
Xslt Specifies the XSL document used for XML transformation using XSLT
XsltParams Specifies parameters for the XSL document


MaxRows

This property specifies the maximum number of rows from the result set of the query that can be represented in the result XML document.


Declaration
// C#
public int MaxRows {get; set;}

Property Value

The maximum number of rows.


Exceptions

ArgumentException - The new value for MaxRows is not valid.


Remarks

Default value is -1.

Possible values are:


RootTag

This property specifies the root element of the result XML document.


Declaration
// C#
public string RootTag {get; set;}

Property Value

The root element of the result XML document.


Remarks

The default root tag is ROWSET.

To indicate that no root tag is be used in the result XML document, set this property to null or "" or String.Empty.

If both RootTag and RowTag are set to null, an XML document is returned only if the result set returns one row and one column.


RowTag

This property specifies the value of the XML element which identifies a row of data from the result set in an XML document.


Declaration
// C#
public string RowTag {get; set;}

Property Value

The value of the XML element.


Remarks

The default is ROW.

To indicate that no row tag is be used in the result XML document, set this property to null or "" or String.Empty.

If both RootTag and RowTag are set to null, an XML document is returned only if the result set returns one row and one column.


Xslt

This property specifies the XSL document used for XML transformation using XSLT.


Declaration
// C#
public string Xslt {get; set;}

Property Value

The XSL document used for XML transformation.


Remarks

Default value is null.

The XSL document is used for XML transformation of the XML document generated from the result set of the query.


XsltParams

This property specifies parameters for the XSL document.


Declaration
// C#
public string XsltParams {get; set;}

Property Value

The parameters for the XSL document.


Remarks

Default value is null.

The parameters are specified as a string of "name=value" pairs of the form "param1=value1; param2=value2; …" delimited by semicolons.

OracleXmlQueryProperties Public Methods

The OracleXmlQueryProperties public methods are listed in Table 4-122.

Table 4-122 OracleXmlQueryProperties Public Methods

Name Description
Clone Creates a copy of an OracleXmlQueryProperties object


Clone

This method creates a copy of an OracleXmlQueryProperties object.


Declaration
// C#
public object Clone();

Return Value

An OracleXmlQueryProperties object


Implements

ICloneable


OracleXmlSaveProperties Class

An OracleXmlSaveProperties object represents the XML properties used by the OracleCommand class when the XmlCommandType property is Insert, Update, or Delete.


Class Inheritance

Object

  OracleXmlSaveProperties


Declaration
public sealed class OracleXmlSaveProperties : ICloneable


Thread Safety

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


Remarks

OracleXmlSaveProperties can be accessed and modified using the XmlSaveProperties property of the OracleCommand class. Each OracleCommand object has its own instance of the OracleXmlSaveProperties class in the XmlSaveProperties property.

Use the default constructor to get a new instance of OracleXmlSaveProperties. Use the OracleXmlSaveProperties.Clone() method to get a copy of an OracleXmlSaveProperties instance.


Example

This sample demonstrates how to do inserts, updates, and deletes to a relational table or view using an XML document.

// C#
string[] KeyColumnsList = null;
string[] UpdateColumnsList = null;
int rows = 0;

// Create the connection.
string constr = "User Id=hr;Password=hr;Data Source=orcl";
OracleConnection conn = new OracleConnection(constr);
conn.Open();

// Create the command.
OracleCommand cmd = new OracleCommand("", conn);

// Set the XML command type to insert.
cmd.XmlCommandType = OracleXmlCommandType.Insert;

// Set the XML document.
cmd.CommandText = "<?xml version=\"1.0\"?>\n" +  
                  "<ROWSET>\n" +  
                  "  <MYROW num = \"1\">\n" +  
                  "    <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" +  
                  "    <LAST_NAME>Smith</LAST_NAME>\n" +  
                  "    <EMAIL>Smith@Oracle.com</EMAIL>\n" +  
                  "    <HIRE_DATE>1/1/2003 0:0:0</HIRE_DATE>\n" +  
                  "    <JOB_ID>IT_PROG</JOB_ID>\n" +  
                  "  </MYROW>\n" +  
                  "  <MYROW num = \"2\">\n" +  
                  "    <EMPLOYEE_ID>1235</EMPLOYEE_ID>\n" +  
                  "    <LAST_NAME>Barney</LAST_NAME>\n" +  
                  "    <EMAIL>Barney@Oracle.com</EMAIL>\n" +  
                  "    <HIRE_DATE>1/1/2003 0:0:0</HIRE_DATE>\n" +  
                  "    <JOB_ID>IT_PROG</JOB_ID>\n" +  
                  "  </MYROW>\n" +  
                  "</ROWSET>\n";

// Set the XML save properties.
KeyColumnsList = new string[1];
KeyColumnsList[0] = "EMPLOYEE_ID";

UpdateColumnsList = new string[5];
UpdateColumnsList[0] = "EMPLOYEE_ID";
UpdateColumnsList[1] = "LAST_NAME";
UpdateColumnsList[2] = "EMAIL";
UpdateColumnsList[3] = "HIRE_DATE";
UpdateColumnsList[4] = "JOB_ID";

cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList;
cmd.XmlSaveProperties.RowTag =  "MYROW";
cmd.XmlSaveProperties.Table =  "employees";
cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList;
cmd.XmlSaveProperties.Xslt =  null;
cmd.XmlSaveProperties.XsltParams =  null;

// Do the inserts.
rows = cmd.ExecuteNonQuery();
Console.WriteLine("rows: " + rows);

// Set the XML command type to update.
cmd.XmlCommandType =  OracleXmlCommandType.Update;

// Set the XML document.
cmd.CommandText = "<?xml version=\"1.0\"?>\n" +  
                  "<ROWSET>\n" +  
                  "  <MYROW num = \"1\">\n" +  
                  "    <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" +  
                  "    <LAST_NAME>Adams</LAST_NAME>\n" +  
                  "  </MYROW>\n" +  
                  "</ROWSET>\n";

// Set the XML save properties.
KeyColumnsList = new string[1];
KeyColumnsList[0] = "EMPLOYEE_ID";

UpdateColumnsList = new string[1];
UpdateColumnsList[0] = "LAST_NAME";

cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList;
cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList;

// Do the updates.
rows = cmd.ExecuteNonQuery();
Console.WriteLine("rows: " + rows);

// Set the XML command type to delete.
cmd.XmlCommandType =  OracleXmlCommandType.Delete;

// Set the XML document.
cmd.CommandText = "<?xml version=\"1.0\"?>\n" +  
                  "<ROWSET>\n" +  
                  "  <MYROW num = \"1\">\n" +  
                  "    <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" +  
                  "  </MYROW>\n" +  
                  "  <MYROW num = \"2\">\n" +  
                  "    <EMPLOYEE_ID>1235</EMPLOYEE_ID>\n" +  
                  "  </MYROW>\n" +  
                  "</ROWSET>\n";

// Set the XML save properties.
KeyColumnsList = new string[1];
KeyColumnsList[0] = "EMPLOYEE_ID";

cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList;
cmd.XmlSaveProperties.UpdateColumnsList = null;

// Do the deletes.
rows = cmd.ExecuteNonQuery();
Console.WriteLine("rows: " + rows);

// Clean up.
cmd.Dispose();
conn.Close();
conn.Dispose();


Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleXmlSaveProperties Members

OracleXmlSaveProperties members are listed in the following tables:


OracleXmlSaveProperties Constructor

OracleXmlSaveProperties constructors are listed in Table 4-123

Table 4-123 OracleXmlSaveProperties Constructor

Constructor Description
OracleXmlSaveProperties Constructor Instantiates a new instance of the OracleXmlSaveProperties class


OracleXmlSaveProperties Properties

The OracleXmlSaveProperties properties are listed in Table 4-124.

Table 4-124 OracleXmlSaveProperties Properties

Name Description
KeyColumnsList Specifies the list of columns used as a key to locate existing rows for update or delete using an XML document
RowTag Specifies the value for the XML element that identifies a row of data in an XML document
Table Specifies the name of the table or view to which changes are saved
UpdateColumnsList Specifies the list of columns to update or insert
Xslt Specifies the XSL document used for XML transformation using XSLT
XsltParams Specifies the parameters for the XSLT document specified in the Xslt property


OracleXmlSaveProperties Public Methods

The OracleXmlSaveProperties public methods are listed in Table 4-125.

Table 4-125 OracleXmlSaveProperties Public Methods

Name Description
Clone Creates a copy of an OracleXmlSaveProperties object

OracleXmlSaveProperties Constructor

The OracleXmlSaveProperties constructor instantiates a new instance of OracleXmlSaveProperties class.


Declaration
// C#
public OracleXmlSaveProperties;

OracleXmlSaveProperties Properties

The OracleXmlSaveProperties properties are listed in Table 4-126.

Table 4-126 OracleXmlSaveProperties Properties

Name Description
KeyColumnsList Specifies the list of columns used as a key to locate existing rows for update or delete using an XML document
RowTag Specifies the value for the XML element that identifies a row of data in an XML document
Table Specifies the name of the table or view to which changes are saved
UpdateColumnsList Specifies the list of columns to update or insert
Xslt Specifies the XSL document used for XML transformation using XSLT
XsltParams Specifies the parameters for the XSLT document specified in the Xslt property


KeyColumnsList

This property specifies the list of columns used as a key to locate existing rows for update or delete using an XML document.


Declaration
// C#
public string[] KeyColumnsList {get; set;}

Property Value

The list of columns.


Remarks

Default value is null.

The first null value (if any) terminates the list.

KeyColumnsList usage with XMLCommandType property values:


RowTag

This property specifies the value for the XML element that identifies a row of data in an XML document.


Declaration
// C#
public string RowTag {get; set;}

Property Value

An XML element name.


Remarks

The default value is ROW.

Each element in the XML document identifies one row in a table or view.

If RowTag is set to "" or null, no row tag is used in the XML document. In this case, the XML document is assumed to contain only one row.


Table

This property specifies the name of the table or view to which changes are saved.


Declaration
// C#
public string Table {get; set;}

Property Value

A table name.


Remarks

Default value is null.

The property must be set to a valid table or view name.


UpdateColumnsList

This property specifies the list of columns to update or insert.


Declaration
// C#
public string[] UpdateColumnsList {get; set;}

Property Value

A list of columns.


Remarks

Default value is null.

The first null value (if any) terminates the list.

UpdateColumnList usage with XMLCommandType property values:


Xslt

This property specifies the XSL document used for XML transformation using XSLT.


Declaration
// C#
public string Xslt {get; set;}

Property Value

The XSL document used for XML transformation.


Remarks

Default = null.

The XSL document is used for XSLT transformation of a given XML document. The transformed XML document is used to save changes to the table or view.


XsltParams

This property specifies the parameters for the XSLT document specified in the Xslt property.


Declaration
// C#
public string XsltParams {get; set;}

Property Value

The parameters for the XSLT document .


Remarks

Default is null.

This property is a string delimited by semicolons in "name=value" pairs of the form "param1=value1; param2=value2; …".