René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Datatypes in Oracle

Internal datatypes

Datatype Max Size Code #define (ocidfn.h)
varchar2/nvarchar2 4000 1 SQLT_CHR
number 21 2 SQLT_NUM
long 2 GB 8 SQLT_LNG
rowid 10 11 SQLT_RID
date 10 12 SQLT_DAT
raw 2000 23 SQLT_BIN
long raw 2 GB 24 SQLT_LBI
char/nchar 2000 96 SQLT_AFC
ref - 110? / 111? SQLT_REF ?
clob/nclob 4 GB 112 SQLT_CLOB
blob 4 GB 113 SQLT_BLOB
bfile 4 GB 114 SQLT_BFILEE
timestamp 11 118? 180? SQLT_TIMESTAMP
timestamp with time zone 13 181? 186? SQLT_TIME_TZ
interval year to month 5 182? 189? SQLT_INTERVAL_YM
interval day to second 11 183? 190? SQLT_INTERVAL_DS?
timestamp with local time zone 11 231? 232? SQLT_TIMESTAMP_LTZ
urowid 3950 208? 104? SQLT_RDD?

User defined datatypes

A user defined datatype can be created with create type.
The code is 108, the #define is SQLT_NTY.

PL/SQL datatypes

In addition to the internal datatypes, Oracle also knows the following datatypes within PL/SQL:
  • binary_double
  • binary_float
  • dec
  • decimal
  • double precision
  • float
  • int
  • integer
  • natural
  • naturaln
  • numeric
  • pls_integer
    binary_integer is equivalent to pls_integer
  • positive
  • positiven
  • real
  • signtype
  • smallint
  • string
  • boolean
  • With Oracle 11g: simple_integer

anydata, anytype and anydataset

See here.

ANSI datatypes

The following table list ANSI datatypes and its corresponding Oracle datatype:
ANSI typeOracle type
characterchar
character varyingvarchar2
national characternchar
national character varyingnvarchar2
decimalnumber
numericnumber
integernumber
intnumber
smallintnumber(38)
floatnumber
double precisionnumber
realnumber
The ANSI datatypes can be used .... 
create table ansi_data_types (
  col_01 character(10),
  col_02 character varying(20),
  col_03 national character(30),
  col_04 national character varying(40),
  col_05 decimal(5,2),
  col_06 numeric(9,3),
  col_07 integer,
  col_08 int,
  col_09 smallint,
  col_10 float,
  col_11 double precision,
  col_12 real
);
... but are implicitely converted to Oracle datatypes: 
desc ansi_data_types

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL_01                                             CHAR(10)
COL_02                                             VARCHAR2(20)
COL_03                                             NCHAR(30)
COL_04                                             NVARCHAR2(40)
COL_05                                             NUMBER(5,2)
COL_06                                             NUMBER(9,3)
COL_07                                             NUMBER(38)
COL_08                                             NUMBER(38)
COL_09                                             NUMBER(38)
COL_10                                             FLOAT(126)
COL_11                                             FLOAT(126)
COL_12                                             FLOAT(63)

Unsupported DB2 datatypes

The following datatypes, valid in DB2, are not supported in Oracle:
  • graphic
  • long vargraphic
  • vargraphic
  • time

Links

See also dbms_types.
lobs (CLOB, BLOB, NCLOB and BFILE).