## SQLWays - Oracle Data Types

This chapter describes the data types supported by Oracle.

- CHAR, NCHAR, VARCHAR2 and NVARCHAR2
- NUMBER
- DATE and TIMESTAMP
- LONG, RAW and LONG RAW
- BLOB, CLOB, NCLOB and BFILE

### CHAR, NCHAR, VARCHAR2 and NVARCHAR2

Character data types store character data in the database character or national character set.

CHAR[(n)]

Fixed-length character data of length n bytes. The maximum length is 2000 bytes, the default value is 1.

NCHAR[(n)]

Fixed-length character data of length n characters or bytes, depending on the national character set. The maximum length is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. The default value is 1.

VARCHAR2(n)

Variable-length character data with length of n characters. The maximum length is 4000 bytes. You must specify the maximum length for VARCHAR2 columns.

NVARCHAR2(n)

Variable-length character data having maximum length n characters or bytes, depending on the national character set. The maximum length is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify the maximum length for NVARCHAR2 columns.

### NUMBER

NUMBER(p,s)

The NUMBER data type stores zero, positive, and negative fixed and floating point numbers with 38 digits of precision.

p is the precision, or the total number of digits. The precision can range from 1 to 38.

s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

To specify integers use the following form: NUMBER(p) or NUMBER(p,0).

### DATE and TIMESTAMP

DATE

The DATE data type stores date and time information. For each DATE value, Oracle stores the following information: year, month, day, hour, minute, and second.

Date data can range from January 1, 4712 BC to December 31, 9999.

TIMESTAMP[(fractional_seconds_precision)]

The TIMESTAMP data type is an extension of the DATE data type. For each TIMESTAMP value, Oracle stores the following information: year, month, day, hour, minute, second and fraction of second.

fractional_seconds_precision optionally specifies the number of digits in the fractional part of second and can be a number in the range 0 to 9. The default is 6.

The TIMESTAMP data type is available in Oracle 9i Release 1 (9.0.1) or later.

### LONG, RAW and LONG RAW

LONG

LONG columns store variable-length character data with a maximum length of 2^31 - 1 (2,147,483,647) bytes. A table cannot contain more than one LONG column.

RAW(n)

RAW is variable-length data type. Oracle does not perform character conversion when transmitting RAW data. The maximum length is 2000 bytes. You must specify the size for RAW columns.

LONG RAW

LONG RAW columns store variable-length data with a maximum length of 2^31 - 1 (2,147,483,647) bytes. Oracle does not perform character conversion when transmitting LONG RAW data.

### BLOB, CLOB, NCLOB and BFILE

The built-in LOB data types BLOB, CLOB and NCLOB (stored internally), and BFILE (stored externally), can store large and unstructured data such as text, images and spatial data up to 4 gigabytes in size.

BLOB

The BLOB data type stores binary large objects. BLOB can store up to 4 gigabytes of binary data.

CLOB

The CBLOB data type stores character large objects. CLOB can store up to 4 gigabytes of character data.

NCLOB

The NCBLOB data type stores character large objects in multibyte national character set. NCLOB can store up to 4 gigabytes of character data.

BFILE

The BFILE data type enables access to binary file LOBs that are stored in file systems outside the Oracle database. A BFILE column stores a locator, which serves as a pointer to a binary file on the server's file system. The maximum file size supported is 4 gigabytes.

[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.