Skip Headers
Oracle® Database Lite SQL Reference
10g (10.3.0)

Part Number B28925-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
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.10, "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:

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.10, "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:

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 [ ( <precision> ) ]

Keywords and Parameters

<precision> is the precision of a floating point number.

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. In the current implementation, the precision of a FLOAT is always set to 15.

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.10, "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.10, "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.10, "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.10, "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.10, "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)