26 Native Datatypes

Use of Unicode Data in an Oracle Database

Unicode is an effort to have a unified encoding of every character in every language known to man. It also provides a way to represent privatel y-defined characters. A database column that stores Unicode can store text written in any language.

Oracle users deploying glo balized applications have a strong need to store Unicode data in Oracle databases. They need a datatype which is guaranteed to be Uni code regardless of the database character set.

Oracle supports a reliable Unicode datatype through NCHAR, N VARCHAR2, and NCLOB. These datatypes are guaranteed to be Unicode encoding and always use character length semant ics. The character sets used by NCHAR/NVARCHAR2 can be either UTF8 or AL16UTF16, depending on the setting of the national character set when the database is created. These datatypes allow character data in Unicode to be stored in a database that may or may not use Unicode as database character set.

LOB Character Datatypes

The LOB datatypes for character data are CLOB and NCLO B. They can store up to 8 terabytes of character data (CLOB) or national character set data (NCLOB).

LONG Datatype

Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems.

LONG datatype columns are used in the data dictionary to store the text of view definitions. You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.

Overview of Numeric Datatypes

The numeric datatypes store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation (that is, is "not a number" or NAN).

column_name NUMBER 

column_name NUMBER (pre
cision, scale) 

column_name NUMBER (*, scale) 

ROUND((length(p)+s)/2))+1

Overview of DATE Datatype

The DATE datatype stores point-in-time va lues (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the ho urs, the minutes, and the seconds (after midnight).

Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era, or 'AD'). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are t he default.

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each , corresponding to century, year, month, day, hour, minute, and second.

For input and output of dates, the standard Oracle dat e format is DD-MON-YY, as follows:

'13-NOV-92' 

You can change this default date format for an instance with the parameter NLS_DATE_FORMAT. You can also change it during a user session with the ALTER SE SSION statement. To enter dates that are not in standard Oracle date format, use the TO_DATE function with a format mask:

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY') 



Oracle stores time in 24-hour format—HH:MI:SS. By d
efault, the time in a date field is 00:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the da
te portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE function
 with a format mask indicating the time portion, as in:
INSERT INTO birthdays (bname, bday) VALUES 
 ('ANDY',TO_DATE('13-AUG-66 12:56 A.M.','DD-MON-YY HH:MI A.M.')); 



Use of Julian Dates
Julian dates allow continuous dating by the number of days from a common reference. (The reference is 01-01-4712 years BCE, s
o current dates are somewhere in the 2.4 million range.) A Julian date is nominally a noninteger, the fractional part being a portion
 of a day. Oracle uses a simplified approach that results in integer values. Julian dates can be calculated and interpreted different
ly. The calculation method used by Oracle results in a seven-digit number (for dates most often used), such as 2449086 for 08-APR-93.








Note:
Oracle Julian date
s might not be compatible with Julian dates generated by other date algorithms.




The format mask 'J' can be used with date functions (TO_DATE or TO_CHAR) to convert date data into Julian dates. For examp
le, the following query returns all dates in Julian date format:

SELECT TO_CHAR (hire_date, 'J') FROM e
mployees; 



You must use the TO_NUMBER function if you want to use J
ulian dates in calculations. You can use the TO_DATE function to enter Julian dates:
INSER
T INTO employees (hire_date) VALUES (TO_DATE(2448921, 'J')); 



 
Date Arithmetic
Oracle date arithmetic takes into account the anomalies of the calendars used throughout history. For ex
ample, the switch from the Julian to the Gregorian calendar, 15-10-1582, eliminated the previous 10 days (05-10-1582 through 14-10-15
82). The year 0 does not exist.
You can enter missing dates into the database, but they are ignored in date arithmetic and tre
ated as the next "real" date. For example, the next day after 04-10-1582 is 15-10-1582, and the day following 05-10-1582 is also 15-1
0-1582.







Note:
This discus
sion of date arithmetic might not apply to all countries' date standards (such as those in Asia).





 

Centuries and the Year 2000
Oracle stores year data with the century information. For example, the Oracle datab
ase stores 1996 or 2001, and not simply 96 or 01. The DATE datatype always stores a four-digit year internally, and all
other dates stored internally in the database have four digit years. Oracle utilities such as import, export, and recovery also deal
with four-digit years.

 
Daylight Savings Support
Oracl
e Database provides daylight savings support for DATETIME datatypes in the serv
er. You can insert and query DATETIME values based on local time in a specific region. The DATETIME datatyp
es TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LO
CAL TIME ZONE are time-zone aware.







See Also:


Oracle Database Application Developer's Guide - Fundamentals for more information about centuries and da
te format masks

Oracle Database SQL
 Reference for information about date format codes





 
Time Zones
You can include the time zon
e in your date/time data and provides support for fractional seconds. Three new datatypes ar
e added to DATE, with the following differences:




Datatype
Time Zone
Fr
actional Seconds


DATE
No
No

<
/a>TIMESTAMP
No
Yes

<
a id="sthref3841" name="sthref3841">TIMESTAMP

WITH TIME ZONE
Explicit
Yes

TIMESTAMP

WITH LOCAL TIME ZONE
R
elative
Yes



<
p>TIMESTAMP WITH LOCAL TIME ZONE is stored in the database time zone
. When a user selects the data, the value is adjusted to the user's session time zone.

For example, a San Francisco database h
as system time zone = -8:00. When a New York client (session time zone = -5:00) inserts into or selects from the San Francisco databa
se, TIMESTAMP WITH LOCAL TIME ZONE data is adjusted as follows:


The New York client inserts TIMESTAMP'1998-1-23 6:00:00-5:00' into a TIMESTAMP WITH LOCAL TIME ZONE column in the San Francisco database. The inserted data is stor
ed in San Francisco as binary value 1998-1-23 3:00:00.

When the New York client selects tha
t inserted data from the San Francisco database, the value displayed in New York is ‘1998-1-23 6:00:00'.


A San Francisco client, selecting the same data, see the value '1998-1-23 3:00:00'.






Note:
To avoid unexpected results in
 your DML operations on datatime data, you can verify the database and session time zones by querying the built-in SQL functions DBTIMEZONE and SESSIONTIMEZONE. If the database time zone or the session time zone has not been set manually, O
racle uses the operating system time zone by default. If the operating system time zone is not a valid Oracle time zone, then Oracle
uses UTC as the default value.










See Also:
Oracle Database SQL Reference for details about the syntax of creating and entering data in time stamp colum
ns







 
 
Overview of LOB Datatypes
The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store and manipulate large blocks of unstructured data (such as text, graphic images, video
clips, and sound waveforms) in binary or character format. They provide efficient, random, piece-wise access to the data. Oracle reco
mmends that you always use LOB datatypes over LONG datatypes. You can perform parallel queries (but not parallel DML or
DDL) on LOB columns.

LOB datatypes differ from LONG and LONG RAW datatypes in several w
ays. For example:


A table can contain multiple LOB columns but only one LONG column.

A table containing one or more LOB columns can be partitioned, but a table containing a LONG colu
mn cannot be partitioned.

The maximum size of a LOB is 8 terabytes, and the maximum size of a LONG
 is only 2 gigabytes.

LOBs support random access to data, but LONGs support only seq
uential access.

LOB datatypes (except NCLOB) can be attributes of a user-defined object typ
e but LONG datatypes cannot.

Temporary LOBs that act like local variables can be used to pe
rform transformations on LOB data. Temporary internal LOBs (BLOBs, CLOBs, and NCLOBs) are crea
ted in a temporary tablespace and are independent of tables. For LONG datatypes, however, no temporary structures are av
ailable.

Tables with LOB columns can be replicated, but tables with LONG columns cannot.
SQL statements define LOB columns in a table and LOB attributes in a user-defined object type. When defining LOBs in a
 table, you can explicitly specify the tablespace and storage characteristics for each LOB.
LOB datatypes can be stored inline
 (within a table), out-of-line (within a tablespace, using a LOB locator), or in an external file (BFILE datatypes). Wit
h compatibility set to Oracle9i or higher, you can use LOBs with SQL VARCHAR operators and functions.








See Also:


Oracle Database SQL Reference for a list of diff
erences between the LOB datatypes and the LONG and LONG RAW datatypes

Oracle Database Application Developer's Guide - Large Objec
ts for more information about LOB storage and LOB locators




 
 
BLOB Datatype
The BLOB d
atatype stores unstructured binary data in the database. BLOBs can store up to 8 terabytes of binary data.

BLOBs participate fully in transactions. Changes made to a BLOB value by the DBMS_LOB package, PL/SQ
L, or the OCI can be committed or rolled back. However, BLOB locators cannot span transactions or sessions.

 
 
CLOB and NCLOB Datatypes
The CLOB and <
code>NCLOB datatypes store up to 8 terabytes of character data in the database. CLOBs store database character se
t data, and NCLOBs store Unicode national character set data. For varying-width
 database character sets, the CLOB value is stored using the two-byte Unicode character set, which has a fixed width. Or
acle translates the stored Unicode value to the character set requested on the client or on the server, which can be fixed-width or v
arying width. When you insert data into a CLOB column using a varying-width character set, Oracle converts the data into
 Unicode before storing it in the database.
CLOBs and NCLOBs participate fully in transactions. Chan
ges made to a CLOB or NCLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed
 or rolled back. However, CLOB and NCLOB locators cannot span transactions or sessions. You cannot create a
n object type with NCLOB attributes, but you can specify NCLOB parameters in a method for an object type.




 
BFILE Datatype

The BFILE datatype stores unstructured binary data in operating-system files outside
the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. BFILEs can store up to 8 terabytes of data.
BFILEs are read only; you cannot modify them. They support
only random (not sequential) reads, and they do not participate in transactions. The underlying operating system must maintain the fi
le integrity, security, and durability for BFILEs. The database administrator must ensure that the file exists and that
Oracle processes have operating-system read permissions on the file.
<
a id="i4146" name="i4146">
 
Overview of RAW and LONG RAW Datatypes

<
div class="inftblnote">





See Also:
Oracle
 Database Globalization Support Guide for more information about national character set data and Unicode




 



 




Note:
The LONG
 RAW datatype is provided for backward compatibility with existing applications. For new applications, use the BLOB and BFILE datatypes for large amounts of binary data. 
Oracle also recommends that you convert existi
ng LONG RAW columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG RAW functionality has been
 static for several releases.




The RAW an
d LONG RAW datatypes are used for data that is not to be interpreted (not converted when moving data betwee
n different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, LONG RA
W can be used to store graphics, sound, documents, or arrays of binary data. The interpretation depends on the use.
RAW is a variable-length datatype like th
e VARCHAR2 character datatype, except Oracle Net Services (which connects user sessions to the instance) and the Import
and Export utilities do not perform character conversion when transmitting RAW or LONG RAW dat
a. In contrast, Oracle Net Services and Import/Export automatically convert CHAR, VARCHAR2, and LONG<
/code> data between the database character set and the user session character set, if the two character sets are different.

Wh
en Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the
binary data is represented in hexadecimal form with one hexadecimal character representing every four bits of RAW data.
For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.
LONG RAW data cannot be indexed, but <
code>RAW data can be indexed.







Se
e Also:
Oracle Database Application Develope
r's Guide - Fundamentals for information about other restrictions on the LONG RAW datatype





 
<
div class="sect1"> 
Overview of ROWID and UROWID Datatypes
Oracle uses a ROWID datatype to store the address (rowid) of every row in
 the database.


Physical rowids store the addre
sses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, an
d index partitions and subpartitions.

Logical rowids store the addresses of rows in index-organized tables.

A single datatype called the universal rowid, or UROWID, supports both logi
cal and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway.
A column of
 the UROWID datatype can store all kinds of rowids. The value of the COMPATIBLE initialization parameter mu
st be set to 8.1 or higher to use UROWID columns.







See Also:
"Rowids in Non-Oracle Databases "




 
The ROWID Pseudocolumn
Each table in an Oracle database internally has a pse
udocolumn named ROWID. This pseudocolumn is not evident when listing the structure of a table by executing a SELECT * FROM ... statement, or a DESCRIBE ... statement using SQL*Plus, nor does the pseudocolu
mn take up space in the table. However, each row's address can be retrieved with a SQL query using the reserved word ROWID as a column name, for example:

SELECT ROWID, last_name FROM employees; 



You cannot set t
he value of the pseudocolumn ROWID in INSERT or UPDATE statements, and you cannot delete a ROWID value. Oracle uses the ROWID values in the pseudocolumn R
OWID internally for the construction of indexes.
You can reference rowids in the pseudocolumn ROWID like o
ther table columns (used in SELECT lists and WHERE clauses), but rowids are not stored in the database, nor
 are they database data. However, you can create tables that contain columns having the ROWID datatype, although Oracle
does not guarantee that the values of such columns are valid rowids. The user must ensure that the data stored in the ROWID column truly is a valid ROWID.








See Also:
"How Rowids Are Used "





 
 
Physical Rowids
Physical rowids provide the fastest possible access to a row of a given t
able. They contain the physical address of a row (down to the specific block) and allow you to retrieve the row in a single block acc
ess. Oracle guarantees that as long as the row exists, its rowid does not change. These performance and stability qualities make rowi
ds useful for applications that select a set of rows, perform some operations on them, and then access some of the selected rows agai
n, perhaps with the purpose of updating them.
Every row in a nonclustered table is assigned a unique rowid that corresponds to
 the physical address of a row's row piece (or the initial row piece if the row is chained among multiple row pieces). In the case of
 clustered tables, rows in different tables that are in the same data block can have the same rowid.
A row's assigned rowid remains unchanged unless the row is exported and impo
rted using the Import and Export utilities. When you delete a row from a table and then commit the encompassing transaction, the dele
ted row's associated rowid can be assigned to a row inserted in a subsequent transaction.
A physical rowid datatype has one of
 two formats:


The extended rowid format supports tablespace-relative data block address
es and efficiently identifies rows in partitioned tables and indexes as well as nonpartitioned tables and indexes. Tables and indexes
 created by an Oracle8i (or higher) server always have extended rowids.

A restricted ro
wid format is also available for backward compatibility with applications developed with Oracle database version 7 or earlie
r releases.
 
Extended Rowids
Ext
ended rowids use a base 64 encoding of the physical address for each row selected. The encoding characters are A-Z, a-z, 0-9, +
, and /. For example, the following query:
SELECT ROWID, last_name FROM employees W
HERE department_id = 20; 



can return the following row information:
ROWID              LAST
_NAME 
------------------ ---------- 
AAAAaoAATAAABrXAAA BORTINS 
AAAAaoAATAAABrXAAE RUGGLES 
AAAAaoAATAAABrXAAG CHEN 
AAAAaoAATAAABr
XAAN BLUMBERG 



An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:



OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.

FFF: The tablespace-relative datafile number of the datafile that contains
the row (file AAT in the example).

BBBBBB: The data block tha
t contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tab
lespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.

RRR: The row in the block.
You can retrieve the data object number f
rom data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. For example, the followi
ng query returns the data object number for the employees table in the SCOTT schema:
SELECT DATA_OBJECT_ID FROM DBA_OBJECTS 
    WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMPLOYEES'; 



You can also us
e the DBMS_ROWID package to extract information from an extended rowid or to convert a rowid from extended format to res
tricted format (or vice versa).







See Als
o:
Oracle Database Application Developer's G
uide - Fundamentals for information about the DBMS_ROWID package





 
Restricted Rowids
Restricted rowids use a binary representation of the physical address for each row selected. When qu
eried using SQL*Plus, the binary representation is converted to a VARCHAR2/hexadecimal representation. The following que
ry:
SELECT ROWID, last_name FROM employees 
    WHERE department_id = 30; 



can return the f
ollowing row information:
ROWID              ENAME 
------------------ ---------- 
00000DD5.0000.0001 K
RISHNAN 
00000DD5.0001.0001 ARBUCKLE 
00000DD5.0002.0001 NGUYEN 



As shown, a restricted rowid's VARCHAR2/hexadecimal representation is in a three-piece format, block.row.file:


<
p>The data block that contains the row (block DD5 in the example). Block numbers are relative to their datafile, not tablespace. Two rows with identical block numbers could reside in two different datafiles of the same tablespace.

The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of
 a given block always start with 0.

The datafile that contains the row (file 1 in the e
xample). The first datafile of every database is always 1, and file numbers are unique within a database.

 
Examples of Rowid Use
You can use the function SUBSTR to break the data in a rowid into its compon
ents. For example, you can use SUBSTR to break an extended rowid into its four components (database object, file, block,
 and row):
SELECT ROWID, 
       SUBSTR(ROWID,1,6) "OBJECT", 
       SUBSTR(ROWID,7,3) "FIL", 
       S
UBSTR(ROWID,10,6) "BLOCK", 
       SUBSTR(ROWID,16,3) "ROW" 
       FROM products; 

ROWID               OBJECT  FIL  BLOCK   ROW 
--
----------------  ------  ---  ------  ---- 
AAAA8mAALAAAAQkAAA  AAAA8m  AAL  AAAAQk  AAA 
AAAA8mAALAAAAQkAAF  AAAA8m  AAL  AAAAQk  A
AF 
AAAA8mAALAAAAQkAAI  AAAA8m  AAL  AAAAQk  AAI 



Or you can use SUBSTR to break a restricted rowid into its
 three components (block, row, and file):
SELECT ROWID, SUBSTR(ROWID,15,4) "FILE", 
       SUBSTR(ROWID
,1,8) "BLOCK", 
       SUBSTR(ROWID,10,4) "ROW" 
       FROM products; 

ROWID               FILE  BLOCK     ROW 
------------------
 ----  --------  ---- 
00000DD5.0000.0001  0001  00000DD5  0000 
00000DD5.0001.0001  0001  00000DD5  0001 
00000DD5.0002.0001  0001
00000DD5  0002 



Rowids can be useful for revealing information about the physical storage of a table's data. For example,
 if you are interested in the physical location of a table's rows (such as for table striping), the following query of an extended ro
wid tells how many datafiles contain rows of a given table:
SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3))) "
FILES" FROM tablename; 

  FILES 
-------- 
    2 










See Also:


Oracle Database SQL Reference

PL/SQL User's Guide and Reference

Oracle Database Performance Tuning Guide
for more examples using rowids





 
 
How R
owids Are Used
Oracle uses rowids internally for the construction of indexes.
 Each key in an index is associated with a rowid that points to the associated row's address for fast access. End users and applicati
on developers can also use rowids for several important functions:


Rowids are the fastest means of acces
sing particular rows.

Rowids can be used to see how a table is organized.

R
owids are unique identifiers for rows in a given table.
Before you use rowids in DML statements, they should be veri
fied and guaranteed not to change. The intended rows should be locked so they cannot be deleted. Under some circumstances, requesting
 data with an invalid rowid could cause a statement to fail.
You can also create tables with columns defined using the R
OWID datatype. For example, you can define an exception table with a column of datatype ROWID to store the rowids
 of rows in the database that violate integrity constraints. Columns defined using the ROWID datatype behave like other
table columns: values can be updated, and so on. Each value in a column defined as datatype ROWID requires six bytes to
store pertinent column data.



 
 
Logical Rowids

<
/a>Rows in
 index-organized tables do not have permanent physical addresses—they are stored in the index leaves and can move within the b
lock or to a different block as a result of insertions. Therefore their row identifiers cannot be based on physical addresses. Instea
d, Oracle provides index-organized tables with logical row identifiers, called logical rowids, that are based on the
 table's primary key. Oracle uses these logical rowids for the construction of secondary indexes on index-organized tables.
Each logical rowid used in a secondary index includes a physical guess, which identifie
s the block location of the row in the index-organized table at the time the guess was made; that is, when the secondary index was cr
eated or rebuilt.
Oracle can use guesses to probe into the leaf block directly, bypassing the full key search. This ensures th
at rowid access of nonvolatile index-organized tables gives comparable performance to the physical rowid access of ordinary tables. I
n a volatile table, however, if the guess becomes stale the probe can fail, in which case a primary key search must be performed.

The values of two logical rowids are considered equal if they have the same primary key values but different guesses.
 
Comparison of Lo
gical Rowids with Physical Rowids
Logical rowids are similar to the physical rowids in the following ways:


Logical rowids are accessible through the ROWID pseudocolumn.
You can use the ROWID pseudocolumn to select logical rowids from an index-organized table. The SELECT ROWID statement returns a
n opaque structure, which internally consists of the table's primary key and the physical guess (if any) for the row, along with some
 control information.
You can access a row using predicates of the form WHERE ROWID = valu
e, where value is the opaque structure returned by SELECT ROWID.


Access through the logical rowid is the fastest way to get to a specific row, although it can require more than
one block access.

A row's logical rowid does not change as long as the primary key value does not change
. This is less stable than the physical rowid, which stays immutable through all updates to the row.

Log
ical rowids can be stored in a column of the UROWID datatype
One difference between physical and logica
l rowids is that logical rowids cannot be used to see how a table is organized.







Note:
An opaque type is one whose internal structure is not k
nown to the database. The database provides storage for the type. The type designer can provide access to the contents of the type by
 implementing functions, typically 3GL routines.











See Also:
"Overvi
ew of ROWID and UROWID Datatypes"





 
Guesses in Logical Rowids
When a row's physical location changes, the logical rowid r
emains valid even if it contains a guess, although the guess could become stale and slow down access to the row. Guess information ca
nnot be updated dynamically. For secondary indexes on index-organized tables, however, you can rebuild the index to obtain fresh gues
ses. Note that rebuilding a secondary index on an index-organized table involves reading the base table, unlike rebuilding an index o
n an ordinary table.
Collect index statistics with the DBMS_STATS package or ANALYZE statement to keep track of the staleness of guesses, so Oracle does not use them unnecessarily. This is particularly
important for applications that store rowids with guesses persistently in a UROWID column, then retrieve the rowids late
r and use them to fetch rows.
When you collect index statistics with the DBMS_STATS package or ANALYZE statement, Oracle checks whether the existing guesses are still valid and records the percentage of stale/valid guesses in the dat
a dictionary. After you rebuild a secondary index (recomputing the guesses), collect index statistics again.

In general, logic
al rowids without guesses provide the fastest possible access for a highly volatile table. If a table is static or if the time betwee
n getting a rowid and using it is sufficiently short to make row movement unlikely, logical rowids with guesses provide the fastest a
ccess.







See Also:
Oracle Database Performance Tuning Guide for more informa
tion about collecting statistics







 
 
Rowids in Non-Oracle Databases
Oracle database applications can be run against non-Oracle database servers using SQL*Connect. The format of rowids var
ies according to the characteristics of the non-Oracle system. Furthermore, no standard translation to VARCHAR2/hexadeci
mal format is available. Programs can still use the ROWID datatype. However, they must use a nonstandard translation to
hexadecimal format of length up to 256 bytes.
Rowids of a non-Oracle database can be stored in a column of the UROWID datatype.








See Also:


Oracle Call Interface Programmer'
s Guide for details on handling rowids with non-Oracle systems

"Overview of RO
WID and UROWID Datatypes"







 
 
Overview of ANSI, DB2, and SQL/DS Datatypes

SQL statements that create tables and clusters can also use ANSI datatypes a
nd datatypes from IBM's products SQL/DS and DB2. Oracle recognizes the ANSI or IBM datatype name that differs from the Oracle datatyp
e name, records it as the name of the datatype of the column, and then stores the column's data in an Oracle datatype based on the co
nversions.







See Also:
Oracle Database SQL Reference
 for more information about the conversions





 
 
Overview
 of XML Datatypes
Oracle provides the XMLType datatype to handle
 XML data.
 
 
XMLType Datatype
XMLType can be used like any other user-defined type. XMLType can be used as the datatype of columns in tables and views. Vari
ables of XMLType can be used in PL/SQL stored procedures as parameters, return values, and so on. You can also use XMLTy
pe in PL/SQL, SQL and Java, and through JDBC and OCI.
A number of useful functions that operate on XML content have been provi
ded. Many of these are provided both as SQL functions and as member functions of XMLType. For example, function extract(
) extracts a specific node(s) from an XMLType instance. You can use XMLType in SQL queries in the same way
as any other user-defined datatypes in the system.







See Also:


<
em>Oracle XML Developer's Kit Programmer's Guide

Oracle XML DB Developer's Guide

Oracle Streams Advanced Queuing User's Guide and Reference for information about using XM
LType with Advanced Queuing

Chapter 1, " Introduction to the Oracle Da
tabase"





<
/div>

 
 
Overview of URI Datatypes

A URI, or uniform resource identifier, is a generalized kind of URL. Like a URL, it can reference any document, and can reference
 a specific part of a document. It is more general than a URL because it has a powerful mechanism for specifying the relevant part of
 the document. By using UriType, you can do the following:


Create table columns that point
to data inside or outside the database.

Query the database columns using functions provided by Uri
Type.






See Also:
Oracle XML DB Developer's Guide


<
!-- class="inftblnotealso -->


 
 

Overview of Data Conversion
In some cases, Oracle supplies data of one dataty
pe where it expects data of a different datatype. This is allowed when Oracle can automatically convert the data to the expected data
type.







See Also:
Oracle Database SQL Reference for the rules for implicit d
atatype conversions















Previous


Next


Copyright © 1993, 2003
 Oracle Corporation

All Rights Reserved.





Home


Book List


Contents


Index


Master Index


Feedback