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.
The following topics are covered in this section:
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|
Single values with no internal components
Internal components that are either scalar or composite
Pointers to other data items such as REF CURSORs
Predefined PL/SQL Scalar Data Types
Scalar data types store single values with no internal components.
These are covered in the following sections:
Scalar Data Types and Type Families
There are predefined PL/SQL scalar data types and type families.
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
Declaring Variables of Scalar Data Types
This examples declares 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; ...
PLS_INTEGER and BINARY_INTEGER Data Types
BINARY_INTEGER data types are identical and are used interchangeably in this document.
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_INTEGERvalues require less storage.
PLS_INTEGERoperations use hardware arithmetic, so they are faster than
NUMBERoperations, 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.
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.
SIMPLE_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 is never null and overflow checking is unnecessary. Without the overhead of checking for null values and overflow,
SIMPLE_INTEGER provides better performance than
See SIMPLE_INTEGER Subtype of PLS_INTEGER in Oracle Database PL/SQL Language Reference.
ROWID Data Type
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 Pseudocolumn in Oracle TimesTen In-Memory Database SQL Reference.
LOB Data Types
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 Classic.
Also see Large Objects (LOBs).
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.
The following sections discuss the use of composite data types:
See PL/SQL Collections and Records in Oracle Database PL/SQL Language Reference.
TimesTen Support for Composite Data Types
TimesTen supports these composite data types.
Associative array (index-by table)
Associative arrays, nested tables, and varrays are also referred to as collections.
Using Collections in PL/SQL
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.
The following 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
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. In TimesTen, however, 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.
Using Records in PL/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 an example in PL/SQL Variables and Constants.
This example declares 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; /
Using associative arrays from applications
Associative arrays, formerly known as index-by tables or PL/SQL tables, are supported as
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 (
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
declare TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; x VARCHARARRTYP; ...
See below and "Using Associative Arrays" for examples.
Also see Binding Associative Arrays in TimesTen OCI and Associative Array Bindings in TimesTen Pro*C/C++" in Oracle TimesTen In-Memory Database C Developer's Guide, and Working with 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.
Note the following restrictions in TimesTen:
The following types are not supported in binding associative arrays: LOBs, REF CURSORs,
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 manipulates an associative array, effectively binding it from
ttIsql and printing the array.
Command> var lngvc 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 : One LNGVC : <NULL> LNGVC : <NULL> LNGVC : <NULL> LNGVC : <NULL> LNGVC : <NULL> LNGVC : <NULL> LNGVC : <NULL> LNGVC : <NULL> LNGVC : 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.
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
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 ...
The following 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
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;