Differences in TimesTen: Data Type Considerations

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
`NUMERIC`	Yes	Yes	No	No	No	No
`CHARACTER`	Yes	Yes	Yes	Yes	Yes	Yes
`DATETIME`	No	Yes	No	Yes	No	No
`TIME`	No	Yes	No	No	No	No
`ROWID`	No	Yes	No	No	No	Yes
`BINARY`	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
`TT_TINYINT`	This is for unsigned integers ranging from 0 to 255. Numeric overflows can occur if you insert a value with type PL/SQL `NUMBER` or PL/SQL `PLS_INTEGER` (or `BINARY_INTEGER`) into a `TT_TINYINT` column.
`TT_SMALLINT`	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 `NUMBER` or PL/SQL `PLS_INTEGER` (or `BINARY_INTEGER`) into a `TT_SMALLINT` column.
`TT_INTEGER`	This is for signed integers in the range -2,147,483,648 to 2,147,483,647. This is equivalent to `PLS_INTEGER`.
`TT_BIGINT`	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 `NUMBER`. A PL/SQL `PLS_INTEGER` (or `BINARY_INTEGER`) variable could overflow.
`NUMBER`, `BINARY_FLOAT`, `BINARY_DOUBLE`	Use when floating point precision is required.
Character types	All PL/SQL character types can hold up to 32,767 bytes of data. TimesTen `CHAR` can hold up to 8300 bytes. TimesTen `NCHAR` can hold up to 4150 characters (8300 bytes). TimesTen `VARCHAR2` can hold up to 4,194,304 bytes. TimesTen `NVARCHAR2` can hold up to 2,097,152 characters (4,194,304 bytes).
Datetime, interval, and time types	Use the `TO_CHAR` and `TO_DATE` built-in functions when you require a format that is different than the default format used when converting these types to and from character types.
Binary types	TimesTen `BINARY` can hold up to 8300 bytes. TimesTen `VARBINARY` can hold up to 4,194,304 bytes. `RAW` and `LONG RAW` can hold up to 32,767 bytes.

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 and UROWID.