Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
|
|
View PDF |
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.
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
[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)
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());
[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)
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());
[ODBC] [SQL-92]
Stores day, month, and year in SQL-92 and ODBC. In Oracle, it also stores the time.
Syntax
DATE
Example
DATE
[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)
[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
[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)
[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
[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
[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)
[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)
[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
[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)
[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)
[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)
[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
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
[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
[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)
[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)
[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
[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)
[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)
[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)