Release 9.2.0.4
Part Number B10961-01
 Contents |
 Index |
| Oracle® Data Provider for .NET Developer's Guide Release 9.2.0.4 Part Number B10961-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 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 |
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
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.
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 |
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 |
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"); } ...
|
See Also:
"OpenWithNewPassword" for further information on connecting after the password expires |
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) 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.
|
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 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.
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:
"OracleDataAdapter Class" for more information |
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.
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:
ROWID
NOT NULL constraint defined on it)
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.
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:
ROWID
NOT NULL constraint defined on it)
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.
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.
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.
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.
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.
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.
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.
The OracleCommand object represents SQL statements or stored procedures executed on the Oracle Database.
This section includes the following topics:
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.
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 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.Types.
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.
CollectionType
This property must be set to OracleCollectionType.PLSQLAssociativeArray to bind a PL/SQL Associative Array.
ArrayBindSize
This property is ignored for the fixed-length element types (such as Int32).
For variable-length element types (such as Varchar2), each element in the ArrayBindSize property specifies the size of the corresponding element in the Value property.
For Output parameters, InputOutput parameters, and return values, this property must be set for variable-length variables.
ArrayBindStatus
This property specifies the execution status of each element in the OracleParameter.Value property.
Size
This property specifies the maximum number of elements to be bound in the PL/SQL Associative Array.
Value
This property must either be set to an array of values or null or DBNull.Value.
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 ...... }
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:
ArrayBindSize Property
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.
ArrayBindStatus Property
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.
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); }
Table 3-10 provides different values 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 (4 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 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 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 |
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.
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, unique column(s), or a ROWID in the result set because the OracleDataReader must uniquely identify the row to re-select it for locking.
INSERT or an UPDATE 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 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 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 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 server through safe type mapping.
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:
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:
OracleDataAdapter.SelectCommand.
OracleDataAdapter.SelectCommand, with at least one involved column having a NOT NULL constraint defined on it.
OracleDataAdapter.SelectCommand, with at least one of the involved columns having a NOT NULL constraint defined on it.
ROWID is present in the select list of the OracleDataAdapter.SelectCommand.
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:
DataTable.PrimaryKey.
ataTable.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.
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.
ROWID is part of the select list, it is set as the DataTable.PrimaryKey.
Additionally, OracleDataAdapter.FillSchema exhibits the following behaviors:
DataTable.PrimaryKey implicitly creates a UniqueConstraint.
DataTable.PrimaryKey or UniqueConstraint, or both, each occurrence of the column is present as part of the DataTable.PrimaryKey or UniqueConstraint, or both.
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.
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:
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, Exit, and SQL execution information
2 = Connection Pooling statistics
4 = Distributed Transactions (Enlistment and Delistment)
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.
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.
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.
For samples related to ODP.NET XML support, see the following directory:
ORACLE_BASE\ORACLE_HOME\ODP.NET\Samples
This section includes these topics:
XML support in ODP.NET provides the following features:
XMLType.
For the .NET application developer, these features include the following:
OracleCommand, OracleConnection, and OracleDataReader classes
OracleXmlType Class
OracleXmlType objects are used to retrieve Oracle native XMLType data.
OracleXmlStream Class
OracleXmlStream objects are used to retrieve XML data from OracleXmlType objects as a read-only .NET Stream object.
OracleXmlQueryProperties Class
OracleXmlQueryProperties objects represent the XML properties used by the OracleCommand class when the XmlCommandType property is Query.
OracleXmlSaveProperties Class
OracleXmlSaveProperties objects represent the XML properties used by the OracleCommand class when the XmlCommandType property is Insert, Update, or Delete.
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:
OracleDataReader using the GetOracleXmlType, GetOracleValue, or GetOracleValues method:
The Connection property is set with a reference to the same OracleConnection object used by the OracleDataReader.
OracleXmlType constructor with one of the parameters of type OracleConnection:
The Connection property is set with a reference to the same OracleConnection object provided in the constructor.
OracleXmlType(OracleClob) constructor:
The Connection property is set with a reference to the OracleConnection object used by the OracleClob object.
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 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:
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.
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.
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.
Applications can set an XMLType column in the database to a NULL value, with or without input binding, as follows:
NULL values in an XMLType column with Input Binding
To set the XMLType column to NULL, the application can bind an input parameter whose value is DBNull.Value. This indicates to the OracleCommand that a NULL value is to be inserted.
Passing in a null OracleXmlType object as an input parameter does not insert a NULL into the XMLType column. In this case, the OracleCommand raises an exception.
NULL Values in an XMLType Column without Input Binding
The following example demonstrates setting NULL values in an XMLType column without input binding:
// Create a table with an XMLType column in the database CREATE TABLE XMLTABLE(NUM_COL number, XMLTYPE_COL xmltype);
An application can set a NULL value in the XMLType column by explicitly inserting a NULL or by not inserting anything into that column as in the following examples:
insert into xml_table(xmltype_col) values(NULL); update xml_table t set t.xmltype_col=NULL;
// Create a table with an XMLType column in the database CREATE TABLE XMLTABLE(NUM_COL number, XMLTYPE_COL xmltype); INSERT INTO XML_TABLE (NUM_COL, XMLTYPE_COL) VALUES (4, XMLType.createxml('<DOC/>'));
There are several ways that XML data can be updated in an OracleXmlType object.
Update method on the OracleXmlType object.
XmlDocument object using the GetXmlDocument method on the OracleXmlType object. This XML data can then be manipulated using suitable .NET Framework classes. A new OracleXmlType can be created with the updated XML data from the .NET Framework classes. This new OracleXmlType is bound as an input parameter to an update or insert statement.
The following characters have special meaning in XML. For more information, refer to the XML 1.0 specifications.
| Special Character | Meaning in XML | Entity Encoding |
|---|---|---|
|
< |
Begins an XML tag |
< |
|
> |
Ends an XML tag |
> |
|
" |
Quotation mark |
" |
|
|
Apostrophe or single quotation mark |
' |
|
& |
Ampersand |
& |
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.
This section discusses retrieving the result set from a SQL query as XML data.
Table 3-17 lists the date and time format handling for different database releases.
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><Jones></NAME> </ROW> </ROWSET>
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\"";
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.
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.
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;
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 |
If this default mapping of SQL identifiers to XML element names is not adequate, you can improve the mapping by the following techniques:
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.
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.
This section discusses making changes to the database using XML.
Table 3-18 lists the date and time format handling for different database releases.
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>
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><Jones></NAME> </ROW> </ROWSET>
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\"".
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.
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.
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><Jones></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><Jones></NAME> </ROW> </ROWSET>
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><Jones></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();
If the default mapping is not adequate, you can improve the mapping by the following techniques:
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:
_ITEM appended as the XML element name.
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 |
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.
|
 Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
|