1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Data types

Discussion in 'SQL PL/SQL' started by SBH, Oct 25, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Objective

    The article explains the various SQL data types used in the Oracle Database.

    Overview

    As a primary concept of database, every tuple must contain a value of a valid data type. Data type represents the family of classification of a value. Any value stored, manipulated under Oracle falls under one category of data type.

    1. Number

    The NUMBER data type is used to store zero, negative, positive, fixed, and floating point numbers with up to 38 digits of precision.

    [​IMG]

    2. Date

    All dates in database are stored as DATE data type. Oracle captures the information about Century, Year, Month, Day, Hour, Minute and stores in database.
    Default date format of the database can be set through an initialization parameter NLS_DATE_FORMAT. By default, the date format is DD-MON-YY and time is 12:00:00 a.m.

    2.1. 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.
    TIMESTAMP can be used in any of the following ways:

    1. datetimestamp (fractional seconds precision): fractional seconds precision must be a number between 0 and 9 (default is 6), eaxample, timestamp(6)
    2. timestamp (fractional seconds precision) with time zone
      Includes year, month, day, hour, minute, and seconds with a time zone displacement value. Example timestamp(9) with time zone
    3. timestamp (fractional seconds precision) with local time zone
      Here time zone is expressed as the session time zone For example: timestamp(6) with local time zone
    4. interval year (year precision) to month
      Here, Time period stored in years and months. For example: interval year(6) to month
    5. interval day (day precision) to second (fractional seconds precision)
      Time period stored in days, hours, minutes, and seconds with a fractional seconds precision. For example: interval day(2) to second(6)


    Below illustration shows the usage of INTERVAL based columns.


    Code (SQL):
    CREATE TABLE INTERVAL_TEST (DURATION INTERVAL YEAR (3) TO MONTH)

    TABLE created.

    SQL> INSERT INTO coupons VALUES   (INTERVAL '5' YEAR);

    1 ROW created.

    SQL> INSERT INTO coupons VALUES   (INTERVAL '1' MONTH);

    1 ROW created.

    SQL> INSERT INTO coupons VALUES   (INTERVAL '23' MONTH);

    1 ROW created.

    SQL> INSERT INTO coupons VALUES   (INTERVAL '16' YEAR);

    1 ROW created.

    SQL> SELECT * FROM INTERVAL_TEST;

    DURATION
    ------------------
    +005-00
    +000-01
    +001-11
    +016-00
    .

    3. Character

    The data type represents the family of characters. String is combination of multiple characters.

    3.1. The CHAR data type useful where length of a column has to be fixed. It can accept maximum string of length 255, which is 1 by default. String less than the precision is padded with blank space characters.

    3.2. The VARCHAR2 is the most commonly used character data type as it allows variable precision. Precision extends to 4000 characters and must be supplied during declaration. String length is the actual length of the string.

    3.3. The LONG data type stores large amount text of variable length up to 2 GB with below restrictions:
    1. A Table can have only one column with LONG data type, which cannot be constrained or indexed. Also it cannot be used in any of the SQL SELECT clauses.
    2. It cannot appear as a parameter to any program and cannot be returned from a function


    3.4. NCHAR [(n)]

    Similar to CHAR, but supports National Character Set. Precision limit is 2000 and default is 1.

    3.5. NVARCHAR2 (n)

    Similar to VARCHAR2, but supports National Character set. Precision limit is 4000 and maximum length must be specified during declaration.

    4. Binary

    RAW and LONGRAW are store the binary type data. Precision for RAW data type is 2000 characters, but for LONGRAW it is 2GB. Data retrieval is in hexadecimal format.

    6. BLOB

    The BLOB data type can store binary large objects up to 8TB to 128TB. For example, A zipped file or image file.

    7. CLOB

    The CLOB data type can store character large objects up to 8TB to 128TB. For example, a text document or XML document

    8. NCLOB

    The CLOB data type can store character large objects in multibyte national character set up to 8TB to 128TB.

    9. BFILE

    The BFILE data type value works as a file locator or pointer to file on the file sever system. The maximum file size supported is 8TB to 128TB.

    10. ROWID

    As we have ROWID as the pseudo column, the ROWID data type is used to store physical address of each row in the database.

    11. UROWID [(n)]

    The UROWID data type is used to store the logical addresses of index-organized and foreign tables where n is the size of a UROWID column. The range of n is 1 to 4000. The default value is 4000.

    12. XMLType

    It is used to store XML data into the database as a CLOB. It can store upto 4GB of XML.


    References
     

    Attached Files: