Oracle 11g Data types

1. Character types

Data type	Length	Description
CHAR (n Byte/char)	Default 1 bytes, maximum n value is 2000	Padding at the end to reach the specified length, exceeding the maximum length error. The default length is the number of bytes, and the character length can be from 1 bytes to four bytes.
NCHAR (N)	Default 1 characters, maximum storage content 2000 bytes	Fills the space at the end to the specified length, and n is the number of Unicode characters. The default is 1 bytes.
NVARCHAR2 (N)	Maximum length must be specified, maximum storage content 4000 bytes	Variable length type. n is the number of Unicode characters
VARCHAR2 (n Byte/char)	The maximum length must be specified, at least 1 bytes or 1 characters, and the maximum n value is 4000	Variable length type. Error exceeding maximum length. The default is to store a string of length 0.
VARCHAR	With VARCHAR2	Do not recommend using

2. Number types

Data type	Length	Description
Number (P[,s])	1-22 bytes. P Value range 1 to 38 s range of values-84 to 127	Stores the number of fixed-point values with an absolute value range of 1.0 x 10-130 to 1.0 x 10 126. Value greater than or equal to 1.0 x 10 126 times wrong. P is a meaningful 10-digit number, positive s is the number of decimal places, and negative s indicates how many bits are rounded to the left of the decimal point.
Binary_float	5 bytes, which have a length byte.	32-bit single-precision floating-point number type. The sign bit 1 bits, refers to the digit 8 bits, the tail digit 23 bits.
Binary_double	9 bytes, which have a length byte.	64-bit double-precision floating-point number type.

3. Time, time interval type

The Time field has a range of values:

Time field	Time type Valid values	Time interval Type valid values
Year	4712 to 9999, including 0	Any integer
MONTH	01 to 12	0 to 11
Day	01 to 31	Any integer
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	XX to 59.9 (n), 9 (n) Not applicable with date type	0 to 59.9 (n)
Timezone_hour	-1 to 14, not applicable with date and timestamp types	Not available
Timezone_minute	00 to 59, not applicable with date and timestamp types	Not available
Timezone_region		Not available
Timezone_abbr		Not available

Time, time interval type:

Data type	Length	Description
DATE	7 bytes	The default value is Sysdate year, month, and Day 01. Contains a time field, if the insertion value does not have a Time field, the default value is: 00:00:00 or 12:00:00 for 24-hour and 12-hour clock times. There are no seconds and time zones.
TIMESTAMP [(Fractional_seconds_precision)]	7 to 11 bytes	The fractional_seconds_precision stores the fractional bits of seconds for Oracle, which defaults to 6 and an optional value of 0 to 9. There is no time zone.
TIMESTAMP [(Fractional_seconds_precision)] with time ZONE	13 bytes	Using UTC, contains fields year, MONTH, Day, HOUR, MINUTE, SECOND, Timezone_ HOUR, Timezone_minute
TIMESTAMP [(Fractional_seconds_precision)] with LOCAL time ZONE	7 to 11 bytes	When using the database time zone, use the reply time zone.
INTERVAL [(Year_precision)] to MONTH	5 bytes	Contains the time interval type for the year and month. Year_precision is the number of digits in the year field, which defaults to 2 and is preferable to 0 to 9.
INTERVAL Day [(day_precision)] To SECOND [(fractional_seconds_precision)]	11 bytes	Day_precision is the number of digits in the month field, which defaults to 2 and is preferable to 0 to 9.

1. The time field values used by To_date () and date are all midnight values. or use the trunc () function to filter to ensure that the time field is a midnight value.
2. Time and time interval type operation rules:

Adds and subtract number type constants on date and timestamp (which are converted to date type values), which are in days.

All timestamp type operations are based on UTC time. That is, for the timestamp with LOCAL time zone, the conversion is first to UTC, and the calculation is completed before being converted back.

1. INTERVAL year to Month constants:

INTERVAL ' Year-month ' year/month (precision) to month

An error is returned when the year digit exceeds precision.

Where precision is the maximum number of digits, the default is 2, preferably 0 to 9.

Example: INTERVAL ' 123-2 ' year (3) to MONTH,

INTERVAL ' 123 ' year (3),

INTERVAL ' MONTH (3).

1. INTERVAL Day to second constants:

INTERVAL ' n/time_expr/n time_expr ' Day/hour/minute (leading_precision) to Hour/minute/second (fractional_second_ Precision

INTERVAL ' n/time_expr ' SECOND (leading_precision, fractional_second_precision)

time_expr format: HH[:MI[:SS[.N]] [or MI[:SS[.N]] or SS[.N] if n is greater than seconds precision, then round N.

You can use n time_expr only if the first field is day.

Leading_precision default is 2, preferably 0 to 9.

4. Large Object Type

Data type	Length	Description
Blob	Maximum (4gb-1) * Database block size	Storage of unstructured binaries. Supports transactional processing.
Clob	Maximum (4gb-1) * Database block size	Stores single-byte or multibyte-character data. Supports transactional processing.
NCLOB	Maximum (4gb-1) * Database block size	Stores Unicode data. Supports transactional processing.
BFILE	Maximum of 2 32-1 bytes	The LOB address points to a binary file on the file system, maintaining the directory and file name. Do not participate in transaction processing. Only read-only operations are supported.

1. The LOB column contains an LOB address that points to a lob type value within the database or outside the database.

5. Other types

Data type	Length	Description
LONG	Maximum of 2GB	Variable-length type, which stores strings. Do not use this type when creating a table.
RAW (N)	The maximum is 2000 bytes, n is the number of bytes, and n must be specified	Variable-length type, which does not change the value when the character set changes.
LONG RAW	Maximum of 2GB	Variable-length types, not recommended, are recommended for conversion to BLOB types, and do not change values when the character set changes.
ROWID	10 bytes	Represents the address of the record. A string that is displayed as a 18-bit. A relative unique address value that is used to locate a record in the database. Typically, this value is determined and unique when the row of data is inserted into a database table.
Urowid (N)

1. ROWID: Data Object number 32 bits (6 characters), file Number 10 bits (3 characters), block number 22 bit (6 characters), line number 16 bit (3 characters)
2. Get rowid information using the DQMS_ROWID package:

Select Dbms_rowid.rowid_object (ROWID) object_id,                 dbms_rowid.rowid_relative_fno (ROWID) file_id,                                                                       Dbms_rowid.rowid_block_number (ROWID) block_id,                                                                      Dbms_rowid.rowid_row_number (ROWID) num                                                                              from Dept;

Oracle 11g Data type