3 Data Types in PL/SQL in TimesTen

This chapter focuses on the range of data types available to you for manipulating data in PL/SQL, TimesTen SQL, and your application programs.

Oracle TimesTen In-Memory Database 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:

Understanding the data type environments
Understanding and Using PL/SQL data types
Data type conversion
Differences in TimesTen: data type considerations

Understanding the data type environments

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 rows, columns, and constants in the TimesTen database. These elements are expressed using TimesTen SQL data types.
Application programs that interact with the TimesTen 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.

Table 3-1 Summarizing the data type environments

Environment	Data type examples
PL/SQL programs	NUMBER, PLS_INTEGER, VARCHAR2, STRING, DATE, TIMESTAMP
TimesTen SQL statements	TT_BIGINT, TT_INTEGER, BINARY_FLOAT, VARCHAR2, DATE, TIMESTAMP
Application programs	int, float, String

Understanding and Using PL/SQL data types

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.

Topics in this section:

PL/SQL data type categories
Predefined PL/SQL scalar data types
PL/SQL composite data types
PL/SQL REF CURSORs

For additional information see "PL/SQL Data Types" in Oracle Database PL/SQL Language Reference.

PL/SQL data type categories

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

Note:

See "Non-supported data types".

Predefined PL/SQL scalar 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
NUMERIC	NUMBER PLS_INTEGER BINARY_FLOAT BINARY_DOUBLE
CHARACTER	CHAR[ACTER] VARCHAR2 NCHAR NVARCHAR2
BINARY	RAW
BOOLEAN	BOOLEAN Note: You cannot bind BOOLEAN types in SQL statements.
DATETIME	DATE TIMESTAMP
INTERVAL	INTERVAL YEAR TO MONTH INTERVAL DAY TO SECONDS
ROWID	ROWID

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

PL S_INTEGER and BINARY_INTEGER data types

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 two PLS_INTEGER data types overflows the PLS_INTEGER range, an overflow exception is raised even if the result is assigned to a NUMBER data type.

SIM PLE_INTEGER 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 will never be 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.

RO WID data type

Each row in a TimesTen database 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 specification" in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and life.

Note:

See "Non-supported data types".

PL/SQL comp osite data types

Composite types have internal components that can be manipulated individually, such as the elements of an array, record, or table.

Oracle TimesTen In-Memory supports the composite data types:

Associative array (index-by table)
Nested table
Varray
Record

Associative arrays, nested tables, and varrays are also referred to as collections.

Note:

While TimesTen PL/SQL supports these types, it does not support passing them between PL/SQL and applications written in other languages.

U sing c ollections

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.

Collections can be passed between PL/SQL subprograms as parameters, but cannot be returned to applications written in other languages.

You can use collections to move data in and out of TimesTen tables using bulk SQL.

Us ing records

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
       > -- NULL does nothing but allows unit to be compiled and tested
       >   NULL;
       > END;
       > /
 
PL/SQL procedure successfully completed.

PL /SQL REF CURSORs

A REF CURSOR is a handle to a cursor over a SQL result set that can be passed as a parameter from PL/SQL to your application. Oracle TimesTen In-Memory Database supports OUT REF CURSORs. REF CURSORs can also be passed from PL/SQL to PL/SQL.

This means you can pass REF CURSORs:

From PL/SQL to PL/SQL: Pass REF CURSORs from one procedure or function to another in any mode (IN, OUT, or IN OUT).
From PL/SQL to your client API (such as ODBC): Use OUT parameters to pass REF CURSORs from PL/SQL to your application.

In your applications, open the REF CURSOR within PL/SQL and pass the REF CURSOR back to your application so that your application can fetch the result set.

Oracle TimesTen In-Memory Database supports REF CURSORs in ODBC, JDBC, OCI, Pro*C, and TTClasses in either a direct-mode or client/server scenario. REF CURSORs are also discussed in the documentation for those programming interfaces.

Note:

Oracle TimesTen In-Memory Database supports one OUT REF CURSOR per statement.

To define a REF CURSOR, perform the same steps as you would in Oracle. First define a REF CURSOR type and 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
       > ...

To declare cursor variables as the formal parameters of functions and procedures:

Command> DECLARE
       >   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
       >   PROCEDURE open_emp_cv (emp_cv OUT EmpCurTyp) IS ...

Example 3-4 Fetch rows from result set of a dynamic multirow query

This example defines a REF CURSOR, EmpCurType, then declares a cursor variable, emp_cv, of the type EmpCurType. In the executable section of the PL/SQL block, the OPEN...FOR statement associates the cursor variable emp_cv with the multirow query, sql_stmt. The FETCH statement returns a row from the result set of a multirow query and assigns the values of the select list items to emp_rec in the INTO clause. When the last row is processed, the cursor variable is closed.

Command> DECLARE
       >   TYPE EmpCurTyp IS REF CURSOR;
       >   emp_cv   EmpCurTyp;
       >   emp_rec  employees%ROWTYPE;
       >   sql_stmt  VARCHAR2 (200);
       >   my_job VARCHAR2 (10) := 'ST_CLERK';
       > BEGIN
       >   sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
       >   OPEN  emp_cv FOR sql_stmt USING my_job;
       >   LOOP
       >     FETCH emp_cv INTO emp_rec;
       >     EXIT WHEN emp_cv%NOTFOUND;
       >     DBMS_OUTPUT.PUT_LINE (emp_rec.employee_id);
       >   END LOOP;
       >   CLOSE emp_cv;
       > END;
       > /
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
PL/SQL procedure successfully completed.

Data type conversion

This section covers the following data type conversions:

Conversion between PL/SQL data types
Conversion between application data types and PL/SQL or SQL data types

Also see type conversion information under "Differences in TimesTen: data type considerations".

Con version between PL/SQL data types

Oracle TimesTen In-Memory Database 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 and 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".

Con version between application data types and PL/SQL or SQL data types

Oracle TimesTen In-Memory Database 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 below shows a sampling of 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 "ODBC to SQL or PL/SQL type mappings" 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 used)
SQL_CHAR	CHAR (bound precision used)
SQL_DATE	DATE
SQL_DECIMAL	NUMBER
SQL_DOUBLE	NUMBER
SQL_FLOAT	BINARY_DOUBLE
SQL_INTEGER	PLS_INTEGER
SQL_NUMERIC	NUMBER
SQL_REAL	BINARY_FLOAT
SQL_REFCURSOR	REF CURSOR
SQL_TIMESTAMP	TIMESTAMP (bound scale used)
SQL_VARCHAR	VARCHAR2 (bound precision used)

Example 3-5 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. Oracle TimesTen In-Memory Database 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-6 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.

Dif ferences in TimesTen: data type considerations

This section covers the following TimesTen-specific considerations regarding data type support and type conversions:

Conversion between PL/SQL and TimesTen SQL data types
Date and timestamp formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT
Non-supported data types

Con version between PL/SQL and TimesTen SQL data types

Oracle TimesTen In-Memory Database 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 bi-directional.

Table 3-5 Supported conversions between PL/SQL and TimesTen SQL data types

Type Family	NUMERIC	CHARACTER	BINARY	DATETIME	INTERVAL	ROWID
NUMERIC	Y	Y
CHARACTER	Y	Y	Y	Y	Y	Y
DATETIME		Y		Y
TIME		Y
ROWID		Y				Y
BINARY		Y	Y			Y

Table 3-6 summarizes the TimesTen data types and suggestions for PL/SQL type mappings:

Table 3-6 Data type usage and sizes

TimesTen data type	Description
TT_TINYINT	Unsigned integer 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	Signed 16-bit integer 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	Signed integer in the range -2,147,483,648 to 2,147,483,647. Equivalent to PLS_INTEGER.
TT_BIGINT	Signed 8-byte integer in 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.

Note:

See "Non-supported data types".

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

Da te and timestamp formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT

TimesTen does not support user-specified NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT settings. In TimesTen:

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

The SQL and PL/SQL TO_DATE and TO_CHAR functions can be used to specify other desired formats. See "Expressions" in Oracle TimesTen In-Memory Database SQL Reference for details of these functions.

Non-supported data types

Note the following non-support of data types:

PL/SQL data type categories: PL/SQL in TimesTen does not support large objects (LOBs), 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.

3 Data Types in PL/SQL in TimesTen

Understanding the data type environments

Understanding and Using PL/SQL data types

PL/SQL data type categories

Predefined PL/SQL scalar data types

PLS_INTEGER and BINARY_INTEGER data types

SIMPLE_INTEGER data type

ROWID data type

PL/SQL composite data types

Using collections

Using records

PL/SQL REF CURSORs

Data type conversion

Conversion between PL/SQL data types

Conversion between application data types and PL/SQL or SQL data types

Differences in TimesTen: data type considerations

Conversion between PL/SQL and TimesTen SQL data types

Date and timestamp formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT

Non-supported data types

PL S_INTEGER and BINARY_INTEGER data types

SIM PLE_INTEGER data type

RO WID data type

PL/SQL comp osite data types

U sing c ollections

Us ing records

PL /SQL REF CURSORs

Con version between PL/SQL data types

Con version between application data types and PL/SQL or SQL data types

Dif ferences in TimesTen: data type considerations

Con version between PL/SQL and TimesTen SQL data types

Da te and timestamp formats: NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT