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