3 Using SQL Data Types in Database Applications

This chapter explains how to use SQL data types in database applications.


See Also:

Overview of SQL Data Types

A data type 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 subprogram. These properties cause Oracle Database to treat values of one data type differently from values of another data type. For example, Oracle Database can add values of NUMBER data type, but not values of RAW data type.

Oracle Database provides a number of built-in data types as well as several categories for user-defined types that can be used as data types.

The Oracle precompilers recognize other data types in embedded SQL programs. These data types are called external data types and are associated with host variables. Do not confuse Oracle Database built-in data types and user-defined types with external data types.

See Also:

Representing Character Data


Overview of Character Data Types

You can use the following SQL data types to store alphanumeric data:

  • CHAR and NCHAR data types store fixed-length character literals.

  • VARCHAR2 and NVARCHAR2 data types store variable-length character literals.

  • NCHAR and NVARCHAR2 data types store Unicode character data only.

  • CLOB and NCLOB data types store single-byte and multibyte character strings of up to (4 gigabytes - 1) * (the value obtained from DBMS_LOB.GETCHUNKSIZE).

  • The LONG data type stores variable-length character strings containing up to two gigabytes, but with many restrictions. This data type is provided only for backward compatibility with existing applications. In general in new applications, use CLOB and NCLOB data types to store large amounts of character data, and BLOB and BFILE to store large amounts of binary data.

  • The LONG RAW data type is similar to the RAW data type, except that it stores raw data with a length up to two gigabytes. The LONG RAW data type is provided only for backward compatibility with existing applications.

See Also:

Specifying Column Lengths as Bytes or Characters

You can specify the lengths of CHAR and VARCHAR2 columns 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.

Consider the following list of column length specifications:

  • id VARCHAR2(32 BYTE)

    The id column contains only single-byte data, up to 32 bytes.

  • name VARCHAR2(32 CHAR)

    The name column contains data in the database character set. If the database character set includes multibyte characters, then the 32 characters can be stored as more than 32 bytes.

  • biography NVARCHAR2(2000)

    The biography column can represent 2000 characters in any Unicode-representable language. The encoding depends on the national character set, but the column can contain multibyte values even if the database character set is single-byte.

  • comment VARCHAR2(2000)

    The representation of comment as 2000 bytes or 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, however, 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:

Choosing Between CHAR and VARCHAR2 Data Types

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

  • Space usage

    To store data more efficiently, use the VARCHAR2 data type. The CHAR data type blank-pads and stores trailing blanks up to a fixed column length for all column values, whereas the VARCHAR2 data type does not add extra blanks.

  • Comparison semantics

    Use the CHAR data type when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2 when trailing blanks are important in string comparisons.

  • Future compatibility

    The CHAR and VARCHAR2 data types are fully supported. At this time, the VARCHAR data type automatically corresponds to the VARCHAR2 data type and is reserved for future use.

When an application interfaces with Oracle Database, there is a character set on the client and server side. Oracle Database uses the NLS_LANGUAGE parameter to automatically convert CHAR, VARCHAR2, and LONG data from the database character set to the character set defined for the user session, if these are different.

Oracle Database SQL Language Reference explains the comparison semantics that Oracle Database uses to compare character data. Because Oracle Database blank-pads values stored in CHAR columns but not in VARCHAR2 columns, a value stored in a VARCHAR2 column can take up less space than the same value 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 may be able to improve performance by storing data in VARCHAR2 rather than in CHAR columns.

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

See Also:

Oracle Database SQL Language Reference for more information about comparison semantics for these data types

Using Character Literals in SQL Statements

Many SQL statements, functions, expressions, and conditions require character literals. For information about using character literals in SQL statements, see Oracle Database SQL Language Reference.

Representing Numeric Data


Overview of Numeric Data Types

The SQL data types NUMBER, BINARY_FLOAT, and BINARY_DOUBLE store numeric data.

Use the NUMBER data type to store real numbers in a fixed-point or floating-point format. Numbers using this data type are guaranteed to be portable among different Oracle Database 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 0, in a NUMBER column.

The BINARY_FLOAT and BINARY_DOUBLE data types store floating-point data in the 32-bit IEEE 754 format and the double precision 64-bit IEEE 754 format respectively. Compared to the Oracle Database NUMBER data type, arithmetic operations on floating-point data are usually faster for BINARY_FLOAT and BINARY_DOUBLE. Also, high-precision values require less space when stored as BINARY_FLOAT and BINARY_DOUBLE.

In client interfaces supported by Oracle Database, the native instruction set supplied by the hardware vendor performs arithmetic operations on BINARY_FLOAT and BINARY_DOUBLE data types. The term native floating-point data types refers to data types including BINARY_FLOAT and BINARY_DOUBLE and to all implementations of these types in supported client interfaces.

The floating-point number system is a common way of representing and manipulating numeric values in computer systems. A floating-point number is characterized by the following components:

  • Binary-valued sign

  • Signed exponent

  • Significand

  • Base

A floating-point value is the signed product of its significand and the base raised to the power of its exponent, as in the following formula:

(-1)sign . significand . base exponent

For example, the number 4.31 is represented as follows:

(-1)0 . 431 . 10 -2

The components of the preceding representation are as follows:

Component Name Component Value
Sign 0
Significand 431
Base 10
Exponent -2

See Also:

Floating-Point Number Formats

A floating-point number format specifies how components of a floating-point number are represented. The choice of representation determines the range and precision of the values the format can represent. By definition, the range is the interval bounded by the smallest and the largest values the format can represent and the precision is the number of digits in the significand.

Formats for floating-point values support neither infinite precision nor infinite range. There are a finite number of bits to represent a number and only a finite number of values that a format can represent. A floating-point number that uses more precision than available with a given format is rounded.

A floating-point number can be represented in a binary system, as in the IEEE 754 standard, or in a decimal system, such as Oracle Database NUMBER. The base affects many properties of the format, including how a numeric value is rounded.

For a decimal floating-point number format like Oracle Database NUMBER, rounding is done to the nearest decimal place (for example. 1000, 10, or 0.01). The IEEE 754 formats use a binary format for floating-point values and round numbers to the nearest binary place (for example: 1024, 512, or 1/64).

The native floating-point data types supported by the database round to the nearest binary place, so they are not satisfactory for applications that require decimal rounding. Use the Oracle Database NUMBER data type for applications in which decimal rounding is required on floating-point data.


Using a Floating-Point Binary Format

The value of a floating-point number that uses a binary format is determined by the following formula:

(-1)s 2E (b0 b1 b2 ... bp-1)

Table 3-1 describes the components of the formula.

Table 3-1 Components of the Binary Format for Floating-Point Numbers

Component Specifies . . .


0 or 1


Any integer between Emin and Emax, inclusive (see Table 3-2)


0 or 1, where the sequence of bits represents a number in base 2 (see Table 3-2)

The leading bit of the significand, b0, must be set (1), except for subnormal numbers (explained later). Therefore, the leading bit is not actually stored, so the formats provide n bits of precision although only n-1 bits are stored.


The IEEE 754 specification also defines extended single-precision and extended double-precision formats, which are not supported by Oracle Database.

The parameters for these formats are described in Table 3-2.

Table 3-2 Summary of Binary Format Parameters

Parameter Single-precision (32-bit) Double-precision (64-bit)










The storage parameters for the formats are described in Table 3-3. The in-memory formats for single-precision and double-precision data types are specified by IEEE 754.

Table 3-3 Summary of Binary Format Storage Parameters

Data Type Sign bits Exponent bits Significand bits Total bits




24 (23 stored)





53 (52 stored)


A significand is normalized when the leading bit of the significand is set. IEEE 754 defines denormal or subnormal values as numbers that are too small to be represented with an implied leading set bit in the significand. The number is too small because its exponent would be too large if its significand were normalized to have an implied leading bit set. IEEE 754 formats support subnormal values. Subnormal values preserve this property: If x - y == 0.0 (using floating-point subtraction), then: x == y.

Table 3-4 shows the range and precision of the required formats in the IEEE 754 standard and those of Oracle Database NUMBER. Range limits are expressed here in terms of positive numbers; they also apply to the absolute value of a negative number. (The notation "number e exponent" used here stands for number multiplied by 10 raised to the exponent power: number . 10 exponent.)

Table 3-4 Range and Precision of IEEE 754 formats

Range and Precision Single-precision 32-bitFoot 1  Double-precision 64-bit1 Oracle Database NUMBER Data Type

Max positive normal number



< 1.0e126

Min positive normal number




Max positive subnormal number



not applicable

Min positive subnormal number



not applicable

Precision (decimal digits)

6 - 9

15 - 17

38 - 40

Footnote 1 These numbers are quoted from the IEEE Numerical Computation Guide.

See Also:

Representing Special Values with Native Floating-Point Formats

Table 3-5 shows the special values that IEEE 754 supports.

Table 3-5 Special Values for Negative Floating-Point Formats

Value Meaning


Positive infinity


Negative infinity


Not a number


Positive zero


Negative zero

NaN represent results of operations that are undefined. Many bit patterns in IEEE 754 represent NaN. Bit patterns can represent NaN with and without the sign bit set. IEEE 754 distinguishes between signalling NaNs and quiet NaNs.

IEEE 754 specifies action for when exceptions are enabled and disabled. In Oracle Database, exceptions cannot be enabled; the database action is that specified by IEEE 754 for when exceptions are disabled. In particular, Oracle Database makes no distinction between signalling and quiet NaNs. Programmers who use OCI can retrieve NaN values from Oracle Database; whether a retrieved NaN value is signalling or quiet depends on the client platform and beyond the control of Oracle Database.

IEEE 754 does not define the bit pattern for either type of NaN. Positive infinity, negative infinity, positive zero, and negative zero are each represented by a specific bit pattern.

Ignoring signs, there are the following classes of values, with each of the classes except for NaN greater than the one preceding it in the list:

  • Zero

  • Subnormal

  • Normal

  • Infinity

  • NaN

In IEEE 754, NaN is unordered with other classes of special values and with itself.

When used with the database, special values of native floating-point data types act as follows:

  • All NaNs are quiet.

  • IEEE 754 exceptions are not raised.

  • NaN is ordered as follows:

    All non-NaN < NaN

    Any NaN == any other NaN

  • -0 is converted to +0.

  • All NaNs are converted to the same bit pattern.

    See Also:

    Comparison Operators for Native Floating-Point Data Types for more information about NaN compared to other values

Comparison Operators for Native Floating-Point Data Types

Oracle Database defines the following comparison operators for operations involving floating-point data types:

  • Equal to

  • Not equal to

  • Greater than

  • Greater than or equal to

  • Less than

  • Less than or equal to

  • Unordered

Special cases:

  • Comparisons ignore the sign of zero (-0 is equal to, not less than, +0).

  • In Oracle Database, NaN is equal to itself. NaN is greater than everything except itself. That is, NaN == NaN and NaN > x, unless x is NaN.

    See Also:

    Representing Special Values with Native Floating-Point Formats for more information about comparison results, ordering, and other actions of special values

Arithmetic Operations with Native Floating-Point Data Types

Oracle Database defines operators for the following arithmetic operations:

  • Multiplication

  • Division

  • Addition

  • Subtraction

  • Remainder

  • Square root

You can define the mode used to round the result of the operation. Exceptions can be raised when operations are performed. Exceptions can also be disabled.

Formerly, Java required floating-point arithmetic to be exactly reproducible. IEEE 754 does not have this requirement. Therefore, results of operations (including arithmetic operations) can be delivered to a destination that uses a range greater than the range that the operands of the operation use.

You can compute the result of a double-precision multiplication at an extended double-precision destination. When this is done, the result must be rounded as if the destination were single-precision or double-precision. The range of the result, that is, the number of bits used for the exponent, can use the range supported by the wider (extended double-precision) destination. This occurrence may result in a double-rounding error in which the least significant bit of the result is incorrect.

This situation can occur only for double-precision multiplication and division on hardware that implements the IA-32 and IA-64 instruction set architecture. Thus, with the exception of this case, arithmetic for these data types is reproducible across platforms. When the result of a computation is NaN, all platforms produce a value for which IS NAN is true. However, all platforms do not have to use the same bit pattern.

Conversion Functions for Native Floating-Point Data Types

Oracle Database defines functions that convert between floating-point and other formats, including string formats that use decimal precision (precision may be lost during the conversion). For example, you can use the following functions:

  • TO_BINARY_DOUBLE, which converts float to double, decimal (string) to double, and float or double to integer-valued double

  • TO_BINARY_FLOAT, which converts double to float, decimal (string) to float, and float or double to integer-valued float

  • TO_CHAR, which converts float or double to decimal (string)

  • TO_NUMBER, which converts a float, double, or string to a number

Oracle Database can raise exceptions during conversion. The IEEE 754 specification defines the following exceptions:

  • Invalid

  • Inexact

  • Divide by zero

  • Underflow

  • Overflow

Oracle Database does not raise these exceptions for native floating-point data types. Generally, situations that raise exceptions produce the values described in Table 3-6.

Table 3-6 Values Resulting from Exceptions

Exception Value





Invalid Operation


Divide by Zero



Any value – rounding was performed

Client Interfaces for Native Floating-Point Data Types

Oracle Database has implemented support for native floating-point data types in the following client interfaces:

  • SQL

  • PL/SQL

  • OCI and OCCI

  • Pro*C/C++

  • JDBC


OCI Native Floating-Point Data Types SQLT_BFLOAT and SQLT_BDOUBLE

The OCI API implements the IEEE 754 single precision and double precision native floating-point data types with the data types SQLT_BFLOAT and SQLT_BDOUBLE respectively. Conversions between these types and the SQL types BINARY_FLOAT and BINARY_DOUBLE are exact on platforms that implement the IEEE 754 standard for the C data types FLOAT and DOUBLE.

Native Floating-Point Data Types Supported in Oracle Database OBJECT Types

Oracle Database supports the SQL data types BINARY_FLOAT and BINARY_DOUBLE as attributes of Oracle Database OBJECT types.

Pro*C/C++ Support for Native Floating-Point Data Types

Pro*C/C++ supports the native FLOAT and DOUBLE data types using the column data types BINARY_FLOAT and BINARY_DOUBLE. You can use these data types in the same way that Oracle Database NUMBER data type is used. You can bind the native C/C++ data types FLOAT and DOUBLE to BINARY_FLOAT and BINARY_DOUBLE types respectively by setting the Pro*C/C++ precompiler command line option NATIVE_TYPES to Y (yes) when you compile your application.

Representing Date and Time Data


Overview of Date and Time Data Types

Oracle Database supports the following date and time data types:

  • DATE

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


    Use the TIMESTAMP data type to store values that are precise to fractional seconds. For example, an application that must decide which of two events occurred first might use TIMESTAMP. An application that specifies the time for a job 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 when the time zone is not significant. For example, you might use it in an application that schedules teleconferences, where participants each see the start and end times for their own time zone.

    The TIMESTAMP WITH LOCAL TIME ZONE type is appropriate for two-tier applications in which you want to display dates and times that use the time zone of the client system. It is generally inappropriate in three-tier applications because data displayed in a Web browser is formatted according to the time zone of the Web server, not the time zone of the browser. The Web server is the database client, so its local time is used.


    Use the INTERVAL DAY TO SECOND data type 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 with high precision, you can use a large value for the days portion.


    Use the INTERVAL YEAR TO MONTH data type to represent the difference between two datetime values, where the only significant portions are the year and the 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 Database stores dates in its own internal format. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

See Also:

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

Displaying Current Date and Time

Use the SQL function SYSDATE to return the system date and time. You can use the FIXED_DATE initialization parameter to set SYSDATE to a constant, which can be useful for testing.

By default, SYSDATE is printed without a BC or AD qualifier. You can add BC to the format string to print the date with the appropriate qualifier, as in Example 3-1.

Example 3-1 Displaying Current Date and Time with AD or BC Qualifier

27-SEP-2007 AD
1 row selected.

For input and output of dates, the standard Oracle Database default date format is DD-MON-RR. The RR datetime format element enables you store 20th century dates in the 21st century by specifying only the last two digits of the year. For example, the format '13-NOV-54' refers to the year 1954 in a query issued between 1950 and 2049, but to the year 2054 in a query issued between 2050 and 2099.

See Also:

Oracle Database SQL Language Reference for information about the RR datetime format element.

Changing the Default Date Format

Use the following techniques to change the default date format:

  • To change on an instance-wide basis, use the NLS_DATE_FORMAT parameter.

  • To change 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, as in Example 3-2.

Example 3-2 Changing the Default Date Format

  2    FROM DUAL;
1 row selected.

Be careful when using a date format such as 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.

See Also:

Oracle Database Concepts for information about Julian dates. Oracle Database Julian dates might not be compatible with Julian dates generated by other date algorithms.

Changing the Default Time Format

Time is stored in the 24-hour format: HH24:MI:SS

By default, the time in a DATE column is 12:00:00 A.M. (midnight) if no time portion is specified or if the DATE is truncated.

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 Example 3-3.

Example 3-3 Changing the Default Time Format

SQL> DROP TABLE birthdays;
DROP TABLE birthdays
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE birthdays (name VARCHAR2(20), day DATE);
Table created.
SQL> INSERT INTO birthdays (name, day)
  2    VALUES ('ANNIE',
  3            TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.')
  4           );
1 row created.

Arithmetic Operations with Date and Time Data Types

Oracle Database provides a number of features to help with date arithmetic, so that you need not perform your own calculations on the number of seconds in a day, the number of days in each month, and so on. Some useful features include the following:

  • ADD_MONTHS function, which returns the date plus the specified number of months.

  • SYSDATE function, which returns the current date and time set for the operating system on which the database resides.

  • SYSTIMESTAMP function, which returns the system date, including fractional seconds and time zone, of the system on which the database resides.

  • TRUNC function, which when applied to a DATE value, trims off the time portion so that it represents the very beginning of the day (the stroke of midnight). By truncating two DATE values and comparing them, you can determine whether they refer to the same day. You can also use TRUNC along with a GROUP BY clause to produce daily totals.

  • Arithmetic operators such as + and -. For example, SYSDATE-7 refers to 7 days before the current system date.

  • INTERVAL data types, which enable you to represent constants when performing date arithmetic rather than performing your own calculations. For example, you can add or subtract INTERVAL constants from DATE values or subtract two DATE values and compare the result to an INTERVAL.

  • Comparison operators such as >, <, =, and BETWEEN.

Converting Between Date and Time Types

Oracle Database provides several useful functions that enable you to convert to a from datetime data types. Some useful functions include:

  • EXTRACT, which extracts and returns the value of a specified datetime field from a datetime or interval value expression

  • NUMTODSINTERVAL, which converts a NUMBER or expression that can be implicitly converted to a NUMBER value to an INTERVAL DAY TO SECOND literal

  • NUMTOYMINTERVAL, which converts a NUMBER or expression that can be implicitly converted to a NUMBER value to an INTERVAL YEAR TO MONTH literal

  • TO_DATE, which converts character data to a DATE data type

  • TO_CHAR, which converts DATE data to character data

  • TO_DSINTERVAL, which converts a character string to an INTERVAL DAY TO SECOND value

  • TO_TIMESTAMP, which converts character data to a value of TIMESTAMP data type

  • TO_TIMESTAMP_TZ, which converts character data to a value of TIMESTAMP WITH TIME ZONE data type

  • TO_YMINTERVAL, which converts a character string to an INTERVAL YEAR TO MONTH type

    See Also:

    Oracle Database SQL Language Reference for details about each function

Importing and Exporting Date and Time Types

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.

Representing Specialized Data

This section contains the following topics:

Representing Geographic Data

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

See Also:

Oracle Spatial Developer's Guide to learn how to use MDSYS.SDO_GEOMETRY

Representing Multimedia Data

Oracle Multimedia enables Oracle Database to store, manage, and retrieve images, audio, video, or other heterogeneous media data in an integrated fashion with other enterprise information. Oracle Multimedia extends Oracle Database reliability, availability, and data management to multimedia content in traditional, Internet, electronic commerce, and media-rich applications.

Whether you store such multimedia data inside the database as BLOB or BFILE values, or store it externally on a Web server or other kind of server, you can use Oracle Multimedia 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.

Oracle Multimedia provides the ORDAudio, ORDDoc, ORDImage, ORDImageSignature, ORDVideo, and SI_StillImage object types and methods for these purposes:

  • Extracting metadata and attributes from multimedia data

  • Retrieving and managing multimedia data from Oracle Multimedia, Web servers, file systems, and other servers

  • Performing manipulation operations on image data

See Also:

Oracle Multimedia Reference for information about Oracle Multimedia types

Representing Large Amounts of Data

Oracle Database provides several data types for representing large amounts of data. These data types are grouped under the general category of Large Objects (LOBs). Table 3-7 describes the different LOBs.

Table 3-7 Large Object Data Types

Data Type Name Description


Binary large object

Represents large amounts of binary data such as images, video, or other multimedia data.


Character large object

Represents large amounts of character data. CLOB types are stored by using the database character set. Oracle Database stores a CLOB up to 4,000 bytes inline as a VARCHAR2. If the CLOB exceeds this length, then Oracle Database moves the CLOB out of line.


National character large objects

Represents large amounts of character data in National Character Set format.


External large object

Stores objects in the operating system's file system, outside of the database files or tablespace. The BFILE type is read-only; other LOB types are read/write. BFILE objects are also sometimes referred to as external LOBs.

An instance of type BLOB, CLOB, or NCLOB can exist as either a persistent LOB instance or a temporary LOB instance. Persistent and temporary instances differ as follows:

  • A temporary LOB instance is declared in the scope of your application.

  • A persistent LOB instance is created and stored in the database.

With the exception of declaring, freeing, creating, and committing, operations on persistent and temporary LOB instances are performed the same way.

The RAW and LONG RAW data types store data that is not interpreted by Oracle Database, that is, it is not converted when moving data between different systems. These data types 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.

Oracle Net and the Export and Import utilities do not perform character conversion when transmitting RAW or LONG RAW data. When Oracle Database 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 database represents the data 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.

You cannot index LONG RAW data, but you can index RAW data. In earlier releases, the LONG and LONG RAW data types were typically used to store large amounts of data. Use of these types is no longer recommended for new development. If your existing application still uses these types, migrate your application to use LOB types. Oracle recommends that you convert LONG RAW columns to binary LOB (BLOB) columns and convert LONG columns to character LOB (CLOB or NCLOB) columns. LOB columns are subject to far fewer restrictions than LONG and LONG RAW columns.

See Also:

Representing Searchable Text

Rather than writing low-level code to do full-text searches, you can use Oracle Text. It stores the search data in a special kind of index, and lets you query the data with operators and PL/SQL packages. This technology enables you 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 with the XPath notation.

See Also:

Oracle Text Application Developer's Guide for more information

Representing XML

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

XMLType columns store their data as either CLOB or binary XML. The XMLType constructor can turn an existing object of any data type into an XML object.

When 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 following packages to transfer XML data into and out of relational tables:

  • DBMS_XMLQUERY, which provides database-to-XMLType functionality

  • DBMS_XMLGEN, which converts the results of a SQL query to a canonical XML format

  • DBMS_XMLSAVE, which provides XML to database-type functionality

You can use the following SQL functions to process XML:

  • EXTRACT, which applies a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment

  • SYS_XMLAGG, which aggregates all of the XML documents or fragments represented by an expression and produces a single XML document

  • SYS_XMLGEN, which takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document

  • UPDATEXML, which takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value

  • XMLAGG, which takes a collection of XML fragments and returns an aggregated XML document

  • XMLCOLATTVAL, which creates an XML fragment and then expands the resulting XML so that each XML fragment has the name column with the attribute name

  • XMLCONCAT, which takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series

  • XMLELEMENT, which takes an element name for identifier, an optional collection of attributes for the element, and arguments that make up the content of the element

  • XMLFOREST, which converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments

  • XMLSEQUENCE, which either takes as input an XMLType instance and returns a varray of the top-level nodes in the XMLType, or takes as input a REFCURSOR instance, with an optional instance of the XMLFormat object, and returns as an XMLSequence type an XML document for each row of the cursor

    XMLTRANSFORM, which takes as arguments an XMLType instance and an XSL style sheet, applies the style sheet to the instance, and returns an XMLType

See Also:

Representing Dynamically Typed Data

Some languages allow data types to change at run time 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. In Oracle Database, you can create variables and columns that can hold data of any type and test such data values to determine their underlying representation. For example, you can have a single table column 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 object type SYS.ANYDATA to represent values of any scalar or object type. SYS.ANYDATA has methods that accept scalar values of any type, and turn them back into scalars or objects. Similarly, you can use the built-in object type SYS.ANYDATASET to represent values of any collection type. To check and manipulate type information, use the DBMS_TYPES package, as in Example 3-4. With OCI, use the OCIType, OCIAnyData, and OCIAnyDataSet interfaces.

See Also:

Example 3-4 Accessing Information in a SYS.ANYDATA Column

  2    OBJECT (empno NUMBER, ename VARCHAR2(10));
  3  /
Type created.
Table created.
  2    VALUES (1, SYS.ANYDATA.ConvertNumber(5));
1 row created.
  2    VALUES (2, SYS.ANYDATA.ConvertObject(Employee_type(5555, 'john')));
1 row created.
  2    CURSOR cur IS SELECT id, data FROM mytab;
  3    v_id                        mytab.id%TYPE;
  4    v_data                      mytab.data%TYPE;
  5    v_type                      SYS.ANYTYPE;
  6    v_typecode                  PLS_INTEGER;
  7    v_typename                  VARCHAR2(60);
  8    v_dummy                     PLS_INTEGER;
  9    v_n                         NUMBER;
 10    v_employee                  employee_type;
 11    non_null_anytype_for_NUMBER exception;
 12    unknown_typename            exception;
 13  BEGIN
 14    OPEN cur;
 15    LOOP
 16      FETCH cur INTO v_id, v_data;
 17      EXIT WHEN cur%NOTFOUND;
 19      /* typecode signifies type represented by v_data.
 20         GetType also produces a value of type SYS.ANYTYPE with methods you
 21         can call to find precision and scale of a number, length of a
 22         string, and so on. */
 24      v_typecode := v_data.GetType (v_type /* OUT */);
 26     /* Compare typecode to DBMS_TYPES constants to determine type of data
 27        and decide how to display it. */
 29      CASE v_typecode
 31          IF v_type IS NOT NULL THEN  -- This condition should never happen.
 32            RAISE non_null_anytype_for_NUMBER;
 33          END IF;
 35          -- For each type, there is a Get method.
 36          v_dummy := v_data.GetNUMBER (v_n /* OUT */);
 38            (TO_CHAR(v_id) || ': NUMBER = ' || TO_CHAR(v_n) );
 41          v_typename := v_data.GetTypeName();
 42          IF v_typename NOT IN ('HR.EMPLOYEE_TYPE') THEN
 43             RAISE unknown_typename;
 44          END IF;
 45          v_dummy := v_data.GetObject (v_employee /* OUT */);
 47            (TO_CHAR(v_id) || ': user-defined type = ' || v_typename ||
 48              ' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' );
 49      END CASE;
 50    END LOOP;
 51    CLOSE cur;
 53    WHEN non_null_anytype_for_NUMBER THEN
 54      RAISE_Application_Error (-20000,
 55        'Paradox: the return AnyType instance FROM GetType ' ||
 56        'should be NULL for all but user-defined types');
 57    WHEN unknown_typename THEN
 58      RAISE_Application_Error( -20000, 'Unknown user-defined type ' ||
 59        v_typename || ' - program written to handle only HR.EMPLOYEE_TYPE');
 60  END;
 61  /
Procedure created.
SQL> SELECT t.data.gettypename() AS "Type Name" FROM mytab t;
Type Name
2 rows selected.

Representing Data with ANSI/ISO, DB2, and SQL/DS Data Types

You can define columns of tables in Oracle Database through ANSI/ISO, DB2, and SQL/DS data types. Oracle Database internally converts such data types to Oracle Database data types.

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

Table 3-8 ANSI Data Type Conversions to Oracle Database Data Types

ANSI SQL Data Type Oracle Database Data Type


CHAR (n)

CHAR (n)



DEC (p,s)

NUMBER (p,s)








FLOAT (63)


FLOAT (126)








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

Table 3-9 SQL/DS, DB2 Data Type Conversions to Oracle Database Data Types

DB2 or SQL/DS Data Type Oracle Database Data Type


CHAR (n)






NUMBER ( p,s)










The data types TIME, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC of IBM products SQL/DS and DB2 have no corresponding Oracle Database data type, and they cannot be used.

Representing Conditional Expressions as Data

The Oracle Expression Filter feature enables you to store conditional expressions as data in the database. The Oracle Expression Filter provides a mechanism that you can use to place a constraint on a VARCHAR2 column to ensure that the values stored are valid SQL WHERE clause expressions. This mechanism also identifies the set of attributes that are legal to reference in the conditional expressions.

Scenario: You created the following table, in which each row holds data for a stock-trading account holder, and you want to define a column that stores information about the stocks in which each trader is interested as a conditional expression.

SQL> DROP TABLE traders;
DROP TABLE traders
ERROR at line 1:
ORA-00942: table or view does not exist
  2    (name     VARCHAR2(10),
  3     email    VARCHAR2(20),
  4     interest VARCHAR2(30));
Table created.


  1. Create a type with attributes for the trading symbol, limit price, and amount of change in the stock price:

      2    (symbol VARCHAR2(20),
      3     price  NUMBER,
      4     change NUMBER);
      5  /
    Type created.
  2. Create an attribute set based on the type ticker:

      2    DBMS_EXPFIL.DROP_ATTRIBUTE_SET (attr_set  => 'ticker');
      3  END;
      4  /
    PL/SQL procedure successfully completed.
      3      (attr_set  => 'ticker',
      4       from_type => 'YES');
      5  END;
      6  /
    PL/SQL procedure successfully completed.
  3. Associate the attribute set with the expression set stored in the database column trader.interest:

      3      (attr_set => 'ticker',
      4       expr_tab => 'traders',
      5       expr_col => 'interest');
      6  END;
      7  /
    PL/SQL procedure successfully completed.

    The preceding code ensures that the interest column stores valid conditional expressions.

  4. Populate the table with trader names, e-mail addresses, and conditional expressions that represent stocks in which the trader is interested, at particular prices. For example:

    SQL> INSERT INTO traders (name, email, interest)
      2    VALUES ('Vishu', 'vishu@example.com', 'symbol = ''ABC'' AND price > 25');
    1 row created.
  5. Use the EVALUATE operator to identify the conditional expressions that evaluate to TRUE for a given data item. For example, the following query returns traders who are interested in the stock quote (symbol='ABC', price=31, change=5.2):

    SQL> SELECT name, email
      2    FROM traders
      3      WHERE EVALUATE (interest,
      4                      'symbol=>''ABC'',
      5                      price=>31,
      6                      change=>5.2'
      7                     ) = 1;
    NAME       EMAIL
    ---------- --------------------
    Vishu      vishu@example.com
    1 row selected.

    To speed up this type of query, you can create an Oracle Expression Filter index on the interest column.

    See Also:

    Oracle Database Rules Manager and Expression Filter Developer's Guide for details on Oracle Expression Filter

Identifying Rows by Address

Each row in a database table has an address called a rowid. You can examine a row address by querying the pseudocolumn ROWID, whose values are strings representing the address of each row. These strings have the data type ROWID or UROWID. You can also create tables and clusters that contain actual columns having the ROWID data type. Oracle Database does not guarantee that the values of such columns are valid rowids.

Rowid values are important for application development for the following reasons:

  • They are the fastest way to access a single row.

  • They can show you how the rows in a table are stored.

  • They are unique identifiers for rows in a table.

See Also:


Querying the ROWID Pseudocolumn

Each table in Oracle Database has a pseudocolumn named ROWID. You can use the ROWID pseudocolumn in the SELECT list or the WHERE clause of a query.

Because pseudocolumns are not stored in the database, you cannot insert, update, or delete their values.

If a row is too large to fit within a single data block, then its 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.

Example 3-5 uses the ROWID pseudocolumn in the SELECT list of a query.

Example 3-5 Querying the ROWID Pseudocolumn

Table created.
  3      FROM employees
  4        WHERE employee_id > 199;
7 rows created.
SQL> SELECT employee_id, rowid
  2    FROM employees
  3      WHERE employee_id > 199;
----------- ------------------
7 rows selected.
7 rows selected.

Accessing the ROWID Data Type

In tables that are not index-organized and foreign tables, the values of the ROWID pseudocolumn have the data type ROWID. The format of this data type is either extended or restricted.


Restricted ROWID

Internally, the ROWID is a structure that holds information that the database server must access a row. The restricted internal ROWID is 6 bytes on most platforms. Each restricted rowid includes the following data:

  • Datafile identifier

  • Block identifier

  • Row identifier

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

Extended ROWID

The extended ROWID data type includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. The extended internal ROWID is 10 bytes on most platforms.

Data in an extended ROWID pseudocolumn is returned to the client application 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. Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that is available directly from a restricted rowid as well as information specific to extended rowids.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_ROWID package

External Binary ROWID

Some client applications use a binary form of the ROWID. For example, OCI and some precompiler applications can map the ROWID data type 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 as follows:

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

Accessing the UROWID Data Type

The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Oracle Database provides these tables with logical row identifiers, called logical rowids. Rowids of foreign tables, such as DB2 tables accessed through a gateway, are not standard Oracle Database rowids. Oracle Database provides foreign tables with identifiers called foreign rowids.Oracle Database uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Both types of urowid are stored in the ROWID pseudocolumn, as are the physical rowids of heap-organized tables.Oracle Database creates logical rowids based on the primary key of the table. The logical rowids do not change as long as the primary key does not change. The ROWID pseudocolumn of an index-organized table has a data type of UROWID. You can access this pseudocolumn as you would access the ROWID pseudocolumn of a heap-organized table (that is, using a SELECT ROWID statement). To store the rowids of an index-organized table, define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.

How Oracle Database Converts Data Types

In some cases, Oracle Database accepts data of one data type where it expects data of a different data type. Generally, an expression cannot contain values with different data types. However, Oracle Database can use various SQL functions to automatically convert data to the expected data type.

See Also:

Oracle Database SQL Language Reference for details about data type conversion


Data Type Conversion During Assignments

The data type conversion for an assignment succeeds if Oracle Database can convert the data type of the value used in the assignment to that of the assignment target.

For the examples in the following list, assume that test_package, its public variable var1, and table1_tab are declared as follows:

SQL> DROP TABLE table1_tab;
DROP TABLE table1_tab
ERROR at line 1:
ORA-00942: table or view does not exist
  2    var1 CHAR(5);
  3  END;
  4  /
Package created.
SQL> CREATE TABLE table1_tab (col1 NUMBER);
Table created.
  • variable := expression

    The data type of expression must be either the same as, or convertible to, the data type of variable. For example, Oracle Database automatically converts the data provided in the following assignment within the body of a stored subprogram:

    var1 := 0;
  • INSERT INTO table1_tab VALUES (expression1, expression2, ...)

    The data types of expression1, expression2, and so on, must be either the same as, or convertible to, the data types of the corresponding columns in table1_tab. For example, Oracle Database automatically converts the data provided in the following INSERT statement for table1_tab:

    INSERT INTO table1_tab VALUES ('19');
  • UPDATE table1_tab SET column = expression

    The data type of expression must be either the same as, or convertible to, the data type of column. For example, Oracle Database automatically converts the data provided in the following UPDATE statement issued against table1_tab:

    UPDATE table1_tab SET col1 = '30';
  • SELECT column INTO variable FROM table1_tab

    The data type of column must be either the same as, or convertible to, the data type of variable. For example, Oracle Database automatically converts data selected from the table before assigning it to the variable in the following statement:

    SELECT Col1 INTO Var1 FROM Table1_tab WHERE Col1 = 30;

Data Type Conversion During Expression Evaluation

For expression evaluation, Oracle Database 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 Database 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 are:

  • Simple expressions, such as:

    commission + '500'
  • Boolean expressions, such as:

    bonus > salary / '10'
  • Subprogram calls, such as:

    MOD (counter, '2')
  • WHERE clause conditions, such as:

    WHERE hiredate = TO_DATE('1997-01-01','yyyy-mm-dd')
  • WHERE clause conditions, such as:


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

In assignments of the form:

variable := expression

Oracle Database 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 data type. Then, Oracle Database tries to assign this value to the target variable using the conversion rules for assignments.

Metadata for SQL Built-In Functions

You can see metadata for SQL built-in functions with the dynamic performance views V$SQLFN_METADATA (which has general metadata) and V$SQLFN_ARG_METADATA (which has metadata about arguments). You can join these views on the column FUNCID. For functions with unlimited arguments, such as LEAST and GREATEST, V$SQLFN_ARG_METADATA has only one row for each repeating argument.

These views enable third-party tools to leverage SQL built-in functions without maintaining their metadata in the application layer.

See Also:

Oracle Database Reference for detailed information about the dynamic performance views V$SQLFN_METADATA and V$SQLFN_ARG_METADATA

Often, an argument for a SQL built-in function can have any data type in a data type family. Table 3-10 shows which data types belong to which families.

Table 3-10 Data Type Families

Family Data Types





































ARGn Data Type

In the view V$SQLFN_METADATA, ARGn is the data type of a function whose return value has the same data type as its nth argument. For example:

  • The MAX function returns a value that has the data type of its first argument, so the MAX function has data type ARG1.

  • The DECODE function returns a value that has the data type of its third argument, so the DECODE function has data type ARG3.

EXPR Data Type

In the view V$SQLFN_ARG_METADATA, EXPR is the data type of an argument that can be any expression. An expression is either a single value or a combination of values and SQL functions that has a single value.

Table 3-11 Display Types of SQL Built-In Functions

Display Type Description Example














CASE statement or DECODE decode