Wednesday, 18 April 2012

Oracle Built-in Datatypes

Datatypes

       Each value manipulated by Oracle Database 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. For example, you can add values of NUMBER datatype, but not values of RAW datatype.

Oracle provides following categories of built-in datatypes:

  • Character Datatypes
                * CHAR
                * NCHAR
                * VARCHAR2 and VARCHAR
                * NVARCHAR2
                * CLOB
                * NLOB
                * LONG 
  • Number Datatype
  • Time and Date Datatype
               * DATE
               * INTERVAL DAY TO SECOND
               * INTERVAL YEAR TO MONTH
               * TIMESTAMP
               * TIMESTAMP WITH THE TIME ZONE
               * TIMESTAMP WITH LOCAL TIME ZONE
  • Binary Datatypes
              * BLOB
              * BFILE
              * RAW
              * LONG RAW
Another datatype, ROWID, is used for values in the ROWID pseudocolumn, which represents the unique address of each row in a table.

The following table summarizes the information about each Oracle built-in datatype.



Datatype 
Description 
Column Length and Default  
CHAR (size[BYTE | CHAR]) 
Fixed-length character data of length size bytes or characters. 
Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (single-byte or multibyte) before setting size. 
VARCHAR2 (size[BYTE | CHAR]) 
Variable-length character data, with maximum length size bytes or characters. 
Variable for each row, up to 4000 bytes per row. Consider the character set (single-byte or multibyte) before setting size. A maximum size must be specified. 
NCHAR (size) 
Fixed-length Unicode character data of length size characters. 
Fixed for every row in the table (with trailing blanks). Column size is the number of characters. (The number of bytes is 2 times this number for theAL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 2000 bytes per row. Default is 1 character.  
NVARCHAR2 (size) 
Variable-length Unicode character data of length size characters. A maximum size must be specified. 
Variable for each row. Column size is the number of characters. (The number of bytes may be up to 2 times this number for a the AL16UTF16 encoding and 3 times this number for the UTF8 encoding.) The upper limit is 4000 bytes per row. Default is 1 character.  
CLOB  
Single-byte character data 
Up to 232 - 1 bytes, or 4 gigabytes. 
NCLOB  
Unicode national character set (NCHAR) data. 
Up to 232 - 1 bytes, or 4 gigabytes.  
LONG 
Variable-length character data. 
Variable for each row in the table, up to 232 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.  
NUMBER (p, s) 
Variable-length numeric data. Maximum precision p and/or scale sis 38. 
Variable for each row. The maximum space required for a given column is 21 bytes per row. 
DATE 
Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E. 
Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-RR) specified by the NLS_DATE_FORMAT parameter.  
INTERVAL YEAR (precision) TO MONTH 
A period of time, represented as years and months. The precisionvalue specifies the number of digits in the YEAR field of the date. The precision can be from 0 to 9, and defaults to 2 for years. 
Fixed at 5 bytes. 
INTERVAL DAY (precision) TO SECOND (precision)

A period of time, represented as days, hours, minutes, and seconds. The precision values specify the number of digits in the DAY and the fractional SECOND fields of the date. The precision can be from 0 to 9, and defaults to 2 for days and 6 for seconds. 
Fixed at 11 bytes. 
TIMESTAMP (precision) 
A value representing a date and time, including fractional seconds. (The exact resolution depends on the operating system clock.)
The precision value specifies the number of digits in the fractional second part of the SECOND date field. The precision can be from 0 to 9, and defaults to 6 
Varies from 7 to 11 bytes, depending on the precision. The default is determined by theNLS_TIMESTAMP_FORMAT initialization parameter. 
TIMESTAMP (precision) WITH TIME ZONE 
A value representing a date and time, plus an associated time zone setting. The time zone can be an offset from UTC, such as'-5:0', or a region name, such as 'US/Pacific'. 
Fixed at 13 bytes. The default is determined by theNLS_TIMESTAMP_TZ_FORMAT initialization parameter. 


TIMESTAMP (precision) WITH LOCAL TIME ZONE 
Similar to TIMESTAMP WITH TIME ZONE, except that the data is normalized to the database time zone when stored, and adjusted to match the client's time zone when retrieved. 
Varies from 7 to 11 bytes, depending on the precision. The default is determined by theNLS_TIMESTAMP_FORMAT initialization parameter. 
BLOB  
Unstructured binary data  
Up to 232 - 1 bytes, or 4 gigabytes. 
BFILE  
Binary data stored in an external file  
Up to 232 - 1 bytes, or 4 gigabytes. 
RAW (size) 
Variable-length raw binary data  
Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.  
LONG RAW 
Variable-length raw binary data 


Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.  

ROWID 
Binary data representing row addresses 
Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.  


Character Data

Use the character datatypes to store alphanumeric data:
  • 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.)
  • NCHAR and NVARCHAR2 datatypes store Unicode character data only.
  • 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.
When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:
  • To store data more efficiently, use the VARCHAR2 datatype. The CHAR datatype blank-pads and stores trailing blanks up to a fixed column length for all column values, while the VARCHAR2 datatype does not add any extra blanks.
          Ex: if you define  empname as char(20) then if you store names like “Sami” then name will occupy 20 bytes( 4 bytes for characters “Sami” and 16 blank spaces)
And if you define  empname as varchar2(20) then if you store names like “Sami” then oracle will take 4 bytes only.
  • Use the CHAR datatype when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2 when trailing blanks are important in string comparisons.
  • The CHAR and VARCHAR2 datatypes are and will always be fully supported. At this time, the VARCHAR datatype automatically corresponds to the VARCHAR2 datatype and is reserved for future use.

Numeric Data

        Use the NUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10-130 through 9.99 x10125, as well as zero, in a NUMBER column.
        You can specify that a column contains a floating-point number, for example:
distance NUMBER
      Or, you can specify a precision (total number of digits) and scale (number of digits to right of decimal point):  price NUMBER (8, 2)
    specifying precision and scale helps to identify bad input values.If a precision is not specified, the column stores values as given. The following table shows examples of how data different scale factors affect storage.
           
Input Data 
Specified As 
Stored As 
4,751,132.79  
NUMBER 
4751132.79 
4,751,132.79  
NUMBER (9) 
4751133
4,751,132.79  
NUMBER (9,2) 
4751132.79 
4,751,132.79 
NUMBER (9,1) 
4751132.7 
4,751,132.79 
NUMBER (6) 
(not accepted, exceeds precision) 
4,751,132.79 
NUMBER (7, -2) 
4,751100 

Date and Time Data

      Use the DATE datatype to store point-in-time values (dates and times) in a table. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds.
     Use the TIMESTAMP datatype to store precise values, down to fractional seconds. For example, an application that must decide which of two events occurred first might use TIMESTAMP. An application that needs to specify the time for a job to execute might use DATE.

Date Format

    For input and output of dates, the standard Oracle default date format is DD-MON-YY.
 Example:  '19-APR-2012'
    To change the format during a session, use the ALTER SESSION statement. To enter dates that are not in the current default date format, use the TO_DATE function with a format mask.
Example:   TO_DATE ('April 19, 2012', 'MONTH DD, YYYY')

Time Format

    Time is stored in 24-hour format, HH24:MI:SS. By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE function with a format mask indicating the time portion, as in:
INSERT INTO Birthdays_tab (bname, bday) VALUES
    ('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)