This chapter focuses on the range of data types available to you for manipulating data in PL/SQL, TimesTen SQL, and your application programs.
TimesTen supports PL/SQL data types and the interactions between PL/SQL data types, TimesTen data types, and client application program data types. Data type conversions and data type mappings are supported.
See the end of the chapter for TimesTen-specific considerations.
Topics in this chapter include the following:
There are three distinct environments to consider when discussing data types:
PL/SQL programs that contain variables and constants that use PL/SQL data types
TimesTen SQL statements that make use of database rows, columns, and constants
These elements are expressed using TimesTen SQL data types.
Application programs that interact with the database and the PL/SQL programming language
Application programs are written in programming languages such as C and Java and contain variables and constants that use data types from these programming languages.
Table 3-1 summarizes the environments and gives examples of data types for each environment.
This section describes the PL/SQL data types that are supported in PL/SQL programs. It does not describe the data types supported in TimesTen SQL statements. For information on data types supported in TimesTen SQL statements, see "Data Types" in Oracle TimesTen In-Memory Database SQL Reference.
The following topics are covered in this section:
For additional information see "PL/SQL Data Types" in Oracle Database PL/SQL Language Reference.
In a PL/SQL block, every constant, variable, and parameter has a data type. PL/SQL provides predefined data types and subtypes and lets you define your own PL/SQL subtypes.
Table 3-2 lists the categories of the predefined PL/SQL data types.
Table 3-2 Predefined PL/SQL data type categories
| Data type category | Description |
|---|---|
|
Scalar |
Single values with no internal components |
|
Composite |
Internal components that are either scalar or composite |
|
Reference |
Pointers to other data items such as REF CURSORs |
Note:
See "Non-supported data types".Scalar data types store single values with no internal components. Table 3-3 lists predefined PL/SQL scalar data types of interest, grouped by data type families.
Table 3-3 Predefined PL/SQL scalar data types
| Data type family | Data type name |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
Note: You cannot bind |
|
|
|
|
|
|
|
|
|
|
|
|
Note:
See "Non-supported data types".Example 3-1 Declaring PL/SQL variables
Command> DECLARE
> v_emp_job VARCHAR2 (9);
> v_count_loop BINARY_INTEGER := 0;
> v_dept_total_sal NUMBER (9,2) := 0;
> v_orderdate DATE := SYSDATE + 7;
> v_valid BOOLEAN NOT NULL := TRUE;
> ...
The PLS_INTEGER and BINARY_INTEGER data types are identical and are used interchangeably in this document.
The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647 represented in 32 bits. It has the following advantages over the NUMBER data type and subtypes:
PLS_INTEGER values require less storage.
PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.
For efficiency, use PLS_INTEGER values for all calculations that fall within its range. For calculations outside the PLS_INTEGER range, use INTEGER, a predefined subtype of the NUMBER data type.
See "PLS_INTEGER and BINARY_INTEGER Data Types" in Oracle Database PL/SQL Language Reference for additional information.
Note:
When a calculation with twoPLS_INTEGER data types overflows the PLS_INTEGER range, an overflow exception is raised even if the result is assigned to a NUMBER data type.SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type that has the same range as PLS_INTEGER (-2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint. It differs from PLS_INTEGER in that it does not overflow.
You can use SIMPLE_INTEGER when the value is never null and overflow checking is unnecessary. Without the overhead of checking for null values and overflow, SIMPLE_INTEGER provides better performance than PLS_INTEGER.
See "SIMPLE_INTEGER Subtype of PLS_INTEGER" in Oracle Database PL/SQL Language Reference for additional information.
Each row in a table has a unique identifier known as its rowid.
An application can specify literal rowid values in SQL statements, such as in WHERE clauses, as CHAR constants enclosed in single quotes.
Also refer to "ROWID data type" and "ROWID" in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and life.
The LOB (large object) type family includes CLOB (character LOBs), NCLOB (national character LOBs), and BLOB (binary LOBs).
A LOB consists of a LOB locator and a LOB value. The locator acts as a handle to the value. When an application selects a LOB or passes a LOB as a parameter, for example, it is using the locator, not the actual value.
LOBs may be either persistent or temporary. A persistent LOB exists in the database, in a particular row of a LOB column. A temporary LOB is used internally within a program, but could then be inserted into a LOB column in the database to become a persistent LOB.
See "LOB data types" in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen.
Also see "Large objects (LOBs)" for usage information.
Composite types have internal components that can be manipulated individually, such as the elements of an array, record, or table.
Oracle TimesTen In-Memory Database supports the following composite data types:
Associative array (index-by table)
Nested table
Varray
Record
Associative arrays, nested tables, and varrays are also referred to as collections.
The following sections discuss the use of composite data types:
See "PL/SQL Collections and Records" in Oracle Database PL/SQL Language Reference for additional information.
You can declare collection data types similar to arrays, sets, and hash tables found in other languages. A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.
In PL/SQL, array types are known as varrays (variable size arrays), set types are known as nested tables, and hash table types are known as associative arrays or index-by tables. These are all collection types.
Example 3-2 Using a PL/SQL collection type
This example declares collection type staff_list as a table of employee_id, then uses the collection type in a loop and in the WHERE clause of the SELECT statement.
Command> DECLARE
> TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
> staff staff_list;
> lname employees.last_name%TYPE;
> fname employees.first_name%TYPE;
> BEGIN
> staff := staff_list(100, 114, 115, 120, 122);
> FOR i IN staff.FIRST..staff.LAST LOOP
> SELECT last_name, first_name INTO lname, fname FROM employees
> WHERE employees.employee_id = staff(i);
> DBMS_OUTPUT.PUT_LINE (TO_CHAR(staff(i)) ||
> ': ' || lname || ', ' || fname );
> END LOOP;
> END;
> /
100: King, Steven
114: Raphaely, Den
115: Khoo, Alexander
120: Weiss, Matthew
122: Kaufling, Payam
PL/SQL procedure successfully completed.
Any collections can be passed between PL/SQL subprograms as parameters, but in TimesTen only associative arrays can be passed between PL/SQL and applications written in other languages. (See "Using associative arrays from applications" below.)
You can use collections to move data in and out of TimesTen tables using bulk SQL.
Records are composite data structures that have fields with different data types. You can pass records to subprograms with a single parameter. You can also use the %ROWTYPE attribute to declare a record that represents a row in a table or a row from a query result set, without specifying the names and types for the fields, as shown in Example 2-2.
Example 3-3 Declaring a record type
Declare various record types.
Command> DECLARE
> TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);
> TYPE meetin_typ IS RECORD (
> date_held DATE,
> duration timerec, -- nested record
> location VARCHAR2(20),
> purpose VARCHAR2(50));
> BEGIN
> ...
> END;
> /
Associative arrays, formerly known as index-by tables or PL/SQL tables, are supported as IN, OUT, or IN OUT bind parameters in TimesTen PL/SQL, such as from an OCI, Pro*C/C++, or JDBC application. This enables arrays of data to be passed efficiently between an application and the database.
An associative array is a set of key-value pairs. In TimesTen, for associative array binding (but not for use of associative arrays only within PL/SQL), the keys, or indexes, must be integers (BINARY_INTEGER or PLS_INTEGER). The values must be simple scalar values of the same data type. For example, there could be an array of department managers indexed by department numbers. Indexes are stored in sort order, not creation order.
You can declare an associative array type and then an associative array in PL/SQL as in the following example (note the INDEX BY):
declare TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; x VARCHARARRTYP; ...
See Example 3-4 below and "Using associative arrays" for examples.
Also see "Associative array bindings in TimesTen OCI" and "Associative array bindings in TimesTen Pro*C/C++" in Oracle TimesTen In-Memory Database C Developer's Guide, and "Binding associative arrays" in Oracle TimesTen In-Memory Database Java Developer's Guide.
For general information about associative arrays, see "Associative Arrays" in Oracle Database PL/SQL Language Reference.
Notes:
Note the following restrictions in TimesTen:The following types are not supported in binding associative arrays: LOBs, REF CURSORs, TIMESTAMP, ROWID.
Associative array binding is not allowed in passthrough statements.
General bulk binding of arrays is not supported in TimesTen programmatic APIs. Varrays and nested tables are not supported as bind parameters.
The following example does some simple manipulation of an associative array, effectively binding it from ttIsql and printing the array.
Example 3-4 Using an associative array from ttIsql
Command> var lngvc[1000] varchar2(30); Command> declare > TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; > x VARCHARARRTYP; > begin > x := :lngvc; > x ( 1 ) := 'One'; > x ( 10 ) := 'Ten'; > :lngvc := x; > end; > / PL/SQL procedure successfully completed. Command> print lngvc; LNGVC : ARRAY [ 1000 ] (Current Size 10) LNGVC[1] : One LNGVC[2] : <NULL> LNGVC[3] : <NULL> LNGVC[4] : <NULL> LNGVC[5] : <NULL> LNGVC[6] : <NULL> LNGVC[7] : <NULL> LNGVC[8] : <NULL> LNGVC[9] : <NULL> LNGVC[10] : Ten
A REF CURSOR is a handle to a cursor over a SQL result set that can be passed as a parameter between PL/SQL and an application. TimesTen supports OUT REF CURSORs, from PL/SQL to the application. The application would open the REF CURSOR within PL/SQL, pass it from there through the applicable API, and fetch the result set.
TimesTen supports REF CURSORs in ODBC, JDBC, ODP.NET, OCI, Pro*C/C++, and TTClasses for either direct connections or client/server connections. REF CURSORs are also discussed in the following TimesTen documents:
"Working with REF CURSORs" in Oracle TimesTen In-Memory Database C Developer's Guide
"Working with REF CURSORs" in Oracle TimesTen In-Memory Database Java Developer's Guide
"Working with REF CURSORs" in Oracle TimesTen In-Memory Database TTClasses Guide
Note:
TimesTen supports oneOUT REF CURSOR per statement.You can define a REF CURSOR in PL/SQL in TimesTen as you would in Oracle Database. (See "Cursor Variables" in Oracle Database PL/SQL Language Reference.) It is typical to use REF CURSOR as a metatype, where you define a "strong" (specific) REF CURSOR type tailored to your data, then declare a cursor variable of that type. For example:
Command> DECLARE
> TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;
> dept_cv DeptCurTyp; -- declare cursor variable
> ...
Example 3-5 Use a REF CURSOR as an output parameter
This example creates a procedure GET_EMP in a package FOO_PACK to retrieve information about employees from the employees table. The procedure declares a REF CURSOR type cursor_out, then uses that type for the output parameter.
First specify the package definition, REF CURSOR type, and procedure definition.
create or replace package foo_pack is type cursor_out is ref cursor; procedure get_emp (results out cursor_out); end foo_pack;
Then specify the package body and procedure implementation.
create or replace package body foo_pack as
procedure get_emp (results out cursor_out) is
begin
open results for select employee_id, last_name from employees
where employee_id < 110 order by last_name;
end get_emp;
end foo_pack;
Declare a REF CURSOR variable for the output, execute the procedure, and display the results. Note that outside of PL/SQL, you can declare only "weak" (generic) REF CURSORs.
Command> var proc_result refcursor; Command> exec foo_pack.get_emp(:proc_result); PL/SQL procedure successfully completed. Command> print proc_result; PROC_RESULT : < 105, Austin > < 102, De Haan > < 104, Ernst > < 109, Faviet > < 108, Greenberg > < 103, Hunold > < 100, King > < 101, Kochhar > < 107, Lorentz > < 106, Pataballa > 10 rows found.
Alternatively, you could declare a weakly typed REF CURSOR variable in FOO_PACK:
create or replace package foo_pack is
procedure get_emp (results out sys_refcursor);
end foo_pack;
create or replace package body foo_pack as
procedure get_emp (results out sys_refcursor) is
begin
open results for select employee_id, last_name from employees
where employee_id < 110 order by last_name;
end get_emp;
end foo_pack;
This section covers the following data type conversions:
Also see type conversion information under "Differences in TimesTen: data type considerations".
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.
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. For SQL, the conversions are the same whether SQL is invoked by your PL/SQL program or is invoked directly by your application.
As an example, Table 3-4 shows a few representative data type mappings from an application using the ODBC API to PL/SQL program data types. For more information about ODBC-to-PL/SQL type mappings, refer to "Determination of 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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example 3-6 ODBC to PL/SQL data type conversions
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.
Example 3-7 ODBC to TimesTen SQL data type conversions
This 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.
This section covers the following TimesTen-specific considerations regarding data type support and type conversions:
TimesTen supports conversions between PL/SQL data types and TimesTen 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. "Y" indicates that a conversion is possible between the two families. Supported conversions are bidirectional.
Table 3-5 Supported conversions between PL/SQL and TimesTen SQL data types
| Type Family | NUMERIC | CHARACTER | BINARY | DATETIME | INTERVAL | ROWID |
|---|---|---|---|---|---|---|
|
|
Y |
Y |
||||
|
|
Y |
Y |
Y |
Y |
Y |
Y |
|
|
Y |
Y |
||||
|
|
Y |
|||||
|
|
Y |
Y |
||||
|
|
Y |
Y |
Y |
Table 3-6 that follows summarizes the TimesTen data types and suggestions for PL/SQL type mappings.
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 |
|
Example 3-8 Conversions between TimesTen SQL data types and PL/SQL data types
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.
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 for details of these functions.
Note the following non-support of 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.
TimesTen PL/SQL does not support the TimesTen type TT_DECIMAL.
In TimesTen type mode, still supported for backward compatibility, the string value "" is considered by TimesTen to be an empty, zero-length string, but not a null value. In PL/SQL, however, a zero-length string is always considered to be null. If a parameter with a value of empty string is passed to a SQL statement that is executed in PL/SQL, the parameter is converted to NULL by PL/SQL before the SQL statement is passed to TimesTen.
See "TimesTen type mode (backward compatibility)" in Oracle TimesTen In-Memory Database SQL Reference for information about this mode.