1 Introduction to PL/SQL in the TimesTen Database

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

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 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 following subsections:

• SQL statements in PL/SQL blocks

• Execution of PL/SQL from SQL

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.

Developers experienced with Oracle Database and Oracle Database PL/SQL

Developers experienced with Oracle Database 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 Database 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.

• "How to execute PL/SQL procedures and functions": This includes a comparison between how you can execute them in TimesTen and in Oracle Database.

• "Differences in TimesTen: transaction behavior": This discusses cursor behavior when a transaction ends in TimesTen.

• "Differences in TimesTen: data type considerations": This includes TimesTen-specific conversions, and types that TimesTen does not support.

• "Differences in TimesTen: exception handing and error behavior": This describes differences in error support, handling, and reporting.

• Chapter 6, "PL/SQL Installation and Environment": This includes discussion of TimesTen connection attributes.

• Chapter 8, "TimesTen Supplied PL/SQL Packages": This documents the subset of Oracle Database PL/SQL packages that TimesTen supports.

• Chapter 9, "TimesTen PL/SQL Support: Reference Summary": This reference chapter provides a detailed treatment of differences between TimesTen PL/SQL and Oracle Database PL/SQL.

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 Database PL/SQL and may be of less interest.