Skip Headers

Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# E Oracle Database Lite Datatypes

Oracle Lite supports the datatypes listed in Table E-1:

Table E-1 Datatypes Supported by Oracle Database Lite

Datatype | Description
--- | ---
BIGINT | An integer datatype with a precision of 19 decimal digits.
BINARY | Enables storage of binary data up to 4,096 bytes.
BIT | Enables your application to store a bit unconstrained by character semantics.
BLOB | A binary large object. Maximum size is 2 gigabytes.
CHAR | Fixed length character data of length size bytes. Maximum size is 4,096 bytes. Default and minimum size is 1 byte.
CLOB | A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 2 gigabytes.
DATE | Valid date range from January 1, 4712 BC to December 31, 4712 AD.
DECIMAL | A number that can be measured in terms of precision (decimal value) or scale (fractional value). You can measure precision by using DECIMAL (p). You can measure scale by using NUMERIC (p, s). Precisions larger than the one you specify are acceptable, but smaller ones are not.
DOUBLE PRECISION | Contains a precision defined during implementation which must be greater than the precision of REAL.
FLOAT | Enables you to specify the precision. The resulting precision must be at least as large as the precision you request. You can specify a precision of some value by typing FLOAT (p). For example, a portable application, may use a single precision on one platform and double precision on another.
INTEGER | An integer value whose precision (the number of decimal values or bits that can be stored) is defined upon implementation.
LONG | Character data of variable length up to 2 gigabytes, or 231 -1 bytes.
LONG RAW | Raw binary data of variable length up to 2 gigabytes.
LONG VARBINARY | Stores but does not interpret up to 2 gigabytes of variable binary data.
LONG VARCHAR | Variable-length character string having maximum length size bytes. Maximum size is 2 gigabytes, and minimum is 1. You must specify size for a VARCHAR2.
NUMBER | Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. A number with no precision now maps to DOUBLE PRECISION in Oracle compatibility mode.
NUMERIC | A number that can be measured in terms of precision (decimal value) or scale (fractional value). You can measure precision by using DECIMIAL (p). You can measure scale by using NUMERIC (p, s). The scale cannot be negative and cannot be larger than the number itself.
RAW | Raw binary data of length size bytes. Maximum size is 4,096 bytes. You must specify size for a RAW value.
REAL | Enables you to request a single-precision floating point with no options. The precision is chosen by the implementation and is normally the default single-precision datatype on the hardware platform.
ROWID | A 16-byte hexadecimal string representing the unique address of a row in its table. ROWID is primarily for values returned by the ROWID pseudocolumn.
SMALLINT | An integer value whose precision is defined upon implementation but whose value is no greater than the implementation of INTEGER.
TIME | Stores a time value in terms of hours minutes and seconds. Hours are represented by two digits ranging from 00 through 23. Minutes are also represented by two digits ranging from 00 through 59. The seconds value ranges from 00 through 60.
TIMESTAMP | Stores the year, month, and day values of a date and the hour, minute, second value of time. TIMESTAMP length and restrictions correspond to DATE and TIME values, except that in TIME the default is 0 and in TIMESTAMP it is 6.
TINYINT | An integer with a precision of 1 byte (-128 to +127).
VARBINARY | Stores but does not interpret variable binary data.
VARCHAR | See VARCHAR2
VARCHAR2 | Variable-length character string with a maximum length size of 4,096 bytes (minimum is 1). You must specify size for a VARCHAR2.

## E.1 BIGINT

[ODBC]

Big integer type. Binds with SQL_C_CHAR or SQL_C_BINARY variables.

Syntax

    BIGINT

Usage Notes

A BIGINT is an exact numeric value with precision 19 and scale 0, typically 8 bytes. `-10^19 < n < 10^19`, where n is the value of a BIGINT.

Example

    BIGINT

## E.2 BINARY

[ODBC]

Variable length binary datatype. Binds with a SQL_C_CHAR or SQL_C_BINARY array.

Syntax

    BINARY [( <precision> )]

Keywords and Parameters

<precision> is the maximum number of bytes.

Usage Notes

BINARY is synonymous with VARBINARY and RAW.

Example

    BINARY(1024)

## E.3 BIT

Bit datatype.

Syntax

    BIT

Usage Notes

Precision is 1.

Example

    BIT

## E.4 BLOB

The BLOB datatype can store large and unstructured data such as text, image, video, and spatial data up to 2 gigabytes in size.

Note:

If retrieving any large object in a READ_COMMITTED transaction, see Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction".

Syntax

    BLOB

Usage Notes

When creating a table, you can optionally specify different tablespace and storage characteristics for BLOB columns.

You can initialize a column with the BLOB datatype by inserting an `EMPTY_BLOB`. See Example 2.

BLOB columns contain LOB locators that can refer to out-of-line or in-line LOB values. Selecting a LOB from a table actually returns the LOB's locator and not the entire LOB value.

BLOB is similar to LONG and LONG RAW types, but differs in the following ways:

- BLOBs can be attributes of a user-defined datatype (object).

- The BLOB locator is stored in the table column, either with or without the actual BLOB value. BLOB values can be stored in separate tablespaces.

- When you access a BLOB column, the locator is returned.

- A BLOB can be up to 2 gigabytes in size.

- BLOBs permit efficient, random, piece-wise access to and manipulation of data.

- You can define more than one BLOB column in a table.

- You can define one or more BLOB attributes in an object.

- You can declare BLOB bind variables.

- You can select BLOB columns and BLOB attributes.

- You can insert a new row or update an existing row that contains one or more BLOB columns and/or an object with one or more BLOB attributes. (You can set the internal BLOB value to NULL, empty, or replace the entire BLOB with data.

- You can update a BLOB row/column intersection or a BLOB attribute with another BLOB row/column intersection or BLOB attribute.

- You can delete a row containing a BLOB column or BLOB attribute. This also deletes the BLOB value.

To access and populate rows of an internal BLOB column (a BLOB column stored in the database), use the INSERT statement first to initialize the internal BLOB value to empty.

Example 1

The following example creates a table with a BLOB column:

    CREATE TABLE  PERSON_TABLE (NAME CHAR(40),
                                PICTURE BLOB);

Example 2

The following example initializes a column with the BLOB datatype by inserting an EMPTY_BLOB:

    INSERT INTO PERSON_TABLE (NAME, PICTURE) VALUES ('Steve', EMPTY_BLOB());

## E.5 CHAR

[ODBC] [SQL-92] [Oracle]

Fixed length character string type. CHAR columns allocate a fixed space in a database row, allowing for the maximum length. Strings shorter than the maximum are padded with trailing blanks.

Syntax

    CHAR
    CHARACTER
    CHAR ( <length> )
    CHARACTER ( <length> )

Keywords and Parameters

<length> is the number of characters in a string. The limit is 4,096 bytes.

Usage Notes

If <length> is omitted, 1 is assumed.

Examples

    CHAR
    CHAR(20)

## E.6 CLOB

The CLOB datatype can store large and unstructured data, such as text and spatial data up to 2 gigabytes in size.

Note:

If retrieving any large object in a READ_COMMITTED transaction, see Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction".

Syntax

    CLOB

Usage Notes

When creating a table, you can optionally specify different tablespace and storage characteristics for CLOB columns.

You can initialize a column with the CLOB datatype by inserting an EMPTY_CLOB. See Example 2.

CLOB columns contain LOB locators that can refer to out-of-line or in-line LOB values. Selecting a LOB from a table actually returns the LOB's locator and not the entire LOB value.

CLOB is similar to LONG and LONG RAW types, but differs in the following ways:

- CLOBs can be attributes of a user-defined datatype (object).

- The CLOB locator is stored in the table column, either with or without the actual CLOB value. CLOB values can be stored in separate tablespaces.

- When you access a CLOB column, the locator is returned.

- A CLOB can be up to 2 gigabytes in size.

- CLOBs permit efficient, random, piece-wise access to and manipulation of data.

- You can define more than one CLOB column in a table.

- You can define one or more CLOB attributes in an object.

- You can declare CLOB bind variables.

- You can select CLOB columns and CLOB attributes.

- You can insert a new row or update an existing row that contains one or more CLOB columns and/or an object with one or more CLOB attributes. (You can set the internal CLOB value to NULL, empty, or replace the entire CLOB with data.

- You can update a CLOB row/column intersection or a CLOB attribute with another CLOB row/column intersection or CLOB attribute.

- You can delete a row containing a CLOB column or CLOB attribute and thereby also delete the BLOB value.

To access and populate rows of an internal CLOB column (a CLOB column stored in the database), use the INSERT statement first to initialize the internal CLOB value to empty.

Example 1

The following example creates a table with a CLOB column:

    CREATE TABLE WORK_HISTORY (NAME CHAR (40),
                                RESUME CLOB);

Example 2

The following example initializes a column with the CLOB datatype by inserting EMPTY_CLOB:

    INSERT INTO WORK_HISTORY (NAME, RESUME) VALUES ('Steve', EMPTY_CLOB());

## E.7 DATE

[ODBC] [SQL-92]

Stores day, month, and year in SQL-92 and ODBC. In Oracle, it also stores the time.

Syntax

    DATE

Example

    DATE

## E.8 DECIMAL

[ODBC] [SQL-92]

Decimal number type.

Syntax

    DECIMAL [ ( <precision>[, <scale> ] ) ] | DEC [ ( <precision>[, <scale> ] ) ]

Keywords and Parameters

<precision> is the precision of a decimal number.

<scale> is the scale of a decimal number (the number of digits to the right of the decimal point).

Usage Notes

A DECIMAL is an exact numeric value. By default, DECIMAL data is returned as a character string or SQL_C_CHAR, but conversion into SQL_C_LONG or SQL_C_FLOAT or other datatypes is supported. If <precision> is not specified, 38 is assumed. If <scale> is not specified, 0 is assumed. 0 <= <scale> <= <precision> <= 38.

DECIMAL is synonymous with NUMERIC and NUMBER.

Examples

    DECIMAL
    DEC (5)
    DECIMAL (10, 5)

## E.9 DOUBLE PRECISION

[ODBC]

Double precision floating point number type. Binds with a SQL_C_DOUBLE variable.

Syntax

    DOUBLE PRECISION

Usage Notes

A DOUBLE PRECISION is a signed, approximate, numeric value with a mantissa decimal precision 15. Its absolute value is either zero or between 10^-308 and 10^308.

Example

    DOUBLE PRECISION

## E.10 FLOAT

[ODBC]

Floating point number type. Binds with a SQL_C_DOUBLE variable.

Syntax

    FLOAT

Usage Notes

A FLOAT is a signed approximate numeric value with a mantissa decimal precision 15. Its absolute value is either zero or between 10^-308 and 10^308. Oracle Database Lite sets the precision based upon the platform type.

Examples

    FLOAT
    FLOAT (10)

## E.11 INTEGER

[ODBC] [SQL-92]

Integer type.

Syntax

    INTEGER
    INT

Usage Notes

An INTEGER is an exact numeric value with precision 10 and scale 0, typically 4 bytes. Binds with SQL_C_LONG or SQL_C_ULONG and SQL_C_SLONG. -2^31 < n < 2^31, where n is the value of an INTEGER.

Examples

    INTEGER
    INT

## E.12 LONG

[Oracle]

Variable-length character string type. Used when the length of the string exceeds 4,096 bytes.

Note:

If retrieving any large object in a READ_COMMITTED transaction, see Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction".

Syntax

    LONG

Keywords and Parameters

<length> is the maximum number of characters in a string.

Usage Notes

The maximum length of a LONG is 2 billion bytes. If <length> is omitted, 2 megabytes is assumed. You can create an index on a LONG column, but only the first 2,000 bytes are used in the index.

Example

    LONG

## E.13 LONG RAW

[Oracle]

Variable length binary datatype. Similar to LONG VARBINARY. Use this type when a VARBINARY column exceeds 4,096 bytes.

Note:

If retrieving any large object in a READ_COMMITTED transaction, see Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction".

Syntax

    LONG RAW [( <precision> )]

Keywords and Parameters

<precision> is the maximum number of bytes. If not specified, the default is 2 megabytes.

Usage Notes

The maximum length of a LONG RAW is 2 billion bytes.

Examples

    LONG RAW(1048576)

## E.14 LONG VARBINARY

[ODBC]

Variable length binary datatype.

Note:

If retrieving any large object in a READ_COMMITTED transaction, see Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction".

Syntax

    LONG BINARY [( <precision> )]

Keywords and Parameters

<precision> is the maximum number of bytes. If not specified, the default is 2 megabytes.

Usage Notes

1 <= <precision> <= 2G.

Examples

    LONG VARBINARY(1048576)

## E.15 LONG VARCHAR

[ODBC]

Variable-length character string type. Used when the length of the string exceeds 4,096 bytes.

Note:

If retrieving any large object in a READ_COMMITTED transaction, see Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction".

Syntax

    LONG VARCHAR
    LONG VARCHAR ( <length> )

Keywords and Parameters

<length> is the maximum number of characters in a string.

Usage Notes

The maximum length of a LONG VARCHAR is 2 billion bytes. If <length> is omitted, 2 megabytes is assumed. You can create an index on a LONG VARCHAR column, but only the first 2,000 bytes are used in the index.

Example

    LONG VARCHAR

## E.16 NUMBER

[Oracle]

DECIMAL number type.

Syntax

    NUMBER [ ( <precision>[, <scale> ] ) ]

Keywords and Parameters

<precision> is the precision of a decimal number.

<scale> is the scale of a decimal number (the number of digits to the right of the decimal point).

Usage Notes

A NUMBER is an exact numeric value. By default, NUMBER data is returned as a character string or SQL_C_CHAR, but conversion into SQL_C_LONG or SQL_C_FLOAT or other datatypes is supported. If <precision> is not specified, 38 is assumed. If <scale> is not specified, 0 is assumed. 0 <= <scale> <= <precision> <= 38.

NUMBER is synonymous with DECIMAL and NUMERIC.

Examples

    NUMBER
    NUMBER (10, 5)

## E.17 NUMERIC

[ODBC] [SQL-92]

DECIMAL number type.

Syntax

    NUMERIC [ ( <precision>[, <scale> ] ) ]

Keywords and Parameters

<precision> is the precision of a decimal number.

<scale> is the scale of a decimal number (the number of digits to the right of the decimal point).

Usage Notes

A NUMERIC is an exact numeric value. By default, NUMERIC data is returned as a character string or SQL_C_CHAR, but conversion into SQL_C_LONG or SQL_C_FLOAT or other datatypes is supported. If <precision> is not specified, 38 is assumed. If <scale> is not specified, 0 is assumed. 0 <= <scale> <= <precision> <= 38.

NUMERIC is synonymous with DECIMAL and NUMBER.

Examples

    NUMERIC
    NUMERIC (10, 5)

## E.18 RAW

[Oracle]

Variable length binary datatype. Binds with a SQL_C_CHAR or SQL_C_BINARY array.

Note:

If retrieving any large object in a READ_COMMITTED transaction, see Section 4.3.46.9, "Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction".

Syntax

    RAW [( <precision> )]

Keywords and Parameters

<precision> is the maximum number of bytes.

Usage Notes

RAW is synonymous with BINARY and VARBINARY, but has a limit of 4,096 bytes.

Examples

    RAW(1024)

## E.19 REAL

[ODBC]

Floating point number type. Binds with SQL_C_REAL variables.

Syntax

    REAL

Usage Notes

A REAL is a signed approximate numeric value with a mantissa decimal precision 7. Its absolute value is either zero or between 10^-38 and 10^38.

Example

    5600E+12

## E.20 ROWID

A 16-byte hexadecimal string representing the unique address of a row in its table. ROWID is primarily for values returned by the ROWID pseudocolumn.

Usage Notes

In Oracle Lite, the ROWID is the hexadecimal string representing the unique object identifier. It is not compatible with the Oracle ROWID, but it may be used to uniquely identify a row for updating. ROWID literals should be enclosed in single quotes.

Example

    A80000.00.03000000

## E.21 SMALLINT

[ODBC] [SQL-92]

Small integer type.

Syntax

    SMALLINT

Usage Notes

A SMALLINT is an exact numeric value with precision 5 and scale 0, typically 2 bytes or 16 bits. If signed, the range can be -32,768 to +32,767 (SQL_C_SSHORT or SQL_C_SHORT) or, if unsigned, 0 to 65,535 (SQL_C_USHORT). -32,768 <= n <= 32,767, where n is the value of a SMALLINT.

Example

    SMALLINT

## E.22 TIME

[ODBC] [SQL-92]

Stores hour, minutes, seconds, and possibly, fractional seconds.

Syntax

    TIME
    TIME ( <precision> ) [SQL-92]

Keywords and Parameters

<precision> is the number of fractional digits in seconds.

Examples

    TIME
    TIME (3)

## E.23 TIMESTAMP

[ODBC] [SQL-92]

Stores both date and time in SQL-92 and is comparable to the Oracle DATE datatype.

Syntax

    TIMESTAMP [ ( <precision> ) ]

Keywords and Parameters

<precision> is the number of fractional digits in seconds. 0 <= <precision> <= 6

Usage Notes

During replication of an Oracle table, DATE columns in Oracle are stored as TIMESTAMP columns in Oracle Lite.

Examples

    TIMESTAMP
    TIMESTAMP (3)

## E.24 TINYINT

[ODBC]

A one byte integer type.

Syntax

    TINYINT

Usage Notes

A one byte integer with range 0 to 127. If unsigned (SQL_C_UTINYINT) or - 128 to + 127, and if signed (SQL_C_STINYINT).

Example

    TINYINT

## E.25 VARBINARY

[ODBC]

Variable length binary datatype. Binds with a SQL_C_CHAR or SQL_C_BINARY array.

Syntax

    VARBINARY [( <precision> )]

Keywords and Parameters

<precision> is the maximum number of bytes.

Usage Notes

VARBINARY is synonymous with BINARY and RAW.

Example

    VARBINARY(1024)

## E.26 VARCHAR

[ODBC] [SQL-92] [Oracle]

Variable-length character string type.

Syntax

    VARCHAR ( <length> )

Keywords and Parameters

<length> is the maximum number of characters in a string, between 1 and 4,096.

Usage Notes

If <length> is omitted, 1 is assumed.

Examples

    VARCHAR(20)

## E.27 VARCHAR2

[Oracle]

Variable-length character string type. VARCHAR and VARCHAR2 are stored exactly as passed, provided the length does not exceed the maximum. No blank padding is added. VARCHAR and VARCHAR2 are equivalent.

Syntax

    VARCHAR2 ( <length> )

Keywords and Parameters

<length> is the maximum number of characters in a string, between 1 and 4,096.

Usage Notes

If <length> is omitted, 1 is assumed.

Examples

    VARCHAR2(20)