Oracle PL SQL

Oracle Data Types

Viswanath A

ORACLE DATA TYPES – Learn about data types classification and their usages with full explanation.

ORACLE DATA TYPES

  • This  are built-in(Predefined) data Types. They categorize as following.

CHARACTER DATA TYPES

  1. CHAR
  2. NCHAR
  3. VARCHAR2 and VARCHAR
  4. NVARCHAR2

NUMBER DATA TYPES

  1. NUMBER
  2. NUMERIC
  3. FLOAT
  4. DEC
  5. DECIMAL
  6. INTEGER
  7. INT
  8. SMALL INT
  9. REAL
  10. DOUBLE PRECISION

LONG AND RAW DATA TYPES

  1. LONG
  2. RAW
  3. LONG RAW

DATETIME DATA TYPES

  1. DATE
  2. TIMESTAMP
  3. TIME STAMP WITH TIME ZONE
  4. TIME STAMP WITH LOCAL TIME ZONE
  5. INTERVAL YEAR
  6. INTERVAL DAY

ROWID DATA TYPES

  1. ROWID
  2. UROWID

BINARY DATA TYPES

  1. BFILE
  2. BLOB
  3. CLOB
  4. NCLOB
  5. BINARY FLOAT
  6. BINARY DOUBLE
DATATYPEDESCRIPTIONDATA TYPE SIZENOTE POINTS
CHARFixed Length Character data type
  • Default size: 1 Byte per row
  • Maximum 2000 bytes per row.
  • When you insert or update a row in the table, the value for the  CHAR  column has the fixed length.
  • If you give a shorter value, then the value is blank-padded to the fixed length.
  • If a value is too large, Oracle Database returns an error.
NCHARFixed Length Character data type
  • Default size: 1 Byte per row.
  • Maximum 2000 bytes per row.
  • NCHAR is a unicode data type, stores unicode character data.
VARCHAR2 or
VARCHAR		Variable length character data type
  • Maximum 4000 bytes per row.
  • Stores each value in the column as a variable-length field unless a value exceeds the column’s maximum length.
  • Saves storage space used by the table.
NVARCHAR2Variable length character data type
  • Default size: 1 Byte per row.
  • Maximumn 4000 bytes per row.
  • NVARCHAR2 is a unicode data type, stores unicode character data.
NUMBERVariable length Numeric data type
  • Maximum 22 bytes per row.
 
FLOATNumeric data type
  • Maximum 22 bytes per row.
 
BINARY_FLOATSingle precision floating point number data type
  • 4 BYTES
 
BINARY_DOUBLEDouble precision floating point data type.
  • 8 BYTES
 
LONGVariable length character data type
  • Maximum 2 Giga bytes per row.
 
RAWVariable length Raw binary data type
  • Maximum 2000 bytes per row.
 
LONG RAWVariable length Raw binary data type
  • Maximum 2 Giga bytes per row.
 
DATEFixed Length Date and Time data type
  • Fixed at 7 Bytes at each row.
 
ROWIDFixed Length Binary Data
  • Fixed at 10 Bytes Per row for Extended ROWID
  • Fixed at 6 Bytes Per row for restricted ROWID
 
BFILEbinary data type
  • Maximum 4 GB
  • Binary Data stored in external file
BLOBBinary data type< 
  • Stores Unstructured Binary data
CLOBSingle-byte Character data.
  • Maximum 4 GB
 
NCLOBSingle Byte or Fixed Length Multi Byte data
  • Maximum 4 GB
 

NOTES

CHARACTER DATA TYPES

  • CHAR and NCHAR datatypes store fixed-length character strings.
  • VARCHAR2 and NVARCHAR2 datatypes store variable-length character strings. (The VARCHAR datatype is synonymous with the VARCHAR2 datatype.)
  • CLOB and NCLOB datatypes store single-byte and multibyte character strings of up to four gigabytes.
  • The LONG datatype stores variable-length character strings containing up to two gigabytes, but with many restrictions.

NUMBER DATA TYPE

  • NUMBER datatype to store real numbers in a fixed-point or floating-point format.

ROWIDS AND ROWID DATATYPE

  • ROWID  datatype to store the address (rowid) of every row in the database.
  • 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.
  • A single datatype called the  universal rowid, or  UROWID, supports both logical and physical rowids, as well as rowids of foreign tables
  • A column of the  UROWID  datatype can store all kinds of rowids. The value of the  COMPATIBLE  initialization parameter (for file format compatibility) must be set to 8.1 or higher to use  UROWID  columns.

Related Posts

Oracle PL SQL

Parametric Cursor in Oracle PL-SQL

Viswanath A
Oracle PL SQL

Dynamic Query for PIVOT In Clause using PIVOT XML

Viswanath A