DataTypes in Oracle

The article below was written By Sanskruti

 

---

 

      A datatype is a classification of a particular type of information or data. Each value manipulated by Oracle has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another.

Numeric

The NUMBER data type is used to store zero, negative, positive, fixed, and floating point numbers with up to 38 digits of precision. Numbers range between 1.0x10 -130 and 1.0x10 126.

Numbers can be defined in following ways:

• NUMBER(p,s)

  where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point). The scale can range between -84 to 127.

• NUMBER (p)

  This is a fixed-point number with a scale of zero and a precision of p.

• FLOAT[(p)]

  Oracle supports the ANSI data type FLOAT. Therefore, specifying a floating-point number you can use FLOAT data type instead of NUMBER data type.
  p is the binary precision that can range from 1 to 126. If p is not specified the default value is binary 126. To convert from binary to decimal precision, multiply p by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

Date

Instead of storing date and time information in a character or numeric format, a separate data type was created. For each DATE data type, the following information is stored:

• Century
• Year
• Month
• Day
• Hour
• Minute
• Second

You can easily retrieve the current date and time by using the function SYSDATE. Date arithmetic is possible using number constants or other dates. Only addition and subtraction are supported. For example, SYSDATE + 7 will return one week from today. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY, where DD is the day of the month (the first day of the month is 01), MON is the abbreviated month name, and YY is a two-digit year designation. If you do not specify a time, the default time is 12:00:00 a.m. If only the time component is captured, the default date will be the first day of the current month.

• TIMESTAMP[(fractional_seconds_precision)]

The TIMESTAMP data type is an extension of the DATE data type. For each TIMESTAMP value, Oracle stores the following information: year, month, day, hour, minute, second and fraction of second. fractional_seconds_precision optionally specifies the number of digits in the fractional part of second and can be a number in the range 0 to 9. The default is 6. The TIMESTAMP data type is available in Oracle 9i Release 1 (9.0.1) or later.

Character

1. The CHAR data type is used where fixed-length fields are necessary. Any length up to 255 characters can be specified. The default length is 1. When data is entered, any space left over will be filled with blanks. All alpha-numeric characters are allowed.
2. The VARCHAR2 is used for variable-length fields. A length component must be supplied when you use this data type. The maximum length is 2000 characters. All alpha-numeric characters are allowed.
3. The LONG data type is used to store large amounts of variable-length text. Any length up to 2 GB can be specified. Be aware that there are some restrictions to using this data type, such as:
   • Only one column per table can be defined as LONG.
   • A LONG column cannot be indexed.
   • A LONG column cannot be passed as an argument to a procedure.
   • A function cannot be used to return a LONG column.
   • A LONG column cannot be used in where, order by, group by, or connect by clauses.
4. NCHAR[(n)]
   Fixed-length character data of length n characters or bytes, depending on the national character set. The maximum length is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. The default value is 1.
5. NVARCHAR2(n)
   Variable-length character data having maximum length n characters or bytes, depending on the national character set. The maximum length is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify the maximum length for NVARCHAR2 columns.

Binary

Two data types, RAW and LONGRAW, are available for storing binary type data such as digitized sound and images. These data types take on similar characteristics as the VARCHAR2 and LONG data types already mentioned. Use the RAW data type to store binary data up to 2000 characters and use the LONGRAW data type to store binary data up to 2 GB. Oracle only stores and retrieves binary data; no string manipulations are allowed. Data is retrieved as hexadecimal character values.

Others

BLOB, CLOB, NCLOB and BFILE

The built-in LOB data types BLOB, CLOB and NCLOB (stored internally), and BFILE (stored externally), can store large and unstructured data such as text, images and spatial data up to 4 gigabytes in size.

BLOB

The BLOB data type stores binary large objects. BLOB can store up to 4 gigabytes of binary data.

CLOB

The CBLOB data type stores character large objects. CLOB can store up to 4 gigabytes of character data.

NCLOB

The NCBLOB data type stores character large objects in multibyte national character set. NCLOB can store up to 4 gigabytes of character data.

BFILE

The BFILE data type enables access to binary file LOBs that are stored in file systems outside the Oracle database. A BFILE column stores a locator, which serves as a pointer to a binary file on the server's file system. The maximum file size supported is 4 gigabytes.

ROWID and UROWID

ROWID

The ROWID data type is used to store physical address of each row in the database. This data type is primarily for values returned by the ROWID pseudocolumn.

UROWID[(n)]

The UROWID data type is used to store the logical addresses of index-organized and foreign tables.
n is the size of a UROWID column. The range of n is 1 to 4000. The default value is 4000.
This data type is available in Oracle 9i Release 1 (9.0.1) or later.