add

About Me

My photo
Oracle Apps - Techno Functional consultant

Sunday, February 3

Data types for Oracle 8 to Oracle 11g

Datatype
Description
Max Size:
Oracle 8
Max Size:
Oracle 9i/10g
Max Size:
Oracle 11g
Max Size:
PL/SQL
PL/SQL
Subtypes/
Synonyms
VARCHAR2(size)
Variable length character string having maximum length size bytes.
You must specify size
4000 bytes
minimum is 1
4000 bytes
minimum is 1
4000 bytes
minimum is 1
32767 bytes
minimum is 1
STRING
VARCHAR
NVARCHAR2(size)
Variable length national character set string having maximum length size bytes.
You must specify size
4000 bytes
minimum is 1
4000 bytes
minimum is 1
4000 bytes
minimum is 1
32767 bytes
minimum is 1
STRING
VARCHAR
VARCHAR
Now deprecated (provided for backward compatibility only)
VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.
-
-
-


CHAR(size)
Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102…
2000 bytes
Default and minimum size is 1 byte.
2000 bytes
Default and minimum size is 1 byte.
2000 bytes
Default and minimum size is 1 byte.
32767 bytes
Default and minimum size is 1 byte.
CHARACTER
NCHAR(size)
Fixed length national character set data of length size bytes. This should be used for fixed length data. Such as codes A100, B102…
2000 bytes
Default and minimum size is 1 byte.
2000 bytes
Default and minimum size is 1 byte.
2000 bytes
Default and minimum size is 1 byte.
32767 bytes
Default and minimum size is 1 byte.

NUMBER(p,s)
Number having precision p and scale s.
The precision p can range from 1 to 38.

The scale s can range from -84 to 127.
The precision p can range from 1 to 38.

The scale s can range from -84 to 127.
The precision p can range from 1 to 38.

The scale s can range from -84 to 127.
Magnitude
1E-130 .. 10E125

maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits

The scale s can range from -84 to 127.

For floating point don't specify p,s

REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits
fixed-point numbers:
DEC
DECIMAL
NUMERIC

floating-point:
DOUBLE PRECISION FLOAT
binary_float (32 bit)
binary_double (64 bit)

integers:
INTEGER
INT
SMALLINT
simple_integer(10g)
BOOLEAN
REAL
PLS_INTEGER
signed integers
PLS_INTEGER values require less storage and provide better performance than NUMBER values.
So use PLS_INTEGER where you can!
PL/SQL only
PL/SQL only
PL/SQL only
magnitude range is -2147483647 .. 2147483647

BINARY_INTEGER
signed integers (older slower version of PLS_INTEGER)



magnitude range is -2147483647 .. 2147483647
NATURAL
NATURALN
POSITIVE
POSITIVEN
SIGNTYPE
LONG
Character data of variable length (A bigger version the VARCHAR2 datatype)
2 Gigabytes
2 Gigabytes - but now deprecated (provided for backward compatibility only).
2 Gigabytes - but now deprecated (provided for backward compatibility only).
32760 bytes
Note this is smalller than the maximum width of a LONG column

DATE
Valid date range
from January 1, 4712 BC to December 31, 9999 AD.
from January 1, 4712 BC to December 31, 9999 AD.
from January 1, 4712 BC to December 31, 9999 AD.
from January 1, 4712 BC to December 31, 9999 AD.
(in Oracle7 = 4712 AD)

TIMESTAMP (fractional_seconds_precision)
the number of digits in the fractional part of the SECOND datetime field.
-
Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)


TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE
As above with time zone displacement value
-
Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)


INTERVAL YEAR (year_precision) TO MONTH
Time in years and months, where year_precision is the number of digits in the YEAR datetime field.
-
Accepted values are 0 to 9. (default = 2)
Accepted values are 0 to 9. (default = 2)


INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)
Time in days, hours, minutes, and seconds.

day_precision is the maximum number of digits in 'DAY'

fractional_seconds_precision is the max number of fractional digits in the SECOND field.
-
day_precision may be 0 to 9. (default = 2)
fractional_seconds_precision may be 0 to 9. (default = 6)
day_precision may be 0 to 9. (default = 2)
fractional_seconds_precision may be 0 to 9. (default = 6)


RAW(size)
Raw binary data of length size bytes.
You must specify size for a RAW value.
Maximum size is 2000 bytes
Maximum size is 2000 bytes
Maximum size is 2000 bytes
32767 bytes

LONG RAW
Raw binary data of variable length. (not intrepreted by PL/SQL)
2 Gigabytes.
2 Gigabytes - but now deprecated (provided for backward compatibility only)
2 Gigabytes - but now deprecated (provided for backward compatibility only)
32760 bytes
Note this is smalller than the maximum width of a LONG RAW column

Hexadecimal string representing the unique address of a row in its table.
(primarily for values returned by the ROWID pseudocolumn.)
10 bytes
10 bytes
10 bytes
Hexadecimal string representing the unique address of a row in its table.
(primarily for values returned by the ROWID pseudocolumn.)

UROWID
Hex string representing the logical address of a row of an index-organized table
The maximum size and default is 4000 bytes
The maximum size and default is 4000 bytes
The maximum size and default is 4000 bytes
universal rowid - Hex string representing the logical address of a row of an index-organized table, either physical, logical, or foreign (non-Oracle)
See CHARTOROWID and the package: DBMS_ROWID
MLSLABEL
Binary format of an operating system label.This datatype is used with Trusted Oracle7.





CLOB
Character Large Object
4Gigabytes
8 TB
8 TB to 128 TB
(4 Gigabytes - 1) * (database block size)


NCLOB
National Character Large Object
4Gigabytes
8 TB
8 TB to 128 TB
(4 Gigabytes - 1) * (database block size)


BLOB
Binary Large Object
4Gigabytes
8 TB
8 TB to 128 TB
(4 Gigabytes - 1) * (database block size)


BFILE
pointer to binary file on disk
4Gigabytes
8 TB
8 TB to 128 TB
(4 Gigabytes - 1) * (database block size)


XMLType
XML data
-
4 Gigabytes
4 Gigabytes
Populate with XML from a CLOB or VARCHAR2.

or query from another XMLType column.

No comments: