Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 5
Selecting a Datatype

This chapter discusses how to use Oracle built-in datatypes in applications. Topics include:

- Oracle Built-In Datatypes
- ROWIDs and the ROWID Datatype
- Trusted Oracle MLSLABEL Datatype
- ANSI/ISO, DB2, and SQL/DS Datatypes
- Data Conversion

  See Also: For information about user-defined datatypes, refer to Oracle8 Concepts and to Chapter 7, "User-Defined Datatypes - An Extended Example" in this manual.

## Oracle Built-In Datatypes

A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle to treat values of one datatype differently from values of another datatype; for example, Oracle can add values of `NUMBER` datatype but not values of `RAW` datatype.

Oracle supplies the following built-in datatypes:

- character datatypes
  - `CHAR`
  - `NCHAR`
  - `VARCHAR2 and VARCHAR`
  - `NVARCHAR2`
  - `CLOB`
  - `NCLOB`
  - `LONG`
- `NUMBER` datatype
- `DATE` datatype
- binary datatypes
  - `BLOB`
  - `BFILE`
  - `RAW`
  - `LONG RAW`

Another datatype, `ROWID`, is used for values in the `ROWID` pseudocolumn, which represents the unique address of each row in a table.

See Also: Figure 5-2 summarizes the information about each Oracle built-in datatype. See Oracle8 Concepts for general descriptions of these datatypes, and see Chapter 6, "Large Objects (LOBs)" in this Guide for information about the `LOB` datatypes.

##### Table 5-1 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. | Variable for each row, up to 4000 bytes per row. Consider the character set (one-byte or multibyte) before setting size. A maximum size must be specified.
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.
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.
`LONG` | Variable-length character data. | Variable for each row in the table, up to 2^31 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
`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 Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E. | 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.
`BLOB` | Unstructured binary 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.
`RAW (size)` | Variable-length raw binary data. | Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.
`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. Provided for backward compatibility.
`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 the Trusted Oracle documentation.

### Using Character Datatypes

Use the character datatypes to store alphanumeric data.

- `CHAR` and `NCHAR` datatypes store fixed-length character strings.
- `VARCHAR2` and `NVARCHAR2` datatypes store variable-length character strings. (The `VARCHAR` datatype is synonymous with the `VARCHAR2` datatype.)
- `CLOB` and `NCLOB` datatypes store single-byte and multibyte character strings of up to four gigabytes.

  See Also: Chapter 6, "Large Objects (LOBs)")

- The `LONG` datatype stores variable-length character strings containing up to two gigabytes, but with many restrictions.

  See Also: "Restrictions on LONG and LONG RAW Data" on page 5-10

  This datatype is provided for backward compatibility with existing applications; in general, new applications should use `CLOB` and `NCLOB` datatypes to store large amounts of character data.

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

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

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

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

`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_LANGUAGE` parameter, where these are different.

#### Column Lengths for Single-Byte and Multibyte Character Sets

The lengths of `CHAR` and `VARCHAR2` columns are specified in bytes rather than characters, and are constrained as such. The lengths of `NCHAR` and `NVARCHAR2` columns are specified either in bytes or in characters, depending on the national character set being used.

When using a multibyte database character encoding scheme, consider carefully the space required for tables with character columns. If the database character encoding scheme is single-byte, the number of bytes and the number of characters in a column is the same. If it is multibyte, there generally is no such correspondence. A character might consist of one or more bytes depending upon the specific multibyte encoding scheme, and whether shift-in/shift-out control codes are present.

See Also: Oracle8 Reference for information about National Language Support features of Oracle and support for different character encoding schemes.

#### Comparison Semantics

Oracle compares `CHAR` and `NCHAR` values using blank-padded comparison semantics. If two values have different lengths, Oracle adds blanks at the end of the shorter value, until the two values are the same length. Oracle then compares the values character-by-character up to the first character that differs. The value with the greater character in the first differing position is considered greater. Two values that differ only in the number of trailing blanks are considered equal.

Oracle compares `VARCHAR2` and `NVARCHAR2` values using non-padded comparison semantics. Two values are considered equal only if they have the same characters and are of equal length. Oracle compares the values character-by-character up to the first character that differs. The value with the greater character in that position is considered greater.

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.

See Also: For more information on comparison semantics for these datatypes, see the Oracle8 SQL Reference.

### Using the NUMBER Datatype

Use the `NUMBER` datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10^-130 to 9.99...x10^125, as well as zero, in a `NUMBER` column.

For numeric columns you can specify the column as a floating-point number:

    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)

Although not required, specifying the precision and scale for numeric fields provides extra integrity checking on input. If a precision is not specified, the column stores values as given. Table 5-2 shows examples of how data would be stored using different scale factors.

##### Table 5-2 How Scale Factors Affect Numeric Data Storage

Input Data | Stored As | Specified 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

See Also: For information about the internal format for the `NUMBER` datatype, see Oracle8 Concepts.

### Using the DATE Datatype

Use the `DATE` datatype to store point-in-time values (dates and times) in a table. The `DATE` datatype stores the century, year, month, day, hours, minutes, and seconds.

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 the Oracle Call Interface Programmer's Guide for a complete description of the Oracle internal date format.

#### 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')

Note: Oracle Julian dates might not be compatible with Julian dates generated by other date algorithms. For information about Julian dates, see Oracle8 Concepts.

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 Format

Time is stored in 24-hour format#`HH:MM: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, 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.

#### 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). The application might hand over a two-digit year to the database, and the procedures that Oracle uses for determining the century could be different from what the programmer expects. Application programmers should therefore review and test their code with regard to the year 2000.

The RR date format element of the `TO_DATE` and `TO_CHAR` functions allows a database site to default the century to different values depending on the two-digit year, so that years 50 to 99 default to 19xx and years 00 to 49 default to 20xx. This can help applications make the conversion to the new century easily.

The CC date format element of the `TO_CHAR` function sets the century value to one greater than the first two digits of a four-digit year (for example, '20' from '1900'). For years that are a multiple of 100, this is not the true century. Strictly speaking, the century of '1900' is not the twentieth century (which began in 1901) but rather the nineteenth century.

The following workaround computes the correct century for any Common Era (CE, formerly known as AD) date. If userdate is a CE date for which you want the true century, use the expression:

    DECODE (TO_CHAR (userdate, 'YY'),

    '00', TO_CHAR (userdate - 366, 'CC'),
    TO_CHAR (userdate, 'CC'))

This expression works as follows: Get the last two digits of the year. If it is '00', then it is a year in which the Oracle century is one year too large so compute a date in the preceding year (whose Oracle century is the desired true century). Otherwise, use the Oracle century.

See Also: For more information about date format codes, see Oracle8 SQL Reference.

### Using the LONG Datatype

Note: The `LONG` datatype is provided for backward compatibility with existing applications. For new applications, you should use the `CLOB` and `NCLOB` datatypes for large amounts of character data. See Chapter 6, "Large Objects (LOBs)" for information about the `CLOB` and `NCLOB` datatypes.

The LONG datatype can store variable-length character data containing up to two gigabytes of information. The length of `LONG` values might be limited by the memory available on your computer.

You can use columns defined as `LONG` in `SELECT` lists, `SET` clauses of `UPDATE` statements, and `VALUES` clauses of `INSERT` statements. `LONG` columns have many of the characteristics of `VARCHAR2` columns.

#### Restrictions on LONG and LONG RAW Data

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

- 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 (C`REATE 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.
- `LONG` and `LONG` `RAW` columns cannot be used in distributed SQL statements.
- `LONG` and `LONG` `RAW` columns cannot be replicated.

  Note: If you design tables containing `LONG` or `LONG` `RAW` data, you should place each `LONG` or `LONG` `RAW` column in a table separate from any other data associated with it, rather than storing the `LONG` or `LONG` `RAW` column and its associated data together in the same table. You can then relate the two tables with a referential integrity constraint. This design allows SQL statements that access only the associated data to avoid reading through `LONG` or `LONG` `RAW` data.

##### Example of LONG Datatype

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

### Using 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. See Chapter 6, "Large Objects (LOBs)" for information about the `BLOB` and `BFILE` datatypes.

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

Net8 and the Export and Import utilities do not perform character conversion when transmitting `RAW` or `LONG` `RAW` data. 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. For more information about restrictions on `LONG` `RAW` data, see "Restrictions on LONG and LONG RAW Data" on page 5-10.

### ROWIDs and the ROWID Datatype

Every row in a nonclustered table of an Oracle database is assigned a unique `ROWID` that corresponds to the physical address of a row's row piece (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`.

See Also: Oracle8 Concepts

for general information about the `ROWID` pseudocolumn and the `ROWID` datatype.

#### Extended ROWID Format

The Oracle Server uses an extended `ROWID` format, which supports features such as table partitions, index partitions, and clusters.

The extended `ROWID` includes the following information:

- data object (segment) identifier
- datafile identifier
- block identifier
- row identifier

The data object identifier is an identification number that Oracle assigns to schema objects in the database, such as nonpartitioned tables or partitions. For example, the query

    SELECT DATA_OBJECT_ID FROM DBA_OBJECTS

    WHERE OWNER = 'SCOTT' AND OBJECT_NAME = 'EMP';

returns the data object identifier for the `EMP` table in the `SCOTT` schema. "The DBMS_ROWID Package" on page 10-81 describes other ways to get the data object identifier, using the `DBMS_ROWID` package functions.

#### Different Forms of the ROWID

Oracle documentation uses the term `ROWID` in different ways, depending on context. These uses are explained in this section.

##### Internal ROWID

The internal `ROWID` format is an internal structure which holds information that the server code needs to access a row. The restricted internal ROWID is 6 bytes on most platforms; the extended `ROWID` is 10 bytes on these platforms.

##### ROWID Pseudocolumn

Each table and nonjoined view has a pseudocolumn called ROWID. Statements such as

    CREATE TABLE T1 (col1 ROWID);

    INSERT INTO T1 SELECT ROWID FROM EMP WHERE empno = 7499;

return the ROWID pseudocolumn of the row of the EMP table that satisfies the query, and insert it into the T1 table.

##### External Character ROWID

The extended `ROWID` pseudocolumn is returned to the client in the form of an 18-character string (for example, "AAAA8mAALAAAAQkAAA"), which represents a base 64 encoding of the components of the extended `ROWID` in a four-piece format, OOOOOOFFFBBBBBBRRR:

- OOOOOO: The data object number identifies the database segment (AAAA8m 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 AAL in the example). File numbers are unique within a database.
- BBBBBB: The data block that contains the row (block AAAAQk 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 (row AAA in the example).

There is no need to decode the external `ROWID`; you can use the functions in the `DBMS_ROWID` package to obtain the individual components of the extended ROWID.

See Also: "The DBMS_ROWID Package" on page 10-81.

.

The restricted `ROWID` pseudocolumn is returned to the client in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the `ROWID`.

##### External Binary ROWID

Some client applications use a binary form of the `ROWID`. For example, OCI and some precompiler applications can map the `ROWID` to a 3GL structure on bind or define calls. The size of the binary `ROWID` is the same for extended and restricted`ROWID`s. The information for the extended `ROWID` is included in an unused field of the restricted `ROWID` structure.

The format of the extended binary `ROWID`, expressed as a C struct, is:

    struct riddef {

    ub4    ridobjnum; /* data obj#--this field is
                         unused in restricted ROWIDs */
    ub2    ridfilenum;
    ub1    filler;
    ub4    ridblocknum;
    ub2    ridslotnum;

    }

#### ROWID Migration and Compatibility Issues

For backward compatibility, the restricted form of the ROWID is still supported. These ROWIDs exist in massive amounts of Oracle7 data, and the extended form of the `ROWID` is required only in global indexes on partitioned tables. New tables always get extended `ROWID`s.

See Also: Oracle8 Administrator's Guide.

It is possible for an Oracle7 client to access an Oracle8 database. Similarly, an Oracle8 client can access an Oracle7 Server. A client in this sense can include a remote database accessing a server using database links, as well as a client 3GL or 4GL application accessing a server.

See Also: FThe description of "ROWID_TO_EXTENDED Function" on page 10-88 has more information, as has Oracle8 Migration.

##### Accessing an Oracle7 Database from an Oracle8 Client

The `ROWID` values that are returned are always restricted ROWIDs. Also, Oracle8 uses restricted ROWIDs when returning a `ROWID` value to an Oracle7 or earlier server.

The following `ROWID` functionality works when accessing an Oracle7 Server:

- selecting a `ROWID` and using the obtained value in a `WHERE` clause
- `WHERE CURRENT OF` cursor operations
- storing `ROWID`s in user columns of `ROWID` or `CHAR` type
- interpreting `ROWID`s using the hexadecimal encoding (not recommended, use the `DBMS_ROWID` functions)

##### Accessing an Oracle8 Database from an Oracle7 Client

Oracle8 returns `ROWID`s in the extended format. This means that you can only:

- select a `ROWID` and use it in a `WHERE` clause
- use `WHERE CURRENT OF` cursor operations
- store `ROWID`s in user columns of `CHAR(18)` datatype

##### Import and Export

It is not possible for an Oracle7 client to import an Oracle8 table that has a `ROWID` column (not the `ROWID` pseudocolumn), if any row of the table contains an extended `ROWID` value.

## Trusted Oracle MLSLABEL Datatype

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

You can define a column using the `MLSLABEL` datatype 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.

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

## ANSI/ISO, DB2, and SQL/DS Datatypes

You can define columns of tables in an Oracle database using ANSI/ISO, DB2, and SQL/DS datatypes. Oracle internally converts such datatypes to Oracle datatypes.

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

##### Table 5-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 5-4 shows the DB2 and SQL/DS conversions.

##### Table 5-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 allows data of one datatype where it expects data of a different datatype. Generally, an expression cannot contain values with different datatypes. However, Oracle can use the following functions to automatically convert data to the expected datatype:

- `TO_NUMBER()`
- `TO_CHAR()`
- `TO_DATE()`
- `HEXTORAW()`
- `RAWTOHEX()`
- `ROWIDTOCHAR()`
- `CHARTOROWID()`

Implicit datatype conversions work according to the rules explained below.

See Also: If you are using Trusted Oracle, see "Data Conversion for Trusted Oracle" on page 5-22 for information about data conversions and the `MLSLABEL` datatype.

I

### Rule 1: Assignments

For assignments, Oracle can automatically convert the following:

- `VARCHAR2` or `CHAR` to `NUMBER`
- `NUMBER` to `VARCHAR2`
- `VARCHAR2` or `CHAR` to `DATE`
- `DATE` to `VARCHAR2`
- `VARCHAR2` or `CHAR` to `ROWID`
- `ROWID` to `VARCHAR2`
- `VARCHAR2` or `CHAR` to `MLSLABEL`
- `MLSLABEL` to `VARCHAR2`
- `VARCHAR2` or `CHAR` to `HEX`
- `HEX` to `VARCHAR2`

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

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

- `variable := expression`

  The datatype of expression must be either 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 be either 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 be either 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 be either 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 perform the same conversions as for assignments. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to NUMBER and operands to string functions are converted to VARCHAR2.

Oracle can automatically convert the following:

- `VARCHAR2` or `CHAR` to `NUMBER`
- `VARCHAR2` or `CHAR` to `DATE`

Character to NUMBER conversions succeed only if the character string represents a valid number. Character to DATE conversions succeed only if the character string satisfies the session default format, which is specified by the initialization parameter NLS_DATE_FORMAT.

Some common types of expressions follow:

- Simple expressions, such as

      comm + '500'

- Boolean expressions, such as

      bonus > sal / '10'

- Function and procedure calls, such as

      MOD (counter, '2')

- `WHERE` clause conditions, such as

      WHERE hiredate = TO_DATE('1997-01-01','yyyy-mm-dd')

- `WHERE` clause conditions, such as

      WHERE rowid = 'AAAAaoAATAAAADAAA'

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 as simple or complex as desired. If it succeeds, the evaluation of expression results in a single value and datatype. Then, Oracle tries to assign this value to the assignment's target using Rule 1.

### Data Conversion for Trusted Oracle

In Trusted Oracle, labels are stored internally as compact binary structures. Trusted Oracle provides the `TO_LABEL` function that enables you to convert a label from its internal binary format to an external character format. To convert a label from character format to binary format in Trusted Oracle, you use the `TO_CHAR` function.

The `TO_LABEL` function is provided for compatibility with Trusted Oracle applications. It returns the `NULL` value in Oracle8.

See Also: The Trusted Oracle documentation has more information about using the `TO_LABEL` and `TO_CHAR` functions to convert label formats.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index