Release 9.2.0.2
Part Number A96160-01
 Contents |
 Index |
| Oracle Data Provider for .NET Developer's Guide Release 9.2.0.2 Part Number A96160-01 |
|
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:
This section describes OracleConnection provider-specific features, including:
Table 3-1 lists the supported connection string attributes.
The following example uses connection string attributes to connect to an Oracle database:
// 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 |
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:
Pooling
Connection Lifetime
Connection Timeout
Max Pool Size
Min Pool Size
Incr Pool Size
Decr Pool Size
For 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 Open and Close methods of the OracleConnection object implicitly use the connection pooling service. The connection pooling service is part of ODP.NET. In the code above, 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 with the ConnectionString as the signature to uniquely identify a pool. If a pool does not exist with a ConnectionString acting as a signature, the connection pooling service creates a new connection pool. If a pool already exists with the exact attribute values in the ConnectionString, a connection is returned to the application from the 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 can be available in the pool or used by the application. When a request for a connection comes to the connection pooling service, the connection pooling service goes to the right connection pool and returns an available connection from the pool. If there is no connection available, the connection pooling service creates and returns a new connection. 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 a new connection is not available.
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.
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 Windows for information on how to set up Oracle to authenticate database users using Windows user login credentials |
Oracle allows database administrators to connect to an Oracle database 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 |
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;"; con.Open(); // Here the con.Open() fails if the password has expired. // An application catches this and asks for the new password and call following // API. The initial connection string must remain intact. // The following API gives panther as the new Password. con.OpenWithNewPassword("panther"); ...
|
See Also:
"OpenWithNewPassword" for further information password expiration in the Oracle database |
Oracle allows a middle-tier server to connect to proxy clients in a secure fashion.
In multi-tier environments, proxy authentication allows control of middle-tier application security by preserving client identities and privileges through all tiers, and auditing actions taken on behalf of clients. For example, this feature allows the identity of a user using a web application (also known as a proxy) 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) 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.
Due to the delay that a failover can incur, the application may wish to be notified by a TAF callback. ODP.NET supports TAF callback through the Failover event of the OracleConnection object. 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 server 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 snippet 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.
|
See Also:
|
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 a safer and efficient way of obtaining Oracle native data in a .NET application. 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.
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:
The OracleDataReader provides two types of typed 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:
"OracleDataReader Class" for more information |
ODP.NET has introduced several new types that natively represent the datatypes in Oracle. There are several advantages of using types introduced by ODP.NET over .NET types.
|
See Also:
"ODP.NET Types Overview" for a list of all ODP.NET types |
Table 3-4 lists the valid ODP.NET type accessors used to obtain ODP.NET Types for an Oracle native type.
When an OracleDataReader is created containing LONG or LONG RAW types, OracleDataReader defers the fetch of the LONG or LONG RAW column data. The initial number of characters for LONG or bytes for LONG RAW fetched on the client side depends on the InitialLONGFetchSize property of the OracleCommand. By default, InitialLONGFetchSize is 0.
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, a primary key column must be provided in the list of selected columns. The requested data is fetched from the database when the appropriate typed accessor method (GetOracleString for LONG or GetOracleBinary for LONG RAW) is called on the OracleDataReader object.
In order to fetch the data in a non-defer mode or when the list of columns does not have a primary key column or a ROWID, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the amount of bytes or characters needs to be retrieved.
Application performance depends on the number of database round-trips to retrieve data. The less round-trips, the better the performance.
The OracleCommand FetchSize property defines the maximum number of rows that can be fetched in one server round-trip.
The FetchSize property takes the size of memory (in bytes) that is used by the OracleDataReader to fetch numbers rows from the database. If a query returns 100 rows and each row takes 1024 (1 K) bytes, then setting FetchSize to 102400 takes just one database round-trip to fetch the hundred rows. For the same query, if the FetchSize is set to 10240, it takes 10 database round-trips to retrieve 100 rows.
In order to set the FetchSize property to a proper value, the exact size of a row returned from the query must be used. This is provided by the RowSize property on the OracleCommand object. RowSize is a design time property, which is only populated after the execution of the query. Once the RowSize is available, it can be used to set the correct FetchSize on the command.
Applications that do not know the exact size of a row at design time can use the FetchSize property of the OracleDataReader object. This FetchSize property should be set after invoking the ExecuteDataReader method of the OracleCommand object and before the first row is fetched using the Read method on the OracleDataReader object.
The RowSize property of the OracleCommand object is set by execution of the ExecuteDataReader. The RowSize property can then be used to set the value of the FetchSize property on the OracleDataReader object. This provides a dynamic way of setting the FetchSize property on the OracleDataReader based on the size of a row.
The OracleCommand object represents SQL statements or stored procedures executed on the Oracle database server.
This section includes the following topics:
The Oracle database server starts a transaction only in the context of a connection. Once a transaction starts on a connection, all the successive command execution on that connection run in the context of that transaction. ODP.NET reflects this by only starting a transaction on a OracleConnection object and not allowing the Transaction property on the OracleCommand object to be set. The Transaction property on the OracleCommand object is read-only and depends solely on the connection used for the command object.
ODP.NET allows applications to retrieve data as either a .NET Framework type or an ODP.NET type. If the application sets the OracleParameter's DbType property for an OUT parameter, the data is retrieved as .NET Framework type. That is, 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 application sets OracleParameter's OracleDbType property, the data is retrieved as a ODP.NET type. That is, 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.
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 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.
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.
In the OracleParameter class, specifying the value of OracleDbType infers the value of DbType as shown in Table 3-6.
In the OracleParameter class, specifying the value of DbType infers the value of OracleDbType as shown in Table 3-7.
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-9 shows the inference of DbType and OracleDbType from Value when type of Value is one of Oracle.DataAccess.Client.
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 |
The OracleParameter object provides two properties for granular control when using the array bind feature:
The ArrayBindSize property is an array of integers specifying the maximum size for each corresponding value in an array. The ArrayBindSize property is similar to the Size property of an OracleParameter except ArrayBindSize specifies the size for each value in an array.
Before the execution, the application must populate ArrayBindSize; after the execution, ODP.NET populates the ArrayBindSize.
ArrayBindSize is used only for parameter types that have variable length such as Clob, Blob and Varchar2. The size is represented in bytes for binary datatypes and characters for the Unicode string types. The count for string types does not include the terminating character. The size is inferred from the actual size of the value, if it is not explicitly set. For an output parameter, the size of each value is set by ODP.NET. The ArrayBindSize property is ignored for fixed length datatypes.
The ArrayBindStatus property is an array of OracleParameterStatus values specifying status of each corresponding value in an array for a parameter. This property is similar to the Status property of OracleParameter, except that ArrayBindStatus specifies the status for each value in an array.
Before the execution, the application must populate the ArrayBindStatus property and after the execution, ODP.NET populates it. Before the execution, an application using ArrayBindStatus can specify a NULL value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the ArrayBindStatus array, indicating whether the corresponding element in the array has a NULL value or if data truncation occurred when the value was fetched.
Table 3-10 provides different value for OracleParameterStatus enumeration.
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:
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.
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.
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.
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.
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.
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.
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 |
ODP.NET provides an easy and optimal way to access and manipulate large datatypes. Oracle supports large character and large binary datatypes.
CLOB - Character data can store up to 4 gigabytes (GB).
NCLOB - Unicode National character set data can store up to 4 gigabytes.
BLOB - Unstructured binary data can store up to 4 gigabytes.
BFILE - Binary data stored in external file can store up to 4 gigabytes.
ODP.NET provides three objects for LOBs for manipulating LOB data: OracleBFile, OracleBlob, and OracleClob.
Table 3-11 below shows the proper ODP.NET class to use for a particular Oracle LOB type.
| Oracle LOB Type | ODP.NET LOB object |
|---|---|
|
|
OracleBFile object |
|
|
OracleBlob object |
|
|
OracleClob object |
|
|
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, which takes a byte[] as the buffer, populates it with CLOB data as Unicode byte array. Conversely, the Read method, which 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 |
BFILE and BLOB data are stored in the DataSet as byte arrays while CLOB and NCLOB data are stored as strings. Similarly 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.
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.
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.
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.
Add the FOR UPDATE clause to the end of the SELECT statement. After execution of the command, the entire result set is locked.
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 or a ROWID in the result set because the OracleDataReader must uniquely identify the row to re-select it for locking.
SELECT statement that returns a LOB in the RETURNING clause.
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:
CopyTo operation.
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.
This section includes the following:
An OracleGlobalization object can be used to represent the following:
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 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. 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 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.
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 |
This section lists ODP.NET types and operations that are dependent on or sensitive to 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[].
The local computer's client character set is also used by the OracleCommand when parameters are bound as DbType.AnsiString or DbType.AnsiStringFixedLength.
As input parameters, the parameter value must be in the character set specified by the OracleGlobalization object's ClientCharacterSet property if it is bound as either DbType.AnsiString or DbType.AnsiStringFixedLength.
As output parameters, those parameter values that are bound as DbType.AnsiString or DbType.AnsiStringFixedLength are populated with ANSI string data in the character set specified by the OracleGlobalization object's ClientCharacterSet property.
Note that the ClientCharacterSet property is read-only and its value is the same regardless of whether the OracleGlobalization object represents the settings of the session, thread, or client.
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.
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 through 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 the types defined above, 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:
The following sections provide more detail about the types and circumstances where data can be lost.
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.
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.
| 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 |
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.
| 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 |
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.
| 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 |
By default, Safe Type Mapping is disabled.
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.
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.
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.
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:
The following registry settings should be configured under
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET
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.
The valid values for TraceLevel are:
0 = None
1 = Entry and Exit information as well as SQL statements
2 = Distributed Transactions (Enlistment and Delistment)
4 = Connection Pooling statistics
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, and SQL and Distributed Transactions.
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.
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|