Skip Headers
Oracle® Database Advanced Application Developer's Guide
11g Release 2 (11.2)

E41502-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

1 Using SQL Data Types in Database Applications

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

Topics:

See Also:

Overview of SQL Data Types

A data type associates fixed properties with the values that can be inserted in table columns or passed as parameters to subprograms. These properties cause Oracle Database to treat values of different data types differently. For example, Oracle Database can add values of NUMBER data type, but cannot add values of RAW data type.

Oracle Database provides many data types and 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 external data types with Oracle built-in, Oracle-supplied, and user-defined data types.

See Also:

Representing Character Data

Table 1-1 summarizes the SQL data types that store alphanumeric data.

Table 1-1 SQL Character Data Types

Data Types Values Stored

CHAR

Fixed-length character literals

NCHAR

Fixed-length Unicode character literals

VARCHAR2

Variable-length character literals

NVARCHAR2

Variable-length Unicode character literals

CLOB

Single-byte and multibyte character strings of up to (4 gigabytes - 1) * (the value obtained from DBMS_LOB.GETCHUNKSIZE)

NCLOB

Single-byte and multibyte Unicode character strings of up to (4 gigabytes - 1) * (the value obtained from DBMS_LOB.GETCHUNKSIZE)

LONG

Variable-length character data of up to 2 gigabytes - 1. Provided only for backward compatibility.


For a client/server application, if the character set on the client side differs from the character set on the server side, then Oracle Database automatically converts CHAR, VARCHAR2, and LONG data from the database character set (determined by the NLS_LANGUAGE parameter) to the character set defined for the user session.

Topics:

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 character literals, where a character might consist of multiple bytes. This table shows some column length specifications and their meanings:

Column Length Specification Meaning
id VARCHAR2(32 BYTE) The id column contains up to 32 single-byte characters.
name VARCHAR2(32 CHAR) The name column contains up to 32 characters of the database character set. If the database character set includes multibyte characters, then the 32 characters can occupy more than 32 bytes.
biography NVARCHAR2(2000) The biography column contains up to 2000 characters of any Unicode-representable language. The encoding depends on the national character set. The column can contain multibyte values even if the database character set is single-byte.
comment VARCHAR2(2000) The comment column contains up to 2000 bytes or characters, depending on the value of 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 generally there is no such correspondence. A character might consist of one or more bytes, depending on 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 choosing a data type for a column that stores alphanumeric data in a table, consider:

  • Space usage

    Oracle Database blank-pads values stored in CHAR columns but not values stored in VARCHAR2 columns. Therefore, VARCHAR2 columns use space more efficiently than CHAR columns.

  • Performance

    Because of the blank-padding difference, a full table scan on a large table containing VARCHAR2 columns might 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 data in VARCHAR2 columns rather than in CHAR columns.

  • Comparison semantics

    When you need ANSI compatibility in comparison semantics, use the CHAR data type. When trailing blanks are important in string comparisons, use the VARCHAR2 data type.

    See Also:

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

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

Representing Numeric Data

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

The NUMBER data type stores real numbers in either a fixed-point or floating-point format. NUMBER offers up to 38 decimal digits of precision. In a NUMBER column, you can store positive and negative numbers of magnitude 1 x 10-130 through 9.99 x10125, and 0. All Oracle Database platforms support NUMBER values.

The BINARY_FLOAT and BINARY_DOUBLE data types store floating-point numbers in the single-precision (32-bit) IEEE 754 format and the double-precision (64-bit) IEEE 754 format, respectively. High-precision values use less space when stored as BINARY_FLOAT and BINARY_DOUBLE than when stored as NUMBER. Arithmetic operations on floating-point numbers are usually faster for BINARY_FLOAT and BINARY_DOUBLE values than for NUMBER values.

In client interfaces that Oracle Database supports, arithmetic operations on BINARY_FLOAT and BINARY_DOUBLE values are performed by the native instruction set that the hardware vendor supplies. The term native floating-point data type includes BINARY_FLOAT and BINARY_DOUBLE data types and all implementations of these types in supported client interfaces.

Native floating-point data types conform substantially with the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985 (IEEE754). For details, see Oracle Database SQL Language Reference.

Topics:

See Also:

Floating-Point Number Components

A floating-point number has these components:

  • Binary-valued sign

  • Signed exponent

  • Significand

  • Base

The formula for a floating-point value is:

(-1)sign.significand.baseexponent

For example, the floating-point value 4.31 is represented:

(-1)0.431.10-2

The components of the preceding representation are:

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

Floating-Point Number Formats

A floating-point number format specifies how the components of a floating-point number are represented, thereby determining the range and precision of the values that the format can represent. The range is the interval bounded by the smallest and largest values and the precision is the number of significant digits. Both range and precision are finite. If a floating-point number is too precise for a given format, then the number is rounded.

How the number is rounded depends on the base of its format, which can be either decimal or binary. A number stored in decimal format is rounded to the nearest decimal place (for example, 1000, 10, or 0.01). A number stored in binary format is rounded to the nearest binary place (for example, 1024, 512, or 1/64).

NUMBER values are stored in decimal format. For calculations that need decimal rounding, use the NUMBER data type.

Native floating-point values are stored in binary format.

Topics:

Binary Floating-Point Formats

This formula determines the value of a floating-point number that uses a binary format:

(-1)sign 2E (bit0 bit1 bit2 ... bitp-1)

Table 1-2 describes the components of the preceding formula.

Table 1-2 Binary Floating-Point Format Components

Component Component Value

sign

0 or 1

E (exponent)

For single-precision (32-bit) data type, an integer from -126 through 127.

For double-precision (64-bit) data type, an integer from -1022 through 1023.

biti

0 or 1. (The bit sequence represents a number in base 2.)

p (precision)

For single-precision data type, 24.

For double-precision data type, 53.


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, and a binary format provides n bits of precision while storing only n-1 bits. The IEEE 754 standard defines the in-memory formats for single-precision and double-precision data types, which Table 1-3 shows.

Table 1-3 Summary of Binary Format Storage Parameters

Data Type Sign Bit Exponent Bits Significand Bits Total Bits

Single-precision

1

8

24 (23 stored)

32

Double-precision

1

11

53 (52 stored)

64


Note:

Oracle Database does not support the extended single- and double-precision formats that the IEEE 754 standard defines.

A significand whose leading bit is set is called normalized. The IEEE 754 standard defines subnormal numbers (also called denormal numbers) that are too small to represent with normalized significands. If the significand of a subnormal number were normalized, then its exponent would be too large. Subnormal numbers preserve this property: If x-y==0.0 (using floating-point subtraction), then x==y. IEEE 754 formats support subnormal values.

Table 1-4 shows the range and precision of the IEEE 754 single- and double-precision formats and Oracle Database NUMBER. Range limits are expressed as positive numbers, but they also apply to absolute values of negative numbers. (The notation "number e exponent" means number * 10exponent.)

Table 1-4 Range and Precision of Floating-Point Data Types

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

Maximum positive normal number

3.40282347e+38

1.7976931348623157e+308

< 1.0e126

Minimum positive normal number

1.17549435e-38

2.2250738585072014e-308

1.0e-130

Maximum positive subnormal number

1.17549421e-38

2.2250738585072009e-308

not applicable

Minimum positive subnormal number

1.40129846e-45

4.9406564584124654e-324

not applicable

Precision (decimal digits)

6 - 9

15 - 17

38 - 40


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

See Also:

Special Values for Native Floating-Point Formats

The IEEE 754 standard supports the special values shown in Table 1-5.

Table 1-5 Special Values for Native Floating-Point Formats

Value Meaning

+INF

Positive infinity

-INF

Negative infinity

NaN

Not a number

+0

Positive zero

-0

Negative zero


Each value in Table 1-5 is represented by a specific bit pattern, except NaN. NaN, the result of any undefined operation, is represented by many bit patterns. Some of these bits patterns have the sign bit set and some do not, but the sign bit has no meaning.

The IEEE 754 standard distinguishes between quiet NaNs (which do not raise additional exceptions as they propagate through most operations) and signaling NaNs (which do). The IEEE 754 standard specifies action for when exceptions are enabled and action for when they are disabled.

In Oracle Database, exceptions cannot be enabled. Oracle Database acts as the IEEE 754 standard specifies for when exceptions are disabled. In particular, Oracle Database does not distinguish between quiet and signaling NaNs. You can use Oracle Call Interface (OCI) to retrieve NaN values from Oracle Database, but whether a retrieved NaN value is signaling or quiet depends on the client platform and is beyond the control of Oracle Database.

The IEEE 754 standard defines these classes of special values:

  • Zero

  • Subnormal

  • Normal

  • Infinity

  • NaN

The values in each class in the preceding list are larger than the values in the classes that precede it in the list (ignoring signs), except NaN. NaN is unordered with other classes of special values and with itself.

In Oracle Database:

  • All NaNs are quiet.

  • Any non-NaN value < NaN

  • Any NaN == any other NaN

  • All NaNs are converted to the same bit pattern.

  • -0 is converted to +0.

  • IEEE 754 exceptions are not raised.

See Also:

Oracle Database SQL Language Reference for information about floating-point conditions, which let you determine whether an expression is infinite or is the undefined result of an operation (is not a number or NaN).

Comparison Operators for Native Floating-Point Data Types

Oracle Database defines these comparison operators for native floating-point data types:

  • Equal to

  • Not equal to

  • Greater than

  • Greater than or equal to

  • Less than

  • Less than or equal to

  • Unordered

Comparisons ignore the sign of zero (-0 equals +0).

See Also:

"Special Values for 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 these arithmetic operators for native floating-point data types:

  • 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, but 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; however, this might cause 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. Therefore, except for 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 Floating-Point Data Types

Oracle Database defines functions that convert between floating-point and other data types, including string formats that use decimal precision (but precision might be lost during the conversion). For example:

Oracle Database can raise exceptions during conversion. The IEEE 754 standard defines these exceptions:

  • Invalid

  • Inexact

  • Divide by zero

  • Underflow

  • Overflow

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

Table 1-6 Values Resulting from Exceptions

Exception Value

Underflow

0

Overflow

-INF, +INF

Invalid Operation

NaN

Divide by Zero

-INF, +INF, NaN

Inexact

Any value – rounding was performed


Client Interfaces for Native Floating-Point Data Types

Oracle Database supports native floating-point data types in these client interfaces:

  • SQL

  • PL/SQL

  • Oracle Call Interface (OCI)

  • Oracle C++ Call Interface (OCCI)

  • Pro*C/C++

  • JDBC

Topics:

OCI Native Floating-Point Data Types SQLT_BFLOAT and SQLT_BDOUBLE

The OCI API implements the IEEE 754 single- 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 ADTs

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

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 FLOAT and DOUBLE to BINARY_FLOAT and BINARY_DOUBLE, 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

Oracle Database stores date and time (datetime) data in its own internal format, in 7-byte fields that correspond to century, year, month, day, hour, minute, and second.

Table 1-7 summarizes the SQL datetime data types. For more information about these data types, see Oracle Database SQL Language Reference.

Table 1-7 SQL Datetime Data Types

Date Type Usage

DATE

Use to store point-in-time (datetime) values in a table—for example, dates of jobs.

TIMESTAMP

Use to store datetime values that are precise to fractional seconds—for example, times of events that must be compared to determine the order in which they occurred.

TIMESTAMP WITH TIME ZONE

Use to store datetime values that must be gathered or coordinated across geographic regions.

TIMESTAMP WITH LOCAL TIME ZONE

Use to store datetime values when the time zone is insignificant—for example, in an application that schedules teleconferences, where participants each see the start and end times for their own time zone.

Appropriate for two-tier applications in which you want to display dates and times that use the time zone of the client system. Usually inappropriate for 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.

INTERVAL YEAR TO MONTH

Use to store the difference between two datetime values, where only the year and month are significant—for example, to set a reminder for a date 18 months in the future, or check whether 6 months have elapsed since a particular date.

INTERVAL DAY TO SECOND

Use to store the precise difference between two datetime values—for example, 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, use a large number of days.


Topics:

See Also:

Oracle Call Interface Programmer's Guide for more information about Oracle Database internal date types

Displaying Current Date and Time

The simplest way to display the current date and time is:

SELECT SYSDATE FROM DUAL

The preceding command displays the current date and time in the default date format, which depends on the initialization parameter NLS_DATE_FORMAT.

The standard Oracle Database default date format is DD-MON-RR. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year. For example, in the datetime format DD-MON-YY, 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.

To display SYSDATE in a nondefault format, use the TO_CHAR function with a datetime format model.

Example 1-1 uses TO_CHAR with a format model to display SYSDATE in a nondefault format, which includes the qualifier BC or AD. (By default, SYSDATE is displayed without this qualifier.)

Example 1-1 Displaying Current Date and Time in Nondefault Format

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY BC') NOW FROM DUAL;
 

Result:

NOW
-----------------------
18-MAR-2009 AD
 
1 row selected.

Tip:

When testing code that uses SYSDATE, it can be helpful to set SYSDATE to a constant. Do this with the initialization parameter FIXED_DATE, described in Oracle Database Reference.

See Also:

Displaying and Inserting Dates in Nondefault Formats

Although Oracle Database always stores dates in the default date format (set by the initialization parameter NLS_DATE_FORMAT), you can display and insert dates in nondefault formats by using the TO_CHAR and TO_DATE functions, respectively, with datetime format models.

Example 1-2 creates a table with a DATE column and inserts into it a date specified in a nondefault format. The date is stored in the default format, as the first SELECT statement shows. The second SELECT statement displays the date in a nondefault format.

Example 1-2 Inserting and Displaying Date in Nondefault Formats

DROP TABLE dates;
CREATE TABLE dates (d DATE);
 
INSERT INTO dates VALUES (TO_DATE('OCT 27, 1998', 'MON DD, YYYY'));
 
SELECT d FROM dates;
 

Result:

D
---------
27-OCT-98
 
1 row selected.

SELECT TO_CHAR(d, 'YYYY-MON-DD') D FROM dates;

Result:

D
--------------------
1998-OCT-27
 
1 row selected.

Caution:

Be careful when using the YY datetime format element, which indicates the year in the current century. For example, in the 21st century, the format DD-MON-YY, 31-DEC-92 is December 31, 2092 (not December 31, 1992, as you might expect). To store 20th century dates in the 21st century by specifying only the last two digits of the year, use the RR datetime format element (the default).

See Also:

Displaying and Inserting Times in Nondefault Formats

Although Oracle Database always stores times in the 24-hour format HH24:MI:SS, you can display and insert times in nondefault formats by using the TO_CHAR and TO_DATE functions, respectively, with datetime format models.

In a DATE column:

  • The default time is 12:00:00 A.M. (midnight).

    The default time applies to any value in the column that has no time portion, either because none was specified or because the value was truncated.

  • The default date is the first day of the current month.

    The default date applies to any value in the column that has no date portion, because none was specified.

Example 1-3 creates a table with a DATE column and inserts into it three dates specified in nondefault formats—one with both date and time portions, one with no time portion, and one with no date portion. The first SELECT statement shows the current date. The second SELECT statement displays the three dates in a nondefault format that includes both date and time portions.

Example 1-3 Inserting and Displaying Dates and Times in Nondefault Formats

DROP TABLE birthdays;
CREATE TABLE birthdays (name VARCHAR2(20), day DATE);
 
INSERT INTO birthdays (name, day)
VALUES ('Annie',
        TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-RR HH:MI A.M.')
       );
 
INSERT INTO birthdays (name, day)
VALUES ('Bobby',
        TO_DATE('5-APR-02','DD-MON-RR')
       );
 
INSERT INTO birthdays (name, day)
VALUES ('Cindy',
        TO_DATE('8:25 P.M.','HH:MI A.M.')
       );
 

Display current date:

SELECT SYSDATE FROM DUAL;
 

Result:

SYSDATE
---------
05-NOV-10
 
1 row selected.

Display both date and time portions of stored datetime values:

SELECT name,
       TO_CHAR(day, 'Mon DD, RRRR') DAY,
       TO_CHAR(day, 'HH:MI A.M.') TIME
FROM birthdays;
 

Result:

NAME                 DAY                   TIME
-------------------- --------------------- ----------
Annie                Nov 13, 1992          10:56 A.M.
Bobby                Apr 05, 2002          12:00 A.M.
Cindy                Nov 01, 2010          08:25 P.M.
 
3 rows selected.

Arithmetic Operations with Datetime Data Types

You can perform arithmetic operations on datetime values. The results of such operations are determined by the rules in Oracle Database SQL Language Reference.

SQL has many datetime functions that you can use in datetime expressions. For example, the function ADD_MONTHS returns the date that is a specified number of months from a specified date. For the complete list of datetime functions, see Oracle Database SQL Language Reference.

Conversion Functions for Datetime Data Types

Table 1-8 summarizes the SQL functions that convert to or from datetime data types.

Table 1-8 SQL Conversion Functions for Datetime Data Types

Function Converts ... To ...

NUMTODSINTERVAL

NUMBER

INTERVAL DAY TO SECOND

NUMTOYMINTERVAL

NUMBER

INTERVAL DAY TO MONTH

TO_CHAR

DATE

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

VARCHAR2

TO_DATE

CHAR

VARCHAR2

NCHAR

NVARCHAR2

DATE

TO_DSINTERVAL

CHAR

VARCHAR2

NCHAR

NVARCHAR2

INTERVAL DAY TO SECOND

TO_TIMESTAMP

CHAR

VARCHAR2

NCHAR

NVARCHAR2

TIMESTAMP

TO_TIMESTAMP_TZ

CHAR

VARCHAR2

NCHAR

NVARCHAR2

TIMESTAMP WITH TIME ZONE

TO_YMINTERVAL

CHAR

VARCHAR2

NCHAR

NVARCHAR2

INTERVAL DAY TO MONTH


Importing, Exporting, and Comparing Datetime Types

You can import, export, and compare TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE values without worrying about time zone offsets, because the database stores these values in normalized format.

When importing, exporting, and comparing DATE and TIMESTAMP values, you must adjust them to account for any time zone differences between source and target databases, because the database does not store their time zones.

Representing Specialized Data

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 for information about Oracle Spatial features

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

Oracle Multimedia provides the ORDAudio, ORDDoc, ORDImage, ORDImageSignature, ORDVideo, and SI_StillImage ADTs (including 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

Representing Large Amounts of Data

For representing large amounts of data, Oracle Database provides:

Large Objects (LOBs)

Large Objects (LOBs) are data types that are designed to store large amounts of data (the maximum size of a LOB depends on how your database is configured). Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

Table 1-9 summarizes the LOBs.

Table 1-9 Large Objects (LOBs)

Data Type Description

BLOB

Binary large object

Stores any kind of data in binary format.

Typically used for multimedia data such as images, audio, and video.

CLOB

Character large object

Stores string data in the database character set format.

Used for large strings or documents that use the database character set exclusively.

NCLOB

National character large object

Stores string data in National Character Set format.

Used for large strings or documents in the National Character Set.

BFILE

External large object

Stores a binary file outside the database in the host operating system file system. Applications have read-only access to BFILEs.

Used for static data that applications do not manipulate, such as image data.

Any kind of data (that is, any operating system file) can be stored in a BFILE. For example, you can store character data in a BFILE and then load the BFILE data into a CLOB, specifying the character set when loading.


An instance of type BLOB, CLOB, or NCLOB can be either temporary (declared in the scope of your application) or persistent (created and stored in the database).

See Also:

LONG and LONG RAW Data Types

Note:

Oracle supports the LONG and LONG RAW data types for backward compatibility, but strongly recommends that you convert LONG columns to LOB columns and LONG RAW columns to BLOB columns.

LONG columns store variable-length character strings containing up to 2 gigabytes - 1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer. For more information about the LONG data type, including its many restrictions, see Oracle Database SQL Language Reference.

The LONG RAW (and RAW) data types store data that is not to be explicitly converted by Oracle Database when moving data between different systems. These data types are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use. You can index RAW data, but not LONG RAW data. For more information about the RAW and LONG RAW data types, see Oracle Database SQL Language Reference.

Representing Searchable Text

Rather than writing low-level code to do full-text searches, you can use Oracle Text. Oracle Text 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 about Oracle Text

Representing XML Data

If you have information stored as files in XML format, or want to store an ADT in XML format, then you can use the Oracle-supplied type XMLType.

When you create an XMLType column in a table, you can store the XML data in any of these ways:

  • In a CLOB column

  • As binary XML (stored internally as a CLOB)

  • Object relationally

XMLType has member functions that access, extract, and query the XML data using W3C XPath expressions (see Oracle XML DB Developer's Guide). Also, Oracle provides SQL XML functions that manipulate or return whole or partial XML documents (see Oracle Database SQL Language Reference) and these PL/SQL packages (described in Oracle Database PL/SQL Packages and Types Reference):

See Also:

Representing Dynamically Typed Data

Some languages allow data types to change at run time, and some 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 their values to determine their underlying representation. For example, a single table column can have a numeric value in one row, a string value in another row, and an object in another row.

You can use the Oracle-supplied ADT SYS.ANYDATA to represent values of any scalar type or ADT. SYS.ANYDATA has methods that accept scalar values of any type, and turn them back into scalars or objects. Similarly, you can use the Oracle-supplied ADT SYS.ANYDATASET to represent values of any collection type. For more information about these ADTs, see Oracle Database Object-Relational Developer's Guide.

To check and manipulate type information, use the DBMS_TYPES package, as in Example 1-4. For more information about this package, see Oracle Database PL/SQL Packages and Types Reference.

With OCI, use the OCIAnyData and OCIAnyDataSet interfaces, described in Oracle Call Interface Programmer's Guide.

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

CREATE OR REPLACE TYPE employee_type AS
  OBJECT (empno NUMBER, ename VARCHAR2(10));
/
 
DROP TABLE mytab;
CREATE TABLE mytab (id NUMBER, data SYS.ANYDATA);
 
INSERT INTO mytab (id, data)
VALUES (1, SYS.ANYDATA.ConvertNumber(5));
 
INSERT INTO mytab (id, data)
VALUES (2, SYS.ANYDATA.ConvertObject(Employee_type(5555, 'john')));
 
CREATE OR REPLACE PROCEDURE p IS
  CURSOR cur IS SELECT id, data FROM mytab;
  v_id                        mytab.id%TYPE;
  v_data                      mytab.data%TYPE;
  v_type                      SYS.ANYTYPE;
  v_typecode                  PLS_INTEGER;
  v_typename                  VARCHAR2(60);
  v_dummy                     PLS_INTEGER;
  v_n                         NUMBER;
  v_employee                  employee_type;
  non_null_anytype_for_NUMBER exception;
  unknown_typename            exception;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_id, v_data;
    EXIT WHEN cur%NOTFOUND;
 
    /* typecode signifies type represented by v_data.
       GetType also produces a value of type SYS.ANYTYPE with methods you
       can call to find precision and scale of a number, length of a
       string, and so on. */
 
       v_typecode := v_data.GetType (v_type /* OUT */);
 
    /* Compare typecode to DBMS_TYPES constants to determine type of data
       and decide how to display it. */
  
    CASE v_typecode
      WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
        IF v_type IS NOT NULL THEN  -- This condition should never happen.
          RAISE non_null_anytype_for_NUMBER;
        END IF;
 
      -- For each type, there is a Get method.
      v_dummy := v_data.GetNUMBER (v_n /* OUT */);
      DBMS_OUTPUT.PUT_LINE
        (TO_CHAR(v_id) || ': NUMBER = ' || TO_CHAR(v_n) );
 
       WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
         v_typename := v_data.GetTypeName();
         IF v_typename NOT IN ('HR.EMPLOYEE_TYPE') THEN
           RAISE unknown_typename;
         END IF;
         v_dummy := v_data.GetObject (v_employee /* OUT */);
         DBMS_OUTPUT.PUT_LINE
           (TO_CHAR(v_id) || ': user-defined type = ' || v_typename ||
            ' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' );
    END CASE;
  END LOOP;
  CLOSE cur;
EXCEPTION
  WHEN non_null_anytype_for_NUMBER THEN
    RAISE_Application_Error (-20000,
      'Paradox: the return AnyType instance FROM GetType ' ||
      'should be NULL for all but user-defined types');
  WHEN unknown_typename THEN
    RAISE_Application_Error( -20000, 'Unknown user-defined type ' ||
      v_typename || ' - program written to handle only HR.EMPLOYEE_TYPE');
END;
/
 
SELECT t.data.gettypename() AS "Type Name" FROM mytab t;
 

Result:

Type Name
--------------------------------------------------------------------------------
SYS.NUMBER
HR.EMPLOYEE_TYPE
 
2 rows selected.

Representing ANSI, DB2, and SQL/DS Data

SQL statements that create tables and clusters can use ANSI data types and data types from the IBM products SQL/DS and DB2 (except those noted after this paragraph). Oracle Database converts the ANSI or IBM data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type. For conversion details, see Oracle Database SQL Language Reference.

Note:

SQL statements cannot use the SQL/DS and DB2 data types TIME, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC, because they have no equivalent Oracle data types.

Representing Conditional Expressions as Data

Oracle Expression Filter (a feature of Rules Manager) enables you to store, index, and evaluate conditional expressions in one or more columns of a database table. Then Oracle Expression Filter compares the stored expressions to incoming data, identifying rows of interest.

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.

DROP TABLE traders;
CREATE TABLE traders (
  name     VARCHAR2(10),
  email    VARCHAR2(20),
  interest VARCHAR2(30)
);

Solution:

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

    CREATE OR REPLACE TYPE ticker AS OBJECT (
      symbol VARCHAR2(20),
      price  NUMBER,
      change NUMBER
    );
    /
    
  2. Create an attribute set based on the type ticker:

    BEGIN
      DBMS_EXPFIL.DROP_ATTRIBUTE_SET (attr_set  => 'ticker');
    END;
    /
    BEGIN
      DBMS_EXPFIL.CREATE_ATTRIBUTE_SET
      (attr_set  => 'ticker',
      from_type => 'YES');
    END;
    /
    
  3. Associate the attribute set with the expression set stored in the column trader.interest:

    BEGIN
      DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET
      (attr_set => 'ticker',
      expr_tab => 'traders',
      expr_col => 'interest');
      END;
    /
    

    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 specific prices. For example:

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

    SELECT name, email
    FROM traders
    WHERE EVALUATE (
      interest,
      'symbol=>''ABC'',
      price=>31,
      change=>5.2'
    ) = 1;
     
    

    Result:

    NAME       EMAIL
    ---------- --------------------
    Vishu      vishu@example.com
     
    1 row selected.
    

    Tip:

    To speed up the query, create an Oracle Expression Filter index on the interest column.

    See Also:

    Oracle Database Rules Manager and Expression Filter Developer's Guide for information about developing applications using Oracle Expression Filter

Identifying Rows by Address

The fastest way to access a row is by its address, or rowid, which uniquely identifies it. Different rows in the same data block can have the same rowid only if they are in different clustered tables. If a row is larger than one data block, then its rowid identifies its initial row piece.

To see rowids, query the ROWID pseudocolumn, whose value is a string that represents the address of the row. The string has the data type ROWID or UROWID.

Note:

When you update a row in a table compressed with Hybrid Columnar Compression (HCC), the ROWID of the row changes. HCC, a feature of certain Oracle storage systems, is described in Oracle Database Concepts.

Topics:

See Also:

Oracle Database SQL Language Reference for more information about the ROWID pseudocolumn

Querying the ROWID Pseudocolumn

Each table in Oracle Database has a pseudocolumn named ROWID, which can appear in a query in either the SELECT list or WHERE clause.

Example 1-5 creates a table of with a column of the data type ROWID, populates it with rowids by querying the ROWID pseudocolumn inside an INSERT statement, and then displays it. The rowids of the table rows show how they are stored.

Example 1-5 Querying the ROWID Pseudocolumn

DROP TABLE t_tab;  -- in case it exists
CREATE TABLE t_tab (col1 ROWID);
 
INSERT INTO t_tab (col1)
SELECT ROWID
FROM employees
WHERE employee_id > 199;
 

Query:

SELECT employee_id, rowid
FROM employees
WHERE employee_id > 199;
 

ROWID varies, but result is similar to:

EMPLOYEE_ID ROWID
----------- ------------------
        200 AAAPeSAAFAAAABTAAC
        201 AAAPeSAAFAAAABTAAD
        202 AAAPeSAAFAAAABTAAE
        203 AAAPeSAAFAAAABTAAF
        204 AAAPeSAAFAAAABTAAG
        205 AAAPeSAAFAAAABTAAH
        206 AAAPeSAAFAAAABTAAI
 
7 rows selected.
 

Query:

SELECT * FROM t_tab;
 

COL1 varies, but result is similar to:

COL1
------------------
AAAPeSAAFAAAABTAAC
AAAPeSAAFAAAABTAAD
AAAPeSAAFAAAABTAAE
AAAPeSAAFAAAABTAAF
AAAPeSAAFAAAABTAAG
AAAPeSAAFAAAABTAAH
AAAPeSAAFAAAABTAAI
 
7 rows selected.

ROWID Data Type

In heap-organized tables generated by Oracle Database, the values in the ROWID pseudocolumn have the data type ROWID. Internally, this data type is a structure that stores information that the database server needs to access a row. The format of this structure is either restricted, extended, or external binary.

Note:

Creating a column of the type ROWID (like col1 in Example 1-5) does not guarantee that its values will be valid rowids.

Topics:

Restricted Internal ROWID Format

A ROWID structure with the restricted internal format has these components:

  • Data file identifier

  • Block identifier

  • Row identifier

On most platforms, the size of this structure is 6 bytes.

The database server returns a ROWID pseudocolumn value to the client application as an 18-character string with a hexadecimal encoding of each component.

Extended Internal ROWID Format

A ROWID structure with the extended internal format has the same components as the restricted format and a data object number, which identifies a database segment. On most platforms, the size of this structure is 10 bytes.

The database server returns a ROWID pseudocolumn value to the client application as an 18-character string with a base-64 encoding of each component. For example, the string might be "AAAA8mAALAAAAQkAAA", which represents a base-64 encoding of the components of the extended ROWID in a four-piece format, OOOOOOFFFBBBBBBRRR.

To access and interpret the contents of an extended rowid, use the DBMS_ROWID package, described in Oracle Database PL/SQL Packages and Types Reference.

External Binary Internal ROWID Format

Some client applications use a binary internal format for the ROWID structure. For example, OCI and some precompiler applications can map the ROWID data type to a 3GL structure on bind or define calls.

In binary internal format, the ROWID structure is the same size for restricted and extended rowids. For a restricted rowid, the data object number is stored in an unused field.

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

UROWID Data Type

In tables that are foreign (that is, not generated by Oracle Database) or index-organized, the values in the ROWID pseudocolumn have the data type UROWID. This data type stores a universal rowid (urowid).

Urowids for foreign tables (such as DB2 tables accessed through a gateway) are called foreign rowids.

Urowids for index-organized tables (whose rows are stored in index leaves, which can move) are called logical rowids. Oracle Database creates logical rowids based on the primary key of the table. The logical rowids do not change if the primary key does not change.

To store urowids in a table, define a column of data type UROWID for the table and then retrieve the value of the ROWID pseudocolumn into that column.

How Oracle Database Converts Data Types

Generally, you cannot assign a value of one data type to a variable or column of another data type, or create an expression with values of different data types. However, in some cases, Oracle Database accepts data of one type where it expects data of another type and then automatically converts the accepted data to the expected type. This is called implicit data conversion.

Topics:

See Also:

Oracle Database SQL Language Reference for more information about data type conversion

Data Type Conversion During Assignments

For the assignment

variable := expression

if the data type of expression differs from that of variable, then Oracle Database tries to convert the data type of expression to that of variable. For information about when that is possible, see Oracle Database SQL Language Reference.

A character-to-NUMBER conversion succeeds only if the character string represents a valid number. A character-to-DATE conversion succeeds only if the character string satisfies the session default date format. (For information about the default date format, see "Displaying Current Date and Time".)

Examples

Assume that test_package, its public variable var1, and table1_tab are declared as follows:

CREATE OR REPLACE PACKAGE test_package AS
  var1 CHAR(5);
END;
/
 
DROP TABLE table1_tab;
CREATE TABLE table1_tab (col1 NUMBER);

In the assignment

variable := expression

the data type of expression must be either the same as, or implicitly convertible to, the data type of variable. For example, for this assignment, Oracle Database automatically converts zero to the data type of var1, which is CHAR(5):

var1 := 0;

In the statement

INSERT INTO table1_tab (col1) VALUES (expression)

the data type of expression must be either the same as, or implicitly convertible to, the data type of col1. For example, for this statement, Oracle Database automatically converts the string '19' to the data type of col1, which is NUMBER:

INSERT INTO table1_tab (col1) VALUES ('19')

In the statement

UPDATE table1_tab SET column = expression

the data type of expression must be either the same as, or implicitly convertible to, the data type of column. For example, for this statement, Oracle Database automatically converts the string '30' to the data type of col1, which is NUMBER:

UPDATE table1_tab SET col1 = '30';

In the statement

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, for this statement, Oracle Database automatically converts the value selected from col1, which is 30, to the data type of var1, which is CHAR(5):

SELECT col1 INTO var1 FROM table1_tab WHERE col1 = 30;

Data Type Conversion During Expression Evaluation

When evaluating an expression, Oracle Database can perform the same automatic conversions that it does for assignments. The target data type is determined by the context of the expression. For example, if an expression is the operand of an arithmetic operator, then Oracle Database tries to convert the value of the expression to NUMBER; if the expression is the operand of a string function, then Oracle Database tries to convert the value of the expression to VARCHAR2.

For the assignment

variable := expression

Oracle Database first evaluates expression, using the conversion rules for expressions. If the evaluation succeeds, the result is a single value of a single data type, which Oracle Database tries to assign to variable, using the conversion rules for assignments.

Metadata for SQL Operators and Functions

The dynamic performance view V$SQLFN_METADATA contains metadata about SQL operators and functions. For every function in V$SQLFN_METADATA, the dynamic performance view V$SQLFN_ARG_METADATA has one row of metadata about each function argument. If a function argument can be repeated (as in the functions LEAST and GREATEST), then V$SQLFN_ARG_METADATA has only one row for each repeating argument. You can join these two views on the column FUNCID.

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

Topics:

See Also:

ARGn Data Type

In the view V$SQLFN_METADATA, the column DATATYPE is the data type of the function (that is, the data type that the function returns). This data type can be an Oracle data type, data type family (see "Data Type Families"), or ARGn. ARGn is the data type of the nth argument of the function. For example:

DISP_TYPE Data Type

In the view V$SQLFN_METADATA, DISP_TYPE 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 1-10 Display Types of SQL Functions

Display Type Description Example

NORMAL

FUNC(A,B,...)

LEAST(A,B,C)

ARITHMETIC

A FUNC B)

A+B

PARENTHESIS

FUNC()

SYS_GUID()

RELOP

A FUNC B

A IN B

CASE_LIKE

CASE statement or DECODE decode

 

NOPAREN

FUNC

SYSDATE


Data Type Families

Often, a SQL function argument can have any data type in a data type family. Table 1-11 shows the data type families and their member data types.

Table 1-11 Data Type Families

Family Data Types

STRING

CHARACTER

 

VARCHAR2

 

CLOB

 

NCHAR

 

NVARCHAR2

 

NCLOB

 

LONG

NUMERIC

NUMBER

 

BINARY_FLOAT

 

BINARY_DOUBLE

DATETYPE

DATE

 

TIMESTAMP

 

TIMESTAMP WITH TIME ZONE

 

TIMESTAMP WITH LOCAL TIME ZONE

 

INTERVAL YEAR TO MONTH

 

INTERVAL DAY TO SECOND

BINARY

BLOB

 

RAW

 

LONGRAW