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 allows you to integrate procedural constructs with SQL in your TimesTen database. PL/SQL is an integral part of Oracle Database. As such, many of the PL/SQL features present in Oracle 11g (11.1.0.7) 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

• Audiences for this document

• About the TimesTen PL/SQL demos

Features of PL/SQL in TimesTen

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

• Take full advantage of the PL/SQL programming language.

• Execute PL/SQL from your client applications that use these APIs:

  • JDBC

  • OCI

  • ODBC

  • Pro*C/C++

  • TTClasses

• Execute TimesTen SQL from PL/SQL.

• CREATE, ALTER, and DROP standalone procedures, functions, packages and package bodies.

• Use PL/SQL packages to extend your database functionality and to provide PL/SQL access to SQL features.

• Handle exceptions and errors in your PL/SQL applications.

• Set connection attributes in your TimesTen database to customize your PL/SQL environment.

• ALTER session parameters so you can manage your PL/SQL environment.

• Display PL/SQL metadata in your in TimesTen database by using PL/SQL system views.

Audiences for this document

There are two primary developer audiences for this document:

• Developers experienced with Oracle Database and Oracle PL/SQL who want to learn how to use PL/SQL in TimesTen. In particular, they want to learn the differences between PL/SQL in Oracle and PL/SQL in TimesTen.

• Developers experienced with TimesTen who are not familiar with PL/SQL. These readers need general information about PL/SQL.

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.

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

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

• 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 PL/SQL packages that TimesTen supports.

• Chapter 9, "TimesTen PL/SQL Support: Reference Summary." This reference chapter provides a thorough and low-level treatment of differences between TimesTen PL/SQL and Oracle 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 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:

• Demo schema and setup

  The build_sampledb script creates a sample database and demo schema. You must run this before you start using the demos.

• Demo environment and setup

  The ttquickstartenv script, a superset of the ttenv script generally used for TimesTen setup, sets up the demo environment. You must run this each time you enter a session where you want to compile and run any of the demos.

• Demos and setup

  TimesTen provides demos for PL/SQL in a subdirectory under the quickstart/sample_code directory. For instructions on running the demos, see the README file in the subdirectory.

• What the demos do

  A synopsis of each demo is provided.