3 Data Types in PL/SQL in TimesTen

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.

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.

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/SQL composite 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 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:

• Using collections in PL/SQL

• Using records in PL/SQL

• Using associative arrays from applications

See "PL/SQL Collections and Records" in Oracle Database PL/SQL Language Reference for additional information.

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.

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

declare
   TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
   x VARCHARARRTYP;
   ...

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

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

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 meta type, 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
       > ...

create or replace package foo_pack is 
   type cursor_out is ref cursor;
   procedure get_emp (results out cursor_out);
end foo_pack;

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;

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;

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

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.

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.

Conversion between PL/SQL and TimesTen SQL data types

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-6 that follows summarizes the TimesTen data types and suggestions for PL/SQL type mappings.

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

Zero-length strings in TimesTen type mode

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.