| Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide Release 11.2.1 Part Number E13076-06 |
|
|
View PDF |
| Oracle® TimesTen In-Memory Database PL/SQL Developer's Guide Release 11.2.1 Part Number E13076-06 |
|
|
View PDF |
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 (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:
PL/SQL support in TimesTen enables 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:
ODBC
JDBC
OCI
Pro*C/C++
TTClasses
Execute TimesTen SQL from PL/SQL.
Create, alter, or 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 database to customize your PL/SQL environment.
Alter session parameters so you can manage your PL/SQL environment.
Display PL/SQL metadata in your database by using PL/SQL system views.
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:
Figure 1-1 shows the PL/SQL components and their interactions with each other and with other TimesTen components during PL/SQL operations.
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.
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 they were in previous releases.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:
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.
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.
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 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 detailed treatment of differences between TimesTen PL/SQL and Oracle PL/SQL.
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.
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 when you click PL/SQL under SAMPLE PROGRAMS.
|
 Copyright © 1996, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|