Export (0) Print
Expand All
ADO.NET and System.Transactions
Contrasting the ADO.NET DataReader and DataSet
Data Access Strategies Using ADO.NET and SQL
Data Points: Efficient Coding With Strongly Typed DataSets
Teach Old Data New Tricks with the Fully Loaded Advances in ADO.NET 2.0
Handling Data Concurrency Using ADO.NET, Part 2
Handling Data Concurrency Using ADO.NET
Migrating from ADO to ADO.NET, Part 2
Migrating from ADO to ADO.NET
Saving Parent-child Data in a Multitiered App Using ADO.NET
Expand Minimize

Advanced Data Access with ADO.NET and Oracle

 

Bill Hamilton

August 2004

Applies to:
   Microsoft ADO.NET 1.1
   Oracle data types

Summary: Learn how to use ADO.NET 1.1 to retrieve data from complex Oracle data types. (28 printed pages)

Contents

Introduction
Any
BFILE
Interval
LOB
RAW
REF CURSOR
ROWID
Timestamp
UriType
User Defined
XmlType
Conclusion
Related Books

Introduction

The .NET Framework Data Provider for Oracle does not always make it easy to retrieve data stored in complex ways. This article discusses how to access data stored in the Oracle-specific data types listed in the following table:

Data Type Description
Any Data types used to explicitly define data types that can hold any type of data.
BFile A pointer to an operating system file containing unstructured binary data stored outside of the Oracle database.
Interval Data types that specify a time interval.
LOB Data types that store unstructured binary data.
RAW Data types that store binary data that is not interpreted by Oracle.
REF CURSOR A PL/SQL pointer to a SQL cursor used to return a row from the database.
ROWID Data types that represent the specific address of a row in the database.
Timestamp Data types that extend the functionality of the DATE data type.
URI Data types that store Uniform Resource Identifiers (URIs) that reference a document or a specific part of a document.
User-defined Complex data types created from basic Oracle data types and user-defined data types.
XMLType Data types that store XML data.

The System.Data.OracleClient namespace contains types used to access Oracle data source using the.NET Framework Data Provider for Oracle. In addition to Oracle-specific versions of the DataReader, DataAdapter, Command, and Connection classes, the namespace contains types to support and facilitate working with Oracle data types. These are listed in the following table:

Type Description
OracleBFile Class that represents an Oracle BFILE data type.
OracleBinary Structure that represents a variable-length stream of binary data.
OracleBoolean Structure that represents a value returned from a database comparison between Oracle data types.
OracleDateTime Structure that represents a date and time value ranging from January 1, 4712 BC to December 31, 4712 AD.
OracleLob Class that represents a large binary object (LOB) stored in an Oracle database.
OracleMonthSpan Structure that stores a time interval measured in months and represents the Oracle INTERVAL YEAR TO MONTH data type.
OracleNumber Structure that represents a fixed-precision and scale numeric value between -10-38 – 1 and 1038 – 1 stored in an Oracle database.
OracleString Structure that represents a variable-length stream of characters stored in an Oracle database.
OracleTimeSpan Structure that stores a time interval and represents the Oracle INTERVAL DAY TO SECOND data type.
OracleType Enumeration of Oracle data types

For each data type, the code samples show how to create the data type within a table, how to populate each data type column with data, and how to access the data.

Any

Oracle 9i introduces three data types that let you store generic data—any known data type or an unnamed data type. The data types are:

SYS.ANYTYPE
A data type description for any SQL type including object and collection types.
SYS.ANYDATA
Contains an instance of a given type together with a description of the type. ANYDATA can contain different types of data in different rows.
SYS.ANYDATASET
Contains a set of data and its type description.

The following code creates a table containing an ANYDATA column:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyAnyTable (Id NUMBER,
    AnydataCol SYS.ANYDATA)";
cmd.ExecuteNonQuery();

ANYDATA has a set of static functions that allow you to create an ANYDATA type by explicitly casting an argument and returning it as an ANYDATA data type value:

ConvertNumber(num IN NUMBER)
ConvertDate(dat IN DATE)
ConvertChar(c IN CHAR)
ConvertVarchar(c IN VARCHAR)
ConvertVarchar2(c IN VARCHAR2)
ConvertRaw(r IN RAW)
ConvertBlob(b IN BLOB)
ConvertClob(c IN CLOB)
ConvertBfile(b IN BFILE)
ConvertObject(obj IN "<object_type>")
ConvertRef(rf IN "<object_type>")
ConvertCollection(n IN "COLLECTION_1")

The following code inserts two rows into the table—a VARCHAR2 in the ANYDATA column of the first row and a NUMBER in the ANYDATA column of the second row:

OracleConnection conn = new OracleConnection(CONNECTSTRING);
conn.Open();

OracleCommand cmd = conn.CreateCommand();

cmd.CommandText = "INSERT INTO MyAnyTable (Id, AnydataCol) VALUES
    (1, SYS.ANYDATA.CONVERTVARCHAR2('TEST STRING'))";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO MyAnyTable (Id, AnydataCol) VALUES
    (2, SYS.ANYDATA.CONVERTNUMBER(100))";
cmd.ExecuteNonQuery();

conn.Close();

The GETTYPENAME function of the ANYTYPE type returns the ANYTYPE corresponding to the actual data stored in the ANYDATA field. The following code uses the GETTYPENAME to retrieve the data types of the ANYDATA column in the two rows inserted:

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand(
    "SELECT e.AnydataCol.GetTypeName() FROM MyAnyTable e", conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
while(dr.Read())
    Console.WriteLine(dr.GetString(0));
      
dr.Close();
conn.Close();

The console output is:

SYS.VARCHAR2
SYS.NUMBER

ANYDATA has a set of member functions that let you retrieve ANYDATA data as a value of the appropriate data type:

GetNumber(self IN AnyData, num OUT NOCOPY NUMBER)
GetDate(self IN AnyData, dat OUT NOCOPY DATE)
GetChar(self IN AnyData, c OUT NOCOPY CHAR)
GetVarchar(self IN AnyData, c OUT NOCOPY VARCHAR)
GetVarchar2(self IN AnyData, c OUT NOCOPY VARCHAR2)
GetRaw(self IN AnyData, r OUT NOCOPY RAW)
GetBlob(self IN AnyData, b OUT NOCOPY BLOB)
GetClob(self IN AnyData, c OUT NOCOPY CLOB)
GetBfile(self IN AnyData, b OUT NOCOPY BFILE)
GetObject(self IN AnyData, obj OUT NOCOPY "<object_type>")
GetRef(self IN AnyData, rf OUT NOCOPY "<object_type>")
GetCollection(self IN col AnyData, OUT NOCOPY "<collection_type>")

The following function lets you query an ANYDATA column, returning the result for each row in a VARCHAR2:

CREATE OR REPLACE FUNCTION GETANYDATA (data IN SYS.ANYDATA)
return VARCHAR2
as
    l_varchar2  varchar2(4000);
    l_rc        number;
begin
    case data.getTypeName
    when 'SYS.NUMBER' then
        l_rc := data.getNumber(l_varchar2);
    when 'SYS.VARCHAR2' then
        l_rc := data.getVarchar2(l_varchar2);
    else
        l_varchar2 := 'ERROR: unknown data type';
    end case;
  
    return l_varchar2; 
end;

The function can be extended to support other native and user-defined data types. The following code uses function to return the values for the two rows in the table.

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand("SELECT id,
    e.AnydataCol.GetTypeName(), GETANYDATA(anydataCol) dataValue
    FROM MyAnyTable e", conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
while(dr.Read())
   Console.WriteLine(dr.GetInt32(0) + "; " + dr.GetString(1) + "; " +
       dr.GetString(2));

dr.Close();
conn.Close();

The console output is:

1; SYS.VARCHAR2; TEST STRING
2; SYS.NUMBER; 100

You can also store instances of user-defined data types in an ANYTYPE field. The following code creates the user-defined type EMPLOYEE_T:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE OR REPLACE TYPE Employee_t IS OBJECT
    (Name VARCHAR(50), YearsService INTEGER)";
cmd.ExecuteNonQuery();

Insert a value of the EMPLOYEE_T data type into the table with the following code:

cmd.CommandText = "INSERT INTO MyAnyTable (Id, AnydataCol) VALUES
    (3, SYS.ANYDATA.CONVERTOBJECT(Employee_t('Bill Hamilton', 5)))";
cmd.ExecuteNonQuery();

The GetTypeName function will return the data type in the format <schemaname>.<UserTypeName>—for example MySchema.EMPLOYEE_T. You can access the contents an ANYTYPE field that contains a user-defined type by extending the stored procedure with the techniques used to access user-defined data types discussed later in this document.

BFILE

The BFILE data type is a reference to binary data stored externally to the database in an operating system file. It has a maximum size of 4GB. Data stored in these columns is read-only.

The following code creates a table containing a BFILE data type column:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyBfileTable (Id NUMBER, BfileCol BFILE)";
cmd.ExecuteNonQuery();

The following code creates a DIRECTORY and inserts the file SH_ANALZ.SQL (installed with Oracle 9i) into the BFILE data type. The account that runs this code will need CREATE ANY DIRECTORY privilege:

int id = 1;
OracleCommand cmd = conn.CreateCommand();            
cmd.CommandText = @"CREATE OR REPLACE DIRECTORY MYBFILEDIR AS
    'C:\oracle\ora92\demo\schema\sales_history'";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO MyBfileTable VALUES
    (" + id + ", BFILENAME('MYBFILEDIR', 'SH_ANALZ.SQL'))";
cmd.ExecuteNonQuery();

The following code retrieves the BFILE and saves its contents to a string:

int id = 1;
byte[] bfile = null;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
conn.Open();

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM MyBfileTable WHERE id = " + id;

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    OracleBFile bf = dr.GetOracleBFile(1);

    bfile = new byte[bf.Length];
    bf.Read(bfile, 0, bfile.Length);
    bf.Close();
}

dr.Close();
conn.Close();

UTF7Encoding utf = new UTF7Encoding();
string s = utf.GetString(bfile);

Interval

Interval data types specify an interval of time. There are two interval data types:

INTERVAL DAY TO SECOND
A fixed-sized 11 byte data type that stores a time interval in days, hours, minutes, and seconds.
INTERVAL YEAR TO MONTH
A fixed sized 5-byte data type that stores a time interval in years and months.

The following code creates a table containing INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH data type columns:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyIntervalTable (Id NUMBER,
    IntervalYTMCol INTERVAL YEAR TO MONTH,
    IntervalDTSCol INTERVAL DAY TO SECOND)";
cmd.ExecuteNonQuery();

The following code uses the OracleMonthSpan and OracleTimeSpan structures to insert a row containing interval data:

OracleDataAdapter da = new OracleDataAdapter(
    "SELECT Id, IntervalYTMCol, IntervalDTSCol FROM MyIntervalTable",
    CONNECTSTRING);
DataTable dt = new DataTable();
// get the schema
da.FillSchema(dt, SchemaType.Source);

OracleCommandBuilder cb = new OracleCommandBuilder(da);

int id = 1;
// 1 year, 2 months
OracleMonthSpan intervalYTMCol = new OracleMonthSpan(1, 2);
// 1 day, 2 sours, 3 minutes, 4 seconds, 5 milliseconds
OracleTimeSpan intervalDTSCol = new OracleTimeSpan(1, 2, 3, 4, 5);

// create a row containing the data
DataRow row = dt.NewRow();
row["Id"] = id;
row["IntervalYTMCol"] = intervalYTMCol.Value;
row["IntervalDTSCol"] = intervalDTSCol.Value;
dt.Rows.Add(row);

da.Update(dt);

The following code uses the GetOracleMonthSpan() and GetOracleTimeSpan() typed accessor methods of the OracleDataReader to retrieve the interval data:

int id = 1;
OracleMonthSpan intervalYTMCol;
OracleTimeSpan intervalDTSCol;
OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand("SELECT IntervalYTMCol,
    IntervalDTSCol FROM MyIntervalTable WHERE Id = " + id, conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    intervalYTMCol = dr.GetOracleMonthSpan(0);
    intervalDTSCol = dr.GetOracleTimeSpan(1);

    Console.WriteLine(intervalYTMCol.ToString() + "; " +
        intervalDTSCol.ToString());
}
      
dr.Close();
conn.Close();

The console output is:

14; 1.02:03:04.0050000
 

LOB

Oracle LOB data types store unstructured data (e.g., multimedia content, text) up to 4GB in size. LOBs allow random, piece-wise access to data. Oracle recommends using LOB types instead of LONG RAW types for new applications. Unlike a LONG RAW, a table can contain multiple LOB columns. The LOB data types are:

BLOB
Stores binary data.
CLOB
Stores single-byte, database character set data, based on the default server character set.
NCLOB
Stores Unicode character data that is based on the national character set of the database.

The following code creates a table containing BLOB, CLOB, and NCLOB columns:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyBlobTable (Id NUMBER, BlobCol BLOB,
    ClobCol CLOB, NclobCol NCLOB)";
cmd.ExecuteNonQuery();

The following code inserts a user-selected file into the BLOB column, and some test text into the CLOB and NCLOB columns:

OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
    FileStream fs = new FileStream(ofd.FileName, FileMode.Open,
        FileAccess.Read);
    byte[] blob = new byte[fs.Length];
    fs.Read(blob, 0, blob.Length);
    fs.Close();

    OracleDataAdapter da = new OracleDataAdapter("
        SELECT Id, BlobCol, ClobCol, NclobCol FROM MyBlobTable",
        CONNECTSTRING);
    DataTable dt = new DataTable();
    // get the schema
    da.FillSchema(dt, SchemaType.Source);

    OracleCommandBuilder cb = new OracleCommandBuilder(da);

    int id = 1;
    string clob = "test CLOB text";
    string nclob = "test NCLOB text";
    
    // create a row containing the data
    DataRow row = dt.NewRow();
    row["Id"] = id;
    row["BlobCol"] = blob;
    row["ClobCol"] = clob;
    row["NclobCol"] = nclob;
    dt.Rows.Add(row);

    // update the table
    da.Update(dt);
}

The following code retrieves the LOB data:

int id = 1;
OracleLob blob = null;
String clob = "";
String nclob = "";

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand(
    "SELECT BlobCol, ClobCol, NclobCol FROM MyBlobTable WHERE Id = " + id,
    conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    if(!dr.IsDBNull(0))
        blob = dr.GetOracleLob(0);

    MemoryStream ms = new MemoryStream((byte[])blob.Value);
    // do something with the stream
    ms.Close();

    if(!dr.IsDBNull(1))
        clob = dr.GetOracleLob(1).Value.ToString();

    if(!dr.IsDBNull(2))
        nclob = dr.GetOracleLob(2).Value.ToString();

    Console.WriteLine("BLOB length = " + blob.Length + "; " +
        clob.ToString() + "; " + nclob.ToString());
 }

dr.Close();
conn.Close();

The console output is:

BLOB length = 1171; test CLOB text; test NCLOB text

RAW

Stores variable-length binary or other data that is not intended to be interpreted. Oracle does not try to interpret the data as it does with VARCHAR2—no character set conversion or blank padding for example.

The RAW data type is provided for compatibility with existing applications—LOB and BFILE data types should be used for new applications.

There are two RAW types:

RAW
Has the same maximum length as VARCHAR2 (32767 bytes)—the length must be specified when the variable is declared.
LONG RAW
Variable-length binary data with a maximum length of 2GB. Only one LONG RAW data type is allowed in a table.

The following code creates a table with RAW and LONG RAW columns:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyRawTable (Id NUMBER, RawCol RAW(50), LongRawCol LONG RAW)";
cmd.ExecuteNonQuery();

The following code creates a row and inserts data into the RAW and LONG RAW columns—a byte array into the RAW field and the contents of a user-selected file into the LONG RAW field:

OpenFileDialog ofd = new OpenFileDialog();

if (ofd.ShowDialog() == DialogResult.OK)
{
    // create the RAW
    byte[] raw = new byte[50];
    for (byte i = 0; i < raw.Length; i++)
        raw[i] = i;

    // create the LONGRAW
    FileStream fs = new FileStream(ofd.FileName,
        FileMode.Open, FileAccess.Read);
    byte[] longRaw = new byte[fs.Length];
    fs.Read(longRaw, 0, raw.Length);
    fs.Close();

    OracleDataAdapter da = new OracleDataAdapter(
        "SELECT Id, RawCol, LongRawCol FROM MyRawTable", CONNECTSTRING);
    DataTable dt = new DataTable();
    // get the schema
    da.FillSchema(dt, SchemaType.Source);

    OracleCommandBuilder cb = new OracleCommandBuilder(da);

    int id = 1;
    // create a row containing the data
    DataRow row = dt.NewRow();
    row["Id"] = id;
    row["RawCol"] = raw;
    row["LongRawCol"] = longRaw;
    dt.Rows.Add(row);

    // update the table
    da.Update(dt);
}

The following code retrieves the data in the RAW and LONG RAW fields into OracleBinary structures:

int id = 1;
OracleBinary raw = null;
OracleBinary longRaw = null;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand(
    "SELECT RawCol, LongRawCol FROM MyRawTable WHERE Id = " + id, conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    if(!dr.IsDBNull(0))
        raw = dr.GetOracleBinary(0);

    if(!dr.IsDBNull(1))
        longRaw = dr.GetOracleBinary(1);
}

dr.Close();
conn.Close();      

REF CURSOR

A data type that points into a result set returned by a PL/SQL query. A REF CURSOR is used as an output parameter to pass a result set from an Oracle structured program back to a calling application. The cursor can be advanced within a rowset to return multiple rows.

Use a REF CURSOR output parameter to pass a result set from an Oracle structured program back to the calling application.

The following package and package body returns a REF CURSOR to a result set containing all employees in the HR.EMPLOYEES table. The package spec follows:

CREATE OR REPLACE PACKAGE GET_EMPLOYEES AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR);
END GET_EMPLOYEES;

The package body defines a single procedure that retrieves all data for all employees and returns it as a REF CURSOR output parameter:

CREATE OR REPLACE PACKAGE BODY GET_EMPLOYEES AS
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR)
IS
BEGIN
    OPEN cur_Employees FOR
    SELECT * FROM EMPLOYEES;
END GetEmployees;
END GET_EMPLOYEES;

The following code shows how to use an OracleDataReader to retrieve the result set returned by the stored procedure GetEmployees.

// create connection
OracleConnection conn = new OracleConnection(CONNECTSTRING);

// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "GET_EMPLOYEES.GetEmployees";
cmd.CommandType = CommandType.StoredProcedure;

// add the REF CURSOR parameter to retrieve the result set
cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =
     ParameterDirection.Output;

// open the connection and create the DataReader
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();

// output the employee ID, name, and email (first four fields)
// and close the connection.
while(dr.Read())
{
    for(int i = 0; i <= 3; i++)
        Console.Write(dr[i].ToString() + ";");

    Console.WriteLine();
}
conn.Close();

The console output lists all employee IDs, names, and emails. The first five lines are:

100;Steven;King;SKING;
101;Neena;Kochhar;NKOCHHAR;
102;Lex;De Haan;LDEHAAN;
103;Alexander;Hunold;AHUNOLD;
104;Bruce;Ernst;BERNST;

ROWID

A data type used by Oracle to store the unique address for every row in the database. The ROWID types are:

ROWID
A 10-byte long data type that represents the page, record, and offset address for a row in the database. The value is a representation of the actual binary displayed using the format bbbbbbbb.ssss.ffff where:

bbbbbbbb is the block ID

ssss is the sequence within the block

ffff is the file ID

UROWID
The universal ROWID (UROWID) supports both logical and physical ROWIDs and ROWIDs of foreign tables including non-Oracle tables. The maximum length of a UROWID is 4000 bytes.
ROWID pseudocolumn
Each table has a pseudocolumn named ROWID that lets you access the address for any row using the reserved word ROWID as a column name. ROWID pseudocolumns are not stored in the database, do not take up any space, and cannot be changed or deleted. The ROWID for a row in the table is guaranteed not to change as long as a row exists and provides the fastest way to access a row in a table.

The following code creates a table with both a ROWID and UROWID column:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyRowidTable (Id NUMBER, RowidCol ROWID,
    UrowidCol UROWID)";
cmd.ExecuteNonQuery();

The following code creates a record and stores the value of the ROWID pseudocolumn for the first row in the HR.Employees table in both the ROWID and UROWID fields:

int id = 1;
OracleString rowId = null;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
conn.Open();

// get the ROWID pseudocolumn value for the first row in HR.Employees
OracleCommand cmd =
    new OracleCommand("SELECT ROWID FROM HR.Employees", conn);
OracleDataReader dr = cmd.ExecuteReader();

if(dr.Read())
   rowId = dr.GetOracleString(0);

conn.Close();

// store the ROWID pseudocolumn value into the ROWID and UROWID
OracleDataAdapter da = new OracleDataAdapter("SELECT ID, RowidCol,
    UrowidCol FROM MyRowidTable", CONNECTSTRING);
DataTable dt = new DataTable();
da.FillSchema(dt, SchemaType.Source);

OracleCommandBuilder cb = new OracleCommandBuilder(da);

DataRow row = dt.NewRow();
row["Id"] = id;
row["RowidCol"] = rowId;
row["UrowidCol"] = rowId;
dt.Rows.Add(row);

da.Update(dt);

The following code retrieves the values from the ROWID and UROWID columns and then uses that value to retrieve the first row in the HR.Employees table:

int id = 1;
OracleString rowId = null;
OracleString urowId = null;

// get the ROWID and UROWID values
OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand("SELECT RowidCol, UrowidCol FROM
    MyRowIdTable WHERE Id = " + id, conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    rowId = dr.GetOracleString(0);
    urowId = dr.GetOracleString(1);

    Console.WriteLine(rowId + "; " + urowId);
}

int employeeId;
string firstName;
string lastName;

// get the row from the EMPLOYEES table
cmd = new OracleCommand("SELECT * FROM Employees WHERE ROWID = '" +
    rowId.Value + "'", conn);

dr = cmd.ExecuteReader();
if(dr.Read())
{
    employeeId = dr.GetInt32(0);
    firstName = dr.GetString(1);
    lastName = dr.GetString(2);

    Console.WriteLine(employeeId.ToString() + "; " + firstName + "; " +
        lastName);
}

conn.Close();

The console output is:

AAAHM7AAFAAAABWAAA; AAAHM7AAFAAAABWAAA
100; Steven; King

Timestamp

A data type used to represent a date/time value. The data type adds fractional seconds to the DATE type to improve granularity. The Timestamp data types takes a precision parameter that defines how accurate the data in the seconds field is stored—the precision value can range from 0 to 9 with a default of 6. There are three Timestamp data types:

TIMESTAMP
Variable-sized value ranging from 7 to 11 bytes representing the date/time.
TIMESTAMP WITH TIME ZONE
Fixed-sized value of 13 bytes that includes a time zone with the date/time. The time zone can be set using either a UTC offset—the number of hours ahead of or behind the current time at zero meridian Greenwich, England or Greenwich Mean Time (GMT)—or by time zone name in the V$TIMEZONE_NAMES table. You can get a list of valid regions by issuing the query:
SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES

TIMESTAMP WITH LOCAL TIME
A TIMESTAMP value similar to TIMESTAMP WITH TIME ZONE except the data is normalized to the time zone of the database server. The data type allows you to adjust the date/time to account for time zone differences including whether the time zone observes daylight savings time. The offset from UTC is not stored.

The following code creates a table containing TIMESTAMP, TIMESTAMP WITH TIMEZONE, and TIMESTAMP WITH LOCAL TIME ZONE columns:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyTimestampTable (Id NUMBER,
    TimestampCol TIMESTAMP, TimeStampTZCol TIMESTAMP WITH TIME ZONE,
    TimeStampLTZCol TIMESTAMP WITH LOCAL TIME ZONE)";
cmd.ExecuteNonQuery();

The following code inserts the current time into each of the Timestamp data type columns:

int id = 1;
DateTime timestampCol = DateTime.Now;
DateTime timestampTZCol = DateTime.Now;
DateTime timestampLTZCol = DateTime.Now;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
conn.Open();

OracleCommand cmd = conn.CreateCommand();
            
cmd.CommandText = "INSERT INTO MyTimestampTable VALUES (" + id + ", '" +
    timestampCol.ToString("dd MMM yyyy hh:mm:sstt") + "', '" +
    timestampTZCol.ToString("dd MMM yyyy hh:mm:sstt") + "', '" +
    timestampLTZCol.ToString("dd MMM yyyy hh:mm:sstt") + "')";

cmd.ExecuteNonQuery();

conn.Close();

The following code retrieves the values for the three Timestamp data types:

int id = 1;
DateTime timestampCol;
DateTime timestampTZCol;
DateTime timestampLTZCol;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand("SELECT TimestampCol,
    TimestampTZCol, TimestampLTZCol FROM MyTimestampTable WHERE Id = " +
    id, conn);
conn.Open();

// get the Timestamp values
OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    timestampCol = dr.GetDateTime(0);
    timestampTZCol = dr.GetDateTime(1);
    timestampLTZCol = dr.GetDateTime(2);

    Console.WriteLine(timestampCol.ToString() + "; " +
        timestampTZCol.ToString() + "; " +
        timestampLTZCol.ToString());
}
      
dr.Close();
conn.Close();

The console output is:

7/9/2004 1:43:31 PM; 7/9/2004 1:43:31 PM; 7/9/2004 10:43:31 AM

UriType

The UriType data type stores pointers to internal or external Uniform Resource Identifier (URI) resources—these can reference a document or a specific part of a document. The following subtypes are derived from the general UriType data type:

HttpURIType
A URL to a Web page or file accessed using Hyper-Text Transfer Protocol (HTTP).
DBUriType
A reference to data—a row, set of rows, or a single column—within the database. Data can be accessed using member functions that use syntax similar to XPath.
XdbUriType
Stores a reference to an XML document stored in the Oracle XML DB repository in the database.

You can create columns using the UriType or one of the specific subtypes.

The UriType contains the following functions that can be used on any of the subtypes:

Function Description
createUri(uri IN VARCHAR2) Constructs a URITYPE subtype. Each subtype has its own constructors:

DBUriType dbUriType()

HttpUriType httpUriType()

XDBUriType XDBUritype()

getBlob() Returns the BLOB pointed to by the URL.
getClob() Returns the value pointed to by the URL as a CLOB encoded using the database character set.
getContentType() Returns the MIME information for the URL.
getExternalUrl() Similar to getUrl() except it escapes the characters in the URL.
getUrl() Returns the URL stored in the data type.
getXML() Returns the XMLType object for the URI—the URI must point to a valid XML document.

The following code creates a table containing URITYPE, HTTPURITYPE, and DBURITYPE columns:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyUritypeTable (Id NUMBER,
    UriTypeCol SYS.URITYPE, HttpUriTypeCol SYS.HTTPURITYPE,
    DBUriTypeCol SYS.DBURITYPE)";
cmd.ExecuteNonQuery();

The following code inserts a row of data into the table. The row contains URLs to an MSDN article and a reference to the EMAIL value for the employee in the HR.EMPLOYEES table with EMPLOYEE_ID = 100:

int id = 1;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
conn.Open();

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO MyUritypeTable VALUES (" + id +
    ", SYS.URIFACTORY.GETURI('http://msdn.microsoft.com/data/default.aspx?
    pull=/library/en-us/dnadonet/html/msdnorsps.asp')" +
    ", SYS.HTTPURITYPE('msdn.microsoft.com/data/default.aspx?
    pull=/library/en-us/dnadonet/html/msdnorsps.asp')" +
    ", SYS.DBURITYPE('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=''100'']/EMAIL',
    NULL))";
cmd.ExecuteNonQuery();

conn.Close();

The following code retrieves the URLs for the three UriTypes and the contents of the HTTPURITYPE and DBURITYPE as CLOBs:

int id = 1;
string uriTypeCol;
string httpUriTypeCol;
string dBUriTypeCol;
OracleLob httpUriTypeColClob;
OracleLob dBUriTypeColClob;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand("SELECT e.UriTypeCol.getUrl(),
     e.HttpUriTypeCol.getUrl(), e.DBUriTypeCol.getUrl(),
     e.HttpUriTypeCol.getClob(), e.DBUriTypeCol.getClob() FROM
     HR.MyUritypeTable e WHERE Id = " + id, conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    // get the URIs
    uriTypeCol = dr.GetString(0);
    httpUriTypeCol = dr.GetString(1);
    dBUriTypeCol = dr.GetString(2);

    // get the HTML for the HTTPURITYPE column
    httpUriTypeColBlob = dr.GetOracleLob(3);
    // get the EMAIL value from HR.EMPLOYEES
    dBUriTypeColClob = dr.GetOracleLob(4);

    Console.WriteLine(uriTypeCol + "; " +
        httpUriTypeCol + "; " + dBUriTypeCol +
        "; HTTPURITYPE CLOB length = " + httpUriTypeColClob.Length +
        "; " + dBUriTypeColClob.Value);
}
      
dr.Close();
conn.Close();

The console output is:

http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/msdnorsps.asp;
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/msdnorsps.asp;
/ORADB/HR/EMPLOYEES/ROW[EMPLOYEE_ID='100']/EMAIL;
HTTPURITYPE CLOB length = 112964;
<?xml version="1.0"?><EMAIL>SKING</EMAIL>

User Defined

User-defined data types aggregate built-in data type and other user-defined data types. User-defined data types abstract the structure of real-world entities in application data. This type is a template for an object identified by a name and having both attributes (built-in types or other user-defined types) and methods.

The following code creates the user-defined type Employee_t:

OracleCommand cmd = conn.CreateCommand();
 cmd.CommandText = "CREATE OR REPLACE TYPE Employee_t
    IS OBJECT (Name VARCHAR(50), YearsService INTEGER)";
cmd.ExecuteNonQuery();

The following code creates a table with an Employee_t data type column:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyUserdefinedTable
    (Id NUMBER, Employee HR.EMPLOYEE_T)";
cmd.ExecuteNonQuery();
The following code inserts a row into the table:
OracleConnection conn = new OracleConnection(CONNECTSTRING);
conn.Open();

OracleCommand cmd = conn.CreateCommand();

cmd.CommandText = "INSERT INTO MyUserdefinedTable VALUES
    (1, EMPLOYEE_T('Bill Hamilton', 5))";
cmd.ExecuteNonQuery();

conn.Close();

The following code retrieves the data for the row:

int id = 1;
string employee_Name;
OracleNumber employee_YearsService;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand("SELECT Id, e.Employee.Name,
    e.Employee.YearsService FROM MyUserdefinedTable e WHERE Id = " + id,
    conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    id = dr.GetInt32(0);
    employee_Name = dr.GetString(1);
    employee_YearsService = dr.GetOracleNumber(2);

    Console.WriteLine(id.ToString() + "; " + employee_Name + "; " +
        employee_YearsService); 
}
      
dr.Close();
conn.Close();

The console output is:

1; Bill Hamilton; 5

XmlType

Data type stores an XML document in a table, using a CLOB under the covers. The data type has member functions to create XML, perform XPath queries on the document, extract data fragments, validate the XML, and return the contents.

The following code creates a table with an XMLType column:

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE MyXmltypeTable (Id NUMBER,
    XmlTypeCol SYS.XMLTYPE)";
cmd.ExecuteNonQuery();

The following code inserts a row and sets the XMLType column using the CREATEXML member function of XMLType:

int id = 1;
string xmltypeCol = @"<?xml version=""1.0""?><EMAIL>SKING</EMAIL>";

OracleConnection conn = new OracleConnection(CONNECTSTRING);
conn.Open();

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO MyXmltypeTable VALUES (" + id +
    ", SYS.XMLTYPE.CREATEXML('" + xmltypeCol + "'))";
cmd.ExecuteNonQuery();

conn.Close();      

The following code retrieves the value from the XMLType column using the GETSTRINGVAL member function of XMLType:

int id = 1;
string xmltypeCol;

OracleConnection conn = new OracleConnection(CONNECTSTRING);
OracleCommand cmd = new OracleCommand(
    "SELECT SYS.XMLTYPE.GETSTRINGVAL(xmltypeCol) FROM MyXmltypeTable WHERE
     Id = " + id, conn);
conn.Open();

OracleDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
    xmltypeCol = dr.GetString(0);

    Console.WriteLine(xmltypeCol);
}
      
dr.Close();
conn.Close();      

The console output is:

<?xml version="1.0"?><EMAIL>SKING</EMAIL>

Conclusion

Oracle provides data types that facilitate working with complex data including large objects stored either within or outside of the database, XML, generic (ANY) data, dates and times, and user-defined aggregate data structures. The Microsoft .NET Oracle Provider in the Microsoft .NET Framework 1.1 provides types within the System.Data.OracleClient namespace that let you easily access and manipulate data stored in these types.

Related Books

ADO.NET Cookbook

ADO.NET in a Nutshell


Bill Hamilton is a software architect specializing in designing, developing, and implementing distributed applications using Microsoft .NET and J2EE technologies. An early technology adopter, he frequently evaluates, recommends, and helps his clients use new technologies effectively. Bill has written the two ADO.NET books referenced aboive in the Related Books section.

Show:
© 2014 Microsoft