Advanced Data Access with ado.net and oracle

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

IntroductionAnybfileIntervallobrawref CursorrowIdTimeStampuritypeuser DefineDxmltypeConclusionRelated 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 TypeDescriptionAnyData types used to explicitly define data types that can hold any type of data.BFileA pointer to an operating system file containing unstructured binary data stored outside of the Oracle database.IntervalData types that specify a time interval.LOBData types that store unstructured binary data . rAWData types that store binary data that is not interpreted by Oracle.REF CURSORA PL / SQL pointer to a SQL cursor used to return a row from the database.ROWIDData types that represent the specific address of a row in the database. TimestampData types that extend the functionality of the DATE data type. URIData types that store Uniform Resource Identifiers (URIs) that reference a document or a specific part of a document.User-definedComplex data types created from basic Oracle data types and user-defined data types.XMLTypeData 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: TypeDescriptionOracleBFileClass that represents an Oracle BFILE data type OracleBinaryStructure that represents a variable-length stream of binary data.OracleBooleanStructure that represents a value returned from a database comparison between Oracle data. types.OracleDateTimeStructure that represents a date and time value ranging from January 1, 4712 BC to December 31, 4712 AD.OracleLobClass that represents a large binary object (LOB) stored in an Oracle database.OracleMonthSpanStructure that stores a time interval measured in months and REPRESENTS The Oracle Interval Year To Month Data Type.OraclenumBerstructure That Repr esents a fixed-precision and scale numeric value between -10-38 - 1 and 1038 - 1 stored in an Oracle database.OracleStringStructure that represents a variable-length stream of characters stored in an Oracle database.OracleTimeSpanStructure that stores a time interval and represents The Oracle Interval Day To Second Data Type.OracletypeENUMERATION OF ORACLETYPEENUMERATION OF ORACLE DATA TYPES

For Each Data Type, The Create The Data Type Wtem.

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.Anddata

...................... ..

Sys.anydataset

Contains a set of data and its type description.

The Following Cred 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 expenery casting an argument and returning it as an affa 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 ")

ConvertRef (RF in ")

ConvertCollection (N in "Collection_1")

THE FOLLOWING CODE INSERTS Two Rows Into The Table-a Varchar2 in The Andata Column of The First Row and a Number in The anydata column of the second row:

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.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.Nydatacol.gettypename () from myanytable e", conn);

Cn.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 no copy 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 "")

GetRef (Self in Anydata, Rf Out Nocopy "")

GetCollection (Self in coliadata, out no copy "")

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 varcha2

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' ThenL_rc: = DATA.GETVARCHAR2 (L_VARCHAR2);

Else

l_varchar2: = 'Error: unknown data type';

End Case;

Return L_Varchar2;

END;

.............................

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new OracleCommand ("SELECT ID,

E.Anydatacol.gettypename (), getanydata (anydatacol) DataValue

From myanytable e ", conn);

Cn.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

Sys.Number; 100

You Can Also Store Instances of User-Defined Data Types in An Anytype Field. The Following 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 . -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 External The DataBase In an Operating System File. It has a maximum size of 4GB. Data Stored in these Column 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 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);

Cn.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 DataTaPes:

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 Size 5-Byte Data Type That Stores A Time Interval in Years and Months.

The Following Cred Creates A Table Containing Interval Day To Second and Interval Year To Month Data Type Column:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MyIntervalTable (ID Number,

Intervalmcol 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, Intervalmcol, 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 Intervalmcol = 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 ["Intervalmcol"] = intervaltmcol.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 Intervalmcol;

OracleTimeSpan Intervaldtscol;

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new OracleCommand ("Select Intervalmcol,

Intervaldtscol from MyInterValTable where id = " id, conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

Intervalmcol = Dr.GetoracleMonthspan (0);

Intervaldtscol = Dr.GETORACLETIMESPAN (1);

Console.writeline (Intervalmcol.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 (eg, 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 Column. 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 Creates A Table Containing Blob, Clob, And NClob Column:

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 Column: OpenFiledialog Off = 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);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

IF (! Dr.Indbnull (0))

BLOB = Dr.GETORACLOB (0);

MemoryStream MS = New MemoryStream ((byte []) blob.value);

// Do Something with the street

Ms.close ();

IF (! Dr.isdbnull (1))

Clob = Dr.GETORACLOB (1) .Value.toString ();

IF (! Dr.isdbnull (2))

NClob = Dr.GetoracleLob (2) .value.toT7tring (); 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 type..

There Are Two Raw Types:

Raw

Has The Same Maximum Length As Varchar2 (32767 Bytes) - The lineth must be specified by 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 Cred 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 Cred Creates A Row and INSERTS DATA INTO THE RAW AND Long Raw Column-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 [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);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

IF (! Dr.Indbnull (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 curd 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 backn:

Create or replace package get_employees astype t_cursor is ref.

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 curd 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 curd parameter to Retrieve The Result Set

Cmd.Parameters.Add ("cur_employees", oracletype.cursor) .direction =

ParameterDirection.output;

// Open the connection and create the datareader

Cn.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:

STEVEN;

NEENA; kochhar; nkochhar;

102; Lex; de Haan; Ldehaan

Alexander; Hunold; Ahunold;

Bruce; ernst;

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 activity binary displayed using the format bbbbbbbb.ssss.fffff where:

Bbbbbbbb is the block

SSSS Is The Sequence Withnin The Block

FFFF is The File ID

UroWID

The Universal Rowid (Urowid) Supports Both Logical and Physical Rowids And Rowids of Foreign Tables include 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 can not be changed or deleted. The ................ ..

The Following Cred 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 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);

Cn.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 ["" "= rowid;

Dt.Rows.Add (Row);

Da.UPDATE (DT);

THE FOLLOWING CODE RETRIEVES The Values ​​from the rowid and urowid columns and the usned there.

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

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

RowId = Dr.Getoraclestring (0);

Urowid = Dr.GETORACLESTRING (1);

Console.writeline (Rowid ";" urowid);

}

Int EmployeeID;

String dimstname;

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

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 Ran 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 questsuing 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 store.

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,

TimeStamp WIMESTAMP with local time zone "; cmd.executenonquery ();

The Following Code Inserts The Current Time Into Each of The TimeStamp Data Type Column:

INT ID = 1;

DateTime TimeStampcol = datetime.now;

DateTime TimeStamptzcol = datetime.now;

DateTime TimeStampltzcol = datetime.now;

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.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);

Cn.open ();

// Get the timestamp value

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

TimeStampCol = Dr.GetdateTime (0);

TimeStamptzcol = Dr.getdateTime (1);

TimeStampltzcol = Dr.GetdateTime (2);

Console.writeline (TimeStampcol.tostring () ";

TimeStamptzcol.toTostring () ";"

Timestampltzcol.toTString ());

}

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 Amuritype

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 an xml document stores. 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:

FunctionDescriptioncreateUri (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 .

The Following Cred Creates A Table Containing Uritype, Httpuritype, and Dburitype Column:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MyurityPetable (ID Number,

URITYPECOL SYS.URitype, httpuritypecol sys.httpuritype,

DBURITYPECOL SYS.DBURITYPE "; cmd.executenon query ();

The Following Code Inserts a Row of Data Into The 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);

Cn.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;

Oracleob 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 FROM

Hr.MyurityPetable e where id = " ID, conn);

Cn.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 columnhttpuritypecolblob = Dr.getraclelob (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;

SKING

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 Cred Creates The User-Defined Type Employee_t:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE OR REPLACE TYPLOYEE_T

IS Object (Name Varchar (50), Yearsservice Integer ";

cmd.executenonquery ();

The Following Cred 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 (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.employe.name,

E.employee.YearSservice from myUserDefinedTable e where id = " id,

CONN);

Cn.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 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 = @ " SKING "; OracleConnection conn = new OracleConnection (ConnectString);

Cn.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 use the getstringvalemr 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);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

XMLTYPECOL = Dr.getstring (0);

Console.writeLine (XMLTYPECOL);

}

Dr.close ();

CONN.CLOSE ();

The console output is:

SKING

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 forin 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 Reference Aboive In The Related Books Section.top of Page