Oracle Datatypes

Character Datatypes

Because Oracle blank-pads values stored in CHAR columns but not in VARCHAR2 columns, a value stored in a VARCHAR2 column may take up less space than if it were stored in a CHAR column. For this reason, a full table scan on a large table containing VARCHAR2 columns may read fewer data blocks than a full table scan on a table containing the same data stored in CHAR columns. If your application often performs full table scans on large tables containing character data, you might be able to improve performance by storing this data in VARCHAR2 columns rather than in CHAR columns.

However, performance is not the only factor to consider when deciding which of these datatypes to use. Oracle uses different semantics to compare values of each datatype. You might choose one datatype over the other if your application is sensitive to the differences between these semantics. For example, if you want Oracle to ignore trailing blanks when comparing character values, you must store these values in CHAR columns. For information on the comparison semantics for these datatypes, see the Oracle7 Server SQL Reference manual.

CHAR Datatype

• If a shorter value is given, the value is blank-padded to the fixed length.

• If a value is too large, Oracle returns an error.

VARCHAR2 Datatype

Oracle compares VARCHAR2 values using non-padded comparison semantics. Two values are only considered equal if they have the same characters and are of equal length.

VARCHAR Datatype

Column Lengths for Character Datatypes

NUMBER Datatype

For numeric columns you can specify the column as

column_name NUMBER

or you can specify a precision (total number of digits) and scale (number of digits to right of decimal point):

column_name NUMBER (precision, scale)

If a precision is not specified, the column stores values as given. If no scale is specified, it is implied that the scale is zero. The scale can range from -84 to 127.

Although not required, specifying the precision and scale for numeric fields provides extra integrity checking on input. Table 5 - 1 shows examples of how data would be stored using different scale factors.

Notice that if a negative scale is specified, the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (7,-2) means to round to the nearest hundreds, as shown in Table 5 - 1.

Input Data	Specified As	Stored As
7,456,123.89	NUMBER	7456123.89
7,456,123.89	NUMBER (9)	7456124
7,456,123.89	NUMBER (9,2)	7456123.89
7,456,123.89	NUMBER (9,1)	7456123.9
7,456,123.89	NUMBER (6)	(not accepted, exceeds precision)
7,456,123.89	NUMBER (7, -2)	7456100

Table 5 - 1. How Scale Factors Affect Numeric Data Storage

DATE Datatype

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second. See Chapter 3 in the Programmer's Guide to the Oracle Call Interface for a complete description of the Oracle internal date format.

For input and output of dates, the standard Oracle default date format is DD-MON-YY, as in

'13-NOV-92'

To change this default date format on an instance-wide basis, use the NLS_DATE_FORMAT parameter. 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, as in

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')

If the date format DD-MON-YY is used, YY indicates the year in the 20th century (for example, 31-DEC-92 is December 31, 1992). If you want to indicate years in any century other than the 20th century, use a different format mask, as shown above.

Time is stored in 24-hour format--HH:MM:SS. By default, the time in a date field is 12:00:00 A.M. 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 (bname, bday) VALUES
('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));

To compare dates that have time data, use the SQL function TRUNC if you want to ignore the time component. Use the SQL function SYSDATE to return the system date and time. The FIXED_DATE initialization parameter allows you to set SYSDATE to a constant; this can be useful for testing.

Using Julian Dates

Use the format mask `J' with date functions (TO_DATE or TO_CHAR, but not TO_NUMBER) to convert date data into Julian dates. For example, the following query returns all dates in Julian date format:

SELECT TO_CHAR (hiredate, 'J') FROM emp;

Use the TO_NUMBER function to use Julian dates in calculations. You can use the TO_DATE function to enter Julian dates:

INSERT INTO emp (hiredate) VALUES (TO_DATE(2448921, 'J'));

Note: Oracle Julian dates might not be compatible with Julian dates generated by other date algorithms.

Date Arithmetic

Missing dates can be entered into the database, but are ignored in date arithmetic and treated as the next date. For example, the next day after 04 October 1582 is 15 October 1582 and the day following 05 October 1582 is 16 Oct 1582.

Note: This discussion of date arithmetic may not apply to all countries' date standards (for example, Asian countries).

LONG Datatype

Uses of LONG Data

Restrictions on LONG and LONG RAW Data

• Only one LONG column is allowed per table.

• LONG columns cannot be indexed.

• LONG columns cannot appear in integrity constraints.

• LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses, or with the DISTINCT operator in SELECT statements.

• LONG columns cannot be referenced by SQL functions (such as SUBSTR or INSTR).

• LONG columns cannot be used in the SELECT list of a subquery or queries combined by set operators (UNION, UNION ALL, INTERSECT, or MINUS).

• LONG columns cannot be used in SQL expressions.

• LONG columns cannot be referenced when creating a table with a query (CREATE TABLE . . . AS SELECT . . .) or when inserting into a table (or view) with a query (INSERT INTO . . . SELECT . . .).

• A variable or argument of a PL/SQL program unit cannot be declared using the LONG datatype.

Example

To store information on magazine articles, including the texts of each article, create two tables:

CREATE TABLE article_header (id             NUMBER
                                            PRIMARY KEY,
                             title          VARCHAR2(200),
                             first_author   VARCHAR2(30),
                             journal        VARCHAR2(50),
                             pub_date       DATE)
CREATE TABLE article_text   (id             NUMBER
                                            REFERENCES
                                            article_header,
                             text           LONG)

The ARTICLE_TEXT table stores only the text of each article. The ARTICLE_HEADER table stores all other information about the article, including the title, first author, and journal and date of publication. The tables are related by the referential integrity constraint on the ID column of each table.

This design allows SQL statements to query data other than the text of an article without reading through the text. If you want to select all first authors published in Nature magazine during July 1991, you can issue this statement that queries the ARTICLE_HEADER table:

SELECT first_author
   FROM article_header
   WHERE journal = 'NATURE'
      AND TO_CHAR(pub_date, 'MM YYYY') = '07 1991')

If the text of each article were stored in the same table with the first author, publication, and publication date, Oracle would have to read through the text to perform this query.

RAW and LONG RAW Datatypes

Like the character datatype VARCHAR2, RAW and LONG RAW are variable length; however, the length of RAW cannot exceed 255 bytes, and there is no character set conversion done for RAW or LONG RAW data. CHAR, VARCHAR2, and LONG data is automatically converted from the database character set to the character set defined for the user session by the NLS_LANG parameter, where these are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data (as is the case when entering RAW data as a literal in an INSERT statement), the data is represented as one hexadecimal character representing the bit pattern for every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.

LONG RAW data cannot be indexed, but RAW data can be indexed.

ROWIDs and the ROWID Datatype

Each table in an Oracle database internally has a pseudo-column named ROWID; this pseudo-column is not evident when listing the structure of a table by executing a SELECT * FROM . . . statement, or a DESCRIBE . . . statement using SQL*Plus. However, each row's address can be retrieved with a SQL query using the reserved word ROWID as a column name, as in

SELECT ROWID, ename FROM emp;

ROWIDs use a binary representation of the physical address for each row selected. When queried using SQL*Plus or SQL*DBA, the binary representation is converted to a VARCHAR2/hexadecimal representation. The above query might return the following row information:

ROWID              ENAME
------------------ ----------
00000DD5.0000.0001 SMITH
00000DD5.0001.0001 ALLEN
00000DD5.0002.0001 WARD

As shown above, a ROWID's VARCHAR2/hexadecimal representation is divided into three pieces: block.row.file.

• The data block that contains the row (block DD5 in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.

• The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of a given block always start with 0.

• The datafile that contains the row (file 1 in the example). The first datafile of every database is always 1, and file numbers are distinct within a database.

You cannot set the value of the pseudo-column ROWID in INSERT or UPDATE statements. The ROWIDs in the pseudo-column ROWID are used internally by Oracle for various operations (see the next section). Though you can reference ROWIDs in the pseudo-column ROWID like other table columns (used in SELECT lists and WHERE clauses), ROWIDs in this pseudo-column are not stored in the database, nor are they database data.

ROWIDs and Non-Oracle Databases To execute Oracle database applications against non-Oracle database servers, use the Oracle Open Gateway. In such cases, the binary format of ROWIDs varies according to the characteristics of the non-Oracle system. Furthermore, no standard translation to VARCHAR2/hexadecimal format is available. Programs can still use the ROWID datatype; however, they must use a non-standard translation to hexadecimal format of length up to 256 bytes. Refer to the relevant manual for OCIs or Precompilers for further details on handling ROWIDs with non-Oracle systems.

How ROWIDs Are Used

• ROWIDs are the fastest means of accessing particular rows.

• ROWIDs can be used to see how a table is organized.

• ROWIDs are unique identifiers for rows in a given table.

You can also create tables with columns defined using the ROWID datatype; for example, you might define an exception table with a column of datatype ROWID to store the ROWIDs of rows in the database that violate integrity constraints. Such columns defined using the ROWID datatype behave like other table columns; that is, values can be updated, etc. All values in a column defined as datatype ROWID require six bytes to store pertinent column data.

Examples of Using ROWIDs

SELECT ROWID, SUBSTR(ROWID,15,4) "FILE",
      SUBSTR(ROWID,1,8) "BLOCK",
      SUBSTR(ROWID,10,4) "ROW"
      FROM emp;

might return the following data:

ROWID               FILE  BLOCK     ROW
------------------  ----  --------  ----
00000DD5.0000.0001  0001  00000DD5  0000
00000DD5.0001.0001  0001  00000DD5  0001
00000DD5.0002.0001  0001  00000DD5  0002

ROWIDs can be useful to reveal information about the physical storage of a table's data. For example, if you are interested in the physical location of a table's rows (for example, for table striping), the following query tells how many datafiles contain rows of a given table:

SELECT COUNT(DISTINCT(SUBSTR(ROWID,15,4))) "FILES" FROM tablename;

which might return

   FILES
--------

       2

Summary of Oracle Datatype Information

Datatype	Description
CHAR (size)	Fixed-length character data of length size. Maximum size is 255 bytes. Default size is 1 byte.
VARCHAR2 (size)	Variable-length character data. Maximum size is 2000 bytes.
FLOAT (p)	A floating-point number with binary precision p. FLOAT with no precision is the same as FLOAT (126).
NUMBER (p, s)	Variable-length numeric data. The precision p (total number of digits) can range from 1 to 38. The scale is s (number of decimal places) and can range from -84 to 127.
DATE	Fixed-length date and time data, ranging from January 1, 4712 BC to December 31, 4712 AD. Default format is the value of the NLS_DATE_FORMAT or ALTER SESSION parameter.
LONG	Variable-length character date up to 2^31 * 1 bytes - 1, or 2 gigabytes - 1 byte.
RAW (size)	Variable-length raw binary data. A maximum size must be specified, up to 255.
LONG RAW	Variable-length raw binary data up to 2^31 * 1 bytes - 1, or 2 gigabytes - 1 byte.
ROWID	Binary data representing row addresses.
MLSLABEL	Variable-length tag (2 - 5 bytes) that maps to a binary operating system label. For use with Trusted Oracle; see for details.

Table 5 - 2. Summary of Oracle Datatype Information