10
Built-In Datatypes

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 built-in datatypes, their properties, and how they map to non-Oracle datatypes. Topics include:

Oracle Datatypes

You can use the following built-in datatypes in column definitions:

MLSLABEL Datatype

Additional Information:

PL/SQL has additional datatypes, such as BOOLEAN, reference types, composite types (collections and records), and user-defined subtypes. See the PL/SQL User's Guide and Reference for information about PL/SQL datatypes.

Character Datatypes

The character datatypes store character (alphanumeric) data 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 multibyte encoding schemes.

Additional Information:

See Oracle8 Application Developer's Guide for information about how to select a character datatype.

CHAR Datatype

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

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.
If a value is too large, Oracle returns an error.

Oracle compares CHAR values using the blank-padded comparison semantics.

Additional Information:

See Oracle8 SQL Reference for 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 string length (in bytes, not characters) between 1 and 4000 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, in which case 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 stores only the 10 characters (10 bytes), not 50.

Oracle compares VARCHAR2 values using the nonpadded comparison semantics.

Additional Information:

See Oracle8 SQL Reference.

VARCHAR Datatype

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

Column Lengths for Character Datatypes and NLS Character Sets

The Oracle National Language Support (NLS) feature allows the use of various character sets for the character datatypes. National Language Support enables you to process single-byte and multi-byte character data and convert between character sets. Client sessions can use national character sets different from the database character set.

You should consider the size of characters when you specify the column length for character datatypes. You must consider this issue when estimating space for tables with columns that contain character data.

Additional Information:

See Oracle8 Reference and Oracle8 Utilities for more information about the NLS feature of Oracle.

NCHAR and NVARCHAR2 Datatypes

The NCHAR and NVARCHAR2 datatypes store NLS character data. The NCHAR datatype stores fixed-length character strings that correspond to a fixed-length or variable-length national character set. The NVARCHAR2 datatype stores variable-length character strings.

When you create a table with an NCHAR or NVARCHAR2 column, you specify a maximum size that is either the number of characters (for a fixed-length national character set) or the number of bytes (for a variable-length national character set).

The maximum length for an NCHAR column is 2000 bytes, or the number of characters that can be stored in 2000 bytes.
The maximum length for an NVARCHAR2 column is 4000 bytes, or the number of characters that can be stored in 4000 bytes.

Additional Information:

See Oracle8 Reference and PL/SQL User's Guide and Reference for more information about NCHAR and NVARCHAR2 datatypes.

LOB Character Datatypes

The LOB datatypes for character data are CLOB and NCLOB. They can store up to four gigabytes of character data (CLOB) or national character set data (NCLOB). These datatypes are described in "LOB Datatypes" on page 10-9.

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.

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.

Note:

The LONG datatype is provided for backward compatibility with existing applications. In new applications, you should use CLOB and NCLOB datatypes for large amounts of character data.

Additional Information:

The LONG datatype has many restrictions - see Oracle8 Application Developer's Guide.

Also see "RAW and LONG RAW Datatypes" on page 10-11 for information about the LONG RAW datatype.

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:

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)
zero
positive and negative infinity (generated only by importing from an Oracle Version 5 database)

For numeric columns you can specify the column as:

column_name NUMBER

Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the 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 10-1 shows examples of how data would be stored using different scale factors.

Table 10-1 How Scale Factors Affect Numeric Data Storage

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

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 10-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 initialization 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. (The resulting value is limited to 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; 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 in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era). Unless BCE ('BC' in the format mask) is specifically used, CE 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:

'13-NOV-92'

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 gives 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 00: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, use the TO_DATE function 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 by the number of days from a common reference. (The reference is 01-01-4712 years BCE, so current dates are somewhere in the 2.4 million range.) A Julian date is nominally a noninteger, 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.

Note:

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

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).

Centuries and the Year 2000

Oracle stores year data with the century information. For example, the Oracle database stores 1996 or 2001, and not just 96 or 01. The DATE datatype always stores a four-digit year internally, and all other dates stored internally in the database have four digit years. Oracle utilities such as import, export, and recovery also deal properly with four-digit years.

However, some applications might be written with an assumption about the year (such as assuming that everything is 19xx). Application programmers should therefore review and test their code with regard to the year 2000.

Additional Information:

For more information about centuries and date format masks, see Oracle8 Application Developer's Guide. For general information about date format codes, see Oracle8 SQL Reference.

LOB Datatypes

The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) up to four gigabytes in size. They provide efficient, random, piece-wise access to the data.

LOB datatypes differ from LONG and LONG RAW datatypes in several ways. For example, LOB datatypes (except NCLOB) can be attributes of a user-defined object type but LONG datatypes cannot. The maximum size of a LOB is four gigabytes, but the maximum size of a LONG is two gigabytes. LOBs support random access to data, but LONGs support only sequential access.

LOB datatypes can be stored inline (within a table), out-of-line (within a tablespace, using a LOB locator), or in an external file (BFILE datatypes).

Additional Information:

See Oracle8 Application Developer's Guide for more information about LOB storage and LOB locators.

You can use SQL statements to define LOB columns in a table and LOB attributes in a user-defined object type. When defining LOBs in a table, you can explicitly specify the tablespace and storage characteristics for each LOB.

See "Default Logging Mode" on page 21-7 for information about the LOB attribute LOGGING or NOLOGGING.

BLOB Datatype

The BLOB datatype stores unstructured binary data in the database. BLOBs can store up to four gigabytes of binary data.

BLOBs participate fully in transactions. Changes made to a BLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, BLOB locators cannot span transactions or sessions.

CLOB and NCLOB Datatypes

The CLOB and NCLOB datatypes store up to four gigabytes of character data in the database. CLOBs store single-byte character set data and NCLOBs store fixed-length multibyte national character set data (NCHAR data).

CLOBs and NCLOBs participate fully in transactions. Changes made to a CLOB or NCLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, CLOB and NCLOB locators cannot span transactions or sessions.

You cannot create an object type with NCLOB attributes, but you can specify NCLOB parameters in a method for an object type.

BFILE Datatype

The BFILE datatype stores unstructured binary data in operating-system files outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. BFILEs can store up to four gigabytes of data.

BFILEs are read-only; you cannot modify them. They support only random (not sequential) reads, and they do not participate in transactions. The underlying operating system must maintain the file integrity and durability for BFILEs. The database administrator must ensure that the file exists and that Oracle processes have operating-system read permissions on the file.

RAW and LONG RAW Datatypes

Note:

The RAW and LONG RAW datatypes are provided for backward compatibility with existing applications. For new applications, you should use the BLOB and BFILE datatypes for large amounts of binary data.

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 Net8 (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, Net8 and Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the database character set and 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.

Additional Information:

See Oracle8 Application Developer's Guide for information about additional restrictions on the LONG RAW datatype.

ROWID Datatype

Oracle uses an extended ROWID datatype to store the address of every row in the database. The extended ROWID efficiently identifies rows in partitioned tables and indexes as well as nonpartitioned tables and indexes. It supports tablespace-releative data block addresses. A restricted ROWID datatype is also available for backward compatibility with existing applications.

ROWIDs and the ROWID Datatype

Every row in a nonclustered table 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, for example:

SELECT ROWID, ename FROM emp;

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, and you cannot delete a ROWID value. Oracle uses the ROWIDs in the pseudocolumn ROWID internally for various operations as described in "How ROWIDs Are Used" on page 10-16.

You can reference ROWIDs in the pseudocolumn ROWID like other table columns (used in SELECT lists and WHERE clauses), but ROWIDs are not stored in the database, nor are they database data. However, you can create tables that contain columns having the ROWID datatype, although Oracle does not guarantee that the values of such columns are valid ROWIDs.

Extended ROWIDs

Extended ROWIDs use a base 64 encoding of the physical address for each row selected. For example, the following query

SELECT ROWID, ename FROM emp 
    WHERE deptno = 20;

might return the following row information:

ROWID              ENAME 
------------------ ---------- 
AAAAaoAATAAABrXAAA BORTINS 
AAAAaoAATAAABrXAAE RUGGLES 
AAAAaoAATAAABrXAAG CHEN 
AAAAaoAATAAABrXAAN BLUMBERG

An extended ROWID has a four-piece format, OOOOOOFFFBBBBBBRRR:

OOOOOO: The data object number identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.
FFF: The datafile that contains the row (file AAT in the example). File numbers are unique within a database.
BBBBBB: The data block that contains the row (block AAABrX 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.
RRR: The row in the block.

You can retrieve the data object number from data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. For example, the following query returns the data object number for the EMP table in the SCOTT schema:

SELECT DATA_OBJECT_ID FROM DBA_OBJECTS 
    WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMP';

You can also use the DBMS_ROWID package to extract information from an extended ROWID or to convert a ROWID from extended format to restricted format (or vice versa).

Additional Information:

See the Oracle8 Application Developer's Guide for information about the DBMS_ROWID package.

Restricted ROWIDs

Restricted ROWIDs use a binary representation of the physical address for each row selected. When queried using SQL*Plus, the binary representation is converted to a VARCHAR2/hexadecimal representation. The following query

SELECT ROWID, ename FROM emp 
    WHERE deptno = 30;

might return the following row information:

ROWID              ENAME 
------------------ ---------- 
00000DD5.0000.0001 KRISHNAN 
00000DD5.0001.0001 ARBUCKLE 
00000DD5.0002.0001 NGUYEN

As shown above, a restricted 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.
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.

Examples of Using ROWIDs

You can use the function SUBSTR to break the data in a ROWID into its components. For example, you can use SUBSTR to break an extended ROWID into its four components (database object, file, block, and row):

SELECT ROWID, 
       SUBSTR(ROWID,1,6) "OBJECT", 
       SUBSTR(ROWID,7,3) "FIL", 
       SUBSTR(ROWID,10,6) "BLOCK", 
       SUBSTR(ROWID,16,3) "ROW" 
       FROM products; 

ROWID               OBJECT  FIL  BLOCK   ROW 
------------------  ------  ---  ------  ---- 
AAAA8mAALAAAAQkAAA  AAAA8m  AAL  AAAAQk  AAA 
AAAA8mAALAAAAQkAAF  AAAA8m  AAL  AAAAQk  AAF 
AAAA8mAALAAAAQkAAI  AAAA8m  AAL  AAAAQk  AAI

Or you can use SUBSTR to break a restricted ROWID into its three components (block, row, and file):

SELECT ROWID, SUBSTR(ROWID,15,4) "FILE", 
       SUBSTR(ROWID,1,8) "BLOCK", 
       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 of an extended ROWID tells how many datafiles contain rows of a given table:

SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3))) "FILES" FROM tablename; 

  FILES 
-------- 
    2

Additional Information:

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

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 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 nonstandard translation to hexadecimal format of length up to 256 bytes.

Additional Information:

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 functions:

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.

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.

MLSLABEL Datatype

Trusted Oracle provides the MLSLABEL datatype, which stores Trusted Oracle's internal representation of labels generated by multilevel secure operating systems. Trusted Oracle uses labels to control database access.

You can define a column using the MLSLABEL datatype in Oracle8 for compatibility with Trusted Oracle applications, but the only valid value for the column in Oracle8 is NULL.

When you create a table in Trusted Oracle, a column called ROWLABEL is automatically appended to the table. This column contains a label of the MLSLABEL datatype for every row in the table.

Additional Information:

See your Trusted Oracle documentation for more information about the MLSLABEL datatype, the ROWLABEL column, and Trusted Oracle.

Summary of Oracle Datatype Information

Table 10-2 summarizes the important information about each Oracle datatype.

Table 10-2 Summary of Oracle Built-In Datatypes

Datatype	Description	Column Length and Default
CHAR (size)	Fixed-length character data of length size bytes.	Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row. Consider the character set (one-byte or multibyte) before setting size.
VARCHAR2 (size)	Variable-length character data. A maximum size must be specified.	Variable for each row, up to 4000 bytes per row. Consider the character set (one-byte or multibyte) before setting size.
NCHAR(size)	Fixed-length character data of length size characters or bytes, depending on the national character set.	Fixed for every row in the table (with trailing blanks). Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 2000 bytes per row. Default is 1 character or 1 byte, depending on the character set.
NVARCHAR2 (size)	Variable-length character data of length size characters or bytes, depending on national character set. A maximum size must be specified.	Variable for each row. Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 4000 bytes per row. Default is 1 character or 1 byte, depending on the character set.
LONG	Variable-length character data.	Variable for each row in the table, up to 2^31 - 1 bytes, or 2 gigabytes, per row.
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 BCE to December 31, 4712 CE ("A.D.")	Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY) specified by NLS_DATE_FORMAT parameter.
RAW (size)	Variable-length raw binary data. A maximum size must be specified.	Variable for each row in the table, up to 2000 bytes per row.
LONG RAW	Variable-length raw binary data.	Variable for each row in the table, up to 2^31 - 1 bytes, or 2 gigabytes, per row.
BLOB	Binary data.	Up to 2^32 - 1 bytes, or 4 gigabytes.
CLOB	Single-byte character data.	Up to 2^32 - 1 bytes, or 4 gigabytes.
NCLOB	Single-byte or fixed-length multibyte national character set (NCHAR) data.	Up to 2^32 - 1 bytes, or 4 gigabytes.
BFILE	Binary data stored in an external file.	Up to 2^32 - 1 bytes, or 4 gigabytes.
ROWID	Binary data representing row addresses.	Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.
MLSLABEL	Trusted Oracle datatype.	See your Trusted Oracle documentation.

ANSI, DB2, and SQL/DS Datatypes

The ANSI datatype conversions to Oracle datatypes are shown in Table 10-3. The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s (scale) defaults to 0.

Table 10-3 ANSI Datatype Conversions to Oracle Datatypes

ANSI SQL Datatype	Oracle Datatype
CHARACTER (n), CHAR (n)	CHAR (n)
NUMERIC (p,s), DECIMAL (p,s), DEC (p,s)	NUMBER (p,s)
INTEGER, INT, SMALLINT	NUMBER (38)
FLOAT (p)	FLOAT (p)
REAL	FLOAT (63)
DOUBLE PRECISION	FLOAT (126)
CHARACTER VARYING(n), CHAR VARYING(n)	VARCHAR2 (n)

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.

Table 10-4 shows the DB2 and SQL/DS conversions.

Table 10-4 SQL/DS, DB2 Datatype Conversions to Oracle Datatypes

DB2 or SQL/DS Datatype	Oracle Datatype
CHARACTER (n)	CHAR (n)
VARCHAR (n)	VARCHAR2 (n)
LONG VARCHAR	LONG
DECIMAL (p,s)	NUMBER (p,s)
INTEGER, SMALLINT	NUMBER (38)
FLOAT (p)	FLOAT (p)
DATE	DATE

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:

TO_NUMBER()
TO_CHAR()
TO_DATE()
CHARTOROWID()
ROWIDTOCHAR()
HEXTORAW()

RAWTOHEX()

Additional Information:

The rules for implicit datatype conversions are explained in the Oracle8 Application Developer's Guide.

If you are using Trusted Oracle, see your Trusted Oracle documentation for additional information involving data conversions and the MLSLABEL datatype.

10Built-In Datatypes

Oracle Datatypes

Character Datatypes

CHAR Datatype

VARCHAR2 Datatype

VARCHAR Datatype

Column Lengths for Character Datatypes and NLS Character Sets

NCHAR and NVARCHAR2 Datatypes

LOB Character Datatypes

LONG Datatype

NUMBER Datatype

Table 10-1 How Scale Factors Affect Numeric Data Storage

Internal Numeric Format

DATE Datatype

Using Julian Dates

Date Arithmetic

Centuries and the Year 2000

LOB Datatypes

BLOB Datatype

CLOB and NCLOB Datatypes

BFILE Datatype

RAW and LONG RAW Datatypes

ROWID Datatype

ROWIDs and the ROWID Datatype

Extended ROWIDs

Restricted ROWIDs

Examples of Using ROWIDs

ROWIDs and Non-Oracle Databases

How ROWIDs Are Used

MLSLABEL Datatype

Summary of Oracle Datatype Information

Table 10-2 Summary of Oracle Built-In Datatypes

ANSI, DB2, and SQL/DS Datatypes

Table 10-3 ANSI Datatype Conversions to Oracle Datatypes

Table 10-4 SQL/DS, DB2 Datatype Conversions to Oracle Datatypes

Data Conversion

10
Built-In Datatypes