Data Type Conversion

There are several kinds of data type conversions.

Also see type conversion information under Differences in TimesTen: Data Type Considerations.

Conversion Between PL/SQL Data Types

TimesTen supports implicit and explicit conversions between PL/SQL data types.

Consider this example: The variable v_sal_hike is of type VARCHAR2. When calculating the total salary, PL/SQL first converts v_sal_hike to NUMBER then performs the operation. The result is of type NUMBER. PL/SQL uses implicit conversion to obtain the correct result.

Command> DECLARE
           v_salary NUMBER (6) := 6000;
           v_sal_hike VARCHAR2(5) := '1000';
           v_total_salary v_salary%TYPE;
         BEGIN
         v_total_salary := v_salary + v_sal_hike;
         DBMS_OUTPUT.PUT_LINE (v_total_salary);
         end;
         /
7000

PL/SQL procedure successfully completed.

Note:

Also see Date and Timestamp Formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT.

Conversion Between Application Data Types and PL/SQL or SQL Data Types

TimesTen supports data type conversions between application program data types and PL/SQL data types, and between application program data types and TimesTen SQL data types.

This section includes the following:

Application Data Type Conversion Mappings

Data types from an application using the ODBC API can be mapped to PL/SQL program data types.

For more information about ODBC-to-PL/SQL type mappings, refer to Parameter Type Assignments and Type Conversions in Oracle TimesTen In-Memory Database C Developer's Guide.

Table 3-4 Sampling of ODBC SQL to PL/SQL Type Mapping

ODBC Type	PL/SQL Type
`SQL_BINARY`	`RAW` (Bound precision is used.)
`SQL_CHAR`	`CHAR` (Bound precision is used.)
`SQL_DATE`	`DATE`
`SQL_DECIMAL`	`NUMBER`
`SQL_DOUBLE`	`NUMBER`
`SQL_FLOAT`	`BINARY_DOUBLE`
`SQL_INTEGER`	`PLS_INTEGER`
`SQL_REFCURSOR`	`REF CURSOR`
`SQL_TIMESTAMP`	`TIMESTAMP` (Bound scale is used.)
`SQL_VARCHAR`	`VARCHAR2` (Bound precision is used.)

Application Data Type Conversion Examples

Consider a scenario where your C program uses the ODBC API and your goal is to bind your C variable of type VARCHAR2 to a PL/SQL variable of type NUMBER. TimesTen performs the implicit conversion for you.

Command> VARIABLE c_var VARCHAR2 (30) := '961';
Command> DECLARE v_var NUMBER;
         BEGIN
           v_var := :c_var;
           DBMS_OUTPUT.PUT_LINE (v_var);
         END;
         /
961
 
PL/SQL procedure successfully completed.

The next example creates a table with a column of type TT_BIGINT and uses PL/SQL to invoke the TimesTen SQL INSERT statement. A bind variable of type SQL_VARCHAR is used in the INSERT statement. The conversions are the same as the conversions that would occur if your application invoked the INSERT statement directly.

Command> CREATE TABLE conversion_test2 (Col1 TT_BIGINT);
Command>  VARIABLE v_var VARCHAR2 (100) := '1000';
Command>  BEGIN
           INSERT INTO conversion_test2 VALUES (:v_var);
          END;
          /
 
PL/SQL procedure successfully completed.

Command> SELECT * FROM conversion_test2;
< 1000 >
1 row found.

Note:

For SQL, the conversions are the same whether SQL is invoked by your PL/SQL program or is invoked directly by your application.