Datatypes in Oracle

Datatypes in Oracle has been the most common topic you would find easily across the web. Lots of articles, blogs and posts are written about it. Then what is it I am doing different here? Nothing. I am penning down this post on datatypes because I don’t want my visitors to hunt web for such basic information. My wish is to make Oracle related stuff as handy as possible. Coming to the datatypes, in Oracle SQL there are many datatypes but I have tried to give information about those that are used commonly by developers.

datatypes_in_oracle

Click here to read brief history of Oracle SQL

Here, I have classified dataypes in Oracle into five categories

Character Datatypes

Syntax11g10g9iDescription
Varchar2 (N)Maximum size 4000 bytes or 32 KB in PLSQLMaximum size 4000 bytes or 32 KB in PLSQLMaximum size 4000 bytes or 32 KB in PLSQLN: Number of characters to store. Variable-length string
Char(N)Maximum size 2000 bytesMaximum size 2000 bytesMaximum size 2000 bytesN: Number of characters to store. Fixed-length string
Nvarchar2(N)Maximum size 4000 bytes Maximum size 4000 bytes Maximum size 4000 bytes N: Number of characters to store. Variable-length NLS string
Nchar(N)Maximum size 2000 bytes Maximum size 2000 bytes Maximum size 2000 bytes N: Number of characters to store. Fixed-length NLS string
LongMaximum size 2gbMaximum size 2gbMaximum size 2gbVariable-length strings
RawMaximum size 2000 bytes Maximum size 2000 bytes Maximum size 2000 bytes Variable-length binary strings
Long rawMaximum size 2gbMaximum size 2gbMaximum size 2gbVariable-length binary strings

Date / Time Datatypes

Syntax11g10g9iDescription
DateDate between January 1, 4712 BC and December 31, 9999 ADDate between January 1, 4712 BC and December 31, 9999 ADDate between January 1, 4712 BC and December 31, 9999 ADNormal date in standard format (DD-MON-YYYY)
Timestamp (fractional_seconds_precision)Stores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores year, month, day, hour, minute and second. Stores seconds in fractional
Timestamp (fractional_seconds_precision) With Time ZoneStores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores year, month, day, hour, minute, second and time zone offset. The time zone offset is the difference (in hours and minutes) between local time and UTC(Coordinated Universal Time, formerly Greenwich Mean Time). Stores seconds in fractional
Timestamp [(fractional_seconds_precision)] With Local Time ZoneStores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores date and fractional seconds can be a number between 0 and 9 (default is 6)Stores year, month, day, hour, minute, second and time zone offset. When users retrieve the data, Oracle returns it in the users' local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time). Stores seconds in fractional
Interval Year [(year_precision)] to Monthyear_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default value of year_precision is 2 year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default value of year_precision is 2 year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default value of year_precision is 2 Stores a period of time using the YEAR and MONTH datetime fields

For example, INTERVAL '100-5' YEAR(4) TO MONTH.

Specify an interval of 100 years and 5 months.The year precision is 4.
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2 day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2 day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2 Stores a period of time in terms of days, hours, minutes, and seconds

For example, INTERVAL '10 4:12:4.12' DAY TO SECOND(3).

Specify 10 days, 4 hours, 12 minutes, 4 seconds and 12 thousands of a second

Numeric Datatypes

Syntax11g10g9iDescription
Number (P, S)Stores zero, negative and positive fixed numbers with absolute values from 1E-130 (but not including) to 10E125. If value is out of this range oracle will return errorStores zero, negative and positive fixed numbers with absolute values from 1E-130 (but not including) to 10E125. If value is out of this range oracle will return errorStores zero, negative and positive fixed numbers with absolute values from 1E-130 (but not including) to 10E125. If value is out of this range oracle will return errorP = Precision

S = Scale

The precision can range from 1 to 38.

The scale can range from -84 to 127
BINARY_FLOATMagnitude:
1.17549E-38F

3.40282E+38F
Magnitude:
1.17549E-38F

3.40282E+38F
-A 32-bit, single-precision floating-point number data type. Each BINARY_FLOAT value requires 5 bytes.
BINARY_DOUBLEMagnitude:
2.2250748585072E-308

1.79769313486231E+308
Magnitude:
2.2250748585072E-308

1.79769313486231E+308
-A 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes.
PLS_INTEGER---Signed Integers.

PLS_INTEGER values require less storage and provide better performance than NUMBER values

Magnitude range is -2,147,483,647 .. 2,147,483,647

USED ONLY IN PL/SQL
BINARY_INTEGAR---Signed Integers.

Slower version of PLS_INTEGER

Magnitude range is -2,147,483,647 .. 2,147,483,647

USED ONLY IN PL/SQL

Large Objects / LOB Datatypes

Syntax11g10g9iDescription
BFILEMaximum Size: 4GB

Maximum size of a file name: 255 characters

Maximum size of a directory name: 30 characters

Maximum number of open BFILES (See Note in Description column)
Maximum Size: 4GB

Maximum size of a file name: 255 characters

Maximum size of a directory name: 30 characters

Maximum number of open BFILES (See Note in Description column)
Maximum Size: 4GB

Maximum size of a file name: 255 characters

Maximum size of a directory name: 30 characters

Maximum number of open BFILES (See Note in Description column)
Stores large chunk of unstructured binary data in operating system files outside of database.

Stores a file locator that points to an external file containing data.

Note: Maximum number of BFILES if limited by value of SESSION_MAX_OPEN_FILES parameter which itself is limited by number of open files OS will allow
BLOBMaximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Maximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Maximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Used to store binary large objects
CLOBMaximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Maximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Maximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Used to store single-byte and multi-byte character data
NCLOBMaximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Maximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Maximum Size: (4 GB – 1) * DB_BLOCK_SIZE parameter (8 TB to 128 TB)Used to store Unicode National character set data

ROWID Datatypes

Syntax11g10g9iDescription
ROWIDFormat:

BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;

RRRR is the row in the block;

FFFFF is the database file
Format:

BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;

RRRR is the row in the block;

FFFFF is the database file
Format:

BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;

RRRR is the row in the block;

FFFFF is the database file
RowId stores fixed-length binary data. Every row in a table has unique rowid or physical address

Physical rowids: store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.

Logical rowids: store the addresses of rows in index-organized tables.
UROWIDFormat:

BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;

RRRR is the row in the block;

FFFFF is the database file
Format:

BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;

RRRR is the row in the block;

FFFFF is the database file
Format:

BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;

RRRR is the row in the block;

FFFFF is the database file
Universal ROWID stores logical and physical rowids of Oracle tables

Read here the official documentation of Oracle Datatypes.

Put your comments below if you liked this post. Your feedback and suggestions are always valuable to us.

Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.