Before reading this article, please go through the following articles
OracleParameterStatus Enumeration Types
Table 3.10 provides different values for OracleParameterStatus enumeration.
Table 3.10 OracleParameterStatus Members
| Member Names |
Description |
| Success |
For input parameters, it indicates that the input value has been assigned to the column. For output parameters, it indicates that the provider assigned an intact value to the parameter. |
| NullFetched |
Indicates that a NULL value has been fetched from a column or an OUT parameter. |
| NullInsert |
Indicates that a NULL value is to be inserted into a column. |
| Truncation |
Indicates that truncation has occurred when fetching the data from the column. |
PL/SQL REF CURSOR and OracleRefCursor
The REF CURSOR is a datatype in the Oracle PL/SQL language. It represents a cursor or a result set in the Oracle database server. The OracleRefCursor is a corresponding ODP.NET type for the REF CURSOR type.
Obtaining an OracleRefCursor
There are no constructors for OracleRefCursor objects. They can only be acquired as parameter values from PL/SQL stored procedures, stored functions, or anonymous blocks.
An OracleRefCursor is a connected object. The connection used to execute the command returning a OracleRefCursor object is required for its lifetime. Once the connection associated with an OracleRefCursor is closed, the OracleRefCursor cannot be used.
Obtaining a REF CURSOR
A REF CURSOR can be obtained as an OracleDataReader, DataSet, or OracleRefCursor. If the REF CURSOR is obtained as an OracleRefCursor object, it can be used to create an OracleDataReader or populate a DataSet from it. When accessing a REF CURSOR, always bind as a OracleDbType.RefCursor.
Populating an OracleDataReader from a REF CURSOR
An Oracle REF CURSOR can be obtained as an OracleDataReader by calling the OracleCommand ExecuteReader method. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor. None of the output parameters of type OracleDbType.RefCursor are populated after the ExecuteReader is invoked.
If there are multiple output REF CURSOR parameters, use the OracleDataReader NextResult method to access the next REF CURSOR. The OracleDataReader NextResult method provides sequential access to the REF CURSORs; only one REF CURSOR can be accessed at a given time.
The order in which OracleDataReader objects are created for the corresponding REF CURSOR depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR, then it becomes the first OracleDataReader and all the output REF CURSOR objects follow the order in which the parameters are bound.
Populating the DataSet From a REF CURSOR
For the Fill method to populate the DataSet properly, the SelectCommand of the OracleDataAdapter must be bound with an output parameter of type OracleDbType.RefCursor. If the Fill method is successful, the DataSet is populated with a DataTable that represents a REF CURSOR.
If the command execution returns multiple REF CURSORs, the DataSet is populated with multiple DataTables.
Populating an OracleRefCursor From a REF CURSOR
When ExecuteNonQuery is invoked on a command that returns one or more REF CURSORs, each of the OracleCommand parameters that are bound as OracleDbType.RefCursor gets a reference to an OracleRefCursor object. To create an OracleDataReader from an OracleRefCursor object, invoke GetDataReader from an OracleRefCursor object. Subsequent calls to GetDataReader return the reference to the same OracleDataReader.
To populate a DataSet with an OracleRefCursor object, the application can invoke an OracleDataAdapter Fill method that takes an OracleRefCursor object.
When multiple REF CURSORs are returned from a command execution as OracleRefCursor objects, the application can choose to create an OracleDataReader or populate a DataSet with a particular OracleRefCursor object. All the OracleDataReaders or DataSet created from the OracleRefCursor are active at the same time and can be accessed in any order.
Updating a DataSet Obtained From a REF CURSOR
REF CURSORs are not updatable. However, data that is retrieved into a DataSet can be updated. Therefore, the OracleDataAdapter requires a custom SQL statement to flush any REF CURSOR data updates to the database.
The OracleCommandBuilder cannot be used to generate SQL for REF CURSOR updates.
Behavior of ExecuteScalar Method for REF CURSOR
ExecuteScalar returns the return value of a stored function or the first bind parameter of a stored procedure or an anonymous PL/SQL block. Therefore, if the REF CURSOR is not the return value of a stored function or the first bind parameter of a stored procedure or an anonymous PL/SQL block, the REF CURSOR is ignored by ExecuteScalar.
However, if the REF CURSOR is a return value of a stored function or the first bind parameter of a stored procedure or an anonymous PL/SQL block, the value of the first column of the first row in the REF CURSOR is returned.
LOB Support
ODP.NET provides an easy and optimal way to access and manipulate large datatypes. Oracle supports large character and large binary datatypes.
Large Character Datatypes
- CLOB: Character data can store up to 4 gigabytes (4 GB).
- NCLOB: Unicode National character set data can store up to 4 gigabytes.
Large Binary Datatypes
- 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 ODP.NET LOB Objects
| Oracle LOB Type |
ODP.NET LOB object |
| BFILE |
OracleBFile object |
| BLOB |
OracleBlob object |
| CLOB |
OracleClob object |
| NCLOB |
OracleClob object |
The ODP.NET LOB objects can be obtained by calling the proper typed accessor on the OracleDataReader or as an output parameter on a command execution with the proper bind type.
All ODP.NET LOB objects inherit from the .NET Stream class to provide generic Stream operations. The LOB data (except for BFILEs) can be updated using the ODP.NET LOB objects by using methods such as Write. Data is not cached in the LOB objects when read and write operations are carried out. Therefore, each Read or Write request incurs a server round-trip. The OracleClob overloads the Read method, providing two ways to read data from a CLOB. The Read method that takes a byte[] as the buffer populates it with CLOB data as Unicode byte array. The Read method that takes a char[] as the buffer populates it with Unicode characters.
Extensions can also be found on the OracleBFile object. An OracleBFile object must be explicitly opened using the OpenFile method before any data can be read from it. To close a previously opened BFILE, use the CloseFile method.
Every ODP.NET LOB object is a connected object and requires a connection during its lifetime. If the connection associated with a LOB object is closed, then the LOB object is not usable and should be disposed.
If an ODP.NET LOB object is obtained from an OracleDataReader through a typed accessor, then its Connection property is set with a reference to the same OracleConnection object used by the OracleDataReader. If a LOB object is obtained as an output parameter, then its Connection property is set with a reference to the same OracleConnection property used by the OracleCommand. If a LOB object is obtained by invoking an ODP.NET LOB object constructor to create a temporary LOB, the Connection property is set with a reference to the OracleConnection object provided in the constructor.
The ODP.NET LOB object Connection property is read-only and cannot be changed during its lifetime. In addition, the ODP.NET LOB types object can only be used within the context of the same OracleConnection referenced by the ODP.NET LOB object. For example, the ODP.NET LOB object's Connection must reference the same connection as the OracleCommand if the ODP.NET LOB object is a parameter of the OracleCommand. If that is not the case, ODP.NET raises an exception when the command is executed.
Temporary LOBs
Temporary LOBs can be instantiated for BLOBs, CLOBs, and NCLOBs. To instantiate an ODP.NET LOB object that represents a temporary LOB, the OracleClob or the OracleBlob constructor can be used.
Temporary ODP.NET LOB objects can be used for the following purposes
- To initialize and populate a LOB column with empty or non-empty LOB data.
- To pass a LOB type as an input parameter to a SQL statement, anonymous PL/SQL blocks, or stored procedure.
- To act as the source or the destination of data transfer between two LOB objects as in the CopyTo operation.
ODP.NET XML Support Features
XML support in ODP.NET provides the following features
- Store XML data natively in the database server as the Oracle database native type, XMLType.
- Access relational and object-relational data as XML data from an Oracle database instance into Microsoft .NET environment, process the XML using Microsoft .NET framework.
- Save changes to the database server using XML data.