| Predefined Scalar Data Types |
| Data types that do not contain internal components |
| Data Dictionary Source |
DBA_TYPES |
| |
| 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 and VARCHAR2 columns.
The national character set controls the character set of NCHAR and 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,647 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
(BINARY_INTEGER range 0..2147483647) |
32 bit |
N/A |
POSITIVE
(BINARY INTEGER range 1..2147483647) |
32 bit |
N/A |
| Not Nullable Non-negative Integers |
| NATURALN (NATURAL NOT NULL) |
32 bit |
N/A |
| POSITIVEN (POSITIVE NOT NULL) |
32 bit |
N/A |
| Not Nullable Integers |
| SIMPLE_INTEGER (NOT NULL) |
32 bit |
N/A |
| SIMPLE_DOUBLE (NOT NULL) |
32 bit |
N/A |
| SIMPLE_FLOAT (NOT NULL) |
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 |
| INTEGER (NUMBER(38,0) |
38 integer digits |
38 |
| INT (INTEGER) |
38 integer digits |
38 |
| SMALLINT (NUMBER(38,0) |
38 integer digits |
38 |
| Fixed point numbers up to 38 decimal digits |
| DECIMAL (NUMBER(38,0) |
38 decimal digits |
38 |
| DEC(DECIMAL) |
38 decimal digits |
38 |
| NUMERIC(DECIMAL) |
38 decimal digits |
38 |
| Floating point numbers up to 126 binary digits |
| DOUBLE PRECISION (FLOAT) |
126 binary digits |
126 |
| FLOAT (NUMBER(126)) |
126 binary digits |
126 |
| REAL (FLOAT(63)) |
63 binary digits |
63 bin. digits |
| Floating point numbers using native machine arithmetic |
| BINARY_DOUBLE (single prec) |
64 bit |
64 bit |
| BINARY_FLOAT (double prec) |
32 bit |
32 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 := 1;
simpledouble SIMPLE_DOUBLE := 1;
simplefloat SIMPLE_FLOAT := 1;
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
(see link at page bottom) |
| INTERVAL DAY TO SECOND |
DD MI SS |
DD MI SS |
| INTERVAL YEAR TO MONTH |
YYYY MM |
YYYY MM |
| Timestamp
(see link at page bottom) |
| 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
For more information on XMLType follow the link at page bottom. |
| 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 121
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; |
