Date Type: Each value manipulated by Oracle Database has a data type. The data type of a value associates a fixed set of properties with the value. Data type is associated with a specific storage format, constraints, and a valid range of values
There are Two Types: 1. Scalar Datatypes
2. Composite Datatypes
Scalar Datatypes:
PL/SQL Scalar Data Types and Subtypes
Composite Datatypes:
Scalar Datatypes:
PL/SQL Scalar Data Types and Subtypes
Date Type
|
Description
|
Numeric
|
Numeric values on which arithmetic operations are performed.
|
Character
|
Alphanumeric values that represent single characters or strings of characters.
|
Boolean
|
Logical values on which logical operations are performed.
|
Datetime
|
Dates and times.
|
PL/SQL Numeric Data Types and Subtypes
Data Type
|
Description
|
PLS_INTEGER
|
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
|
BINARY_INTEGER
|
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
|
BINARY_FLOAT
|
Single-precision IEEE 754-format floating-point number
|
BINARY_DOUBLE
|
Double-precision IEEE 754-format floating-point number
|
NUMBER(prec, scale)
|
Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.
|
DEC(prec, scale)
|
ANSI specific fixed-point type with maximum precision of 38 decimal digits.
|
DECIMAL(prec, scale)
|
IBM specific fixed-point type with maximum precision of 38 decimal digits.
|
NUMERIC(pre, secale)
|
Floating type with maximum precision of 38 decimal digits.
|
DOUBLE PRECISION
|
ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
|
FLOAT
|
ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
|
INT
|
ANSI specific integer type with maximum precision of 38 decimal digits
|
INTEGER
|
ANSI and IBM specific integer type with maximum precision of 38 decimal digits
|
SMALLINT
|
ANSI and IBM specific integer type with maximum precision of 38 decimal digits
|
REAL
|
Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)
|
PL/SQL Character Data Types and Subtypes
Data Type
|
Description
|
CHAR
|
Fixed-length character string with maximum size of 32,767 bytes
|
VARCHAR2
|
Variable-length character string with maximum size of 32,767 bytes
|
RAW
|
Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL
|
NCHAR
|
Fixed-length national character string with maximum size of 32,767 bytes
|
NVARCHAR2
|
Variable-length national character string with maximum size of 32,767 bytes
|
LONG
|
Variable-length character string with maximum size of 32,760 bytes
|
LONG RAW
|
Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL
|
ROWID
|
Physical row identifier, the address of a row in an ordinary table
|
UROWID
|
Universal row identifier (physical, logical, or foreign row identifier)
|
PL/SQL Datetime and Interval Types
Field Name
|
Valid Datetime Values
|
Valid Interval Values
|
YEAR
|
-4712 to 9999 (excluding year 0)
|
Any nonzero integer
|
MONTH
|
01 to 12
|
0 to 11
|
DAY
|
01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)
|
Any nonzero integer
|
HOUR
|
00 to 23
|
0 to 23
|
MINUTE
|
00 to 59
|
0 to 59
|
SECOND
|
00 to 59.9(n), where 9(n) is the precision of time fractional seconds
|
0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
|
TIMEZONE_HOUR
|
-12 to 14 (range accommodates daylight savings time changes)
|
Not applicable
|
TIMEZONE_MINUTE
|
00 to 59
|
Not applicable
|
TIMEZONE_REGION
|
Found in the dynamic performance view V$TIMEZONE_NAMES
|
Not applicable
|
TIMEZONE_ABBR
|
Found in the dynamic performance view V$TIMEZONE_NAMES
|
Not
|
PL/SQL Large Object (LOB) Data Types
Data Type
|
Description
|
Size
|
BFILE
|
Used to store large binary objects in operating system files outside the database.
|
System-dependent. Cannot exceed 4 gigabytes (GB).
|
BLOB
|
Used to store large binary objects in the database.
|
8 to 128 terabytes (TB)
|
CLOB
|
Used to store large blocks of character data in the database.
|
8 to 128 TB
|
NCLOB
|
Used to store large blocks of NCHAR data in the database.
|
8 to 128 TB
|
There are two types 1. Records
2. Collections
i. Index-by tables or Associative array
ii. Nested table
iii. Variable-size array or Varray