Skip Headers

Oracle® Data Provider for .NET Developer's Guide
Release 9.2.0.4

Part Number B10961-01
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

3
Features of Oracle Data Provider for .NET

This chapter describes Oracle Data Provider for .NET provider-specific features and how to use them to develop .NET applications.

This chapter contains these topics:

Connecting to the Oracle Database Server

This section describes OracleConnection provider-specific features, including:

Connection String Attributes

Table 3-1 lists the supported connection string attributes.

Table 3-1 Supported Connection String Attributes  
Connection String Attribute Default value Description

Connection Lifetime

0

Maximum life time (in seconds) of the connection

Connection Timeout

15

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

Data Source

empty string

Oracle Net Service Name that identifies the database to connect to

DBA Privilege

empty string

Administrative privileges: SYSDBA or SYSOPER

Decr Pool Size

1

Controls the number of connections that are closed when an excessive amount of established connections are unused

Enlist

true

Enables or disables serviced components to automatically enlist in distributed transactions

Incr Pool Size

5

Controls the number of connections that are established when all the connections in the pool are used

Max Pool Size

100

Maximum number of connections in a pool

Min Pool Size

1

Minimum number of connections in a pool

Password

empty string

Password for the user specified by User Id

Persist Security Info

false

Enables or disables the retrieval of password in the connection string

Pooling

true

Enables or disables connection pooling

Proxy User Id

empty string

User name of the proxy user

Proxy Password

empty string

Password of the proxy user

User Id

empty string

Oracle user name

The following example uses connection string attributes to connect to an Oracle database server:

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
con.Open();
...

See Also:

"ConnectionString" for detailed information on connection attributes

Connection Pooling

ODP.NET connection pooling is enabled and disabled using the Pooling connection string attribute. By default, connection pooling is enabled. The following are ConnectionString attributes that control the behavior of the connection pooling service:

Connection Pooling Example

The following code opens a connection using ConnectionString attributes related to connection pooling.

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;" +
         "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
         "Incr Pool Size=5; Decr Pool Size=2";
con.Open();
...

With connection pooling enabled (the default), the Open and Close methods of the OracleConnection object implicitly use the connection pooling service. In the preceding code, the Open call uses the connection pooling service, which is responsible for returning a connection to the application.

Connection pools are created by the connection pooling service using the ConnectionString as a signature to uniquely identify a pool.

If no pool with the exact attribute values in the ConnectionString exists, the connection pooling service creates a new connection pool. If a pool already exists with the requested signature, a connection is returned to the application from that pool.

When a connection pool is created, the connection-pooling service initially creates the number of connections defined by the Min Pool Size attribute of the ConnectionString. This number of connections is always maintained by the connection pooling service for the connection pool.

At any given time, these connections are available in the pool or used by the application.

The Incr Pool Size attribute of the ConnectionString defines the number of new connections to be created by the connection pooling service when more connections are needed in the connection pool.

When the application closes a connection, the connection pooling service determines whether the connection lifetime has exceeded the Connection Lifetime attribute; if so, the connection pooling service closes the connection; otherwise, the connection goes back to the connection pool. The connection pooling service only enforces the Connection Lifetime when a connection is going back to the connection pool.

The Max Pool Size attribute of the ConnectionString sets the maximum number of connections for a connection pool. If a new connection is requested, no connections are available, and Max Pool Size has been reached, then the connection pooling service waits for the time defined by Connection Timeout. If the Connection Timeout has been reached and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the pooled connection request has timed-out.

The connection pooling service closes connections when they are not used; connections are closed every three minutes. The Decr Pool Size attribute of the ConnectionString provides connection pooling service for the maximum number of connections that can be closed in one run.

Operating System Authentication

The Oracle database server can use Windows user login credentials to authenticate database users. To open a connection using Windows user login credentials, the User Id ConnectionString attribute must be set to /. If Password is provided, it is ignored.

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=/;Data Source=oracle;";
con.Open();
...

See Also:

Oracle9i Security and Network Integration Guide for information on how to set up Oracle to authenticate database users using Windows user login credentials

Privileged Connections

Oracle allows database administrators to connect to an Oracle database server with either SYSDBA or SYSOPER privileges. This is done through the DBA Privilege attribute of the ConnectionString.

The following example connects SYS/SYS as SYSDBA:

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=SYS;Password=SYS;" +
    "DBA Privilege=SYSDBA;Data Source=oracle;";
con.Open();
...

See Also:

"DBA Privilege" for further information on privileged connections in the database server

Password Expiration

Oracle allows users' password to expire. ODP.NET lets applications handle the password expiration by providing a new method, OpenWithNewPassword, that opens the connection with a new password.

The following code snippet uses the OracleConnection OpenWithNewPassword method to connect with a new password of panther:

// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
//  Here the con.Open() fails if the password has expired.
//  An application catches this and attempts to reconnect with a new password 
//  of "panther". The initial connection string must remain intact.

try {
con.Open();
}
catch {
con.OpenWithNewPassword("panther");
}
...


Note:

OpenWithNewPassword should only be used when the user password has expired, not for changing the password.


See Also:

"OpenWithNewPassword" for further information on connecting after the password expires

Proxy Authentication

Oracle allows a middle-tier server to connect to proxy clients in a secure fashion.

In multitier environments, proxy authentication allows control of middle-tier application security by preserving client identities and privileges through all tiers, and by auditing actions taken on behalf of clients. The proxy authentication feature allows the identity of a user using a Web application to be passed through the application to the database server.

ODP.NET supports proxy authentication with or without a client password by providing the Proxy User Id and Proxy Password attributes of the ConnectionString property.

// C#
...
OracleConnection con = new OracleConnection();
// Connecting using proxy authentication
con.ConnectionString = "User Id=customer;Password=lion;" + 
     "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; ";
con.Open();
...

See Also:

Transparent Application Failover (TAF) Callback Support

Transparent Application Failover (TAF) is a feature in Oracle that provides high availability.

TAF enables an application connection to automatically reconnect to a database if the connection fails. Active transactions roll back, but the new database connection, made by way of a different node, is identical to the original. This is true regardless of how the connection fails.

With Transparent Application Failover, a client notices no loss of connection as long as there is one instance left serving the application. The database administrator controls which applications run on which instances and also creates a failover order for each application.

Given the delays that failovers can cause, applications may wish to be notified by a TAF callback. ODP.NET supports TAF callback through the Failover event of the OracleConnection object, which allows applications to be notified whenever a failover occurs. To receive TAF callbacks, an event handler function must be registered with the Failover event.

When a failover occurs, the Failover event is raised and the registered event handler is invoked several times during the course of reestablishing the connection to another Oracle instance.

The first call to the event handler occurs when the Oracle Database first detects an instance connection loss. This allows the application to act accordingly for the upcoming delay for the failover.

If the failover is successful, the Failover event is raised again when the connection is reestablished and usable. At this time, the application can resynchronize the OracleGlobalization session setting and inform the application user that a failover has occurred.

If failover is unsuccessful, the Failover event is raised to inform the application that a failover did not take place.

The application can determine whether or not the failover is successful by checking the OracleFailoverEventArgs that is passed to the event handler.

The following code example registers an event handler method called OnFailover:


// C#
...
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
con.Open();
con.Failover += new OracleFailoverEventHandler(OnFailover);
...

The Failover event only invokes one event handler. If multiple Failover event handlers are registered with the Failover event, only the event handler registered last is invoked.


Note:

Distributed transactions are not supported in an environment where failover is enabled.


See Also:

ODP.NET Types Overview

ODP.NET types represent Oracle native types as a structure or as a class. ODP.NET type structures follow value semantics while ODP.NET type classes follow reference semantics. ODP.NET types provide safer and more efficient ways of obtaining Oracle native data in a .NET application than .NET types. For example, an OracleDecimal holds up to 38 precisions while a .NET Decimal holds only up to 28 precisions.

Table 3-2 lists all the Oracle native types supported by ODP.NET and their corresponding ODP.NET type. The third column lists the .NET Framework datatype that corresponds to the Value property of each ODP.NET Type.

Table 3-2 Oracle Native Types Supported by ODP.NET  
Oracle Native Type ODP.NET Type .NET Framework Datatypes

BFILE

OracleBFile class

System.Byte[]

BLOB

OracleBlob class

System.Byte[]

CHAR

OracleString structure

System.String

CLOB

OracleClob class

System.String

DATE

OracleDate structure

System.DateTime

INTERVAL DAY TO SECOND

OracleIntervalDS structure

System.TimeSpan

INTERVAL YEAR TO MONTH

OracleIntervalYM structure

System.Int64

LONG

OracleString structure

System.String

LONG RAW

OracleBinary structure

System.Byte[]

NCLOB

OracleClob class

System.String

NCHAR

OracleString structure

System.String

NUMBER

OracleDecimal structure

System.Decimal

NVARCHAR2

OracleString structure

System.String

RAW

OracleBinary structure

System.Byte[]

REF CURSOR

OracleRefCursor class

Not Applicable

TIMESTAMP

OracleTimeStamp structure

System.DateTime

TIMESTAMP WITH LOCAL TIME ZONE

OracleTimeStampLTZ structure

System.DateTime

TIMESTAMP WITH TIME ZONE

OracleTimeStampTZ structure

System.DateTime

UROWID

OracleString structure

System.String

VARCHAR2

OracleString structure

System.String

XMLType

OracleXmlType class

System.String

Obtaining Data From an OracleDataReader

The ExecuteReader method of the OracleCommand object returns an OracleDataReader object, which is a read-only, forward-only result set.

This section provides the following information about the OracleDataReader:

Typed OracleDataReader Accessors

The OracleDataReader provides two types of typed accessors:

.NET Type Accessors

Table 3-3 lists all the Oracle native database types that ODP.NET supports and the corresponding .NET Type that best represents the Oracle native type. The third column indicates the valid typed accessor that can be invoked for an Oracle native type to be obtained as a .NET type. If an invalid typed accessor is used for a column, an InvalidCastException is thrown. Oracle native datatypes depend on the version of the database; therefore, some datatypes are not available in earlier versions of Oracle.

See Also:

"OracleDataAdapter Class" for more information

Table 3-3 .NET Type Accessors  
Oracle Native Datatype .NET Type Typed Accessor

BFILE

System.Byte[ ]

GetBytes

BLOB

System.Byte[ ]

GetBytes

CHAR

System.String

GetString

GetChars

CLOB

System.String

GetString

GetChars

DATE

System.DateTime

GetDateTime

INTERVAL (DS)

System.Interval

GetTimeSpan

INTERVAL (YM)

System.Interval

GetTimeSpan

LONG

System.String

GetString

GetChars

LONG RAW

System.Byte[ ]

GetBytes

NCHAR

System.String

GetString

GetChars

NCLOB

System.String

GetString

GetChars

NUMBER

System.Decimal

GetDecimal

NVARCHAR2

System.String

GetString

GetChars

RAW

System.Byte[ ]

GetBytes

ROWID

System.String

GetString

GetChars

TIMESTAMP

System.TimeStamp

GetTimeStamp

TIMESTAMP WITH LOCAL TIME ZONE

System.TimeStamp

GetTimeStamp

TIMESTAMP WITH TIME ZONE

System.TimeStamp

GetTimeStamp

UROWID

System.String

GetString

GetChars

VARCHAR2

System.String

GetString

GetChars

XMLType

System.String

System.Xml.XmlReader

GetString

GetXmlReader

ODP.NET Type Accessors

ODP.NET exposes provider-specific types that natively represent the datatypes in Oracle. In some cases, these ODP.NET types provide better performance and functionality that is not available to the corresponding .NET types. The ODP.NET types can be obtained from the OracleDataReader by calling their respective typed accessor.

See Also:

"ODP.NET Types Overview" for a list of all ODP.NET types

Table 3-4 lists the valid type accessors that ODP.NET uses to obtain ODP.NET Types for an Oracle native type.

Table 3-4 ODP.NET Type Accessors  
Oracle Native Database Type ODP.NET Type Typed Accessor

BFILE

OracleBFile

GetOracleBFile

BLOB

OracleBlob

OracleBlob

OracleBinary

GetOracleBlob

GetOracleBlobForUpdate

GetOracleBinary

CHAR

OracleString

GetOracleString

CLOB

OracleClob

OracleClob

OracleString

GetOracleClob

GetOracleClobForUpdate

GetOracleString

DATE

OracleDate

GetOracleDate

INTERVAL (DS)

OracleIntervalDS

GetOracleIntervalDS

INTERVAL (YM)

OracleIntervalYM

GetOracleIntervalYM

LONG

OracleString

GetOracleString

LONG RAW

OracleBinary

GetOracleBinary

NCHAR

OracleString

GetOracleString

NCLOB

OracleString

GetOracleString

NUMBER

OracleDecimal

GetOracleDecimal

NVARCHAR2

OracleString

GetOracleString

RAW

OracleBinary

GetOracleBinary

ROWID

OracleString

GetOracleString

TIMESTAMP

OracleTimeStamp

GetOracleTimeStamp

TIMESTAMP WITH LOCAL TIME ZONE

OracleTimeStampLTZ

GetOracleTimeStampLTZ

TIMESTAMP WITH TIME ZONE

OracleTimeStampTZ

GetOracleTimeStampTZ

UROWID

OracleString

GetOracleString

VARCHAR2

OracleString

GetOracleString

XMLType

OracleString

GetOracleString

OracleXmlType

GetOracleXmlType

Obtaining LONG and LONG RAW Data

When an OracleDataReader is created containing a LONG or LONG RAW column type, OracleDataReader determines whether this column data needs to be fetched immediately or not, by checking the value of the InitialLONGFetchSize property of the OracleCommand that created the OracleDataReader.

By default, InitialLONGFetchSize is set to 0. If the InitialLONGFetchSize property value of the OracleCommand is left as 0, the entire LONG or LONG RAW data retrieval is deferred until that data is explicitly requested by the application. If the InitialLONGFetchSize property is set to a nonzero value, the LONG or LONG RAW data is immediately fetched up to the number of characters or bytes that the InitialLONGFetchSize property specifies.

ODP.NET does not support CommandBehavior.SequentialAccess. Therefore, LONG and LONG RAW data can be fetched in a random fashion.

To obtain data beyond InitialLONGFetchSize bytes or characters, one of the following must be in the select list:

The requested data is fetched from the database when the appropriate typed accessor method (GetOracleString or GetString for LONG or GetOracleBinary or GetBytes for LONG RAW) is called on the OracleDataReader object.

In order to fetch the data in a non-defer mode or when the columns in the select list do not have a primary key column, a ROWID, or unique columns, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the amount of bytes or characters needed to be retrieved.

Obtaining LOB Data

When an OracleDataReader is created containing LOB column types, OracleDataReader determines whether the LOB column data needs to be fetched immediately or not by checking the value of the InitialLONGFetchSize property of the OracleCommand that created the OracleDataReader. By default, InitialLOBFetchSize is set to 0. If the InitialLOBFetchSize property value of the OracleCommand is left as 0, the entire LOB data retrieval is deferred until that data is explicitly requested by the application. If the InitialLOBFetchSize property is set to a nonzero value, the LOB data is immediately fetched up to the number of characters or bytes that the InitialLOBFetchSize property specifies.

By default, when InitialLOBFetchSize property is 0, GetOracleBlob() and GetOracleClob() can be invoked on the OracleDataReader to obtain OracleBlob and OracleClob objects. However, if the InitialLOBFetchSize is set to a nonzero value, GetOracleBlob() and GetOracleClob() methods are disabled. In this scenario, the BLOB and CLOB data needs to be fetched by using GetBytes() and GetChars(), respectively.

Setting InitialLOBFetchSize to a nondefault value can improve performance in certain cases. Using InitialLOBFetchSize in conjunction with GetBytes() and GetChars() can provide better performance than retrieving the underlying LOB data using OracleBlob or OracleClob objects in the following situation: If an application does not need to obtain OracleBlob and OracleClob objects from the OracleDataReader and the size the LOB column data is not very large. InitialLOBFetchSize is particularly useful in cases where the size of the LOB column data returned by query is approximately the same for all the rows.

It is generally recommended that InitialLOBFetchSize be set to a value larger than the size of the LOB data for more than 80% of the rows returned by the query. For example, if the size of the LOB data is less than 1KB in 80% of the rows and more than 1MB for 20% of the rows, set InitialLOBFetchSize to 1KB.

ODP.NET does not support CommandBehavior.SequentialAccess. Therefore, LOB data can be fetched in a random fashion.

To obtain data beyond InitialLOBFetchSize bytes or characters, one of the following must be in the select list:

The requested data is fetched from the database when the appropriate typed accessor method is called on the OracleDataReader object. Note that the primary key column is not required if InitialLOBFetchSize is set to 0.

In order to fetch the data in a non-defer mode or when the columns in the select list do not have a primary key column, a ROWID, or unique columns, set the size of the InitialLOBFetchSize property on the OracleCommand object to an amount equal to or greater than the bytes or characters that need to be retrieved.

Controlling the Number of Rows Fetched in One Server Round-Trip

Application performance depends on the number of rows the application needs to fetch and the number of database round-trips that are needed to retrieve them.

Use of FetchSize

The FetchSize property represents the total memory size in bytes that ODP.NET allocates to cache the data fetched from a server round-trip.

The FetchSize property can be set either on the OracleCommand or the OracleDataReader depending on the situation. Additionally, the FetchSize property of the OracleCommand is inherited by the OracleDataReader and can be modified.

If the FetchSize property is set on the OracleCommand, then the newly created OracleDataReader inherits the FetchSize property of the OracleCommand. This inherited FetchSize can be left as is or modified to override the inherited value. The FetchSize property of the OracleDataReader object can be changed before the first Read method invocation, which allocates memory specified by the FetchSize. All subsequent fetches from the database use the same cache allocated for that OracleDataReader. Therefore, changing the FetchSize after the first Read method invocation has no effect.

Fine-Tuning FetchSize

By fine-tuning the FetchSize property, applications can control memory usage and the number of rows fetched in one server round-trip for better performance. For example, if a query returns 100 rows and each row takes 1024 bytes, then setting FetchSize to 102400 takes just one server round-trip to fetch the hundred rows. For the same query, if the FetchSize is set to 10240, it takes 10 server round-trips to retrieve 100 rows. If the application requires all the rows to be fetched from the result set, the first scenario is faster than the second. However, if the application requires just the first 10 rows from the result set, the second scenario can perform better since it only fetches 10 rows and not 100 rows.

Using the RowSize Property

The RowSize property of the OracleCommand object is populated with the row size (in bytes) after an execution of a SELECT statement. The FetchSize property can then be set to a value relative to the RowSize by setting it to the product of RowSize and the number of rows to fetch for each server round-trip.

For example, setting the FetchSize to RowSize * 10 forces the OracleDataReader to fetch exactly 10 rows for each server round-trip. Note that the RowSize does not change due to the data length in each individual columns. Instead, the RowSize is determined strictly from the metadata information of the database table(s) that the SELECT is executed against.

The RowSize property can be used to set the FetchSize at design time or at runtime as described in the following sections.

Setting FetchSize Value at Design Time

If the row size for a particular SELECT statement is already known from a previous execution, FetchSize of the OracleCommand can be set at design time to the product of that row size and the number of rows the application wishes to fetch for each server round-trip. The FetchSize value set on the OracleCommand object is inherited by the OracleDataReader that is created by the ExecuteReader method invocation on the OracleCommand. Rather than setting the FetchSize on the OracleCommand, the FetchSize can also be set on the OracleDataReader directly. In either case, the FetchSize is set at design time without accessing the RowSize property value at runtime.

Setting FetchSize Value at Runtime

Applications that do not know the row size at design time can use the RowSize property of the OracleCommand object to set the FetchSize property of the OracleDataReader object. The RowSize property provides a dynamic way of setting the FetchSize property based on the size of a row.

After an OracleDataReader object is obtained by invoking the ExecuteReader method on the OracleCommand, the RowSize property is populated with the size of the row (in bytes). By using the RowSize property, the application can dynamically set the FetchSize property of the OracleDataReader to the product of the RowSize property value and the number of rows the application wishes to fetch for each server round-trip. In this scenario, the FetchSize is set by accessing the RowSize property at runtime.

OracleCommand Object

The OracleCommand object represents SQL statements or stored procedures executed on the Oracle Database.

This section includes the following topics:

Transaction

The Oracle Database starts a transaction only in the context of a connection. Once a transaction starts, all the successive command execution on that connection run in the context of that transaction. Transactions can only be started on a OracleConnection object and the read-only Transaction property on the OracleCommand object is implicitly set by the OracleConnection object. Therefore, the application cannot set the Transaction property, nor does it need to.

Parameter Binding

ODP.NET allows applications to retrieve data as either a .NET Framework type or an ODP.NET type.

How the data is retrieved depends on whether application sets the OUT parameter to the DbType property (.NET type) or OracleDbType property (ODP.NET type) of the OracleParameter.

For example, if the output parameter is bound as an DbType.String, the output data is returned as a .NET String. On the other hand, if the parameter is bound as OracleDbType.Char, the output data is returned as OracleString type.

When the DbType of an OracleParameter is set, the OracleDbType of the OracleParameter changes accordingly, and vice versa. The parameter set last prevails.

Lastly, an application can simply bind the data and have ODP.NET infer both the DbType and OracleDbType from the .NET type of the parameter value.

ODP.NET populates InputOutput, Output, and ReturnValue parameters with the Oracle data, through the execution of the following OracleCommand methods:

An application should not bind a value for output parameters; it is the responsibility of ODP.NET to create the value object and populate the OracleParameter Value property with the object.

This section describes the following:

OracleDbType Enumeration Type

OracleDbType enumerated values are used to explicitly specify the OracleDbType of an OracleParameter.

Table 3-5 lists all the OracleDbType enumeration values with a description of each enumerated value.

Table 3-5 OracleDbType Enumeration Values  
Member Name Description

BFile

Oracle BFILE type

Blob

Oracle BLOB type

Byte

byte type

Char

Oracle CHAR type

Clob

Oracle CLOB type

Date

Oracle DATE type

Decimal

Oracle NUMBER type

Double

8-byte FLOAT type

Int16

2-byte INTEGER type

Int32

4-byte INTEGER type

Int64

8-byte INTEGER type

IntervalDS

Oracle INTERVAL DAY TO SECOND type

IntervalYM

Oracle INTERVAL YEAR TO MONTH type

Long

Oracle LONG type

LongRaw

Oracle LONG RAW type

NChar

Oracle NCHAR type

NClob

Oracle NCLOB type

NVarchar2

Oracle NVARCHAR2 type

Raw

Oracle RAW type

RefCursor

Oracle REF CURSOR type

Single

4-byte FLOAT type

TimeStamp

Oracle TIMESTAMP type

TimeStampLTZ

Oracle TIMESTAMP WITH LOCAL TIME ZONE type

TimeStampTZ

Oracle TIMESTAMP WITH TIME ZONE type

Varchar2

Oracle VARCHAR2 type

XmlType

Oracle XMLType type

Inference of DbType, OracleDbType, and .NET Types

This section explains the inference from the System.Data.DbType, OracleDbType, and Value properties in the OracleParameter class.

In the OracleParameter class, DbType, OracleDbType, and Value properties are linked. Specifying the value of any of these properties infers the value of one or more of the other properties.

Inference of DbType from OracleDbType

In the OracleParameter class, specifying the value of OracleDbType infers the value of DbType as shown in Table 3-6.

Table 3-6 Inference of System.Data.DbType from OracleDbType  
OracleDbType System.Data.DbType

BFile

Object

Blob

Object

Byte

Byte

Char

StringFixedLength

Clob

Object

Date

Date

Decimal

Decimal

Double

Double

Int16

Int16

Int32

Int32

Int64

Int64

IntervalDS

TimeSpan

IntervalYM

Int64

Long

String

LongRaw

Binary

NChar

StringFixedLength

NClob

Object

NVarchar2

String

Raw

Binary

RefCursor

Object

Single

Single

TimeStamp

DateTime

TimeStampLTZ

DateTime

TimeStampTZ

DateTime

Varchar2

String

XmlType

String

Inference of OracleDbType from DbType

In the OracleParameter class, specifying the value of DbType infers the value of OracleDbType as shown in Table 3-7.

Table 3-7 Inference of OracleDbType from DbType  
System.Data.DbType OracleDbType

Binary

Raw

Boolean

Not Supported

Byte

Byte

Currency

Not Supported

Date

Date

DateTime

TimeStamp

Decimal

Decimal

Double

Double

Guid

Not Supported

Int16

Int16

Int32

Int32

Int64

Int64

Object

Not Supported

Sbyte

Not Supported

Single

Single

String

Varchar2

StringFixedLength

Char

Time

TimeStamp

UInt16

Not Supported

UInt32

Not Supported

Uint64

Not Supported

VarNumeric

Not Supported

Inference of DbType and OracleDbType from Value

In the OracleParameter class, Value is an object type which can be of any .NET Framework datatype or ODP.NET type. If the OracleDbType and DbType in the OracleParameter object are not specified, OracleDbType is inferred from the type of the Value property.

Table 3-8 shows the inference of DbType and OracleDbType from Value when the type of Value is one of the .NET Framework datatypes.

Table 3-8 Inference of DbType and OracleDbType from Value (.NET Datatypes)  
Value (.NET Datatypes) System.Data.DbType OracleDbType

Byte

Byte

Byte

Byte[]

Binary

Raw

Char / Char []

String

Varchar2

DateTime

DateTime

TimeStamp

Decimal

Decimal

Decimal

Double

Double

Double

Float

Single

Single

Int16

Int16

Int16

Int32

Int32

Int32

Int64

Int64

Int64

Single

Single

Single

String

String

Varchar2

TimeSpan

TimeSpan

IntervalDS


Note:

Using other .NET Framework datatypes as values for OracleParameter without specifying either the DbType or the OracleDbType raises an exception because inferring DbType and OracleDbType from other .NET Framework datatypes is not supported.


Table 3-9 shows the inference of DbType and OracleDbType from Value when type of Value is one of Oracle.DataAccess.Types.

Table 3-9 Inference of DbType and OracleDbType from Value (ODP.NET Types)  
Value (Oracle.DataAccess.Types) System.Data.DbType OracleDbType

OracleBFile

Object

BFile

OracleBinary

Binary

Raw

OracleBlob

Object

Blob

OracleClob

Object

Clob

OracleDate

Date

Date

OracleDecimal

Decimal

Decimal

OracleIntervalDS

Object

IntervalDS

OracleIntervalYM

Int64

IntervalYM

OracleRefCursor

Object

RefCursor

OracleString

String

Varchar2

OracleTimeStamp

DateTime

TimeStamp

OracleTimeStampLTZ

DateTime

TimeStampLTZ

OracleTimeStampTZ

DateTime

TimeStampTZ

OracleXmlType

String

XmlType

PL/SQL Associative Array

ODP.NET supports PL/SQL Associative Array (formerly known as PL/SQL Index-By Tables) binding.

An application can bind an OracleParameter, as a PL/SQL Associative Array, to a PL/SQL stored procedure. The following OracleParameter properties are used for this feature.

Code Example

This example binds three OracleParameter objects as PL/SQL Associative Arrays: Param1 as an In parameter, Param2 as an InputOutput parameter, and Param3 as an Output parameter.

PL/SQL Package: My Pack

CREATE PACKAGE MYPACK AS
TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;
PROCEDURE TestVarchar2(
    Param1 IN     AssocArrayVarchar2_t,
    Param2 IN OUT AssocArrayVarchar2_t,
    Param3 OUT    AssocArrayVarchar2_t);
END MYPACK;    

PL/SQL Package Body: My Pack

CREATE PACKAGE BODY MYPACK AS
PROCEDURE TestVarchar2(
    Param1 IN     AssocArrayVarchar2_t,
    Param2 IN OUT AssocArrayVarchar2_t,
    Param3 OUT    AssocArrayVarchar2_t)
    IS
    i integer;
    BEGIN
      -- copy a few elements from y to z
      Param3(1) := Param2(1);
      Param3(2) := NULL;
      Param3(3) := Param2(3);

      -- copy all elements from x to y
      Param2(1) := Param1(1);
      Param2(2) := Param1(2);
      Param2(3) := Param1(3);
      
      FOR i IN 1..3 LOOP
        insert into T1 values(i, Param2(i));
      END LOOP;
           
      FOR i IN 1..3 LOOP
        select COL2 into Param2(i) from T2 where COL1 = i;
      END LOOP;
    END TestVarchar2;
END MYPACK;

ODP.NET Example

                    
public void BindAssocArray()
{
...

      OracleCommand cmd = new OracleCommand(
        "begin MyPack.TestVarchar2(:1, :2, :3); end;", con);

      OracleParameter Param1 = cmd.Parameters.Add(...);
      OracleParameter Param2 = cmd.Parameters.Add(...);
      OracleParameter Param3 = cmd.Parameters.Add(...);

      Param1.Direction = ParameterDirection.Input;
      Param2.Direction = ParameterDirection.InputOutput;
      Param3.Direction = ParameterDirection.Output;

      // Specify that we are binding PL/SQL Associative Array
      Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

      // Setup the values for PL/SQL Associative Array
      Param1.Value = new string[3]{"First Element",
                                   "Second Element ",
                                   "Third Element ",
      Param2.Value = new string[3]{"First Element",
                                   "Second Element ",
                                   "Third Element ",
      Param3.Value = null;

      // Specify the maximum number of elements in the PL/SQL Associative Array
      Param1.Size = 3;
      Param2.Size = 3;
      Param3.Size = 3;

      // Setup the ArrayBindSize for Param1
      Param1.ArrayBindSize = new int[3]{13, 14, 13};  

      // Setup the ArrayBindStatus for Param1 
      Param1.ArrayBindStatus = new OracleParameterStatus[3]{
        OracleParameterStatus.Success,
        OracleParameterStatus.Success,
        OracleParameterStatus.Success};

      // Setup the ArrayBindSize for Param2
      Param2.ArrayBindSize = new int[3]{20, 20, 20};

      // Setup the ArrayBindSize for Param3
      Param3.ArrayBindSize = new int[3]{20, 20, 20};

      // execute the cmd
      cmd.ExecuteNonQuery(); 

      //print out the parameter's values
      ......
    }

Array Binding

The array bind feature enables applications to bind arrays of a type using the OracleParameter class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.

The following code example inserts three rows into the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property defines the number of elements of the array to use when executing the statement.

// C# 
... 
// Create an array of values that need to be inserted 
int[] myArrayDeptNo = new int[3]{10, 20, 30}; 

// Set the command text on an OracleCommand object 
cmd.CommandText = "insert into dept(deptno) values (:deptno)"; 
// Set the ArrayBindCount to indicate the number of values 
cmd.ArrayBindCount = 3; 

// Create a parameter for the array operations 
OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32); 
prm.Direction = ParameterDirection.Input; 
prm.Value     = myArrayDeptNo; 

// Add the parameter to the parameter collection 
cmd.Parameters.Add(prm); 

// Execute the command 
cmd.ExecuteNonQuery(); 

See Also:

"Value" for more information

OracleParameter Array Bind Properties

The OracleParameter object provides two properties for granular control when using the array bind feature:

Error Handling for Array Binding

If an error occurs during an Array Bind execution, it can be difficult to determine which element in the Value property caused the error. ODP.NET provides a way to determine the row where the error occurred, making it easier to find the element in the row that caused the error.

When an OracleException is thrown during an Array Bind execution, the OracleErrorCollection contains one or more OracleError objects. Each of these OracleError objects represents an individual error that occurred during the execution and contains a provider-specific property, ArrayBindIndex, which indicates the row number at which the error occurred.

Code Snippet

try { 
  / An Array Bind execution errors out
}

catch (OracleException e)
{
  Console.WriteLine ("OracleException {0} occured", e.Message);
  for (int i = 0; i < e.Errors.Count; i++)
    Console.WriteLine("Array Bind Error {0} occured at Row Number {1}", 
e.Errors[i].Message,
      e.Errors[i].ArrayBindIndex);
}

See Also:

"ArrayBindIndex"

OracleParameterStatus Enumeration Types

Table 3-10 provides different values for OracleParameterStatus enumeration.

Table 3-10 OracleParameterStatus Members  
Member Names Description

Success

For input parameters, it indicates that the input value has been assigned to the column.

For output parameters, it indicates that the provider assigned an intact value to the parameter.

NullFetched

Indicates that a NULL value has been fetched from a column or an OUT parameter.

NullInsert

Indicates that a NULL value is to be inserted into a column.

Truncation

Indicates that truncation has occurred when fetching the data from the column.

PL/SQL REF CURSOR and OracleRefCursor

The REF CURSOR is a datatype in the Oracle PL/SQL language. It represents a cursor or a result set in the Oracle database server. The OracleRefCursor is a corresponding ODP.NET type for the REF CURSOR type.

This section discusses the following aspects of using REF CURSOR and OracleRefCursor objects:

Obtaining an OracleRefCursor

There are no constructors for OracleRefCursor objects. They can only be acquired as parameter values from PL/SQL stored procedures, stored functions, or anonymous blocks.

An OracleRefCursor is a connected object. The connection used to execute the command returning a OracleRefCursor object is required for its lifetime. Once the connection associated with an OracleRefCursor is closed, the OracleRefCursor cannot be used.

Obtaining a REF CURSOR

A REF CURSOR can be obtained as an OracleDataReader, DataSet, or OracleRefCursor. If the REF CURSOR is obtained as an OracleRefCursor object, it can be used to create an OracleDataReader or populate a DataSet from it. When accessing a REF CURSOR, always bind as a OracleDbType.RefCursor.

Populating an OracleDataReader from a REF CURSOR

An Oracle REF CURSOR can be obtained as an OracleDataReader by calling the OracleCommand ExecuteReader method. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor. None of the output parameters of type OracleDbType.RefCursor are populated after the ExecuteReader is invoked.

If there are multiple output REF CURSOR parameters, use the OracleDataReader NextResult method to access the next REF CURSOR. The OracleDataReader NextResult method provides sequential access to the REF CURSORs; only one REF CURSOR can be accessed at a given time.

The order in which OracleDataReader objects are created for the corresponding REF CURSOR depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR, then it becomes the first OracleDataReader and all the output REF CURSOR objects follow the order in which the parameters are bound.

Populating the DataSet From a REF CURSOR

For the Fill method to populate the DataSet properly, the SelectCommand of the OracleDataAdapter must be bound with an output parameter of type OracleDbType.RefCursor. If the Fill method is successful, the DataSet is populated with a DataTable that represents a REF CURSOR.

If the command execution returns multiple REF CURSORs, the DataSet is populated with multiple DataTables.

Populating an OracleRefCursor From a REF CURSOR

When ExecuteNonQuery is invoked on a command that returns one or more REF CURSORs, each of the OracleCommand parameters that are bound as OracleDbType.RefCursor gets a reference to an OracleRefCursor object.

To create an OracleDataReader from an OracleRefCursor object, invoke GetDataReader from an OracleRefCursor object. Subsequent calls to GetDataReader return the reference to the same OracleDataReader.

To populate a DataSet with an OracleRefCursor object, the application can invoke an OracleDataAdapter Fill method that takes an OracleRefCursor object.

When multiple REF CURSORs are returned from a command execution as OracleRefCursor objects, the application can choose to create an OracleDataReader or populate a DataSet with a particular OracleRefCursor object. All the OracleDataReaders or DataSet created from the OracleRefCursor are active at the same time and can be accessed in any order.

Updating a DataSet Obtained From a REF CURSOR

REF CURSORs are not updatable. However, data that is retrieved into a DataSet can be updated. Therefore, the OracleDataAdapter requires a custom SQL statement to flush any REF CURSOR data updates to the database.

The OracleCommandBuilder cannot be used to generate SQL for REF CURSOR updates.

Behavior of ExecuteScalar Method for REF CURSOR

ExecuteScalar returns the return value of a stored function or the first bind parameter of a stored procedure or an anonymous PL/SQL block. Therefore, if the REF CURSOR is not the return value of a stored function or the first bind parameter of a stored procedure or an anonymous PL/SQL block, the REF CURSOR is ignored by ExecuteScalar.

However, if the REF CURSOR is a return value of a stored function or the first bind parameter of a stored procedure or an anonymous PL/SQL block, the value of the first column of the first row in the REF CURSOR is returned.

See Also:

Oracle9i Application Developer's Guide - Large Objects (LOBs) for more information

LOB Support

ODP.NET provides an easy and optimal way to access and manipulate large datatypes. Oracle supports large character and large binary datatypes.

Large Character Datatypes
Large Binary Datatypes

ODP.NET provides three objects for LOBs for manipulating LOB data: OracleBFile, OracleBlob, and OracleClob.

Table 3-11 shows the proper ODP.NET class to use for a particular Oracle LOB type.

Table 3-11 ODP.NET LOB Objects
Oracle LOB Type ODP.NET LOB object

BFILE

OracleBFile object

BLOB

OracleBlob object

CLOB

OracleClob object

NCLOB

OracleClob object

The ODP.NET LOB objects can be obtained by calling the proper typed accessor on the OracleDataReader or as an output parameter on a command execution with the proper bind type.

All ODP.NET LOB objects inherit from the .NET Stream class to provide generic Stream operations. The LOB data (except for BFILEs) can be updated using the ODP.NET LOB objects by using methods such as Write. Data is not cached in the LOB objects when read and write operations are carried out. Therefore, each Read or Write request incurs a server round-trip. The OracleClob overloads the Read method, providing two ways to read data from a CLOB. The Read method that takes a byte[] as the buffer populates it with CLOB data as Unicode byte array. The Read method that takes a char[] as the buffer populates it with Unicode characters.

Extensions can also be found on the OracleBFile object. An OracleBFile object must be explicitly opened using the OpenFile method before any data can be read from it. To close a previously opened BFILE, use the CloseFile method.

Every ODP.NET LOB object is a connected object and requires a connection during its lifetime. If the connection associated with a LOB object is closed, then the LOB object is not usable and should be disposed.

If an ODP.NET LOB object is obtained from an OracleDataReader through a typed accessor, then its Connection property is set with a reference to the same OracleConnection object used by the OracleDataReader. If a LOB object is obtained as an output parameter, then its Connection property is set with a reference to the same OracleConnection property used by the OracleCommand. If a LOB object is obtained by invoking an ODP.NET LOB object constructor to create a temporary LOB, the Connection property is set with a reference to the OracleConnection object provided in the constructor.

The ODP.NET LOB object Connection property is read-only and cannot be changed during its lifetime. In addition, the ODP.NET LOB types object can only be used within the context of the same OracleConnection referenced by the ODP.NET LOB object. For example, the ODP.NET LOB object's Connection must reference the same connection as the OracleCommand if the ODP.NET LOB object is a parameter of the OracleCommand. If that is not the case, ODP.NET raises an exception when the command is executed.

See Also:

Oracle9i Application Developer's Guide - Large Objects (LOBs) for complete information about Oracle9i LOBs and how to use them

Updating LOBs Using a DataSet

BFILE and BLOB data are stored in the DataSet as byte arrays while CLOB and NCLOB data are stored as strings. In a similar manner to other types, an OracleDataAdapter object can be used to fill and update LOB data changes along with the use of the OracleCommandBuilder for auto-generating SQL.

Note that an Oracle LOB column can store up to 4 GB of data. When the LOB data is fetched into the DataSet, the actual amount of LOB data the DataSet can hold for a LOB column is limited to the maximum size of a .NET string type, which is 2 GB. Therefore, when fetching LOB data that is greater than 2 GB, ODP.NET LOB objects must be used to avoid any data loss.

Updating LOBs Using OracleCommand and OracleParameter

To update LOB columns, LOB data can be bound as a parameter for SQL statements, anonymous PL/SQL blocks, or stored procedures. The parameter value can be set as a NET Framework type, ODP.NET type, or as an ODP.NET LOB object type. For example, when inserting a .NET string data into a LOB column in a Oracle9i database, that parameter can be bound as OracleDbType.Varchar2. For a parameter whose value is set to an OracleClob object, the parameter should be bound as OracleDbType.Clob.

Updating LOBs Using ODP.NET LOB Objects

Oracle BFILEs are not updatable and hence OracleBFile objects do not allow updates to BFILE columns.

Two requirements must be met to update LOB data using ODP.NET LOB objects.

  1. A transaction must be started before a LOB column is selected.

    The transaction must be started using the BeginTransaction method on the OracleCommand before the command execution so that the lock can be released when OracleTransaction Commit or Rollback is invoked.

  2. The row in which the LOB column resides must be locked; on a row by row basis or as part of an entire result set.

    1. Locking the entire result

      Add the FOR UPDATE clause to the end of the SELECT statement. After execution of the command, the entire result set is locked.

    2. Locking the row - There are two options:

      • Invoke one of OracleDataReader's typed accessors (GetOracleClobForUpdate or GetOracleBlobForUpdate) on the OracleDataReader to obtain an ODP.NET LOB object while also locking the current row.

        This approach requires a primary key, unique column(s), or a ROWID in the result set because the OracleDataReader must uniquely identify the row to re-select it for locking.

      • Execute an INSERT or an UPDATE statement that returns a LOB in the RETURNING clause.

Temporary LOBs

Temporary LOBs can be instantiated for BLOBs, CLOBs, and NCLOBs. To instantiate an ODP.NET LOB object that represents a temporary LOB, the OracleClob or the OracleBlob constructor can be used.

Temporary ODP.NET LOB objects can be used for the following purposes:

Globalization Support

ODP.NET globalization support enables applications to manipulate culture-sensitive data appropriately. This feature ensures proper string format, date, time, monetary, numeric, sort order, and calendar conventions depending on the Oracle globalization settings.

See Also:

"OracleGlobalization Class"

This section includes the following:

Globalization Settings

An OracleGlobalization object can be used to represent the following:

Client Globalization Settings

Client globalization settings are derived from the Oracle globalization setting (NLS_LANG) in the Windows registry of the local computer. The client globalization parameter settings are read-only and remain constant throughout the lifetime of the application. The client globalization settings can be obtained by calling the OracleGlobalization.GetClientInfo() static method.

The following example retrieves the client globalization setting:

// C#
...
// GetClientInfo() is a static method on OracleGlobalization class
OracleGlobalization ClientGlob = OracleGlobalization.GetClientInfo();

The properties of the OracleGlobalization object provide the Oracle globalization value settings.

Session Globalization Settings

Session globalization parameters are initially identical to client globalization settings. Unlike client settings, session globalization settings can be updated. However, they can only be obtained after establishing a connection against the database server. The session globalization settings can be obtained by calling GetSessionInfo() on the OracleConnection. Invoking this method returns an instance of an OracleGlobalization object whose properties represent the globalization settings of the session.

When the OracleConnection object establishes a connection, it implicitly opens a session whose globalization parameters are initialized with those values specified by the client computer's Oracle globalization (or National Language Setting (NLS)) registry settings. The session settings are updatable and can change during its lifetime.

The following example changes the date format setting on the session:

// C#
...
OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;");
con.Open();
OracleGlobalization SessionGlob = con.GetSessionInfo();

// SetSessionInfo updates the Session with the new value
SessionGlob.DateFormat = "YYYY/MM/DD"; 
con.SetSessionInfo(SessionGlob);       
...

Thread-Based Globalization Settings

Thread-based globalization parameter settings are specific to each thread. Initially, these settings are identical to the client globalization parameters, but they can be changed as specified by the application. When ODP.NET Types are converted to and from strings, the thread-based globalization parameters are used, if applicable.

Thread-based globalization parameter settings are obtained by invoking the GetThreadInfo static method of the OracleGlobalization object. The SetThreadInfo static method of the OracleGlobalization object can be called to set the thread's globalization settings.

ODP.NET classes and structures rely solely on the OracleGlobalization settings when manipulating culture-sensitive data. They do not use .NET thread culture information. If the application uses only .NET types, OracleGlobalization settings have no effect. However, when conversions are made between ODP.NET types and .NET types, OracleGlobalization settings are used where applicable.


Note:

Changes to System.Threading.Thread. CurrentThread.CurrentCulture do not impact the settings of the OracleGlobalization settings of the thread or the session and vice versa.


The following code snippet shows how the thread's globalization settings are used by the ODP.NET Types:

...
OracleGlobalization ThreadGlob = OracleGlobalization.GetThreadInfo();
// set and validate the format
ThreadGlob.DateFormat = "YYYY-MM-DD";

// set the thread with the new format
OracleGlobalization.SetThreadInfo(ThreadGlob); 

// create a new instance of OracleDate
OracleDate date = new OracleDate("2002-01-01"); 
...

The OracleGlobalization object validates property changes made to it. If an invalid value is used to set a property, an exception is thrown. Note that changes made to the Territory and Language properties change other properties of the OracleGlobalization object implicitly.

See Also:

Oracle9i Database Globalization Support Guide for more information on the properties affected by Territory and Language Globalization settings

Globalization-Sensitive Operations

This section lists ODP.NET types and operations that are dependent on or sensitive to globalization settings.

Operations Dependent on Client Computer's Globalization Settings

The OracleString structure depends on the client computer's OracleGlobalization settings. The local computer's client character set is used when it converts a Unicode string to a byte[] in the GetNonUnicode method and when it converts a byte[] of ANSI characters to Unicode in the OracleString constructor which accepts a byte[].

Operations Dependent on Thread Globalization Settings

The thread globalization settings are used by ODP.NET types whenever they are converted to and from .NET string types, where applicable. In most cases, the ToString method, the Parse static method, constructors that accept .NET string data, and conversion operators to and from .NET strings use specific thread globalization settings depending on the ODP.NET type used.

For example, the OracleDate type uses the DateFormat property of the thread globalization settings when the ToString method is invoked on it. This returns a DATE as a string in the format specified by the thread's settings.

For more details, read the remarks in Chapter 5 for the ODP.NET type methods that convert between ODP.NET types and .NET string types, to identify which thread globalization settings are used for that particular method.

See Also:

Chapter 5, "Oracle.DataAccess.Types Namespace (ODP.NET Types)"

Operations Sensitive to Session Globalization Parameters

Session globalization settings affect any data that is retrieved from or sent to the server as a string.

For example, if a DATE column is selected with the TO_CHAR() function applied on it, the DATE column data will be a string in the date format specified by the DateFormat of the session globalization settings. Transmitting data in the other direction, the string data that is to be inserted into the DATE column, must be in the format specified by the DateFormat property of the session globalization settings.

The session globalization settings also affect data that is retrieved into the DataSet as a string using Safe Type Mapping. If the type is format-sensitive, the strings are always in the format specified by the session globalization settings.

For example, VARCHAR2 and CHAR data are not affected by session settings since no format is applicable for these types. However, the DateFormat and NumericCharacters properties can impact the string representation of DATE and NUMBER types, respectively, when they are retrieved as strings from the database server through safe type mapping.

See Also:

"OracleDataAdapter Safe Type Mapping"

Guaranteeing Uniqueness in Updating DataSet to Database

This section describes how the OracleDataAdapter configures the PrimaryKey and Constraints properties of the DataTable which guarantee uniqueness when the OracleCommandBuilder is updating DataSet changes to the database.

Using the OracleCommandBuilder object to dynamically generate DML statements to be executed against the database is one of the ways to reconcile changes made in a single DataTable with the database.

In this process, the OracleCommandBuilder must not be allowed to generate DML statements that may affect (update or delete) more that a single row in the database when reconciling a single DataRow change. Otherwise the OracleCommandBuilder could corrupt data in the database.

To guarantee that each DataRow change affects only a single row, there must be a set of DataColumns in the DataTable for which all rows in the DataTable have a unique set of values. The set of DataColumns indicated by the properties DataTable.PrimaryKey and DataTable.Constraints meet this requirement. The OracleCommandBuilder determines uniqueness in the DataTable by checking whether the DataTable.PrimaryKey is non-null or if there exists a UniqueConstraint in the DataTable.Constraints collection.

This discussion first explains what constitutes uniqueness in DataRows and then explains how to maintain that uniqueness while updating, through DataTable property configuration.

This section includes the following topics:

What Constitutes Uniqueness in DataRows?

This section describes the minimal conditions that must be met to guarantee uniqueness of DataRows. The condition of uniqueness must be guaranteed before the DataTable.PrimaryKey and DataTable.Constraints properties can be configured, as described in the next section.

Uniqueness is guaranteed in a DataTable if any one of the following is true:

Configuring PrimaryKey and Constraints Properties

If the minimal conditions described in "What Constitutes Uniqueness in DataRows?" are met, then the DataTable.PrimaryKey or DataTable.Constraints properties can be set.

After these properties are set, the OracleCommandBuilder can determine uniqueness in the DataTable by checking the DataTable.PrimaryKey property or the presence of a UniqueConstraint in the DataTable.Constraints collection. Once uniqueness is determined, OracleCommandBuilder can safely generate DML statements to perform updates.

The OracleDataAdapter.FillSchema method attempts to set these properties according to this order of priority:

  1. If the primary key is returned in the select list, it is set as the DataTable.PrimaryKey.

  2. If a set of columns that meets the following criteria is returned in the select list, it is set as the DataTable.PrimaryKey.

    Criteria: The set of columns has a unique constraint defined on it or a unique index created on it, with each column having a NOT NULL constraint defined on it.

  3. If a set of columns that meets the following criteria is returned in the select list, a UniqueConstraint is added to the DataTable.Constraints collection, but the DataTable.PrimaryKey is not set.

    Criteria: The set of columns has a unique constraint defined on it or a unique index created on it, with at least one column having a NOT NULL constraint defined on it.

  4. If a ROWID is part of the select list, it is set as the DataTable.PrimaryKey.

Additionally, OracleDataAdapter.FillSchema exhibits the following behaviors:

Updating Without PrimaryKey and Constraints Configuration

If the DataTable.PrimaryKey or Constraints properties have not been configured, for example, if the application has not called OracleDataAdapter.FillSchema, the OracleCommandBuilder directly checks the select list of the OracleDataAdapter.SelectCommand to determine if it guarantees uniqueness in the DataTable. However this check results in a server round-trip to retrieve the metadata for the SELECT statement of the OracleDataAdapter.SelectCommand.

Note that OracleCommandBuilder cannot update a DataTable created from PL/SQL statements because they do not return any key information in their metadata.

OracleDataAdapter Safe Type Mapping

The ODP.NET OracleDataAdapter provides the Safe Type Mapping feature because the following Oracle datatypes can potentially lose data when converted to their closely related .NET type:

When populating Oracle data containing any of these types into a .NET DataSet there is a possibility of data loss. The OracleDataAdapter Safe Type Mapping feature prevents data loss. By setting the SafeMapping property appropriately, these types can be safely represented in the DataSet, as either of the following:

Potential Data Loss

The following sections provide more detail about the types and circumstances where data can be lost.

Oracle NUMBER Type to .NET Decimal Type

The Oracle datatype NUMBER can hold up to 38 precisions whereas .NET Decimal type can hold up to 28 precisions. If a NUMBER datatype that has more than 28 precisions is retrieved into .NET decimal type, it loses precision.

Table 3-12 lists the maximums and minimums for Oracle NUMBER and .NET Decimal.

Table 3-12 Oracle NUMBER to .NET Decimal Comparisons  
Oracle NUMBER .NET Decimal

Maximum

9.9999999999999999999999999999999999999 e125

79,228,162,514,264,337,593,543,950,335

Minimum

-9.9999999999999999999999999999999999999 e125

-79,228,162,514,264,337,593,543,950,335

Oracle Date Type to .NET DateTime Type

The Oracle datatype DATE can represent dates in BC whereas .NET DateTime cannot. If a DATE that goes to BC get retrieved into .NET DateTime type, it loses data.

Table 3-13 lists the maximums and minimums for Oracle Date and .NET DateTime.

Table 3-13 Oracle Date to .NET DateTime Comparisons  
Oracle Date .NET DateTime

Maximum

Dec 31, 9999 AD

Dec 31, 9999 AD 23:59:59.9999999

Minimum

Jan 1, 4712 BC

Jan 1, 0001 AD 00:00:00.0000000

Oracle TimeStamp Type to .NET DateTime Type

Similarly to DATE, Oracle datatype TimeStamp can represent a date in BC whereas .NET DateTime type cannot. If a TimeStamp that goes to BC is retrieved into .NET DateTime type, it loses data. Oracle TimeStamp type can represent values in units of e-9, whereas the .NET DateTime type can only represent values in units of e-7. The Oracle TimeStamp with time zone datatype can store time zone information whereas .NET DateTime cannot.

Table 3-14 lists the maximums and minimums for Oracle TimeStamp and .NET DateTime.

Table 3-14 Oracle TimeStamp to .NET DateTime Comparisons  
Oracle TimeStamp .NET DateTime

Maximum

Dec 31, 9999 AD 23:59:59.999999999

Dec 31, 9999 AD 23:59:59.9999999

Minimum

Jan 1, 4712 BC 00:00:00.000000000

Jan 1, 0001 AD 00:00:00.0000000

Oracle INTERVAL DAY TO SECOND to .NET TimeSpan

Similarly to DATE, the Oracle datatype INTERVAL DAY TO SECOND can represent dates in BC, whereas the .NET TimeSpan type cannot. If an INTERVAL DAY TO SECOND that goes to BC is retrieved into .NET TimeSpan type, it loses the data. The Oracle INTERVAL DAY TO SECOND type can represent values in units of e-9 whereas .NET TimeSpan type can only represent values in units of e-7.

Table 3-15 lists the maximums and minimums for Oracle INTERVAL DAY TO SECOND and .NET DateTime.

Table 3-15 Oracle INTERVAL DAY TO SECOND to .NET TimeSpan Comparisons  
Oracle INTERVAL DAY TO SECOND .NET TmeSpan

Maximum

+999999999 23:59:59.999999999

+10675199 02:48:05.4775807

Minimum

-999999999 23:59:59.999999999

-10675199 02:48:05.4775808

SafeMapping Property

By default, Safe Type Mapping is disabled.

Using Safe Type Mapping

To use the Safe Type Mapping functionality, the OracleDataAdapter.SafeMapping property must be set with a hashtable of key-value pairs. The key-value pairs must map database table column names (of type string) to a .NET type (of type Type). ODP.NET supports safe type mapping to byte[] and String types. Any other type mapping causes an exception.

In situations where the column names are not known at design time, an asterisk ("*") can be used to map all occurrences of database types to a safe .NET type where it is needed. If both the valid column name and the asterisk are present, the column name is used.


Note:

  • Database table column names are case sensitive.

  • Column names in the hashtable that correspond to invalid column names are ignored.


Mapping to a .NET String

The safe type mapping as a string is more readable without further conversion. Converting certain Oracle datatypes to a string requires extra conversion, which can be slower than converting it to a byte[]. Conversion of .NET strings back to ODP.NET types relies on the formatting information of the session.

OracleDataAdapter Requery Property

The OracleDataAdapter Requery property controls whether queries are reexecuted for OracleDataAdapter Fill calls after the initial Fill call.

The OracleDataAdapter Fill method allows appending or refreshing data in the DataSet. When appending the DataSet using the same query with subsequent Fill calls, it may be desired not to reexecute the query.

When the Requery property is set to true, each subsequent Fill call reexecutes the query and fills the DataSet. It is an expensive operation and if the reexecution is not required, set Requery to false. If any of the SelectCommand properties or associated parameters needs to be changed, Requery must be true.

When the Requery property is set to false, the DataSet has the entire data as a snapshot at a particular time. The query is executed only for the first Fill call, subsequent Fill calls fetch the data from a cursor opened with the first execution of the query. This feature is only supported for forward-only fetches. Fill calls that try to fetch rows before the last fetched row raise an exception. The connection used for the first Fill call must be available for subsequent Fill calls.

When filling a DataSet with a OracleRefCursor, the Requery property can be used in a similar manner. When the Requery property is set to false, both the connection used for the first Fill and the OracleRefCursor must be available for the subsequent Fill calls.

See Also:

Debug Tracing

ODP.NET provides debug tracing support, which allows logging of all the ODP.NET activities into a trace file. Different levels of tracing are available.

The provider can record the following information:

Registry Settings for Tracing Calls

The following registry settings should be configured under

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET

TraceFileName

The valid values for TraceFileName are: any valid path and filename

TraceFileName specifies the filename that is to be used for logging trace information. If TraceOption is set to 0, the name is used as is. However, if TraceOption is 1, the Thread ID is appended to the filename provided.

See Also:

"TraceOption"

TraceLevel

The valid values for TraceLevel are:

TraceLevel specifies the level of tracing in ODP.NET. Because tracing all the entry and exit calls for all the objects can be excessive, TraceLevel is provided to limit tracing to certain areas of the provider.

To obtain tracing on multiple objects, simply add the valid values. For example, if TraceLevel is set to 3, trace information is logged for Entry, Exit, SQL, and Connection pooling information.

TraceOption

The valid values for TraceOption are:

TraceOption specifies whether to log trace information in single or multiple files for each Thread ID. If a single trace file is specified, the filename specified in TraceFileName is used. If the multiple trace files option is requested, a Thread ID is appended to the filename provided to create a trace file for each thread.

ODP.NET XML Support

From Oracle8i Release 3 (8.1.7) and on, Oracle allows the extraction of data from relational and object-relational tables and views as XML documents. The use of XML documents for insert, update, and delete operations to the database server is also allowed.

With Oracle9i Release 2 (9.2), Oracle supports XML natively in the database, through Oracle XML Database (Oracle XML DB), a distinct group of technologies related to high-performance XML storage and retrieval. Oracle XML DB is an evolution of Oracle that encompasses both SQL and XML data models in a highly interoperable manner, providing native XML support.


Note:

For database releases 8.1.7 and 9.0.1 only, certain OracleCommand methods require Oracle9i XML Developer's Kits (Oracle XDK) or higher, to be installed in the database. The XDK can be downloaded from Oracle Technology Network (OTN).


For samples related to ODP.NET XML support, see the following directory:

ORACLE_BASE\ORACLE_HOME\ODP.NET\Samples

This section includes these topics:

Supported XML Features

XML support in ODP.NET provides the following features:

For the .NET application developer, these features include the following:

OracleXmlType and Connection Dependency

The read-only Connection property of the OracleXmlType object holds a reference to the OracleConnection object used to instantiate the OracleXmlType object.

How the OracleXmlType object obtains a reference to an OracleConnection object depends on how the OracleXmlType object is instantiated:

An OracleXmlType object that is associated with one connection cannot be used with a different connection. For example, if an OracleXmlType object is obtained using OracleConnection A, that OracleXmlType object cannot be used as an input parameter of a command that uses OracleConnection B. By checking the Connection property of the OracleXmlType objects, the application can ensure that OracleXmlType objects are used only within the context of the OracleConnection referenced by its connection property. Otherwise, ODP.NET raises an exception.

Updating XMLType Data in the Database Server

Updating XMLType columns does not require a transaction. However, encapsulating the entire database update process within a transaction is highly recommended. This allows the updates to be rolled back if there are any errors.

XMLType columns in the database can be updated using the Oracle Data Provider for .NET in several ways:

Updating with DataSet, OracleDataAdapter, and OracleCommandBuilder

If the XMLType column is fetched into the DataSet, the XMLType data is represented as a .NET String.

Modifying XMLType data in the DataSet does not require special treatment. XMLType data can be modified in the same way as any data that is stored in the DataSet. When a change is made and OracleDataAdapter.Update() is invoked, the OracleDataAdapter ensures that the XMLType data is handled properly. OracleDataAdapter uses any custom SQL INSERT, UPDATE, or DELETE statements that are provided. Otherwise, valid SQL statements are generated by the OracleCommandBuilder as needed to flush the changes to the database server.

Updating with OracleCommand and OracleParameter

OracleCommand provides a powerful way of updating XMLType data, especially with the use of OracleParameter. To update columns in a database table, the new value for the column can be passed as an input parameter of a command.

Input Binding

To update an XMLType column in the database, a SQL statement can be executed using static values. In addition, input parameters can be bound to SQL statements, anonymous PL/SQL blocks, or stored procedures to update XMLType columns. The parameter value can be set as .NET Framework Types, ODP.NET Types, or OracleXmlType objects.

While XMLType columns can be updated using the OracleXmlType object, having an instance of an OracleXmlType object does not guarantee that the XMLType column in the database can be updated.

Setting XMLType Column to NULL Value

Applications can set an XMLType column in the database to a NULL value, with or without input binding, as follows:

Setting XMLType Column to Empty XML Data

Updating XML Data in OracleXmlType

There are several ways that XML data can be updated in an OracleXmlType object.

Special Characters in XML

The following characters have special meaning in XML. For more information, refer to the XML 1.0 specifications.

Table 3-16 Special Characters
Special Character Meaning in XML Entity Encoding

<

Begins an XML tag

&lt;

>

Ends an XML tag

&gt;

"

Quotation mark

&quot;

'

Apostrophe or single quotation mark

&apos;

&

Ampersand

&amp;

When these characters appear as data in an XML element, they are replaced with their equivalent entity encoding.

Also certain characters are not valid in XML element names. When SQL identifiers (such as column names) are mapped to XML element names, these characters are converted to a sequence of hexadecimal digits, derived from the Unicode encoding of the character, bracketed by an introductory underscore, a lowercase x and a trailing underscore. For example, the space is not a valid character in an XML element name. If a SQL identifier contains a space character, then in the corresponding XML element name, the space character is replaced by _x0020_, which is based on Unicode encoding of the space character.

Retrieving Query Result Set as XML

This section discusses retrieving the result set from a SQL query as XML data.

Handling Date and Time Format

Table 3-17 lists the date and time format handling for different database releases.

Table 3-17 Database Release Date and Time Differences When Retrieving Data
Database Release Date and Time Format Supported

Oracle8i release 3 (8.1.7) and Oracle9i release 1 (9.0.x)

Oracle DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE type data is always retrieved in the result XML document as the ISO Date and Time Format: YYYY-MM-DDThh:mm:ss.sss (ISO Format notation).

The following string is the ISO Date and Time Format notation represented in the Oracle Date and Time Format notation: YYYY-MM-DD "T"HH:MI:SS.FF3.

Oracle9i release 2 (9.2.x)

Oracle DATE type data is retrieved in the format specified using the NLS_DATE_FORMAT in the session.

TIMESTAMP and TIMESTAMP WITH TIME ZONE type data is retrieved in the format specified using the NLS_TIMESTAMP_FORMAT and the NLS_TIMESTAMP_TZ_FORMAT in the session.

If the result XML document is used to save changes to database releases 8.1.7, 9.0.x, or 9.2.x, then all DATE and TIMESTAMP data must be retrieved in the XML document as the following ISO Date and Time Format: YYYY-MM-DDThh:mm:ss.sss (ISO Format notation).

To do this, before the query is executed, the application must explicitly perform an ALTER SESSION command on the session for the following NLS session parameters:

  • NLS_DATE_FORMAT - Must be set to the following Oracle Date and Time Format: YYYY-MM-DD"T"HH:MI:SS

  • NLS_TIMESTAMP_FORMAT - Must be set to the following Oracle Date and Time Format: YYYY-MM-DD"T"HH:MI:SS.FF3

  • NLS_TIMESTAMP_TZ_FORMAT - Must be set to the following Oracle Date and Time Format: YYYY-MM-DD"T"HH:MI:SS.FF3

Special Characters in Column Data

If any of the data in the select list columns in the query contain any characters with special meaning in XML (see Table 3-16), these characters are replaced with their corresponding entity encoding in the result XML document.

The following examples demonstrate how ODP.NET handles the angle bracket special characters in the column data:

// Create the following table
create table specialchars ("id" number, name varchar2(255));
insert into specialchars values (1, '<Jones>');

// 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 specialchars";

// Set command properties that affect XML query behavior.
cmd.BindByName = true;

// Set the XML query properties
cmd.XmlQueryProperties.MaxRows = -1;

// 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);

The following XML document is generated for that table:

<?xml version = '1.0'?>
<ROWSET>
   <ROW>
      <id>1</id >
      <NAME>&lt;Jones&gt;</NAME>
   </ROW>
</ROWSET>

Special Characters In Table or View Name

If a table or view name has any non-alphanumeric characters other than an underscore (_), the table or view name must be enclosed in quotation marks.

For example, to select all entries from a table with the name test'ing, the CommandText property of the OracleCommand object must be set to the following string.

"select * from \"test'ing\"";

Case-Sensitivity in Column Name to XML Element Name Mapping

The mapping of SQL identifiers (column names) to XML element names is case sensitive and the element names are in exactly the same case as the column names of the table or view.

However, the root tag and row tag names are case insensitive. The following example demonstrates case-sensitivity in this situation:

//Create the following table
create table casesensitive_table ("Id" number, NAME varchar2(255));

//insert name and id
insert into casesensitive_table values(1, `Smith');

The following XML document is generated:
<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <Id>1</Id>
      <NAME>Smith</NAME>
    </ROW>
  </ROWSET>

Note that the element name for the Id column matches the case with the column name.

Column Name to XML Element Name Mapping

For each row generated by the SQL query, the SQL identifier (column name) maps to an XML element in the generated XML document.

The following example demonstrates this:

// Create the following table
create table emp_table (EMPLOYEE_ID NUMBER(4), LAST_NAME varchar2(25));
// Insert some data
insert into emp_table values(205, `Higgins');

The SQL query, select * from emp_table, generates the following XML document:

<?XML version="1.0"?>
  <ROWSET>
    <ROW>
      <EMPLOYEE_ID>205</EMPLOYEE_ID>
      <LAST_NAME>Higgins</LAST_NAME>
    </ROW>
  </ROWSET>

The EMPLOYEE_ID and LAST_NAME database columns of the employees table map to the EMPLOYEE_ID and LAST_NAME elements of the generated XML document.

Retrieving Results from Oracle 8.1.7

When retrieving the query results as XML from an Oracle 8.1.7 database, the SQL identifiers in the query select-list cannot contain characters that are not valid in XML element names. To handle the lack of support for this feature in Oracle 8.1.7, the SQL query in the following example can be used to get a result as a XML document from the specialchars table:

select "some id" as "some_x0020_id", name from specialchars;
Retrieving Results from Oracle9i or Higher

When retrieving the query results as XML from Oracle9i and higher, the SQL identifiers in the query select-list can contain characters that are not valid in XML element names. When these SQL identifiers (such as column names) are mapped to XML element names, each of these characters are converted to a sequence of hexadecimal digits, derived from the Unicode encoding of the characters, bracketed by an introductory underscore, a lower case x, and a trailing underscore.

Thus, with an Oracle9i database, the SQL query in the following example can be used to get a result as an XML document from the specialchars table:

select "some id", name from specialchars;

See Also:

"Special Characters in XML" for further information on handling invalid XML element names

Improving Default Mapping

If this default mapping of SQL identifiers to XML element names is not adequate, you can improve the mapping by the following techniques:

Object-Relational Data

ODP.NET can generate an XML document for data stored in object-relational columns, tables, and views.

The following example demonstrates this:


// Create the following tables and types
CREATE TYPE "EmployeeType" AS OBJECT (EMPNO NUMBER, ENAME VARCHAR2(20));
/
CREATE TYPE EmployeeListType AS TABLE OF "EmployeeType";
/
CREATE TABLE mydept (DEPTNO NUMBER, DEPTNAME VARCHAR2(20), 
              EMPLIST EmployeeListType)
              NESTED TABLE EMPLIST STORE AS EMPLIST_TABLE;
INSERT INTO mydept VALUES (1, 'depta',
              EmployeeListType("EmployeeType"(1, 'empa')));

The following XML document is generated for the table:

<?xml version = "1.0"?>
<ROWSET>
   <ROW>
      <DEPTNO>1</DEPTNO>
      <DEPTNAME>depta</DEPTNAME>
      <EMPLIST>
         <EmployeeType>
            <EMPNO>1</EMPNO>
            <ENAME>empa</ENAME>
         </EmployeeType>
      </EMPLIST>
   </ROW>
</ROWSET>

ODP.NET encloses each item in a collection element, with the database type name of the element in the collection. The mydept table has a collection in the EMPLIST database column and each item in the collection is of type EmployeeType. Therefore, in the XML document, each item in the collection is enclosed in the type name EmployeeType.

NULL values

If any database row has a column with a NULL value, then that column does not appear for that row in the generated XML document.

Data Manipulation Using XML

This section discusses making changes to the database using XML.

Handling of Date and Time Format

Table 3-18 lists the date and time format handling for different database releases.

Table 3-18 Database Release Date and Time Differences When Saving Data
Database Release Date and Time Format Supported

Oracle8i release 3 (8.1.7), Oracle9i release 1 (9.0.x), and Oracle9i release 2 (9.2.x)

All DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE type data must be specified in the XML document in the ISO Date and Time Format YYYY-MM-DDThh:mm:ss.sss (ISO Format notation).

Saving Changes Using XML

Changes can be saved to database tables and views using XML data. However, insert, update, and delete operations cannot be combined in a single XML document. ODP.NET cannot accept a single XML document and determine which changes are inserts, updates, or deletes.

The inserts must be in an XML document containing only rows to be inserted, the updates only with rows to be updated, and the deletes only with rows to be deleted.

For example, using the employees table that comes with the HR sample schema, you can specify the following query:

select employee_id, last_name from employees where employee_id = 205;

The following XML document is generated:

<?xml version = '1.0'?>
<ROWSET>
   <ROW>
      <EMPLOYEE_ID>205</EMPLOYEE_ID>
      <LAST_NAME>Higgins</LAST_NAME>
   </ROW>
</ROWSET>

To change the name of employee 205 from Higgins to Smith, specify the employees table and the XML data containing the changes as follows:

<?xml version = '1.0'?>
<ROWSET>
   <ROW>
      <EMPLOYEE_ID>205</EMPLOYEE_ID>
      <LAST_NAME>Smith</LAST_NAME>
   </ROW>
</ROWSET>

Special Characters in Column Data

If the data in any of the elements in the XML document contains characters that have a special meaning in XML (see Table 3-16), these characters must be entity- encoded or escaped in the XML document, so that the data is stored correctly in the database table column. Otherwise, ODP.NET throws an exception.

The following examples demonstrate how ODP.NET handles the angle bracket special characters in the column data.

// Create the following table
create table specialchars ("id" number, name varchar2(255));

The following XML document can be used to insert values (1, `<Jones>') to the specialchars table:

<?xml version = '1.0'?>
 <ROWSET>
  <ROW>
   <id>1</id >
   <NAME>&lt;Jones&gt;</NAME>
  </ROW>
 </ROWSET>

Special Characters in Table or View Name

If a table or view name has any non-alphanumeric characters other than underscore an (_), the table or view name must be enclosed in quotation marks.

For example, to save changes to a table with the name test'ing, the OracleCommand.XmlSaveProperties.TableName property must be set to "\"test'ing\"".

Case-Sensitivity in XML Element Name to Column Name Mapping

For each XML element representing a row of data in the XML document, the child XML elements map to database column names. The mapping of the child element name to the column name is always case sensitive, but the root tag and row tag names are case insensitive. The following example demonstrates this case-sensitivity:

//Create the following table
create table casesensitive_table ("Id" number, NAME varchar2(255));

The following XML document can be used to insert values (1, Smith) into the case-sensitive table:

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <Id>1</Id>
      <NAME>Smith</NAME>
    </ROW>
  </ROWSET>

Note the element name for the Id column matches the case with the column name.

XML Element Name to Column Name Mapping

Oracle9i and higher handles the mapping of XML element names to column names differently from Oracle 8.1.7 when using XML for data manipulation in the database. This section demonstrate these differences with changes to the following specialchars table involving the some id column.

// Create the specialchars table
create table specialchars ("some id" number, name varchar2(255));

Note that the specialchars table has a some id column that contains a space character. The space character is not allowed in an XML element name.

Saving changes to Oracle 8.1.7

In this scenario, with an Oracle 8.1.7 database, in order to save changes to the specialchars table using an XML document, a view must be created over the table and the changes saved to the view using XML data.

The column names in the view corresponding to the some id column in the table can be either a column name with no invalid characters or the escaped column name as in the following example.

// Create the view with the escaped column name 
create view view1(some_x0020_id, name) as select * from specialchars;

// Create the view with the column name with no invalid character
create view view2(someid, name) as select * from specialchars;

The following XML document can be used to insert values (1, <Jones>) into the specialchars table using view1:

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <SOME_X0020_id>1</SOME_X0020_id >
      <NAME>&lt;Jones&gt;</NAME>
    </ROW>
  </ROWSET>

The following XML document can be used to insert values (1, <Jones>) into the specialchars table using view2:

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <SOMEID>2</SOMEID>
      <NAME>&lt;Jones&gt;</NAME>
      </ROW>
  </ROWSET>

Saving Changes to Oracle9i or higher

When an XML document is used to save changes to a table or view, the OracleCommand.XmlSaveProperties. UpdateColumnsList is used to specify the list of columns to update or insert.

With Oracle9i or higher, when an XML document is used to save changes to a column in a table or view and the corresponding column name contains any of the characters which are not valid in an XML element name, the escaped column name needs to be specified in the UpdateColumnsList property as in the following example.

The following XML document can be used to insert values (2, <Jones>) into the specialchars table.

<?xml version = '1.0'?>
  <ROWSET>
    <ROW>
      <some_x0020_id>2</some_x0020_id>
      <NAME>&lt;Jones&gt;</NAME>
    </ROW>
  </ROWSET>

The following code example specifies the list of columns to update or insert.

CmdObj.XmlCommandType = OracleXmlCommandType.Insert;
CmdObj.XmlSaveProperties.Table = "specialchars";
string[] ucols = new string[2];
ucols[0] = "some_x0020_id";
ucols[1] = "NAME";
CmdObj.XmlSaveProperties.UpdateColumnsList = ucols;
CmdObj.ExecuteNonQuery();
Improving Default Mapping

If the default mapping is not adequate, you can improve the mapping by the following techniques:

Object-Relational Data

Changes in an XML document can also be saved to object-relational data. Each item in a collection can be specified in one of the following ways in the XML document:

Multiple Tables

Oracle does not support saving changes to multiple relational tables that have been joined together. In this case, Oracle recommends that you create a view on those relational tables, and then update that view. If the view is not updatable, triggers can be used instead.

See Also:

Oracle9i SQL Reference for the description and syntax of the CREATE VIEW command

Commits

When the changes in an XML document are made, either all the changes are committed, or if an error occurs, any changes that were made are rolled back.


Go to previous page Go to next page
Oracle
Copyright © 2002, 2003 Oracle Corporation.

All Rights Reserved.
Go To Table Of Contents
Contents
Go To Index
Index