Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide
Release 11.2.1

Part Number E13076-06
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

1 Introduction to PL/SQL in the TimesTen Database

Oracle TimesTen In-Memory Database supports PL/SQL (Procedural Language Extension to SQL), a programming language that enables you to integrate procedural constructs with SQL in your database. PL/SQL is an integral part of Oracle Database. As such, many of the PL/SQL features present in Oracle 11g ( are also present in TimesTen. In addition, PL/SQL operates in essentially the same way in TimesTen as in Oracle.

This chapter provides a brief introduction to TimesTen PL/SQL, covering the following topics:

Features of PL/SQL in TimesTen

PL/SQL support in TimesTen enables you to do the following:

TimesTen PL/SQL components and operations

This section provides an overview of PL/SQL operations in TimesTen, including discussion of how an application interacts with PL/SQL and how PL/SQL components interact with other components of TimesTen. The following topics are covered:

Application interaction with TimesTen and PL/SQL

Figure 1-1 shows the PL/SQL components and their interactions with each other and with other TimesTen components during PL/SQL operations.

Figure 1-1 TimesTen PL/SQL components

Description of Figure 1-1 follows
Description of "Figure 1-1 TimesTen PL/SQL components"

An application uses the API of its choice—ODBC, JDBC, OCI, Pro*C, or TTClasses—to send requests to the database. ODBC is the TimesTen native API, so each of the other APIs ultimately calls the ODBC layer.

The ODBC driver calls the TimesTen SQL parser to examine each incoming request and determine whether it is SQL or PL/SQL. The request is then passed to the appropriate subsystem within TimesTen. PL/SQL source and SQL statements are compiled, optimized and executed by the PL/SQL subsystem and SQL subsystem, respectively.

The PL/SQL compiler is responsible for generating executable code from PL/SQL source, while the SQL compiler does the same for SQL statements. Each compiler generates intermediate code that can then be executed by the appropriate PL/SQL or SQL execution engine. This executable code, along with metadata about the PL/SQL blocks, is then stored in tables in the database.

When PL/SQL blocks are executed, the PL/SQL execution engine is invoked. As PL/SQL blocks in turn invoke SQL, the PL/SQL execution engine will call the TimesTen SQL compiler and the TimesTen SQL execution engine to handle SQL execution.


The introduction of PL/SQL into TimesTen has little impact on applications that do not use it. If applications execute SQL directly, then requests are passed from the TimesTen ODBC driver to the TimesTen SQL compiler and execution engine in the same way they were in previous releases.

PL/SQL in TimesTen versus PL/SQL in Oracle Database

PL/SQL processing in TimesTen is largely identical to its processing in Oracle Database. The PL/SQL compiler and execution engine that are included with TimesTen originated in Oracle Database, and the relationship between PL/SQL components and the SQL compiler and execution engine is comparable. The tables used to store PL/SQL units are the same in TimesTen and Oracle, as are the views that are available to query information about stored PL/SQL units.

Beyond these basic similarities, however, are some potentially significant differences. These are detailed in the following subsections:

SQL statements in PL/SQL blocks

In TimesTen, as in Oracle Database, PL/SQL blocks may include SQL statements. Consider the anonymous block in the following example:

Command> create table tab2 (x number, last_name VARCHAR2 (25) INLINE NOT NULL);
Command> declare
       >    x number;
       > begin
       >    select salary into x from employees where last_name = 'Whalen';
       >    insert into tab2 values(x, 'Whalen');
       > end;
       > /

PL/SQL procedure successfully completed.

The PL/SQL compiler in TimesTen calls a copy of the Oracle SQL parser to analyze and validate the syntax of such SQL statements. This Oracle parser is included in TimesTen for this purpose. As part of this processing, PL/SQL may rewrite parts of the SQL statements (for example, by removing INTO clauses or replacing PL/SQL variables with binds). This processing is identical in TimesTen and in Oracle Database. The rewritten SQL statements are then included in the executable code for the PL/SQL block. When the PL/SQL block is executed, these SQL statements are compiled and executed by the TimesTen SQL subsystem.

In Oracle Database, the same SQL parser is used by the PL/SQL compiler and the SQL compiler. In TimesTen, however, different SQL parsers are used. TimesTen PL/SQL uses the Oracle SQL parser, while TimesTen SQL uses the native TimesTen SQL parser. This difference is typically, but not always, transparent to the end user. In particular, be aware of the following:

  • SQL statements in TimesTen PL/SQL programs must obey Oracle SQL syntax. While TimesTen SQL is generally a subset of Oracle SQL, there are some expressions that are permissible in TimesTen SQL but not in Oracle SQL. Such TimesTen-specific SQL operations cannot be used within PL/SQL except by using dynamic SQL through EXECUTE IMMEDIATE statements or the DBMS_SQL package. See "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)".

  • SQL statements that would be permissible in Oracle Database will be accepted by the PL/SQL compiler as valid even if they cannot be executed by TimesTen. If SQL features are used that TimesTen does not support, compilation of a PL/SQL block may be successful, but a runtime error would occur when the PL/SQL block is executed.

Execution of PL/SQL from SQL

In Oracle Database, PL/SQL blocks can invoke SQL statements, and SQL statements can in turn invoke PL/SQL functions. For example, a stored procedure can invoke an UPDATE statement that employs a user-written PL/SQL function in its WHERE clause.

In TimesTen, a SQL statement cannot invoke a PL/SQL function.

In addition, TimesTen does not support triggers.

Audiences for this document

There are two primary developer audiences for this document:

The following subsections note areas of particular interest in this document for each audience.

Developers experienced with Oracle Database and Oracle PL/SQL

Developers experienced with Oracle PL/SQL can bypass much of this document, which covers many general concepts of PL/SQL. Likely areas of interest, particularly differences in PL/SQL functionality between Oracle and TimesTen, include the following. Note that TimesTen-specific considerations are discussed at the end of Chapter 2, Chapter 3, and Chapter 4 and throughout Chapter 9.

Developers experienced with TimesTen

Most of this document is geared toward readers without prior PL/SQL experience, especially prior TimesTen users who are not familiar with PL/SQL, and nearly the entire document should be useful. In particular, Chapter 2, "Programming Features in PL/SQL in TimesTen," will help these readers get started and Chapter 5, "Examples Using TimesTen SQL in PL/SQL," includes some additional examples.

Chapter 9, "TimesTen PL/SQL Support: Reference Summary," is geared toward differences between TimesTen PL/SQL and Oracle PL/SQL and may be of less interest.

About the TimesTen PL/SQL demos

After you have configured your environment, you can confirm that everything is set up correctly by compiling and running the TimesTen Quick Start demo applications. Refer to the Quick Start welcome page at install_dir/quickstart.html, especially the links under SAMPLE PROGRAMS, for information about the following: