Oracle Lite supports the datatypes listed in Table E-1:
Table E-1 Datatypes Supported by Oracle Database Lite
| Datatype | Description |
|---|---|
|
An integer datatype with a precision of 19 decimal digits. |
|
|
Enables storage of binary data up to 4,096 bytes. |
|
|
Enables your application to store a bit unconstrained by character semantics. |
|
|
A binary large object. Maximum size is 2 gigabytes. |
|
|
Fixed length character data of length size bytes. Maximum size is 4,096 bytes. Default and minimum size is 1 byte. |
|
|
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. |
|
|
Valid date range from January 1, 4712 BC to December 31, 4712 AD. |
|
|
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. |
|
|
Contains a precision defined during implementation which must be greater than the precision of REAL. |
|
|
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. |
|
|
An integer value whose precision (the number of decimal values or bits that can be stored) is defined upon implementation. |
|
|
Character data of variable length up to 2 gigabytes, or 231 -1 bytes. |
|
|
Raw binary data of variable length up to 2 gigabytes. |
|
|
Stores but does not interpret up to 2 gigabytes of variable binary data. |
|
|
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 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. |
|
|
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 binary data of length size bytes. Maximum size is 4,096 bytes. You must specify size for a RAW value. |
|
|
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. |
|
|
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. |
|
|
An integer value whose precision is defined upon implementation but whose value is no greater than the implementation of INTEGER. |
|
|
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. |
|
|
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. |
|
|
An integer with a precision of 1 byte (-128 to +127). |
|
|
Stores but does not interpret variable binary data. |
|
|
See VARCHAR2 |
|
|
Variable-length character string with a maximum length size of 4,096 bytes (minimum is 1). You must specify size for a VARCHAR2. |
[ODBC]
Big integer type. Binds with SQL_C_CHAR or SQL_C_BINARY variables.
BIGINT
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.
BIGINT
[ODBC]
Variable length binary datatype. Binds with a SQL_C_CHAR or SQL_C_BINARY array.
BINARY [( <precision> )]
<precision> is the maximum number of bytes.
BINARY is synonymous with VARBINARY and RAW.
BINARY(1024)
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".BLOB
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.
The following example creates a table with a BLOB column:
CREATE TABLE PERSON_TABLE (NAME CHAR(40),
PICTURE BLOB);
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());
[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.
CHAR CHARACTER CHAR ( <length> ) CHARACTER ( <length> )
<length> is the number of characters in a string. The limit is 4,096 bytes.
If <length> is omitted, 1 is assumed.
CHAR CHAR(20)
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".CLOB
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.
The following example creates a table with a CLOB column:
CREATE TABLE WORK_HISTORY (NAME CHAR (40),
RESUME CLOB);
The following example initializes a column with the CLOB datatype by inserting EMPTY_CLOB:
INSERT INTO WORK_HISTORY (NAME, RESUME) VALUES ('Steve', EMPTY_CLOB());
[ODBC] [SQL-92]
Stores day, month, and year in SQL-92 and ODBC. In Oracle, it also stores the time.
DATE
DATE
[ODBC] [SQL-92]
Decimal number type.
DECIMAL [ ( <precision>[, <scale> ] ) ] | DEC [ ( <precision>[, <scale> ] ) ]
<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).
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.
DECIMAL DEC (5) DECIMAL (10, 5)
[ODBC]
Double precision floating point number type. Binds with a SQL_C_DOUBLE variable.
DOUBLE PRECISION
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.
DOUBLE PRECISION
[ODBC]
Floating point number type. Binds with a SQL_C_DOUBLE variable.
FLOAT [ ( <precision> ) ]
<precision> is the precision of a floating point number.
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.
FLOAT FLOAT (10)
[ODBC] [SQL-92]
Integer type.
INTEGER INT
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.
INTEGER INT
[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".LONG
<length> is the maximum number of characters in a string.
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.
LONG
[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".
LONG RAW [( <precision> )]
<precision> is the maximum number of bytes. If not specified, the default is 2 megabytes.
The maximum length of a LONG RAW is 2 billion bytes.
LONG RAW(1048576)
[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".
LONG BINARY [( <precision> )]
<precision> is the maximum number of bytes. If not specified, the default is 2 megabytes.
1 <= <precision> <= 2G.
LONG VARBINARY(1048576)
[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".
LONG VARCHAR
LONG VARCHAR ( <length> )
<length> is the maximum number of characters in a string.
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.
LONG VARCHAR
[Oracle]
DECIMAL number type.
NUMBER [ ( <precision>[, <scale> ] ) ]
<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).
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.
NUMBER NUMBER (10, 5)
[ODBC] [SQL-92]
DECIMAL number type.
NUMERIC [ ( <precision>[, <scale> ] ) ]
<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).
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.
NUMERIC NUMERIC (10, 5)
[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".
RAW [( <precision> )]
<precision> is the maximum number of bytes.
RAW is synonymous with BINARY and VARBINARY, but has a limit of 4,096 bytes.
RAW(1024)
[ODBC]
Floating point number type. Binds with SQL_C_REAL variables.
REAL
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.
5600E+12
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.
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.
A80000.00.03000000
[ODBC] [SQL-92]
Small integer type.
SMALLINT
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.
SMALLINT
[ODBC] [SQL-92]
Stores hour, minutes, seconds, and possibly, fractional seconds.
TIME
TIME ( <precision> ) [SQL-92]
<precision> is the number of fractional digits in seconds.
TIME TIME (3)
[ODBC] [SQL-92]
Stores both date and time in SQL-92 and is comparable to the Oracle DATE datatype.
TIMESTAMP [ ( <precision> ) ]
<precision> is the number of fractional digits in seconds. 0 <= <precision> <= 6
During replication of an Oracle table, DATE columns in Oracle are stored as TIMESTAMP columns in Oracle Lite.
TIMESTAMP TIMESTAMP (3)
[ODBC]
A one byte integer type.
TINYINT
A one byte integer with range 0 to 127. If unsigned (SQL_C_UTINYINT) or - 128 to + 127, and if signed (SQL_C_STINYINT).
TINYINT
[ODBC]
Variable length binary datatype. Binds with a SQL_C_CHAR or SQL_C_BINARY array.
VARBINARY [( <precision> )]
<precision> is the maximum number of bytes.
VARBINARY is synonymous with BINARY and RAW.
VARBINARY(1024)
[ODBC] [SQL-92] [Oracle]
Variable-length character string type.
VARCHAR ( <length> )
<length> is the maximum number of characters in a string, between 1 and 4,096.
If <length> is omitted, 1 is assumed.
VARCHAR(20)
[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.
VARCHAR2 ( <length> )
<length> is the maximum number of characters in a string, between 1 and 4,096.
If <length> is omitted, 1 is assumed.
VARCHAR2(20)