Oracle Data Types Explained

The built-in Oracle data types fall in to several sub categories:

character types
number types
long/raw types
datetime and interval types
large object data types (LOBs)
rowid data types

Character data types

Character data types in Oracle can be further subdivided in to fixed length and variable length types. The fixed length character data types are the NCHAR and CHAR data types. The variable length data types are VARCHAR2

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.

and NVARCHAR2. The difference between the fixed-length character types and the variable-length character types is that fixed length character strings are blank padded to the specified string length. This can cause problems when comparing fixed length and varying length strings - both strings may look the same and be assigned the same value in the code but will not be the same due to the blank padding.

Let's look at an example using Oracle PL/SQL.

DECLARE
fixed_len_str CHAR(5) := 'AND';
var_len_str VARCHAR2(5) := 'AND';
BEGIN
IF fixed_len_str = var_len_str THEN
DBMS_OUTPUT.PUT_LINE('Strings match');
ELSE
DBMS_OUTPUT.PUT_LINE('Strings don't match');
END IF:
END;

You might expect the output of this to be 'Strings match', but because of the blank padding for the fixed length string, what it will actually hold is 'AND'+ 2 trailing spaces which is obviously not the same.

Another important point about the CHAR and the VARCHAR2 data types in Oracle is that the length can be specified either as Bytes or as Characters.

For example

DECLARE
fixed_len_str CHAR(5 BYTE) := 'AND';
var_len_str VARCHAR2(5 BYTE) := 'AND';
char_fixed_len_str CHAR(5 CHAR) := 'AND';
char_var_len_str VARCHAR2(5 CHAR) := 'AND';

The default is to use the setting of the NLS_LENGTH_SEMANTICS parameter, but this is over ridden if specified explicitly in the declaration.

For multi-byte character sets used with Oracle this obviously makes a big difference in the number of characters that can be held in the string.

NCHAR and NVARCHAR2 are Unicode-only data types and the size determines the maximum number of characters that variables of this type can hold. They work in the same way as CHAR and VARCHAR2 data types.

There is also a VARCHAR data type but this should not be used as it is likely to be redefined in future releases of the Oracle database.

Number data types

These consist of the following Oracle data types:

NUMBER
FLOAT
BINARY_FLOAT
BINARY_DOUBLE

NUMBER

The NUMBER data type can store signed fixed point values ranging from 10^-130 up to but not including 10¹²⁶ including zero as well as floating point numbers.

Floating point numbers are specified as NUMBER (without precision or scale).

Fixed point values are specified as NUMBER(p,s)where p is the precision - the maximum number of significant digits (this ranges from 1 to 38) - and s is the scale - the number of digits from the decimal point to the least significant digit (this ranges from -84 to -127).

If the scale is negative, that means the number will be rounded. For example a scale of -3 means the number will be rounded to the nearest 1000. If the scale is zero (the default) then the number is an integer.

If precision is less than scale, it specifies the number of significant digits after the decimal point. For example a declaration NUMBER(1,3) means there will be just 1 significant (non-zero) digit to the right the decimal point.

FLOAT

This is a sub type of NUMBER used internally by Oracle when converting ANSI FLOAT data. It is recommended that BINARY_FLOAT or BINARY_DOUBLE be used instead of FLOAT.

BINARY_FLOAT and BINARY_DOUBLE

These Oracle data types differ from the NUMBER data type in the way the values are stored internally in the Oracle Database. The NUMBER data type stores values using decimal precision, whereas BINARY_FLOAT and BINARY_DOUBLE are stored using binary precision.

BINARY_FLOAT is a 32-bit (4 byte), single-precision floating-point number data type.

BINARY_DOUBLE is a 64-bit (8 byte), double-precision floating-point number data type.

Long/raw data types

These Oracle data types are supported for backwards compatibility but new applications should use the large object data types (BLOB, CLOB, BFILE, NCLOB) as these types have less restrictions and have more features.

The LONG type in Oracle stores character data up to 2 gigabytes in length, but there are a number of restrictions applying to them, including:

LONG columns in a table can't be indexed
each table can have only one column of LONG data type
stored functions can't return a LONG value
LONG columns can't appear in GROUP BY or ORDER BY clauses or with the DISTINCT operator in queries

None of these restrictions apply to Oracle LOB's, so it is better to replace LONG definitions with CLOB and LONG RAW with BLOB. A full list of restrictions is available in the Oracle SQL Language Reference.
The RAW data type is used to store binary data up to 2000 bytes in length.

The LONG RAW type stores binary data up to 2 gigabytes in length.

Datetime and interval types

Oracle now supports 4 datetime types:

DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
and TIMESTAMP WITH LOCAL TIME ZONE

and 2 interval types:

INTERVAL YEAR TO MONTH
and INTERVAL DAY TO SECOND

DATE

The Oracle data type DATE has a fixed length of 7 bytes and comprises the century, year, month, day, hour, minute and second of a date between 1 January 4712 BC and 31 December 9999 AD.

Timestamp data types

The Oracle timestamp types extend the DATE data type and enable fractions of a second to be stored (with a precision of up to 9 digits) along with the year, month, day, hour and minute.

TIMESTAMP WITH TIME ZONE also stores the time zone region name or offset from UTC (GMT) to enable local time zone information to be preserved.

TIMESTAMP WITH LOCAL TIME ZONE data is converted to the database time zone when it is stored and to the time zone of the user when it is retrieved.

Interval data types

The interval Oracle data types are designed to store a period of time rather than a specific date and time.

The INTERVAL YEAR TO MONTH data type stores a period of time as years (with a precision from 0 to 9 digits) and months (with a value from 0 to 11).

The INTERVAL DAY TO SECOND data type stores a period of time as days (with a precision from 0 to 9 digits), hours, minutes and seconds (with a fractional second precision from 0 to 9 digits).

As the interval data types do not have format models like the datetime data types, to present the information neatly you have to use the EXTRACT function to retrieve the part of the interval interest and convert it to a character string which can then be concatenated with a label.

For example, a query to see how long employees had been with the company you might run a query something like this:

SELECT
emp_name "Employee"
, EXTRACT(YEAR FROM (SYSDATE - start_date) YEAR TO MONTH )|| ' years '
|| EXTRACT(MONTH FROM (SYSDATE - start_date) YEAR TO MONTH )
|| ' months' "Tenure"
FROM emp;

and produce the following results

Employee Tenure
------------------------------ --------------------------
Mike Smith   2 years 3 months
Andrew Jackson 2 years 6 months
Mike Jones 3 years 3 months
Andrew McKay   12 years 8 months
Chris Jenkins 8 years 1 months
Chris Reynolds   0 years 3 months

Large object (LOB) Oracle data types

The large object Oracle data types are replacements for the old LONG and LONG RAW types and are designed to store unstructured data such as text or multimedia within the database (in the case of CLOB, BLOB and NCLOB data types) or externally in the case of the BFILE data type.

The BFILE data type can store up to 2⁶⁴-1 bytes (subject to operating system restrictions) of binary data in an operating system file. These files are read-only in the database.

The CLOB, NCLOB and BLOB Oracle data types can store up to (2³²-1*LOB chunk size) bytes of character data. If the LOB CHUNK parameter is the same as the Oracle database block size then the limit is (2³²-1*database block size) bytes. If the size of a LOB is less than approximately 4000 bytes, the data can be stored in line if this is enabled when creating the LOB column in the table. The NCLOB data type stores Unicode data.

Rowid Oracle data types

There are two rowid data types - ROWID for heap-organised Oracle tables and UROWID for index-organised Oracle tables (which only have an index segment) and non-Oracle tables (tables in a non-Oracle database accessed through a gateway).

ROWID data type

The Oracle data type ROWID stores the physical address of a row in a heap-organised table and is stored as a base 64 value (by using upper and lower case letters A-Z, the digits 0-9 as well as the plus sign (+) and the forward slash (/)).

ROWIDs are comprised of the following components:

data block of the data file containing the row
row in the block
the number of tech database file containing the row
and the data object number

UROWID data type

The Oracle database creates logical rowids based on the primary key of the table. These logical rowids do not change as long as the primary key does not change.