Differences in TimesTen: Data Type Considerations
There are TimesTen-specific considerations regarding data type support and type conversions.
Conversion Between PL/SQL and TimesTen SQL Data Types
TimesTen supports conversions between PL/SQL data types and TimesTen SQL data types.
This support is described in the following sections:
Supported PL/SQL to SQL Conversions
You can convert certain PL/SQL data types to SQL data types.
Table 3-5 shows supported data type conversions, with PL/SQL types along the top and SQL types down the left side. The data types are grouped by data type families, with columns referring to PL/SQL type families and rows referring to TimesTen type families. "Yes" indicates that a conversion is possible between the two families. Supported conversions are bidirectional.
Note:
Also see Unsupported Data Types.
Table 3-5 Supported Conversions Between PL/SQL and TimesTen SQL Data Types
Type Family | NUMERIC | CHARACTER | BINARY | DATETIME | INTERVAL | ROWID |
---|---|---|---|---|---|---|
|
Yes |
Yes |
No |
No |
No |
No |
|
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
|
No |
Yes |
No |
Yes |
No |
No |
|
No |
Yes |
No |
No |
No |
No |
|
No |
Yes |
No |
No |
No |
Yes |
|
No |
Yes |
Yes |
No |
No |
Yes |
Suggested PL/SQL to SQL Mappings
There are suggestions for mapping TimesTen data types to PL/SQL.
Table 3-6 summarizes TimesTen data types with suggestions for type mappings to PL/SQL.
Table 3-6 Data Type Usage and Sizes
TimesTen Data Type | Description |
---|---|
|
This is for unsigned integers ranging from 0 to 255. Numeric overflows can occur if you insert a value with type PL/SQL |
|
This is for signed 16-bit integers in the range -32,768 to 32,767. Numeric overflows can occur if you insert a value with type PL/SQL |
|
This is for signed integers in the range -2,147,483,648 to 2,147,483,647. This is equivalent to |
|
This is for signed eight-byte integers in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Use PL/SQL |
|
Use when floating point precision is required. |
Character types |
All PL/SQL character types can hold up to 32,767 bytes of data.
|
Datetime, interval, and time types |
Use the |
Binary types |
|
PL/SQL to SQL Conversion Example
This is an example of converting PL/SQL to SQL.
Consider the case where you have a table with two columns. Col1
has a data type of TT_INTEGER
and Col2
has a data type of NUMBER
. In your PL/SQL program, you declare two variables: v_var1
of type PLS_INTEGER
and v_var2
of type VARCHAR2
. The goal is to SELECT
the row of data from your table into the two PL/SQL variables.
Data type conversions occur when you execute the SELECT
statement. Col1
is converted from a TimesTen SQL TT_INTEGER
type into a PLS_INTEGER
type. Col2
is converted from a TimesTen SQL NUMBER
type into a PL/SQL VARCHAR2
type. The query executes successfully.
Command> CREATE TABLE test_conversion (Col1 TT_INTEGER, Col2 NUMBER);
Command> INSERT INTO test_conversion VALUES (100, 20);
1 row inserted.
Command> DECLARE
v_var1 PLS_INTEGER;
v_var2 VARCHAR2 (100);
BEGIN
SELECT Col1, Col2 INTO v_var1, v_var2 FROM test_conversion;
DBMS_OUTPUT.PUT_LINE (v_var1);
DBMS_OUTPUT.PUT_LINE (v_var2);
END;
/
100
20
PL/SQL procedure successfully completed.
Date and Timestamp Formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT
TimesTen does not support user-specified NLS_DATE_FORMAT
and NLS_TIMESTAMP_FORMAT
settings.
-
NLS_DATE_FORMAT
is always 'yyyy-mm-dd
'. -
NLS_TIMESTAMP_FORMAT
is always 'yyyy-mm-dd hh:mi:ss.ff6
' (fractional seconds to six decimal places).
You can use the SQL and PL/SQL TO_DATE
and TO_CHAR
functions to specify other desired formats. See Expressions in Oracle TimesTen In-Memory Database SQL
Reference.
Unsupported Data Types
There are unsupported data types.
-
PL/SQL data type categories: PL/SQL in TimesTen does not support Internet data types (
XMLType
,URIType
,HttpURIType
) or "Any" data types (AnyType
,AnyData
,AnyDataSet
). -
PL/SQL scalar data types: TimesTen does not support the PL/SQL data types
TIMESTAMP WITH [LOCAL] TIME ZONE
andUROWID
.