CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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
AnyData
Associative Arrays
DBMS_TYPES
SIMPLE_INTEGER
Ref Cursor
Timestamp
Type
VArray
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [67 users online]    © 2010 psoug.org