Oracle Data Types Subtypes

Search the Reference Library pages:

Oracle Data Types & Subtypes

Version 11.1

Predefined Scalar Data Types - Data types that do not contain internal components

String Data Types

Data Type Definition	PL/SQL	SQL
Fixed Length
CHAR(<chars>)	32,767 bytes	2,000 chars
NCHAR(<bytes>)	32,767 bytes	1,000 bytes
Variable Length
LONG	32,760 bytes	2GB
VARCHAR2(<chars>)	32,767 bytes	4,000 chars
NVARCHAR2(<bytes>)	32,767 bytes	2,000 bytes

Note:

The database character set controls (and specifies) the character set of CHAR & VARCHAR2 columns.
The national character set controls the character set of NCHAR & NVARCHAR2 columns.

CHAR & NCHAR Subtypes

Data Type Definition	PL/SQL	SQL
CHARACTER(<chars>)	-	2,000 chars
NATIONAL CHAR VARYING(<chars>)	32,767 bytes	2,000 bytes
NATIONAL CHARACTER VARYING(<chars>)	32,767 bytes	2,000 bytes
NCHAR VARYING(<chars>)	32,767 bytes	2,000 bytes

VARCHAR2 & NVARCHAR2 Subtypes

Data Type Definition	PL/SQL	SQL
CHAR VARYING(<chars>)	32,767 bytes	4,000 bytes
CHARACTER(<chars>)	32,767 bytes	-
CHARACTER VARYING(<chars>)	32,767 bytes	4,000 bytes
STRING(<chars>)	32,767 bytes	N/A
VARCHAR	32,767 bytes	4,000 bytes

Create Table

CREATE TABLE test (
charcol                     CHAR(2000),
charvaryingcol              CHAR VARYING(4000),
charactercol                CHARACTER(2000),
charactervaryingcol         CHARACTER VARYING(4000),
nationalcharvarying         NATIONAL CHAR VARYING(2000),
nationalcharactervaryingcol NATIONAL CHARACTER VARYING(2000),
ncharcol                    NCHAR(1000),
ncharvaryingcol             NCHAR VARYING(2000),
nvarchar2col                NVARCHAR2(2000),
varcharcol                  VARCHAR(4000),
varchar2col                 VARCHAR2(4000));

SQL> desc test
Name                        Type
--------------------------- --------------
CHARCOL                     CHAR(2000)
CHARVARYINGCOL              VARCHAR2(4000)
CHARACTERCOL                CHAR(2000)
CHARACTERVARYINGCOL         VARCHAR2(4000)
NATIONALCHARVARYING         NVARCHAR2(2000)
NATIONALCHARACTERVARYINGCOL NVARCHAR2(2000)
NCHARCOL                    NCHAR(1000)
NCHARVARYINGCOL             NVARCHAR2(2000)
NVARCHAR2COL                NVARCHAR2(2000)
VARCHARCOL                  VARCHAR2(4000)
VARCHAR2COL                 VARCHAR2(4000)

Define Variables

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE test IS
charcol                     CHAR(32767);
charvaryingvar              CHAR VARYING(32767);
charactervar                CHARACTER(32767);
charactervaryingvar         CHARACTER VARYING(32767);
nationalcharvaryvar         NATIONAL CHAR VARYING(32767);
nationalcharactervaryingvar NATIONAL CHARACTER VARYING(32767);
ncharvar                    NCHAR(32767);
ncharvaryingvar             NCHAR VARYING(32767);
nvarchar2var                NVARCHAR2(32767);
stringvar                   STRING(32767);
varcharvar                  VARCHAR(32767);
varchar2var                 VARCHAR2(32767);
BEGIN
NULL;
END test;
/

Numeric Data Types

precision 1 to 38
scale -84 to 127

Data Type Definition	Variable Size	Column Size
Integer
PLS_INTEGER or BINARY_INTEGER (Signed integer 32 bits)	-2,147,483,648 through 2,147,483,647	N/A
Floating Point
NUMBER	38 chars	38 chars
Fixed Point
NUMBER(<precision>,<scale>)	38 chars	38 chars

Binary Integer SubTypes

Data Type Definition	Variable Size	Column Size
Non-negative Integers
NATURAL	32 bit	N/A
POSITIVE	32 bit	N/A
Not Nullable Non-negative Integers
NATURALN	32 bit	N/A
POSITIVEN	32 bit	N/A
Not Nullable Integers
SIMPLE_INTEGER	32 bit	N/A
Restricted
SIGNTYPE	-1, 0, +1	N/A

NUMBER SubTypes

Data Type Definition	Variable Size	Column Size
Integers with up to 38 decimal digits
INT	38 integer digits	38
INTEGER	38 integer digits	38
SMALLINT	38 integer digits	38
Fixed point numbers up to 38 decimal digits
DEC(<prec>,<scale>)	38 decimal digits	38
DECIMAL(<prec>,<scale>)	38 decimal digits	38
NUMERIC(<prec>,<scale>)	38 decimal digits	38
Floating point numbers up to 126 binary digits
DOUBLE PRECISION(<bin digits)	126 binary digits	126
FLOAT(<bin digits>)	126 binary digits	126
REAL	63 binary digits	63 bin. digits
Floating point numbers using native machine arithmetic
BINARY_DOUBLE (single prec)	32 bit	32 bit
BINARY_FLOAT (double prec)	64 bit	64 bit

Create Table

CREATE TABLE test (
deccol              DEC(38),
decimalcol          DECIMAL(38),
doubleprecisioncol DOUBLE PRECISION,
floatcol            FLOAT(126),
intcol              INT,
integercol          INTEGER,
numbercol           NUMBER(38),
numberfcol          NUMBER,
numericcol          NUMERIC(38),
numericfcol         NUMERIC,
realcol             REAL,
smallintcol         SMALLINT);

SQL> desc test
Name                Type
------------------- --------------
DECCOL              NUMBER(38)
DECIMALCOL          NUMBER(38)
DOUBLEPRECISIONCOL FLOAT(126)
FLOATCOL            FLOAT(126)
INTCOL              NUMBER(38)
INTEGERCOL          NUMBER(38)
NUMBERCOL           NUMBER(38)
NUMBERFCOL          NUMBER
NUMERICCOL          NUMBER(38)
NUMERICFCOL         NUMBER(38)
REALCOL             FLOAT(63)
SMALLINTCOL         NUMBER(38)

Define Variables

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE test IS
decvar              DEC(38);
decimalvar          DECIMAL(38);
doubleprecisionvar DOUBLE PRECISION;
floatvar            FLOAT(126);
intvar              INT;
integervar          INTEGER;
naturalvar          NATURAL;
naturalnvar         NATURALN := 0;
numbervar           NUMBER(38);
numberfvar          NUMBER;
numericvar          NUMERIC(38);
numericfvar         NUMERIC;
plsvar              PLS_INTEGER;
positivevar         POSITIVE;
positivenvar        POSITIVEN := 1;
realvar             REAL;
signtypevar         SIGNTYPE;
smallintvar         SMALLINT;
simplevar           SIMPLE_INTEGER;
BEGIN
NULL;
END test;
/

Date Data Types

Oracle�s stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:

Byte	Description
1	Century value but before storing it add 100 to it
2	Year and 100 is added to it before storing
3	Month
4	Day of the month
5	Hours but add 1 before storing it
6	Minutes but add 1 before storing it
7	Seconds but add 1 before storing it

Data Type Definition	Variable Size	Column Size
Date
DATE (1/1/4712 BC-12/31/9999)	DD-MON-YYYY HH{A\|P}M :MI:SS	DD-MON-YYYY HH{A\|P}M :MI:SS
Interval
INTERVAL DAY TO SECOND	DD MI SS	DD MI SS
INTERVAL YEAR TO MONTH	YYYY MM	YYYY MM
Timestamp
TIMESTAMP(<precision>)	DD-MON-YYYY HH.MI.SS. SSSSSSSSS {A\|P}M	DD-MON-YYYY HH.MI.SS. SSSSSSSSS {A\|P}M
TIMESTAMP WITH TIME ZONE	as above with timezone	as above with timezone
TIMESTAMP WITH LOCAL TIME ZONE	as above with local timezone	as above with local timezone

Create Table

CREATE TABLE test (
date_col   DATE,
int_d2s_col INTERVAL DAY TO SECOND,
int_y2m_col INTERVAL YEAR TO MONTH,
ts_col      TIMESTAMP,
tswtz_col   TIMESTAMP WITH TIME ZONE,
tswltz_col  TIMESTAMP WITH LOCAL TIME ZONE);

SQL> desc test
Name                Type
------------------- --------------
DATE_COL            DATE
INT_D2S_COL         INTERVAL DAY(2) TO SECOND(6)
INT_Y2M_COL         INTERVAL YEAR(2) TO MONTH
TS_COL              TIMESTAMP(6)
TSWTZ_COL           TIMESTAMP(6) WITH TIME ZONE
TSWLTZ_COL          TIMESTAMP(6) WITH LOCAL TIME ZONE

Define Variables

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE test IS
datevar     DATE;
int_d2s_var INTERVAL DAY TO SECOND;
int_y2m_var INTERVAL YEAR TO MONTH;
ts_var      TIMESTAMP;
tswtz_var   TIMESTAMP WITH TIME ZONE;
tswLtz_var TIMESTAMP WITH local TIME ZONE;
BEGIN
NULL;
END test;
/

Partial text from
Metalink Note:69028.1

Since Oracle 7 the DATE datatype is stored in a proprietary format. DATE values are always stored in 7 bytes, excluding the length byte, within a datafile. These bytes store the century, year, month, day, hour, minute, and second details respectively. The following is the definition of Oracle's internal DATE storage structure:

BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year    --       "                "
3 Month   -- stored in 0 base notation
4 Day     --    "                   "
5 Hour    -- stored in excess-1 notation
6 Minute --    "                   "
7 Second --    "                   "

Note that the century and year components are stored in 'excess 100 format', which means that 100 must be deducted from the byte's value. If a negative number results, then we've got a BC date at which point we take the absolute number. Also, to avoid ever having a zero byte, 1 is added to the hour, minute and second bytes. Therefore, 1 must be detected to get the correct value.

For example, take the following date again:
17-DEC-1980 00:00:00

we would expect this date to be stored internally as follows:
119, 180, 12, 17, 01, 01, 01

Let's confirm that hypothesis by dumping the data block in question from an Oracle 8 database and examining its contents. Let's use the "SCOTT.EMP" table for our example:

1) First let's select a row with DATE information to examine:

SQL> desc emp

Result: We see that the HIREDATE column is the fifth column.

SQL> SELECT to_char(hiredate, 'DD-MON-YYYY HH24:MI:SS')
FROM emp
WHERE rownum = 1;

Result: 17-DEC-1980 00:00:00

2) Next we need to dump the datablock in question:

SQL> SELECT rowid FROM emp WHERE rownum = 1;

Result: AAAAtaAABAAAEG1AAA

SQL> SELECT dbms_rowid.ROWID_TO_ABSOLUTE_FNO('AAAAtaAABAAAEG1AAA', 'SCOTT','EMP')
FROM DUAL;

Result: 1

SQL> SELECT dbms_rowid.ROWID_BLOCK_NUMBER('AAAAtaAABAAAEG1AAA')
FROM DUAL;

Result: 16821

SQL> alter system dump datafile 1 block 16821

Finally, we need to open the dump file which is located in our user trace directory and locate the first row dump in the file:

Locate the 5th column in the first row using a 0 based indexing scheme

Result:
...
col 4: [ 7] 77 b4 0c 11 01 01 01 <= Hexadecimal representation
...

Convert the hexidecimal dump to a decimal representation

Result: 119 180 12 17 01 01 01 <= Decimal representation

As a result, we have proven our assumption for the date "17-DEC-1980 00:00:00".

The above method is quite tedious. Is there no easier means of viewing internal date information? Let's try using the DUMP() function to do the same thing. Issue the following statement:

SQL> SELECT dump(to_date('17-DEC-1980 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))
FROM DUAL;

Result: Typ=13 Len=8: 188,7,12,17,0,0,0,0

What happened? Is the information above incorrect or does the DUMP() function not handle DATE values? No, you have to look at the "Typ=" values to understand why we are seeing these results. The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations. Note that the same result can be seen when DUMPing the value SYSDATE.

Using deductive logic, we can derive the following storage format for type 13 data:

Byte 1 - Base 256 year modifier
2      - Base 256 year
3      - Month
4      - Day
5      - Hours
6      - Minutes
7      - Seconds
8      - Unused

For AD dates, the year and base 256 modifier are stored in base 0 notation and we must add the modifier to the year to obtain the true year. For BC dates, the year and base 256 modifier are stored in excess-255 notation. We must subtract the modifier from the year to obtain the true year.

For our year 1980, we could read this to be, Byte 1 + Byte 2 * 256. In other words, 188 + 7 * 256 = 1980.

Let's try another DUMP but using a date extracted from the original table:

SQL> SELECT dump(hiredate) FROM emp WHERE rownum = 1;

Result: Typ=12 Len=7: 119,180,12,17,1,1,1

Now we have a datatype 12 with a length of 7 and the results are as expected.

In terms of limits, Oracle is capable of handling dates from:

01-JAN-4712 BC 00:00:00
Julian Day: 1

through

31-DEC-9999 AD 23:59:59 AD
Julian Day: 5373484

The Julian Day number is a count of days elapsed since Greenwich mean noon on 1 January 4712 B.C. in the Julian proleptic calendar. The Julian Date is the Julian Day number followed by the fraction of the day elapsed since the preceding noon.

All calculations made on DATE values are based on fractional days. In other words, the values of SYSDATE+1 is tomorrow's DATE at this time. This is reminicent of the Julian Day number behavior described above. By using the widely accepted convention of the Julian calendar that 1 day is the basic unit of time measurement, DATE calculation logic is greatly simplified.

Logical Data Type

BOOLEAN

set serveroutput on

DECLARE
x BOOLEAN;
BEGIN
   x := TRUE;
   IF x THEN
      dbms_output.put_line('TRUE');
   ELSIF NOT x THEN
      dbms_output.put_line('FALSE');
   END IF;
END;
/

-- see diutil.bool_to_int and diutil.int_to_bool

Binary Data Types

Data Type Definition	Variable Size	Column Size
LONG RAW	32,760 bytes	~2GB
MLSLABEL	2-5 bytes	2-5 bytes
RAW(<maximum_size_in_bytes>)	32,767 bytes	2,000 bytes

Rowid Data Types

Data Type Definition	Variable Size	Column Size
ROWID - physical row identifier	block.row.file	block.row.file
UROWID - universal (IOT)	N/A	4,000 bytes

Note: Rowid consists of four parts ... characters 1-6 = data object id, characters 7-9 = file number, characters 10-15 = block, characters 16-18 - row number

Oracle Supplied Data Types

Polymorphic Types

Data Type Definition	Description
SYS.ANYTYPE	Can contain a type description of any persistent SQL type, named or unnamed, including object types and collection types. Only new transient types can be constructed using the ANYTYPE interfaces.
SYS.ANYDATA	See link at page bottom
SYS.ANYDATASET	Contains a description of a given type plus a set of data instances of that type. An ANYDATASET can be persistently stored in the database or can be used as an interface parameter to communicate self-descriptive sets of data, all of which belong to a certain type.

Media Types

Data Type Definition	Description
ORDAudio	Supports the storage of audio data
ORDDoc	Supports the storage and management of any tpe of media data, including audio, image, and video
ORDImage	Supports the storage of image data
ORDImageSignature	Supports the a compact representation of color, texture, and shape information of image data data
SI_AverageColor	Characterizes an image by its average color
SI_Color	Encapsulates color values
SI_ColorHistogram	Characterizes an image by the relative frequencies of the color exhibited by samples of the raw image
SI_FeatureList	For up to 4 image features represented by SI_AVERAGE_COLOR, SI_COLORHISTOGRAM, SI_POSITIONAL_COLOR and SI_TEXTURE where the feature is associated with a feature weight
SI_PositionalColor	Given an image divided into rectangles, represents the feature that characterizes an image by the n by m most significant colors of the rectangles.
SI_StilImage	Represents digital images with inherent image characteristics such as height, width, and format
SI_Texture	Characterizes an image by the size of repeating items (coarseness), brightness variations (contrast), and predominant direction (directionality)
ORDVideo	Supports the storage of video data

Spatial Types

Data Type Definition	Description
SDO_GEOMETRY	A geometric description of a spatial object stored in a single row
SDO_RASTER	A raster grid or image stored in a single row

XML Types

Data Type Definition	Description
URIType	An object type for storing XML
DBURIType	A subtype of URIType used to store DBURIRefs that allow for consistent access to data stored inside and outside the database
HTTPURIType	A subtype of URIType used to store URLs to external web pages or to files. Oracle access these files using HTTP
XDBURIType	A subtype of URIType to expose documents in the XML heirarchy as URIs that can be embedded in any URIType column in a table

CREATE TABLE xml_tab (
rid INTEGER NOT NULL,
xml_data XMLTYPE);

INSERT INTO xml_tab
(rid, xml_data)
VALUES
(1, NULL);

INSERT INTO xml_tab
(rid, xml_data)
VALUES
(2, XMLTYPE('<DATA><ID>4</ID><DESC>MORGAN</DESC></DATA>'));

SELECT *
FROM xml_tab;

Predefined Composite Data Types - Data types that contain internal components

Single And Two Dimensional Arrays

RECORD: A composite variable that can store data values of different types. In PL/SQL records are useful for holding data from table rows. For ease of maintenance they can be declared with %ROWTYPE.

set serveroutput on

DECLARE
TYPE TimeType IS RECORD (seconds SMALLINT := 0,
minutes SMALLINT := 0, hours SMALLINT := 0);

MyTime TimeType;
BEGIN
MyTime.seconds := 24;
MyTime.minutes := 37;
MyTime.hours := 5;

dbms_output.put_line(MyTime.hours || ':' || MyTime.minutes);
END;
/

TABLE: A one-dimensional array with no upper bound.

See link at page bottom for Associative Arrays

VARRAY: A two-dimensional array with a fixed number of elements.

See link at page bottom

Reference Data Types - Data types that are pointers that identify data

REF

REF CURSOR: A pointer to a result set

See link at page bottom

REF object_type: A pointer to an object

CREATE TYPE home_t AS OBJECT (
address    VARCHAR2(35),
owner      VARCHAR2(25),
age        INTEGER,
style      VARCHAR2(15),
floor_plan BLOB,
price      REAL);
/

CREATE TABLE homes OF home_t;

CREATE TYPE person_t AS OBJECT (
first_name VARCHAR2(10),
last_name   VARCHAR2(15),
dob         DATE,
home_addr   REF home_t,
home_phone VARCHAR2(15),
ssn         VARCHAR2(11),
mother      REF person_t,
father      REF person_t);
/

CREATE TABLE person OF person_t;

desc person

set describe depth all linenum on indent on

desc person

LOB Data Type - Data types that holds lob locators specifying the location of large objects stored out-of-line

Large Object Data Types

Data Type Definition	Variable Size	Column Size
BFILE	(4GB - 1 byte)	(4GB - 1 byte)
BLOB	8 to 128 TB	(4GB-1 byte)*(block size)
CLOB	8 to 128 TB	(4GB-1 byte)*(block size)
NCLOB	8 to 128 TB	(4GB-1 byte)*(block size)

Data Type Demos

Floating Point Numbers

CREATE TABLE fpn (
numbercol    NUMBER,
floatcol     FLOAT(126),
bindoubcol   BINARY_DOUBLE,
binfloatcol BINARY_FLOAT,
realcol      REAL);

desc fpn

INSERT INTO fpn
VALUES (1234.56, 1234.56, 1234.56, 1234.56, 1234.56);
COMMIT;

SELECT * FROM fpn;

SELECT VSIZE(numbercol), VSIZE(floatcol), VSIZE(bindoubcol), VSIZE(binfloatcol), VSIZE(realcol)
FROM fpn;

set linesize 120
col numbc format a20
col flotc format a20
col bindc format a20
col binfc format a20
col realc format a20

SELECT DUMP(numbercol) NUMBC, DUMP(floatcol) FLOTC,
DUMP(bindoubcol) BINDC, DUMP(binfloatcol) BINFC, DUMP(realcol) REALC
FROM fpn;

Define table demo

CREATE TABLE tnorm (
somecol VARCHAR2(20));

CREATE TABLE tbyte (
somecol VARCHAR2(20 BYTE));

CREATE TABLE tchar (
somecol VARCHAR2(20 CHAR));

desc tnorm
desc tbyte
desc tchar

BINARY_FLOAT_INFINITY

conn hr/hr

SELECT COUNT(*)
FROM employees
WHERE salary < BINARY_FLOAT_INFINITY;

Related Topics

-----