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:
The following sections describe the Oracle datatypes that you can use in column definitions:
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.
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:
- When you insert or update a row in the table, the value for the CHAR column has the fixed length.
- If you give a shorter value, the value is blank-padded to the fixed length.
- If you give a longer value with trailing blanks, blanks are trimmed from the value to the fixed length.
Oracle compares CHAR values using the blank-padded comparison semantics. See Oracle7 Server SQL Reference for more information on comparison semantics.
- If a value is too large, Oracle returns an error.
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.
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:
- Comparison Semantics
Use the CHAR datatype when you require ANSI compatibility in comparison semantics, that is, when trailing blanks are not important in string comparisons. Use the VARCHAR2 when trailing blanks are important in string comparisons.
- Space Usage
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 blank-pad or store trailing blanks for column values.
- Future Compatibility
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.
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.
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:
- positive numbers in the range 1 x 10^-130 to 9.99..9 x 10^125 (with up to 38 significant digits)
- negative numbers from -1 x 10^-130 to 9.99..99 x 10^125 (with up to 38 significant digits)
For numeric columns you can specify the column as follows:
- positive and negative infinity (generated only in import from a Version 5 database)
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.
Table 6 - 1. How Scale Factors Affect Numeric Data Storage
||(not accepted, exceeds precision)
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.
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'));
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).
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:
- 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, 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 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.
- Variables in database triggers cannot be declared using the LONG or LONG RAW datatypes.
- References to :NEW and :OLD in database triggers cannot be used with LONG or LONG RAW columns.
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:
As shown above, a ROWID's VARCHAR2/hexadecimal representation is in a three-piece format: 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.
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.
- The datafile that contains the row (file 1 in the example). The first datafile of every database is always 1, and file numbers are unique within a database.
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:
- ROWIDs are the fastest means of accessing particular rows.
- ROWIDs can be used to see how a table is organized.
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.
- 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 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:
SELECT ROWID, SUBSTR(ROWID,15,4) "FILE",
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:
SELECT COUNT(DISTINCT(SUBSTR(ROWID,15,4))) "FILES" FROM tablename;
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).
The ROWLABEL Column
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.
Table 6 - 2. Summary of Oracle Datatype Information
||Column Length (bytes)
||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?)
||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.
||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.
||Variable-length character data.
||Variable for each row in the table, up to
2^31 - 1 bytes, or two gigabytes, per row.
||Variable-length raw binary data. A maximum size must be specified.
||Variable for each row in the table, up to 255 bytes per row.
||Variable-length raw binary data.
||Variable for each row in the table, up to
2^31 - 1 bytes, or two gigabytes, per row.
||Binary data representing row addresses.
||Fixed at six bytes for each row in the table.
||Variable-length binary data representing operating system labels.
||Variable for each row in the table, ranging from two to five bytes per row.
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.
Table 6 - 3. ANSI Datatype Conversions to Oracle Datatypes
|ANSI SQL Datatype
|CHARACTER (n), CHAR(n)
|NUMERIC (p, s), DECIMAL (p, s) DEC (p, a)
||NUMBER (p, s)
|INTEGER, INT, SMALLINT
|FLOAT (p), REAL, DOUBLE PRECISION
|CHARACTER VARYING(n), CHAR VARYING(n)
Table 6 - 4. SQL/DS, DB2 Datatype Conversions to Oracle Datatypes
|DB2 or SQL/DS Datatype
|DECIMAL (p, s)
||NUMBER (p, s)
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.
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:
- VARCHAR2 or CHAR to NUMBER
- VARCHAR2 or CHAR to ROWID
The assignment succeeds if Oracle can convert the datatype of the value used in the assignment to that of the assignment's target.
- VARCHAR2 or CHAR to LABEL
Note: For the examples in the following list, assume a package with a public variable and a table declared as in the following statements:
CREATE TABLE table1 (col1 NUMBER);
The datatype of expression must be either be the same as or convertible to the datatype of variable. For example, Oracle automatically converts the data provided in the following assignment within the body of a stored procedure:
VAR1 := 0
- INSERT INTO table VALUES (expression1, expression2, ...)
The datatypes of expression1, expression2, and so on, must either be the same as or convertible to the datatypes of the corresponding columns in table. For example, Oracle automatically converts the data provided in the following INSERT statement for TABLE1 (see table definition above):
INSERT INTO table1 VALUES ('19');
- UPDATE table SET column = expression
The datatype of expression must either be the same as or convertible to the datatype of column. For example, Oracle automatically converts the data provided in the following UPDATE statement issued against TABLE1:
UPDATE table1 SET col1 = '30';
- SELECT column INTO variable FROM table
The datatype of column must either be the same as or convertible to the datatype of variable. For example, Oracle automatically converts data selected from the table before assigning it to the variable in the following statement:
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:
- VARCHAR2 or CHAR to NUMBER
- Simple expressions, such as the following:
- Boolean expressions, such as the following:
- Function and procedure calls, such as the following:
- WHERE clause conditions, such as the following:
WHERE hiredate = '01-JAN-91'
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'.