Oracle9i Application Developer's Guide - Fundamentals
Release 1 (9.0.1)

Part Number A88876-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Selecting a Datatype

This chapter discusses how to use Oracle built-in datatypes in applications. Topics include:

Summary of Oracle Built-In Datatypes

A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle to treat values of one datatype differently from values of another datatype. For example, Oracle can add values of NUMBER datatype, but not values of RAW datatype.

Oracle supplies the following built-in datatypes:

Another datatype, ROWID, is used for values in the ROWID pseudocolumn, which represents the unique address of each row in a table.

See Also:

See Oracle9i SQL Reference for general descriptions of these datatypes, and see Oracle9i Application Developer's Guide - Large Objects (LOBs) for information about the LOB datatypes. 

Table 3-1 summarizes the information about each Oracle built-in datatype.

Table 3-1 Summary of Oracle Built-In Datatypes  
Datatype  Description  Column Length and Default  

CHAR (size [BYTE | CHAR]) 

Fixed-length character data of length size bytes or characters. 

Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (single-byte or multibyte) before setting size. 

VARCHAR2 (size [BYTE | CHAR]) 

Variable-length character data, with maximum length size bytes or characters. 

Variable for each row, up to 4000 bytes per row. Consider the character set (single-byte or multibyte) before setting size. A maximum size must be specified. 

NCHAR (size) 

Fixed-length Unicode character data of length size characters. 

Fixed for every row in the table (with trailing blanks). Column size is the number of characters. (The number of bytes is 2 times this number for the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 2000 bytes per row. Default is 1 character.  

NVARCHAR2 (size) 

Variable-length Unicode character data of length size characters. A maximum size must be specified. 

Variable for each row. Column size is the number of characters. (The number of bytes may be up to 2 times this number for a the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 4000 bytes per row. Default is 1 character.  


Single-byte character data 

Up to 232 - 1 bytes, or 4 gigabytes. 


Unicode national character set (NCHAR) data. 

Up to 232 - 1 bytes, or 4 gigabytes.  


Variable-length character data. 

Variable for each row in the table, up to 232 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.  

NUMBER (p, s)  

Variable-length numeric data. Maximum precision p and/or scale s is 38. 

Variable for each row. The maximum space required for a given column is 21 bytes per row. 


Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E. 

Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS_DATE_FORMAT parameter.  


A period of time, represented as years and months. The precision value specifies the number of digits in the YEAR field of the date. The precision can be from 0 to 9, and defaults to 2 for years. 

Fixed at 5 bytes. 

INTERVAL DAY (precision) TO SECOND (precision)


A period of time, represented as days, hours, minutes, and seconds. The precision values specify the number of digits in the DAY and the fractional SECOND fields of the date. The precision can be from 0 to 9, and defaults to 2 for days and 6 for seconds. 

Fixed at 11 bytes. 

TIMESTAMP (precision) 

A value representing a date and time, including fractional seconds. (The exact resolution depends on the operating system clock.)

The precision value specifies the number of digits in the fractional second part of the SECOND date field. The precision can be from 0 to 9, and defaults to 6 

Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter. 


A value representing a date and time, plus an associated time zone setting. The time zone can be an offset from UTC, such as '-5:0', or a region name, such as 'US/Pacific'

Fixed at 13 bytes. The default is determined by the NLS_TIMESTAMP_TZ_FORMAT initialization parameter. 


Similar to TIMESTAMP WITH TIME ZONE, except that the data is normalized to the database time zone when stored, and adjusted to match the client's time zone when retrieved. 

Varies from 7 to 11 bytes, depending on the precision. The default is determined by the NLS_TIMESTAMP_FORMAT initialization parameter. 


Unstructured binary data  

Up to 232 - 1 bytes, or 4 gigabytes. 


Binary data stored in an external file  

Up to 232 - 1 bytes, or 4 gigabytes. 

RAW (size) 

Variable-length raw binary data  

Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.  


Variable-length raw binary data 

Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.  


Binary data representing row addresses 

Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.  

Representing Character Data

Use the character datatypes to store alphanumeric data:

When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:

Space Usage

Comparison Semantics

Future Compatibility

CHAR, VARCHAR2, and LONG data is automatically converted from the database character set to the character set defined for the user session by the NLS_LANGUAGE parameter, where these are different.

Column Lengths for Single-Byte and Multibyte Character Sets

The lengths of CHAR and VARCHAR2 columns can be specified as either bytes or characters.

The lengths of NCHAR and NVARCHAR2 columns are always specified in characters, making them ideal for storing Unicode data, where a character might consist of multiple bytes.

-- ID contains only single-byte data, up to 32 bytes.
-- NAME contains data in the database character set. The 32 characters might
-- be physically stored as more than 32 bytes, if the database character set 
-- multibyte characters.
-- BIOGRAPHY can represent 2000 characters in any Unicode-representable 
-- The exact encoding depends on the national character set, but the column
-- can contain multibyte values even if the database character set is 
-- The representation of COMMENT, as 2000 bytes or 2000 characters, depends
-- on the initialization parameter NLS_LENGTH_SEMANTICS.

When using a multibyte database character encoding scheme, consider carefully the space required for tables with character columns. If the database character encoding scheme is single-byte, then the number of bytes and the number of characters in a column is the same. If it is multibyte, then there generally is no such correspondence. A character might consist of one or more bytes depending upon the specific multibyte encoding scheme, and whether shift-in/shift-out control codes are present. To avoid overflowing buffers, specify data as NCHAR or NVARCHAR2 if it might use a Unicode encoding that is different from the database character set.

See Also:

for information about globalization support within Oracle and support for different character encoding schemes.  

Implicit Conversion Between CHAR/VARCHAR2 and NCHAR/NVARCHAR2

In previous releases (Oracle8i and earlier), the NCHAR and NVARCHAR2 types were difficult to use because they could not be interchanged with CHAR and VARCHAR2. For example, an NVARCHAR2 literal required special notation, such as N'String value'. Now, you can specify NCHAR and NVARCHAR2 without the N qualifier, and can mix them with CHAR and VARCHAR2 values in SQL statements and functions.

Comparison Semantics

Oracle compares CHAR and NCHAR values using blank-padded comparison semantics. If two values have different lengths, then Oracle adds blanks at the end of the shorter value, until the two values are the same length. Oracle then compares the values character-by-character up to the first character that differs. The value with the greater character in the first differing position is considered greater. Two values that differ only in the number of trailing blanks are considered equal.

Oracle compares VARCHAR2 and NVARCHAR2 values using non-padded comparison semantics. Two values are considered equal only if they have the same characters and are of equal length. Oracle compares the values character-by-character up to the first character that differs. The value with the greater character in that position is considered greater.

Because Oracle blank-pads values stored in CHAR columns but not in VARCHAR2 columns, a value stored in a VARCHAR2 column may take up less space than if it were stored in a CHAR column. For this reason, a full table scan on a large table containing VARCHAR2 columns may read fewer data blocks than a full table scan on a table containing the same data stored in CHAR columns. If your application often performs full table scans on large tables containing character data, then you might be able to improve performance by storing this data in VARCHAR2 columns rather than in CHAR columns.

However, performance is not the only factor to consider when deciding which of these datatypes to use. Oracle uses different semantics to compare values of each datatype. You might choose one datatype over the other if your application is sensitive to the differences between these semantics. For example, if you want Oracle to ignore trailing blanks when comparing character values, then you must store these values in CHAR columns.

See Also:

For more information on comparison semantics for these datatypes, see the Oracle9i SQL Reference

Representing Numeric Data

Use the NUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10-130 through 9.99 x10125, as well as zero, in a NUMBER column.

You can specify that a column contains a floating-point number, for example:

distance NUMBER

Or, you can specify a precision (total number of digits) and scale (number of digits to right of decimal point):

price NUMBER (8, 2)

Although not required, specifying precision and scale helps to identify bad input values. If a precision is not specified, the column stores values as given. Table 3-2 shows examples of how data different scale factors affect storage.

Table 3-2 How Scale Factors Affect Numeric Data Storage  
Input Data  Specified As  Stored As 
7,456,123.89  NUMBER  7456123.89 
7,456,123.89  NUMBER (9)  7456124 
7,456,123.89  NUMBER (9,2)  7456123.89 
7,456,123.89  NUMBER (9,1)  7456123.9 
7,456,123.89  NUMBER (6)  (not accepted, exceeds precision) 
7,456,123.89  NUMBER (7, -2)  7456100 

See Also:

For information about the internal format for the NUMBER datatype, see Oracle9i Database Concepts

Representing Date and Time Data

Use the DATE datatype to store point-in-time values (dates and times) in a table. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds.

Use the TIMESTAMP datatype to store precise values, down to fractional seconds. For example, an application that must decide which of two events occurred first might use TIMESTAMP. An application that needs to specify the time for a job to execute might use DATE.

Because TIMESTAMP WITH TIME ZONE can also store time zone information, it is particularly suited for recording date information that must be gathered or coordinated across geographic regions.

Use TIMESTAMP WITH LOCAL TIME ZONE values when the time zone is not significant. For example, you might use it in an application that schedules teleconferences, where each participant sees the start and end times for their own time zone.

The TIMESTAMP WITH LOCAL TIME ZONE type is appropriate for two-tier applications where you want to display dates and times using the time zone of the client system. You should not use it in three-tier applications, such as those involving a web server, because in that case the client is the web server, so data displayed in a web browser is formatted according to the time zone of the web server rather than the time zone of the browser.

Use INTERVAL DAY TO SECOND to represent the precise difference between two datetime values. For example, you might use this value to set a reminder for a time 36 hours in the future, or to record the time between the start and end of a race. To represent long spans of time, including multiple years, with high precision, you can use a large value for the days portion.

Use INTERVAL YEAR TO MONTH to represent the difference between two datetime values, where the only significant portions are the year and month. For example, you might use this value to set a reminder for a date 18 months in the future, or check whether 6 months have elapsed since a particular date.

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.

See Also:

See the Oracle Call Interface Programmer's Guide for a complete description of the Oracle internal date format. 

Date Format

For input and output of dates, the standard Oracle default date format is DD-MON-RR. For example:


To change this default date format on an instance-wide basis, use the NLS_DATE_FORMAT parameter. To change the format during a session, use the ALTER SESSION statement. To enter dates that are not in the current default date format, use the TO_DATE function with a format mask. For example:

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

See Also:

Oracle Julian dates might not be compatible with Julian dates generated by other date algorithms. For information about Julian dates, see Oracle9i Database Concepts

Be careful using a date format like DD-MON-YY. The YY indicates the year in the current century. For example, 31-DEC-92 is December 31, 2092, not 1992 as you might expect. If you want to indicate years in any century other than the current one, use a different format mask, such as the default RR.

Time Format

Time is stored in 24-hour format, HH24:MI:SS. By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the date 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:


You may need to set up the following data structures for certain examples to work:

CREATE TABLE Birthdays_tab (Bname VARCHAR2(20),Bday DATE)
INSERT INTO Birthdays_tab (bname, bday) VALUES
    ('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));

To compare dates that have time data, use the SQL function TRUNC if you want to ignore the time component. Use the SQL function SYSDATE to return the system date and time. The FIXED_DATE initialization parameter lets you set SYSDATE to a constant; this can be useful for testing.

Performing Date Arithmetic

Oracle provides a number of functions to help with date arithmetic, so that you do not need to perform your own calculations on the number of seconds in a day, the number of days in each month, and so on.

Some useful functions include:

To represent constants when performing date arithmetic, you can use the INTERVAL datatype rather than performing your own calculations. For example, you might add or subtract INTERVAL constants from DATE values, or subtract two DATE values and compare the result to an INTERVAL.

Handling Time Zones

Oracle provides a number of functions to help with calculations involving time zones. Some useful functions include:

TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE values are always stored in normalized format, so that you can export, import, and compare them without worrying about time zone offsets. DATE and TIMESTAMP values do not store an associated time zone, and you must adjust them to account for any time zone differences between source and target databases.

Establishing Year 2000 Compliance

An application must satisfy the following criteria to meet the requirements for Year 2000 (Y2K) compliance:

These criteria are a superset of the Year 2000 conformance requirements set out by the British Standards Institute in DISC PD-2000-1 A Definition of Year 2000 Conformity Requirements.

You can warrant your application as Y2K compliant only if you have validated its conformance at all three of the following system levels:

Centuries and the Year 2000

Oracle stores year data with the century information. For example, the Oracle database stores 1996 or 2001, and not just 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 properly with four-digit years.

Applications that use the Oracle RDBMS (Oracle7 or later) and exploit the DATE data type (for date and/or date with time values) need have no concerns about their stored data and the year 2000. Beginning with Oracle7, the DATE data type stores date and time data to a precision that includes a four digit year and a time component down to seconds (typically `YYYY:MM:DD:HH24:MI:SS')

However, some applications might be written with an assumption about the year (such as assuming that everything is 19xx). The application might hand over a two-digit year to the database, and the procedures that Oracle uses for determining the century could be different from what the programmer expects (see "Troubleshooting Y2K Problems in Applications"). For this reason, you should review and test your code with regard to the Year 2000.

Examples of The 'RR' Date Format

The RR date format element of the TO_DATE and TO_CHAR functions allows a database site to default the century to different values depending on the two-digit year, so that years 50 to 99 default to 19xx and years 00 to 49 default to 20xx. Therefore, regardless of the current century at the time the data is entered, the 'RR' format will ensure that the year stored in the database is as follows:

The `RR' date format is available for inserting and updating DATE data in the database. It is not required for retrieval or query of data already stored in the database as Oracle has always stored the YEAR component of a date in its four-digit form.

Here is an example of the RR usage:

INSERT INTO emp (empno, deptno,hiredate) VALUES 
   (9999, 20, TO_DATE('01-jan-03', 'DD-MON-RR'));

 INSERT INTO emp (empno, deptno,hiredate) VALUES
    (8888, 20, TO_DATE('01-jan-67',  'DD-MON-RR'));

SELECT empno, deptno,
   TO_CHAR(hiredate, 'DD-MON-YYYY') hiredate
FROM emp;

This produces the following data:

----------     ----------    -----------------
8888           20            01-JAN-1967
9999           20            01-JAN-2003

Examples of The 'CC' Date Format

The CC date format element of the TO_CHAR function returns the century of a given date. For example:





The CC date format element of the TO_CHAR function sets the century value to one greater than the first two digits of a four-digit year (for example, '20' from '1900'). For years that are a multiple of 100, this is not the true century. Strictly speaking, the century of '1900' is not the twentieth century (which began in 1901) but rather the nineteenth century.

The following workaround computes the correct century for any Common Era (CE, formerly known as AD) date. If userdate is a CE date for which you want the true century, use the following expression:

    '00', TO_CHAR (Hiredate - 366, 'CC'), 
    TO_CHAR (Hiredate, 'CC'))  FROM Emp_tab;

This expression works as follows: Get the last two digits of the year. If it is '00', then it is a year in which the Oracle century is one year too large, and compute a date in the preceding year (whose Oracle century is the desired true century). Otherwise, use the Oracle century.

See Also:

For more information about date format codes, see Oracle9i SQL Reference

Storing Dates in Character Data Types

Where applications store date values in CHAR or VARCHAR2 datatypes, and the century information is not maintained, you will need to modify the application to include routines which ensure that such dates are treated appropriately when affected by the change in century. You can do this by changing the strings to maintain century information or, with certain constraints, by using the 'RR' date format when interpreting the string as a date.

If you are creating a new application, or if you are modifying an application to ensure that dates stored as character strings are Year 2000 compliant, we advise that you convert dates to use the Oracle DATE data type. If this is not feasible, store the dates in a form which is language and format independent, and which handles full years. For example, utilize `SYYYY/MM/DD' plus the time element as `HH24:MI:SS' if necessary. Note that dates stored in this form must be converted to the correct external format whenever they are displayed or received from users or other programs.

The format 'SYYYY/MM/DD HH24:MI:SS' has the following advantages:

The "S" format element prefixes BC dates with "-".

Viewing Date Settings

The following views let you verify what your settings are:

A format model is a character that describes the format of DATE or NUMBER data stored in a character string. You may use the format model as an argument of the TO_CHAR or TO_DATE function for one of the following:

Please note that the format does not change the internal representation of the value in the database.

To see the available values for time zone region and time zone abbreviation, you can query the view V$TIMEZONE_NAMES.

Altering Date Settings

You may set the date format in your environment or as the default for the entire database. If you set this in your environment, it will override the setting in the initialization parameter.

Change the NLS_DATE_FORMAT parameter settings in the following order:

  1. Set the Client side, such as the Windows NT registry and Unix environment variables.

  2. Set theSession using ALTER SESSION SET NLS_DATE_FORMAT. To change the date format for the session, issue the following SQL command:

  3. Set the Server using the init.ora NLS_DATE_FORMAT parameter. To change the default date format for the entire database, change INIT.ORA to include the following


The NLS_DATE_FORMAT setting relies on the above order. Therefore, for a client/server application, NLS_DATE_FORMAT must be set on the server and on the client.


Changing this parameter at the database level will change all existing date fields as described above. Oracle Corporation suggests that you make changes at the session level unless all users and all currently running applications process dates in the range 1950-2049. 

Troubleshooting Y2K Problems in Applications

In this section we describe some common programming problems around Y2K compliance. These problems may seem to derive from incorrect Year 2000 processing by the database engine, but on closer inspection are seen to arise from incorrect use of Oracle technology.

Y2K Example: Date Columns Too Short

Your application may have defined the year of a date using a column of CHAR(2) or NUMBER(2) in order to save disk space. This can lead to unpredictable results when 20xx dates are mixed with 19xx dates. To resolve this, modify your application to use the full 4-digit year.

Y2K Example: 4-Digit Years Mixed with 2-Digit Years

You application may be designed to store a 4-digit year, but the code may allow for the incorrect storage of 2-digit year rows with the 4-digit year rows. This will lead to unpredictable results for queries by date if the date columns contains dates earlier than 1900. To deal with this problem, have your application check for rows which contain dates earlier than 1900, and then adjust for this.

Y2K Example: Wide Range of Years Stored as 2 Digits

Examine your applications to determine if it processes dates prior to 1950 or later than 2049, and store the year as 2-digits. If both conditions are met, your application should not use the 'RR' format but should instead expand the 2 digit year `YY ` into a 4 digit year `YYYY', and store the 4 digit number in the database.

Y2K Example: Handling Feb. 29, 2000

The following unusual error helps illuminate the interaction between NLS_DATE_FORMAT and the Oracle 'RR' format mask. The following is a syntactically correct statement but contains a logical flaw:


The above query returns 02/28/2000. This is consistent with the defined behavior of the `RR' format element, but it is incorrect because the year 2000 is a leap year.

The problem is that the operation is using the default NLS_DATE_FORMAT, which is 'DD-MON-YY'. If the NLS_DATE_FORMAT is changed to 'DD-MON-RR', then the same select returns 02/29/2000, which is the correct value.

Let us evaluate the query as the Oracle Server engine does. The first function processed is the innermost function, LAST_DAY. Because NLS_DATE_FORMAT is YY, this correctly returns 2/28, because it is using the year 1900 to evaluate the expression. The value 2/28 is then returned to the next outer function. So, the TO_DATE and TO_CHAR functions format the value 02/28/00 using the 'RR' format mask and display the result as 02/28/2000.

If SELECT LAST_DAY('01-FEB-00') FROM DUAL is issued, the result changes depending on the NLS_DATE_FORMAT. With 'YY', the LAST_DAY returned is 28-Feb-00 because the year is interpreted as 1900. With 'RR', the LAST_DAY returned is 29-Feb-00 because the year is interpreted as 2000. The year 1900 is not a leap year, but the year 2000 is.

Y2K Example: Implicit Date Conversion within DECODE

When the DECODE function is used and if the third argument has data type CHAR, VARCHAR2, or if it is NULL, then Oracle converts the return value to datatype VARCHAR2. Therefore, the following statement:

INSERT INTO destination_table (date_column) 
    SELECT DECODE('31.12.2000', '00000000', NULL, 

inserts date 31.12.1900.

Another sample statement:

INSERT INTO destination_table (date_column) 
    SELECT DECODE('01.11.1999', '00000000', NULL, sysdate+1000) 

inserts date 04.10.1901.

In the above examples, the third argument in the DECODE argument list is a NULL value, so Oracle implicitly converted the DATE value to a VARCHAR2 string using the default format mask. This is DD-MON-YY, hence loses the first two digits of the year.

Note: When inserting the record into a table, Oracle implicitly converts the string into a date, using the first 2-digits of the current year. To ensure the correct year is interpreted, set NLS_DATE_FORMAT using 'RR' or 'YYYY'.

Y2K Example: Partitioning Tables Based on DATE Columns

If creating a partitioned table using a DATE data type column in the partition key, use a 4-digit year when specifying date ranges. For example:

CREATE TABLE stock_xactions (stock_symbol CHAR(5),
    stock_series CHAR(1), 
    num_shares NUMBER(10), 
    price NUMBER(5,2), 
    trade_date DATE) 
    PARTITION BY RANGE (trade_date) 
    TABLESPACE ts1, 
    TABLESPACE ts2);

Y2K Example: Views Defined Using 2-Digit Years

Oracle views depend on the session state. In particular, a predicate with a 2-digit year, such as:

WHERE col > '12-MAY-99'

is allowed in a view. Interpretation of the full 4-digit year depends on the setting of NLS_DATE_FORMAT.

Representing Geographic Coordinate Data

To represent Geographic Information System (GIS) or spatial data in the database, you can use the Oracle Spatial features, including the type MDSYS.SDO_GEOMETRY. You can store the data in the database using either an object-relational or a relational model, and manipulate and query the data using a set of PL/SQL packages.

For more information, see Oracle Spatial User's Guide and Reference.

Representing Image, Audio, and Video Data

Whether you store such multimedia data inside the database as BLOBs or BFILEs, or store it externally on a web or other kind of server, you can use interMedia to access the data using either an object-relational or a relational model, and manipulate and query the data using a set of object types.

For more information, see Oracle interMedia User's Guide and Reference.

Representing Searchable Text Data

Rather than writing low-level code to do full-text searches, you can use Oracle9i Text, formerly known as ConText and interMedia Text. It stores the search data in a special kind of index, and lets you query it with operators and PL/SQL packages. This makes it simple to create your own search engine using data from tables, files, or URLs, and combine the search logic with relational queries. You can also search XML data this way, using XPath notation.

For more information, see Oracle Text Application Developer's Guide.

Representing Large Data Types

In times gone by, the way to represent large data objects in the database was to use the LONG, RAW, and LONG RAW types. Oracle recommends that current applications use the various LOB types, such as CLOB, BLOB and BFILE, for this data.

See the Oracle9i Application Developer's Guide - Large Objects (LOBs) , for information about LOB datatypes.

The following sections deal with ways to migrate data from the older datatypes to the LOB types. The LOB types can be used in many situations that formerly required other types such as LONG or VARCHAR2.

Migrating LONG Datatypes to LOB Datatypes

The LONG datatype can store variable-length character data containing up to two gigabytes of information, depending upon available memory. LONG columns have many of the characteristics of VARCHAR2 columns. You can use them in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.

Oracle Corporation recommends using the LONG datatype only for backward compatibility with old applications. For new applications, you should use the CLOB and NCLOB datatypes for large amounts of character data. Typically, you can change LONG data to LOBs in your tables without changing existing applications. SQL, PL/SQL, and OCI interfaces for LONG data can all work on LOB data as well.

See Also:


Changing a LONG or LONG RAW Column to a LOB Datatype

You can use the ALTER TABLE command to change the underlying datatype of a column from LONG to CLOB, or LONG RAW to BLOB. For example:

ALTER TABLE newspaper MODIFY (article CLOB DEFAULT 'Has not been written yet');

This technique preserves all the constraints and triggers on the table. All indexes must be rebuilt. Any domain indexes on a long column, such as indexes for data cartridge or interMedia applications, must be dropped before changing the type of the column.

Restrictions on Changing LONG or LONG RAW Columns to LOB Datatypes

  1. LOBs are not allowed in clustered tables. So if a table is a part of a cluster, its LONG or LONG RAW column cannot be changed to LOB.

  2. If a table is replicated or has materialized views, and its LONG column is changed to LOB, you might have to manually fix the replicas.

  3. Not all triggers are preserved when the column is changed to a LOB datatype.

    1. LOB columns are not allowed in the column list of an UPDATE trigger. For example, the following trigger becomes invalid after changing the type of the column to a LOB, and cannot be recompiled:

      CREATE TABLE t(changed_col LONG);
      CREATE TRIGGER trig BEFORE UPDATE OF lobcol ON t ...;
    2. If a view with a LOB column has an INSTEAD OF trigger, string inserts or updates are not allowed on the LOB column. These operations were allowed when the column was a LONG. So some SQL statements that worked before the migration do not work afterwards. For example:

      create table t(a LONG);
      create view v as select * from t;
      create trigger trig instead of insert on v....;
      alter table t modify (a CLOB);
      insert into v values ('abc'); -- Throws an error because
      -- Implicit conversion from LOB is not allowed in instead-of triggers.

Transparent Access to LOBs from Applications that Use LONG and LONG RAW Datatypes

If your application uses DML (INSERT, UPDATE, DELETE) statements from SQL or PL/SQL for LONG or LONG RAW data, these statements work the same after the column is converted to a LOB datatype. You can use input parameters and output buffers of various character types, and they are converted to and from the corresponding LOB datatypes, and truncated if the output type is not large enough to hold the entire result. For example, you can SELECT a CLOB into a character variable, or a BLOB into a RAW variable.

The following SQL functions that accept or output character types now accept or output CLOB data as well:


In PL/SQL, all the SQLfunctions listed above and the comparison operators (>, =, < and !=), and all user-defined procedures and functions, accept CLOB datatypes as parameters or output types. You can also assign a CLOB to a character variable and vice versa in PL/SQL.

If your application uses OCI calls to perform piecewise inserts, updates, or fetches of LONG data, these calls work the same after the column is converted to a LOB datatype. You can define a CLOB column as SQLT_CHR or a BLOB column as SQLT_BIN and select the LOB data directly into a CHARACTER or RAW buffer without selecting out the locator first. The OCI functions that provide this transparent access, by accepting datatypes of SQLT_LNG, SQLT_CHR, SQLT_BIN, and SQLT_LBI) are:

Restrictions on LONG and LONG RAW Datatypes

You can reference LONG columns in SQL statements in these places:

The use of LONG values is subject to some restrictions:

LONG columns cannot appear in certain parts of SQL statements:

Triggers can use the LONG datatype in the following manner:

You can use the Oracle Call Interface functions to retrieve a portion of a LONG value from the database.

See Also:

Oracle Call Interface Programmer's Guide 


If you design tables containing LONG or LONG RAW data, then you should place each LONG or LONG RAW column in a table separate from any associated data, rather than storing the LONG or LONG RAW column in the same table as other columns. You can then relate the two tables with a referential integrity constraint. This design allows SQL statements that access only the other columns to avoid reading through LONG or LONG RAW data. 

Example of LONG Datatype

To store information on magazine articles, including the texts of each article, create two tables. For example:

CREATE TABLE Article_header   
    (Id               NUMBER  PRIMARY KEY,  
    Title             VARCHAR2(200),
    First_author      VARCHAR2(30),
    Journal           VARCHAR2(50),
    Pub_date          DATE);

CREATE TABLE article_text     
    (Id               NUMBER
    Text              LONG);

The ARTICLE_TEXT table stores only the text of each article. The ARTICLE_HEADER table stores all other information about the article, including the title, first author, and journal and date of publication. The two tables are related by the referential integrity constraint on the ID column of each table.

This design allows SQL statements to query data other than the text of an article without reading through the text. If you want to select all first authors published in Nature magazine during July 1991, then you can issue this statement that queries the ARTICLE_HEADER table:

SELECT First_author
    FROM Article_header
    WHERE Journal = 'NATURE'
        AND TO_CHAR(Pub_date, 'MM YYYY') = '07 1991'; 

If the text of each article were stored in the same table with the first author, publication, and publication date, then Oracle would need to read through the text to perform this query.

Using RAW and LONG RAW Datatypes


The RAW and LONG RAW datatypes are provided for backward compatibility with existing applications. For new applications, you should use the BLOB and BFILE datatypes for large amounts of binary data.  

See Also:

See Oracle9i Application Developer's Guide - Large Objects (LOBs) for information about the BLOB and BFILE datatypes.  

The RAW and LONG RAW datatypes store data that is not interpreted by Oracle (that is, not converted when moving data between different systems). These datatypes are intended for binary data and byte strings. For example, LONG RAW can store graphics, sound, documents, and arrays of binary data; the interpretation is dependent on the use.

Net8 and the Export and Import utilities do not perform character conversion when transmitting RAW or LONG RAW data. When Oracle automatically converts RAW or LONG RAW data to and from CHAR data (as is the case when entering RAW data as a literal in an INSERT statement), the data is represented as one hexadecimal character representing the bit pattern for 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 RAW data can be indexed.

See Also:

For more information about restrictions on LONG RAW data, see "Restrictions on LONG and LONG RAW Datatypes".  

Addressing Rows Directly with the ROWID Datatype

Every row in an Oracle table is assigned a ROWID that corresponds to the physical address of a row. If the row is too large to fit within a single data block, the ROWID identifies the initial row piece. Although ROWIDs are usually unique, different rows can have the same ROWID if they are in the same data block, but in different clustered tables.

Each table in an Oracle database has a pseudocolumn named ROWID.

See Also:

Oracle9i Database Concepts for general information about the ROWID pseudocolumn and the ROWID datatype.  

Extended ROWID Format

The Oracle Server uses an extended ROWID format, which supports features such as table partitions, index partitions, and clusters.

The extended ROWID includes the following information:

The data object identifier is an identification number that Oracle assigns to schema objects in the database, such as nonpartitioned tables or partitions. For example:


This query returns the data object identifier for the EMP_TAB table in the SCOTT schema.

See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference for information about other ways to get the data object identifier, using the DBMS_ROWID package functions.  

Different Forms of the ROWID

Oracle documentation uses the term ROWID in different ways, depending on context.

ROWID Pseudocolumn

Each table and nonjoined view has a pseudocolumn called ROWID. For example:

CREATE TABLE T_tab (col1 Rowid);
INSERT INTO T_tab SELECT Rowid FROM Emp_tab WHERE Empno = 7499;

This command returns the ROWID pseudocolumn of the row of the EMP_TAB table that satisfies the query, and inserts it into the T1 table.

Internal ROWID

The internal ROWID is an internal structure that holds information that the server code needs to access a row. The restricted internal ROWID is 6 bytes on most platforms; the extended ROWID is 10 bytes on these platforms.

External Character ROWID

The extended ROWID pseudocolumn is returned to the client in the form of an 18-character string (for example, "AAAA8mAALAAAAQkAAA"), which represents a base 64 encoding of the components of the extended ROWID in a four-piece format, OOOOOOFFFBBBBBBRRR:

There is no need to decode the external ROWID; you can use the functions in the DBMS_ROWID package to obtain the individual components of the extended ROWID.

See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference for information about the DBMS_ROWID package.  

The restricted ROWID pseudocolumn is returned to the client in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

External Binary ROWID

Some client applications use a binary form of the ROWID. For example, OCI and some precompiler applications can map the ROWID to a 3GL structure on bind or define calls. The size of the binary ROWID is the same for extended and restricted ROWIDs. The information for the extended ROWID is included in an unused field of the restricted ROWID structure.

The format of the extended binary ROWID, expressed as a C struct, is:

struct riddef {
    ub4    ridobjnum; /* data obj#--this field is 
                         unused in restricted ROWIDs */
    ub2    ridfilenum;
    ub1    filler;
    ub4    ridblocknum;
    ub2    ridslotnum;

ROWID Migration and Compatibility Issues

For backward compatibility, the restricted form of the ROWID is still supported. These ROWIDs exist in Oracle7 data, and the extended form of the ROWID is required only in global indexes on partitioned tables. New tables always get extended ROWIDs.

See Also:

Oracle9i Database Administrator's Guide. 

It is possible for an Oracle7 client to access a more recent database, and vice versa. A client in this sense can include a remote database accessing a server using database links, as well as a client 3GL or 4GL application accessing a server.

See Also:

There is more information on the ROWID_TO_EXTENDED function in Oracle9i Supplied PL/SQL Packages and Types Reference and Oracle9i Database Migration

Accessing an Oracle7 Database from an Oracle9i Client

The ROWID values that are returned are always restricted ROWIDs. Also, Oracle9i uses restricted ROWIDs when returning a ROWID value to an Oracle7 or earlier server.

The following ROWID functionality works when accessing an Oracle7 Server:

Accessing an Oracle9i Database from an Oracle7 Client

Oracle9i returns ROWIDs in the extended format. This means that you can only:

Import and Export

It is not possible for an Oracle7 client to import a table from a later version that has a ROWID column (not the ROWID pseudocolumn), if any row of the table contains an extended ROWID value.

ANSI/ISO, DB2, and SQL/DS Datatypes

You can define columns of tables in an Oracle database using ANSI/ISO, DB2, and SQL/DS datatypes. Oracle internally converts such datatypes to Oracle datatypes.

The ANSI datatype conversions to Oracle datatypes are shown in Table 3-3. The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s defaults to 0.

Table 3-3  ANSI Datatype Conversions to Oracle Datatypes
ANSI SQL Datatype  Oracle Datatype 


CHAR (n) 

NUMERIC (p,s), DECIMAL (p,s), DEC (p,s) 

NUMBER (p,s) 


NUMBER (38) 

FLOAT (p) 

FLOAT (p) 


FLOAT (63) 


FLOAT (126) 







The IBM products SQL/DS, and DB2 datatypes TIME, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC have no corresponding Oracle datatype and cannot be used.

Table 3-4 shows the DB2 and SQL/DS conversions.

Table 3-4 SQL/DS, DB2 Datatype Conversions to Oracle Datatypes
DB2 or SQL/DS Datatype  Oracle Datatype 


CHAR (n) 





DECIMAL (p,s) 

NUMBER (p,s) 


NUMBER (38) 

FLOAT (p) 

FLOAT (p) 





How Oracle Converts Datatypes

In some cases, Oracle allows data of one datatype where it expects data of a different datatype. Generally, an expression cannot contain values with different datatypes. However, Oracle can use the following functions to automatically convert data to the expected datatype:

Implicit datatype conversions work according to the rules explained below.

Datatype Conversion During Assignments

For assignments, Oracle can automatically convert the following:

The assignment succeeds if Oracle can convert the datatype of the value used in the assignment to that of the assignment's target.

For the examples in the following list, assume a package with a public variable and a table declared as in the following statements:


You may need to set up the following data structures for certain examples to work:

CREATE TABLE Table1_tab (col1 NUMBER); 

Datatype Conversion During Expression Evaluation

For expression evaluation, Oracle can automatically perform the same conversions as for assignments. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to NUMBER, and operands to string functions are converted to VARCHAR2.

Oracle can automatically convert the following:

Character to NUMBER conversions succeed only if the character string represents a valid number. Character to DATE conversions succeed only if the character string satisfies the session default format, which is specified by the initialization parameter NLS_DATE_FORMAT.

Some common types of expressions follow:

In general, Oracle uses the rule for expression evaluation when a datatype conversion is needed in places not covered by the rule for assignment conversions.

In assignments of the form:

variable := expression

Oracle first evaluates expression using the conversion rules for expressions; expression can be as simple or complex as desired. If it succeeds, then the evaluation of expression results in a single value and datatype. Then, Oracle tries to assign this value to the target variable using the conversion rules for assignments.

Representing Dynamically Typed Data

You might be familiar with features in some languages that allow datatypes to change at runtime, or let a program check the type of a variable. For example, C has the union keyword and the void * pointer, and Java has the typeof operator and wrapper types such as Number. Oracle9i includes features that let you create variables and columns that can hold data of any type, and test such data values to see their underlying representation. Using these features, a single table column can represent a numeric value in one row, a string value in another row, and an object in another row.

You can use the built-in type SYS.ANYDATA to represent values of any scalar or object type. This type is an object type with methods to bring in a scalar value of any type, and turn the value back into a scalar or object.

In the same way, you can use the built-in type SYS.ANYDATASET to represent values of any collection type.

To manipulate and check type information, you can use the built-in type SYS.ANYTYPE in combination with the DBMS_TYPES package. For example, the following program represents data of different underlying types in a table, then interprets the underlying type of each row and processes each value appropriately:

CREATE TABLE tab1 (a SYS.AnyData);
-- Insert a built-in type value after explicit conversion to an AnyData.
insert into tab1 values (SYS.AnyData.Convert(5));
-- Insert a user-defined type value after explicit conversion to an AnyData.
insert into tab1 values(SYS.AnyData.Convert(employee(5555, "John"));
a SYS.AnyData;
t SYS.AnyType;
prec, scale, len, csid, csfrm, count, rval PLS_INTEGER;
sch, tname, version VARCHAR2(50);
t := a.AnyDataGetType();
tc := t.AnyTypeGetInfo(prec, scale, len, csid. csfrm, sch, tname, version, count
/* Dynamically describe the type and do appropriate explicit casts. */
rval := a.AnyDataGetValue(n);
(tname == 'EMPLOYEE')) THEN
rval := a.AnyDataGetValue(e);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ( ' || e.empno || ',' || e.ename || ')');
  DBMS_OUTPUT.PUT_LINE('This operation is not allowed on this type.');

You can access the same features through the OCI interface, using the OCIType, OCIAnyData, and OCIAnyDataSet interfaces.

See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference for details about the DBMS_TYPES package.

Oracle9i Application Developer's Guide - Object-Relational Features for information and examples using the ANYDATA, ANYDATASET, and ANYTYPE types.

Oracle Call Interface Programmer's Guide for details about the OCI interfaces. 

Representing XML Data

If you have information stored as files in XML format, or if you want to take an object type and store it as XML, you can use the XMLType built-in type.

XMLType columns store their data as CLOBs. You can take an existing CLOB, VARCHAR2, or any object type, and call the XMLType constructor to turn it into an XML object.

Once an XML object is inside the database, you can use queries to traverse it (using the XML XPath notation) and extract all or part of its data.

You can also produce XML output from existing relational data, and split XML documents across relational tables and columns. You can use the DBMS_XMLQUERY, DBMS_XMLGEN, and DBMS_XMLSAVE packages, and the SYS_XMLGEN and SYS_XMLAGG functions to transfer XML data into and out of relational tables.

See Also:


Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index