The purpose of this chapter is to summarize PL/SQL language elements and features and compare their support in TimesTen to their support in Oracle. In the Oracle Database documentation, many of these features are covered in "PL/SQL Language Elements" in Oracle Database PL/SQL Language Reference.
Table 9-1 PL/SQL Language Element and Feature Support in TimesTen
Feature Name | Description | Supported? | Example/Comment |
---|---|---|---|
|
Recompiles a PL/SQL procedure, function, or package. |
Y |
Syntax and semantics are the same as in Oracle Database. For information about these statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference. |
|
Changes session parameters dynamically. |
Y |
In TimesTen you can use For more information on this statement in TimesTen, see "ALTER SESSION" in Oracle TimesTen In-Memory Database SQL Reference. |
Assignment statement |
Sets current value of a variable, parameter, or element. |
Y |
|
|
Marks a routine as autonomous. |
N |
TimesTen does not support autonomous transactions. |
Block declaration |
Basic unit of a PL/SQL source program. |
Y |
See "PL/SQL blocks". |
|
Can be used to select multiple rows. |
Y |
This clause can be used with the |
|
Executes a routine from within SQL. |
Y |
In TimesTen, use the |
|
Evaluates an expression, compares it against several values, and takes action according to the comparison that is |
Y |
|
|
Closes cursor or cursor variable. |
Y |
See Example 2-13, "Using a cursor to retrieve information about an employee" (among others). |
Collection definition |
Specifies a collection, which is an ordered group of elements, all of the same type. |
Y |
Examples include: associative arrays (index-by tables), nested tables, and varrays. While TimesTen supports these types, it does not support passing them between PL/SQL and applications written in other languages. See "Using collections". |
Collection methods |
Built-in subprograms that operate on collections and are called using "dot" notation. |
Y |
See "Collection Methods" in Oracle Database PL/SQL Language Reference. Examples include |
Comments |
Text included within your code for explanatory purposes. |
Y |
Single-line and multi-line comments are supported. |
|
Ends the current transaction and makes permanent all changes performed in the transaction. |
Y |
See "COMMIT" in Oracle TimesTen In-Memory Database SQL Reference. Important: |
Connection attributes |
Equivalent to initialization parameters in Oracle Database. |
Y |
See "PL/SQL connection attributes". Also see "PL/SQL first connection attributes" and "PL/SQL general connection attributes" in Oracle TimesTen In-Memory Database Reference. |
Constant and variable declarations |
Specify constants and variables to be used in PL/SQL code, in the declarative part of any PL/SQL block, subprogram, or package. |
Y |
|
|
Exits the current iteration of a loop and transfers control to the next iteration. |
Y |
See "CONTINUE statement". |
|
Creates a PL/SQL function. |
Y |
See "PL/SQL procedures and functions". Also see "CREATE FUNCTION" in Oracle TimesTen In-Memory Database SQL Reference. You are not required to run |
|
Creates a schema object associated with an operating system shared library. |
N |
|
|
These statements are used together to create a PL/SQL package definition and package body. |
Y |
Syntax and semantics are the same as in Oracle Database. See "PL/SQL packages". Also see "CREATE PACKAGE" and "CREATE PACKAGE BODY" in Oracle TimesTen In-Memory Database SQL Reference. You are not required to run |
|
Creates a PL/SQL procedure. |
Y |
See "PL/SQL procedures and functions". Also see "CREATE PROCEDURE" in Oracle TimesTen In-Memory Database SQL Reference. You are not required to run |
|
Creates a user-defined object type or collection type. |
N |
TimesTen does not support |
Cursor attributes |
Appended to the cursor or cursor variable to return useful information about the execution of a data manipulation statement. |
Y |
Explicit cursors and cursor variables have four attributes: The implicit cursor (SQL) has additional attributes: See "Using the %ROWCOUNT and %NOTFOUND attributes" and "Using FORALL with SQL%BULK_ROWCOUNT". Also see "Named Cursor Attribute" in Oracle Database PL/SQL Language Reference. |
Cursor declaration |
Declares a cursor. To execute a multi-row query, TimesTen opens an unnamed work area that stores processing information. A cursor lets you name the work area, access the information, and process the rows individually. |
Y |
|
Cursor variables (REF CURSORs) |
Act as handles to cursors over SQL result sets. |
Y |
TimesTen supports See "PL/SQL REF CURSORs". |
Database links (dblinks) |
A pointer that defines a one-way communication path from an Oracle Database server to another database server. |
N |
TimesTen does not support database links. |
|
Deletes rows from a table. |
Y |
See "DELETE" in Oracle TimesTen In-Memory Database SQL Reference. |
|
Removes a PL/SQL procedure, function, or package, as specified. |
Y |
Syntax and semantics are the same as in Oracle Database. You can refer to information about these statements in "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference. |
Error reporting |
Y |
TimesTen applications report errors using Oracle Database error codes instead of TimesTen error codes. The error messages that accompany the error codes are either TimesTen error messages or Oracle Database error messages. |
|
|
Associates a user-defined exception with a TimesTen error number. |
Y |
See "EXCEPTION_INIT Pragma" in Oracle Database PL/SQL Language Reference. |
Exception definition |
Specifies an exception, which is a runtime error or warning condition. Can be predefined or user-defined. |
Y |
Predefined conditions are raised implicitly. User-defined exceptions are raised explicitly by the |
|
Builds and executes a dynamic SQL statement. |
Y |
TimesTen supports this to execute SQL DML and DDL statements, but not to execute PL/SQL. See "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)". In TimesTen, the |
Executing PL/SQL from client applications |
Y |
Oracle TimesTen In-Memory Database supports ODBC, OCI, Pro*C/C++, TTClasses (a set of TimesTen C++ classes), and JDBC. |
|
Executing PL/SQL from SQL |
N |
In TimesTen, you cannot execute PL/SQL from either a static or dynamic SQL statement. |
|
|
Exits a loop and transfers control to the end of the loop. |
Y |
See Example 6-3, "Use ALTER SESSION to change attribute settings" (among others). |
Expression definition |
Specifies an expression, which is a combination of operands (variables, constants, literals, operators, and so on) and operators. The simplest expression is a single variable. |
Y |
|
|
Retrieves rows of data from the result set of a multi-row query. |
Y |
See Example 2-13, "Using a cursor to retrieve information about an employee" (among others). |
|
Bulk-binds input collections before sending them to the SQL engine. |
Y |
|
Function declaration and definition |
Specifies a subprogram or stored program that can be declared and defined in a PL/SQL block or package and returns a single value. |
Y |
In TimesTen, a stored function or procedure can be executed in an anonymous block or through a Use the Also refer to the table entry below for "Procedure declaration and definition". |
|
Branches unconditionally to a statement label or block label. |
Y |
See "GOTO Statement" in Oracle Database PL/SQL Language Reference. |
|
Executes or skips a sequence of statements depending on the value of the associated boolean expression. |
Y |
|
Initialization parameters |
Initial parameter settings for an Oracle Database. |
TimesTen connection attributes are equivalent. See that entry above. |
|
|
Specifies whether a subprogram call is to be inline. |
Y |
See "INLINE Pragma" in Oracle Database PL/SQL Language Reference. |
|
Inserts one or more rows of data into a table. |
Y |
See "Example using the INSERT statement". Also see "INSERT" in Oracle TimesTen In-Memory Database SQL Reference. |
Literal declaration |
Specifies a numeric, character string, or boolean value. |
Y |
Examples: Numeric literal: 135 String literal: 'TimesTen' |
|
Locks database tables in a specified lock mode. |
N |
TimesTen does not support the |
|
Executes a sequence of statements multiple times. Can be used, for example, in implementing a |
Y |
See Example 2-8, "Using a WHILE loop". Also see "Basic LOOP Statement" in Oracle Database PL/SQL Language Reference. |
|
Allows you to select rows from one or more sources for update or insertion into a target table. |
Y |
TimesTen SQL statement. See "MERGE" in Oracle TimesTen In-Memory Database SQL Reference. |
Native dynamic SQL execution |
Processes most dynamic SQL statements through the |
Y |
See the |
Use of non-ASCII character sets in names of tables, columns, procedures, functions, and other database objects. |
N |
In TimesTen (unlike in Oracle Database), this is not supported. |
|
Use of quoted non-uppercase names of tables, columns, procedures, functions, and other database objects. |
N |
In TimesTen (unlike in Oracle Database), this is not supported (such as create or replace procedure "MixedCase" as begin ... end; / |
|
|
A no-operation statement. Passes control to the next statement without performing any action. |
Y |
See "NULL Statement" in Oracle Database PL/SQL Language Reference. Also, one is used in Example 3-3, "Declaring a record type". |
Object type declaration |
Specifies a custom object type, which is created in SQL and stored in the database. |
N |
Object types are not supported at the database level. For example, |
|
Executes the query associated with a cursor. Allocates database resources to process the query, and identifies the result set. |
Y |
See Example 2-13, "Using a cursor to retrieve information about an employee". |
|
Executes the |
Y |
See Example 3-4, "Fetch rows from result set of a dynamic multirow query". |
Package declaration |
Specifies a package, which is a database object that groups logically related PL/SQL types, items, and subprograms. |
Y |
TimesTen SQL statements See "PL/SQL packages". Also see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for information about the |
Procedure declaration and definition |
Specifies a subprogram or stored program that can be declared and defined in a PL/SQL block or package and performs a specific action. |
Y |
In TimesTen, a stored procedure or function can be executed in an anonymous block or through a Use the Also refer to the table entry above for "Function declaration and definition". |
|
Stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. |
Y |
|
Record definition |
Defines a record, which is a composite variable that stores data values of different types (similar to a database row). |
Y |
See "Using records". |
|
Asserts that a subprogram (usually a function) in a package specification or object type specification does not read or write database tables or package variables. |
N |
TimesTen ignores this. |
Result cache |
This is a mechanism for caching the results of PL/SQL functions in a shared global area (SGA) that is available to every session that runs your application. |
N |
Oracle TimesTen In-Memory Database does not support the PL/SQL function result cache. |
|
Immediately completes the execution of a subprogram and returns control to the invoker. Execution resumes with the statement following the subprogram call. |
Y |
See "RETURN Statement" in Oracle Database PL/SQL Language Reference. |
|
Specifies the variables in which to store the values returned by the statement to which the clause belongs. |
Y |
See "RETURNING INTO clause" and "Examples using RETURNING INTO". |
|
Undoes database changes made during the current transaction. |
Y |
See "ROLLBACK" in Oracle TimesTen In-Memory Database SQL Reference. Important: |
|
Provides a record type that represents a row in a database table. |
Y |
See Example 2-2, "Assigning values to variables with the assignment operator". |
|
Names and marks the current point in the processing of a transaction. |
N |
TimesTen does not support savepoints. |
|
Retrieves values from one row of a table ( |
Y |
See Example 2-3, "Using SELECT INTO to assign values to variables". Also see "Query Result Set Processing" in Oracle Database PL/SQL Language Reference. |
|
Indicates that package state is required only for the duration of one call to the server. |
N |
TimesTen does not support the |
|
Begins a read-only or read and write transaction. |
N |
TimesTen does not support the |
|
Returns a character string containing the phonetic representation of a |
N |
TimesTen does not support this function. |
SQL cursor |
Either explicit or implicit, is used to handle the result set of a |
Y |
|
|
Returns number code of the most recent exception. |
Y |
Given the same error condition, error codes returned by the built-in function This is also noted in "TimesTen error messages and SQL codes". |
|
Returns the error message associated with the error-number argument. |
Y |
Given the same error condition, error messages returned by the built-in function This is also noted in "TimesTen error messages and SQL codes". |
Supplied packages |
PL/SQL packages supplied with the database. |
Y |
TimesTen provides a subset of the Oracle Database PL/SQL supplied packages. |
System tables and views |
Tables and views provided with the database for administrative purposes. |
Y |
TimesTen supports a subset of the Oracle Database system tables and views. See "System Tables" in Oracle TimesTen In-Memory Database System Tables and Limits Reference. |
Triggers |
Procedures that are stored in the database and activated when specific conditions occur, such as adding a row to a table. |
N |
TimesTen does not support triggers. |
Returns statistics about library cache performance and activity. |
Y |
See "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference. In Oracle Database, use the |
|
|
Lets you use the data type of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the data type. Particularly useful when declaring variables, fields, and parameters that refer to database columns. |
Y |
|
|
Updates the values of one or more columns in all rows of a table or in rows that satisfy a search condition. |
Y |
See "UPDATE" in Oracle TimesTen In-Memory Database SQL Reference. |
|
In Oracle Database, use this system view to return statistics about library cache performance and activity. |
In TimesTen, use the |