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

PL/SQL components interact with each other and with other TimesTen components during PL/SQL operations.

Figure 1-1 TimesTen PL/SQL Components

Description of "Figure 1-1 TimesTen PL/SQL Components"

An application uses the API of its choice—ODBC, JDBC, OCI, Pro*C, ODP.NET, 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 calls the TimesTen SQL compiler and the TimesTen SQL execution engine to handle SQL execution.

Note:

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 as in previous releases.

PL/SQL in TimesTen Versus PL/SQL in Oracle Database

This section discusses PL/SQL processing and the differences between TimesTen and Oracle Database.

About PL/SQL Processing

PL/SQL processing in TimesTen is largely identical to the 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 Database, 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 sections that follow

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 Database SQL parser to analyze and validate the syntax of such SQL statements. This Oracle Database 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 Database 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 Database SQL syntax. While TimesTen SQL is generally a subset of Oracle Database SQL, there are some expressions that are permissible in TimesTen SQL but not in Oracle Database 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 are 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. (See XLA and TimesTen Event Management in Oracle TimesTen In-Memory Database C Developer's Guide for information about XLA, a high-performance, asynchronous TimesTen alternative to triggers.)