Oracle7 Server Concepts Manual Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence


I am the voice of today, the herald of tomorrow. ...I am the leaden army that conquers the world -- I am TYPE.

Frederic William Goudy: The Type Speaks

This chapter discusses the Oracle datatypes, their properties, and how they map to non-Oracle datatypes. It includes:

Oracle Datatypes

The following sections describe the Oracle datatypes that you can use in column definitions:

Character Datatypes

The CHAR and VARCHAR2 datatypes store alphanumeric data. Character data is stored in strings, with byte values corresponding to the character encoding scheme (generally called a character set or code page). The database's character set is established when you create the database, and never changes. Examples of character sets are 7-bit ASCII (American Standard Code for Information Interchange), EBCDIC (Extended Binary Coded Decimal Interchange Code) Code Page 500, and Japan Extended UNIX. Oracle supports both single-byte and multi-byte encoding schemes.

CHAR Datatype

The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a column length (in bytes, not characters) between 1 and 255 for the CHAR column (default is 1). Oracle then guarantees the following:

Oracle compares CHAR values using the blank-padded comparison semantics. See Oracle7 Server SQL Reference for more information on comparison semantics.

VARCHAR2 Datatype

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum column length (in bytes, not characters) between 1 and 2000 for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field (unless a value exceeds the column's maximum length and Oracle returns an error). For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece only stores the 10 characters (10 bytes), not 50.

Oracle compares VARCHAR2 values using the non-padded comparison semantics. See Oracle7 Server SQL Reference for more information on comparison semantics.

VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. However, in a future version of Oracle, the VARCHAR datatype might store variable-length character strings compared with different comparison semantics. Therefore, use the VARCHAR2 datatype to store variable-length character strings.

How to Choose the Correct Character Datatype

When deciding which datatype to use for a column that will store alphanumeric data in a table, consider the following points of distinction:

Column Lengths for Character Datatypes and NLS Character Sets

The National Language Support (NLS) feature of Oracle allows the use of various character sets for the character datatypes. You should consider the size of characters when you specify the column length for character datatypes. For example, some characters require one byte, some require two bytes, and so on. You must consider this issue when estimating space for tables with columns that contain character data. See Oracle7 Server Reference and Oracle7 Server Utilities for more information about the NLS feature of Oracle.

NUMBER Datatype

The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle, up to 38 digits of precision. The following numbers can be stored in a NUMBER column:

For numeric columns you can specify the column as follows:

column_name NUMBER 

Optionally, you can also 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, the scale is zero.

Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:

column_name NUMBER (*, scale) 

In this case, the precision is 38 and the specified scale is maintained.

When you specify numeric fields, it is a good idea to specify the precision and scale; this provides extra integrity checking on input. Table 6 - 1 shows examples of how data would be stored using different scale factors.

Input Data Specified As Stored As
7,456,123.89 NUMBER 7456123.89
7,456,123.89 NUMBER(*,1) 7456123.9
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 6 - 1. How Scale Factors Affect Numeric Data Storage

If you specify a negative scale, Oracle rounds the actual data to the specified number of places to the left of the decimal point. For example, specifying (7,-2) means Oracle should round to the nearest hundredths, as shown in Table 6 - 1.

For input and output of numbers, the standard Oracle default decimal character is a period, as in the number "1234.56". (The decimal is the character that separates the integer and decimal parts of a number.) You can change the default decimal character with the parameter NLS_NUMERIC_CHARACTERS. You can also change it for the duration of a session with the ALTER SESSION statement. To enter numbers that do not use the current default decimal character, use the TO_NUMBER function.

Internal Numeric Format

Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. (However, there are only 38 digits of precision.) Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 10^2, with one byte used to store the exponent (2) and two bytes used to store the three significant digits of the mantissa (4, 1, 2).

Taking this into account, the column data size for a particular numeric data value NUMBER (p), where p is the precision of a given value (scale has no effect), can be calculated using the following formula:

  1 byte			(exponent) 
  FLOOR(p/2)+1 bytes	(mantissa) 
+ 1 byte			(only for a negative number where
					the number of significant digits is
					less than 38) 
number of bytes of data 

Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations; zero and negative infinity each require one byte, while positive infinity requires two bytes.

DATE Datatype

The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight). Oracle can store dates ranging from Jan 1, 4712 BC through Dec 31, 4712 AD. Unless you specifically specify BC, AD date entries are the default.

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.

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


You can change this default date format for an instance with the parameter NLS_DATE_FORMAT. You can also change it during a user session with the ALTER SESSION statement. To enter dates that are not in standard Oracle date format, use the TO_DATE function with a format mask:

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

Note: If you use the standard date format DD-MON-YY, 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.

Oracle stores time in 24-hour format -- HH: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, the TO_DATE function must be used with a format mask indicating the time portion, as in

INSERT INTO birthdays (bname, bday) VALUES 
('ANDY',TO_DATE('13-AUG-66 12:56 A.M.','DD-MON-YY HH:MI A.M.')); 

Using Julian Dates

Julian dates allow continuous dating from a common reference. (The reference is 01-01-4712 years B.C., so current dates are somewhere in the 2.4 million range.) A Julian date is nominally a non-integer, the fractional part being a portion of a day. Oracle uses a simplified approach that results in integer values. Julian dates can be calculated and interpreted differently; the calculation method used by Oracle results in a seven-digit number (for dates most often used), such as 2449086 for 08-APR-93.

The format mask "J" can be used with date functions (TO_DATE or TO_CHAR) 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; 

You must use the TO_NUMBER function if you want 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')); 

Date Arithmetic

Oracle date arithmetic takes into account the anomalies of the calendars used throughout history. For example, the switch from the Julian to the Gregorian calendar, 15-10-1582, eliminated the previous 10 days (05-10-1582 through 14-10-1582). The year 0 does not exist.

You can enter missing dates into the database, but they are ignored in date arithmetic and treated as the next "real" date. For example, the next day after 04-10-1582 is 15-10-1582, and the day following 05-10-1582 is also 15-10-1582.

Note: This discussion of date arithmetic may not apply to all countries' date standards (such as those in Asia).

LONG Datatype

Columns defined as LONG can store variable-length character data containing up to two gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems. Also see the next section for information about the LONG RAW datatype.

Uses of LONG Data

LONG datatype columns are used in the data dictionary to store the text of view definitions. You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.

Restrictions on LONG and LONG RAW Data

Although LONG (and LONG RAW; see below) columns have many uses, there are some restrictions on their use:

RAW and LONG RAW Datatypes

The RAW and LONG RAW datatypes are used for data that is not to be interpreted (not converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, LONG RAW can be used to store graphics, sound, documents, or arrays of binary data; the interpretation is dependent on the use.

RAW is a variable-length datatype like the VARCHAR2 character datatype, except that SQL*Net (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, SQL*Net and Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the database character set to the user session character set (set by the NLS_LANGUAGE parameter of the ALTER SESSION command), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing 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

Every row in a non-clustered table of an Oracle database is assigned a unique ROWID that corresponds to the physical address of a row's row piece (the initial row piece if the row is chained among multiple row pieces). In the case of clustered tables, rows in different tables that are in the same data block can have the same ROWID.

Each table in an Oracle database internally has a pseudocolumn named ROWID. This pseudocolumn 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:

SELECT ROWID, ename FROM emp; 

ROWIDs use a binary representation of the physical address for each row selected. When queried using SQL*Plus or Server Manager, 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 in a three-piece format: block.row.file.

A row's assigned ROWID remains unchanged unless the row is exported and imported (using the IMPORT and EXPORT utilities). When you delete a row from a table (and then commit the encompassing transaction), the deleted row's associated ROWID can be assigned to a row inserted in a subsequent transaction.

You cannot set the value of the pseudocolumn ROWID in INSERT or UPDATE statements. Oracle uses the ROWIDs in the pseudocolumn ROWID internally for various operations as described in the following section. Though you can reference ROWIDs in the pseudocolumn ROWID like other table columns (used in SELECT lists and WHERE clauses), ROWIDs are not stored in the database, nor are they database data.

ROWIDs and Non-Oracle Databases Oracle database applications can be executed against non-Oracle database servers using SQL*Connect or 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

Oracle uses ROWIDs internally for the construction of indexes. Each key in an index is associated with a ROWID that points to the associated row's address for fast access.

End-users and application developers can also use ROWIDs for several important uses:

Before you use ROWIDs in DML statements, they should be verified and guaranteed not to change; the intended rows should be locked so they cannot be deleted. Under some circumstances, requesting data with an invalid ROWID could cause a statement to fail.

You can also create tables with columns defined using the ROWID datatype. For example, you can define an exception table with a column of datatype ROWID to store the ROWIDs of rows in the database that violate integrity constraints. Columns defined using the ROWID datatype behave like other table columns; values can be updated, and so on. Each value in a column defined as datatype ROWID requires six bytes to store pertinent column data.

Examples of Using ROWIDs

Using some group functions with ROWID, you can see how data is internally stored in an Oracle database.

Use the function SUBSTR to break the data in ROWID into its three components (file, block, and row). For example:

	   SUBSTR(ROWID,10,4) "ROW" 
	   FROM products; 

 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 for revealing 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 (such as for table striping), the following query tells how many datafiles contain rows of a given table:


For more information on how to use ROWIDs, refer to the Oracle7 Server SQL Reference, the PL/SQL User's Guide and Reference, Oracle7 Server Tuning, and other books that document Oracle tools and utilities.

The MLSLABEL Datatype

Trusted Oracle provides one additional datatype: the MLSLABEL datatype. You can declare columns of the MLSLABEL datatype in standard Oracle, as well as in Trusted Oracle, for compatibility with Trusted Oracle applications.

The MLSLABEL datatype is used to store the binary format of an operating system label. The maximum width of a column declared as MLSLABEL is 255 bytes.

MLSLABEL data is stored as a variable-length tag (two to five bytes, in which the first byte indicates length) that maps to a binary label in the data dictionary. The reason that MLSLABEL data is stored as a representative tag instead of the binary label itself is that binary operating system labels can be very long. Given that a label is stored with every row in the database (in the ROWLABEL column), storing many large labels can consume a lot of space. Storing a representative tag instead of a label is more space efficient.

Any labels that are valid on your operating system can be inserted into an MLSLABEL column. When you insert a label into an MLSLABEL column, Trusted Oracle implicitly converts the data into the binary format of the label.

The following sections further describe this datatype and the ROWLABEL pseudocolumn. If you are using Trusted Oracle, also see the Trusted Oracle7 Server Administrator's Guide for more information.

The ALL_LABELS Data Dictionary View

The ALL_LABELS data dictionary view lists all of the labels ever stored in the database, including the values of DBHIGH and DBLOW. Any label ever stored in an MLSLABEL column (including the ROWLABEL column) is automatically added to this view.

Note that this view does not necessarily contain all labels that are valid in the database, since any valid operating system label, in any valid format, is a valid label within Trusted Oracle. Also note that this view may contain labels that are invalid within the database (if those labels were once used in the database, but are no longer valid).


Oracle automatically appends the ROWLABEL column to each Trusted Oracle table at table creation. This column contains a label of the MLSLABEL datatype for every row in the table.

In OS MAC mode, given that a table can contain rows at one label only, the values in this column are always uniform within a table (and within a single database).

In DBMS MAC mode, the values in this column can range within a single table from DBHIGH to DBLOW (within any constraints defined for that table).

Summary of Oracle Datatype Information

For quick reference, Table 6 - 2 summarizes the important information about each Oracle datatype.

Data Type Description Column Length (bytes)
CHAR (size) Fixed-length character data of length size. Fixed for every row in the table (with trailing blanks); maximum size is 255 bytes per row, default size is one byte per row. Consider the character set that is used before setting size. (Are you using a one-byte or multi-byte character set?)
VARCHAR2 (size) Variable-length character data. A maximum size must be specified. Variable for each row, up to 2000 bytes per row. Consider the character set that is used before setting size. (Are you using a one-byte or multi-byte character set?)
NUMBER (p, s) Variable-length numeric data. Maximum precision p and/or scale s is 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 January 1, 4712 B.C. to December 31, 4712 A. D. Default format: DD-MON-YY. Fixed at seven bytes for each row in the table.
LONG Variable-length character data. Variable for each row in the table, up to 2^31 - 1 bytes, or two gigabytes, per row.
RAW (size) Variable-length raw binary data. A maximum size must be specified. Variable for each row in the table, up to 255 bytes per row.
LONG RAW Variable-length raw binary data. Variable for each row in the table, up to 2^31 - 1 bytes, or two gigabytes, per row.
ROWID Binary data representing row addresses. Fixed at six bytes for each row in the table.
MLSLABEL Variable-length binary data representing operating system labels. Variable for each row in the table, ranging from two to five bytes per row.
Table 6 - 2. Summary of Oracle Datatype Information

ANSI, DB2, and SQL/DS Datatypes

In addition to Oracle datatypes, columns of tables in an Oracle database can be defined using ANSI, DB2, and SQL/DS datatypes. However, Oracle internally converts such datatypes to Oracle datatypes.

ANSI SQL Datatype Oracle Datatype
NUMERIC (p, s), DECIMAL (p, s) DEC (p, a) NUMBER (p, s)
Table 6 - 3. ANSI Datatype Conversions to Oracle Datatypes

DB2 or SQL/DS Datatype Oracle Datatype
DECIMAL (p, s) NUMBER (p, s)
Table 6 - 4. SQL/DS, DB2 Datatype Conversions to Oracle Datatypes

The IBM products SQL/DS and DB2 datatypes TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC have no corresponding Oracle datatype, and cannot be used. The TIME and TIMESTAMP datatypes are subcomponents of the Oracle datatype DATE.

The ANSI datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s defaults to 0.

Data Conversion

In some cases, Oracle supplies data of one datatype where it expects data of a different datatype. This is allowed when Oracle can automatically convert the data to the expected datatype using one of the following functions:

Implicit datatype conversions work according to the rules explained in the following two sections.

Note: If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for additional information involving data conversions and the MLSLABEL and RAW MLSLABEL datatypes.

Rule 1: Assignments

For assignments, Oracle can automatically convert the following:

The assignment succeeds if Oracle can convert the datatype of the value used in the assignment to that of the assignment's target.

Note: For the examples in the following list, assume a package with a public variable and a table declared as in the following statements:

			var1	CHAR(5);  
			CREATE TABLE table1 (col1 NUMBER); 

			VAR1 := 0 

			INSERT INTO table1 VALUES ('19'); 

			UPDATE table1 SET col1 = '30'; 

			SELECT col1 INTO var1 FROM table1 WHERE col1 = 30; 

Rule 2: Expression Evaluation

For expression evaluation, Oracle can automatically convert the following:

Some common types of expressions follow:

In general, Oracle uses the rule for expression evaluation when a datatype conversion is needed in places not covered by the rule for assignment conversions.

In assignments of the form

variable := expression 

Oracle first evaluates expression using the conversions covered by Rule 2; expression can be simple or complex. If it succeeds, expression results in a single value and datatype. Then, Oracle tries to assign this value to the assignment's target using Rule 1.

CHAR to NUMBER conversions only succeed if the character string represents a valid number. CHAR to DATE conversions only succeed if the character string has the default format 'DD-MON-YY'.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index