Oracle Data Types


Two general types of data are found in the Oracle database: built-in data types and user-defined data types. This section provides an explanation of both types and the kinds of data associated with each.

User-Defined Data Types

Oracle supports the ability for users to define their own abstract data types and use them within the application. For example, you could define an address type as including street_address1, street_address2, city, state, country, and zip_code and use that type in tables as a single entity rather than as each individual piece. If you always reference addresses in the same way, this can ease coding and facilitate reuse.

Built-In Data Types

Oracle provides several built-in data types to store scalar data, collections, and relationships.

Scalar Data

Scalar data is data that can take on a single value, even if that value is large. The following list describes in more detail the various scalar types available in Oracle:

  • CHAR(N) Represents character data that is a fixed length of N bytes. If the data stored in the CHAR field does not take up the entire N bytes, the data gets padded with blanks. The minimum size of a CHAR column is 1 byte. The maximum is 2000 bytes. Character, or CHAR, data can be any string of charactersalpha, numeric, or special characters.

  • NCHAR(N) Represents character data that is a fixed length of N positions and designed to support globalization data. If the data stored in the CHAR field does not take up the entire N bytes, the data gets padded with blanks. The minimum number of bytes or characters (for multibyte characters) is one character. The maximum number of bytes, regardless of the number of bytes per character, is 2000 bytes. More can be found in Chapter 7, "Globalization Support."

  • VARCHAR2(N) A data type that can store variable length data, up to the number of bytes specified by N. This data type uses only the exact number of bytes required to store the exact column value with no additional padding. The maximum number of bytes per column is 4000.

  • NVARCHAR2(N) The globalization version of the VARCHAR2 data type and is a data type that can store variable length data, up to the number of bytes specified by N. This data type uses only the exact number of bytes required to store the exact column value with no additional padding. The maximum number of bytes per column is 4000.

  • NUMBER(P,S) Numeric data in Oracle is always stored as variable length data. Number data types can store up to 38 significant digits that can be split into whole numbers and decimals. Numeric data requires one byte for the exponent, one byte for every two significant digits of the decimal portion of the number, and one byte for negative numbers, if the number of significant digits is less than 38 total bytes.

    The decimal portion of a number is called the mantissa.


  • DATE Stored in a fixed-length field of 7 bytes. DATE type always includes time in hours, minutes, and seconds, even when time is not displayed.

  • TIMESTAMP Stores the date and time, including fractional seconds up to 9 decimal positions. TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE both use time zones to factor items such as Daylight Savings Time in the United States. A column defined as TIMESTAMP and a column defined as TIMESTAMP WITH LOCAL TIME ZONE can be candidate keys for the primary key of a table. TIMESTAMP WITH TIME ZONE cannot.

    With TIMESTAMP WITH TIME ZONE you can store the offset or time zone displacement. The displacement is the amount of time difference, in hours and minutes, between the local time and the Universal Time Coordinated (UTC), which was formally know as Greenwich Meridian Time (GMT). TIMESTAMP WITH TIME ZONE represents absolute time. This feature takes up extra bytes of storage.

    With TIMESTAMP WITH LOCAL TIME ZONE you can store the standardized time zone information much as you can with TIMESTAMP WITH TIME ZONE; however, instead of storing the offset as additional bytes of storage, the time values are normalized and stored in terms of the database's time zone. Whenever a user attempts to retrieve the normalized information, the database displays the information in terms of the user's local time.

    TIMESTAMP WITH TIME ZONE requires extra storage bytes for every column with that data type; TIMESTAMP WITH LOCAL TIME ZONE does not.


  • RAW(N) Enables you to store small binary data without Oracle performing character set conversions whenever the RAW data is transmitted either across machines or from one database to another using Oracle utilities. A RAW column can be up to 2000 bytes in size.

  • BLOB (Binary Large Objects or BLOBs) Provided by Oracle for storing unstructured data. Supports object type attributes. If the total size of the BLOB is greater than 4000 bytes, Oracle stores a locator in the table and stores the binary data elsewhere (separate segments or tablespaces). BLOBs are considered to actually be raw data because its structure isn't recognized by the database. They are often images, movie clips, or sound bytes.

  • CLOB (Character Large Objects or CLOBs) Large fixed-width character data. Supports object type attributes. If the total size of the CLOB is greater than 4000 bytes, Oracle stores a locator in the table and stores the character data elsewhere (separate segments or tablespaces). A CLOB may be indexed and can be searched by the Oracle Text search engine.

  • NCLOB The data type used for large fixed-width national character set data. If the total size of the NCLOB is greater than 4000 bytes, Oracle stores a locator in the table and stores the character data elsewhere (separate segments or tablespaces).

  • BFILEs Used for storing unstructured data, referenced by the database yet actually stored in operating system files.

  • LONG and LONG RAW For large fixed-width character data; used to be used for unstructured data such as images, documents, and sound bytes. These are no longer the preferred way and are provided now for backward compatibility. Rather than using these data types, it is preferred that you use the LOB data (CLOB, BLOB, NCLOB, or BFILE). LOBs are distinct from LONG and LONG RAW, are not simply interchangeable terminology, and will not support the same application programming interfaces (APIs).

    Some differences between LONG and LONG RAW and the LOB data types are notable. LONG and LONG RAW were limited to one column per table, whereas LOB data can be included in multiple columns per table. LONG and LONG RAW could hold up to 2 gigabytes of data, LOBs can hold up to 4 gigabytes. With LONG and LONG RAW, data was always stored inline; however, with LOBs, it is stored either inline or out of line. LOBs support object types; LONG and LONG RAW don't. LONG and LONG RAW were limited to sequential access to the chunk of data stored in the column; LOBs support random access to the chunks.

    LONGs and LONG RAWs are stored as chained rows with pieces stored in different blocks, each block pointing to the next. There is no random access possible, nor any filelike interface through which to interact with the data.

  • ROWID A unique identifier for each row in the database that is not stored explicitly as a column value but that can be queried (as implicitly included with the table) along with other columns in the table. Although it does not directly provide the physical address of a row, it can be used to locate the row and provides the fastest means for accessing a row in a table. ROWID is stored in indexes to specify rows with a given set of key values.

    The ROWID column is considered to be a pseudo column (not evident when describing the structure of a table or by executing a SELECT * from the table, nor does the ROWID column take up space in the table). You cannot insert, update, or delete the value of ROWID. You can, however, retrieve the value stored in the column by running the following command:

     SELECT ROWID FROM mytable; 

    Physical ROWIDs store the addresses of rows in regular tables (not index-organized tables), cluster tables, and table partitions and subpartitions. They also store the addresses of indexes and index partitions and subpartitions. Physical ROWIDs provide the fastest possible access to any row of a given table because they contain the physical address of the row (as far down as the specific block in which it is located), and Oracle guarantees that, as long as the row exists, its physical ROWID does not change with the exception of across import/export operations. The format for these ROWIDs will be covered later in this chapter.

    Logical ROWIDs store the addresses of rows in index-organized tables. Because rows in index-organized tables do not exactly have a permanent physical address (rather they are stored in the index leaves and can therefore move within the block or to different blocks due to insertions), their ROWIDs cannot reference physical addresses. Instead, Oracle provides index-organized tables with logical row identifiers (logical ROWIDs) based on the table's primary key. Oracle then constructs secondary indexes on the index-organized tables based on the logical ROWIDs.

  • UROWID Starting in 8i, Oracle provided a data type called universal ROWID or UROWID. UROWID supports ROWIDs of non-Oracle tables and can store all kinds of ROWIDs (both physical and logical). A UROWID is required if you want to store a ROWID for rows stored in index-organized tables, and the COMPATIBLE setting in the initialization file needs to be 8.1 or higher to use UROWID.

Collection Data

Two types of collection data are available in which to store data that is representative of a given row in a given table. In releases prior to Oracle 8i, the Objects option was required to define and use collections. This is no longer the case.

Varying Arrays

Varying arrays (VARRAY) are useful structures in which to store lists that contain a small number of elements (for example, colors for a sweater or phone numbers for a given client). A VARRAY is an ordered set of data elements that for each VARRAY are the same data type. Each element has an index like any other array, which is a number corresponding to the position of the element in the array. The number of elements in the VARRAY determines its size, and Oracle allows them to be of variable size (thus the VARRAY name). However, the maximum size must be specified when declaring the array type.

Nested Tables

Nested tables provide a means of defining entire tables as a column within a table. They can be used to store sets that may have many records (such as items in an order or a bill of materials for an item). Nested tables are unordered sets or records, and the rows in a nested table all have the same structure. The rows in a nested table are stored separately from the parent table, and a pointer from the parent table row points to the corresponding nested table rows. There is no predetermined size for a nested table, unlike VARRAYS, and the storage characteristics for the nested table are assigned and defined by the database administrator.

Relationship Data Types

Relationship data types (or REFs) are used as pointers within the database, and the use of these requires the Objects option of the database. With this data type, a row in an insurance claim table could point to a row in the invoice table without having to store the invoice number on the claim record or the claim number on the invoice record.

Extended Versus a Restricted ROWID

As you learned earlier, each row, be it table or index, in the database has an address. You can examine this address by querying the ROWID pseudo column. This ROWID is a string representing the address of the row that has the data type ROWID. The restricted ROWID format is that which was used in versions prior to Oracle 8. Beginning in Oracle 8, Oracle incorporated an extended format for ROWIDs to efficiently and effectively support partitioned tables and indexes as well as tablespace relative data block addresses without any ambiguity.

Restricted ROWID

The format of the restricted ROWID is block.row.file. block refers to the hexadecimal string that represents the data block of the data file that contains the given row. The length of this portion of the ROWID is operating system dependent. row refers to a four-digit hexadecimal string that identifies the specific row in the data block. The first row in a data block has a value of 0. file is a hexadecimal string that refers to the database file containing the row. The first data file in the database has the number 1 (typically belonging to the SYSTEM tablespace), and the length of this string is operating system dependent.

Extended ROWID

The extended ROWID requires 10 bytes of on-disk storage and is displayed using 18 characters that have similar components to the restricted ROWID. The format is as follows:

 data_object_number.relative_file_number.block_number.row_number 

The data object number is assigned to each data object (table, index) when it is created. Data object number is unique within the database and takes up 32 bits. Relative file number is unique to each file within a tablespace; relative file number needs 10 bits. Block number is the position of the block containing the row within the given file; this requires 22 bits. Row number identifies the position of the row directory slot in the data block header and takes up the remaining 16 bits.

Extended ROWIDs are stored as base 64 values. They can contain the characters A-Z, a-z, and 0-9 as well as the plus sign (+) and the forward slash (/). You can use the DBMS_ROWID package to enable you to interpret the extended ROWID contents.

Although the restricted form is still supported for backward compatibility, all tables that have ROWID returned in a query will be returned in the extended format.


Because segments can only reside in only one tablespace, the Oracle system tables can be used to determine the tablespace in which the row resides. The relative file number within the given tablespace can be used to locate the file, and the block number will be used to locate the block in the tablespace and data file containing the row. The row number is then used to locate the row directory for the row. In this way, Oracle can use the ROWID to locate any row within a database.

You now have an understanding of the different data types that can reside in a table and the way that Oracle uses the ROWID to uniquely find the row in the database. We now need to determine what exactly is meant by a row and its structure because rows make up tables.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net