9 TimesTen PL/SQL Support: Reference Summary

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

ALTER {PROCEDURE| FUNCTION | PACKAGE} statements

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.

ALTER SESSION statement

Changes session parameters dynamically.

          Y

In TimesTen you can use ALTER SESSION to set some PL/SQL connection attributes as discussed in "PL/SQL connection attributes".

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

See "PL/SQL variables and constants".

AUTONOMOUS_TRANSACTION pragma

Marks a routine as autonomous.

          N

TimesTen does not support autonomous transactions.

Block declaration

Declares a block, the basic unit of a PL/SQL source program.

          Y

See "PL/SQL blocks".

BULK COLLECT clause

Select multiple rows.

          Y

This clause can be used with the SELECT statement in PL/SQL to retrieve rows without using a cursor. See "FORALL and BULK COLLECT operations" and "Examples using FORALL and BULK COLLECT".

CALL statement

Executes a routine from within SQL.

          Y

In TimesTen, use the CALL statement to execute PL/SQL stored procedures and functions, as in Oracle Database, or TimesTen built-in procedures. (For TimesTen built-in procedures, use EXECUTE IMMEDIATE if CALL is inside PL/SQL.) See "How to execute PL/SQL procedures and functions" and Example 5-19, "Using EXECUTE IMMEDIATE to call ttConfiguration".

CASE statement

Evaluates an expression, compares it against several values, and takes action according to the comparison that is true.

          Y

See "PL/SQL control structures".

CLOSE statement

Closes a 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 that are all of the same type.

          Y

Examples include associative arrays (index-by tables or PL/SQL tables), nested tables, and varrays.

TimesTen supports all three of these collection types in PL/SQL programs, but supports only associative arrays as bound parameters between PL/SQL and applications written in other languages (such as OCI or JDBC).

See "Using collections in PL/SQL".

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 COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT, NEXT, PRIOR, and TRIM.

Comments

Text included within your code for explanatory purposes.

          Y

Single-line and multi-line comments are supported.

COMMIT statement

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: COMMIT and ROLLBACK statements close all cursors in TimesTen.

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

See "PL/SQL variables and constants".

CONTINUE statement

Exits the current iteration of a loop and transfers control to the next iteration.

          Y

See "CONTINUE statement".

CREATE FUNCTION statement

Creates a PL/SQL function.

          Y

CREATE FUNCTION is supported in TimesTen, but the AS LANGUAGE, AS EXTERNAL, and PIPELINED clauses are not supported.

See "PL/SQL procedures and functions". Also see "CREATE FUNCTION" in Oracle TimesTen In-Memory Database SQL Reference.

You are not required to run DBMSSTDX.SQL in TimesTen.

CREATE LIBRARY statement

Creates a schema object associated with an operating system shared library.

          N

CREATE LIBRARY is not supported in TimesTen.

CREATE PACKAGE statement

CREATE PACKAGE BODY statement

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 DBMSSTDX.SQL in TimesTen.

CREATE PROCEDURE statement

Creates a PL/SQL procedure.

          Y

CREATE PROCEDURE is supported in TimesTen, but the AS LANGUAGE and AS EXTERNAL clauses are not supported.

See "PL/SQL procedures and functions". Also see "CREATE PROCEDURE" in Oracle TimesTen In-Memory Database SQL Reference.

Note: You are not required to run DBMSSTDX.SQL in TimesTen.

CREATE TYPE statement

Creates a user-defined object type or collection type.

          N

TimesTen does not support CREATE TYPE.

CURRENT_DATE function

Returns the current date in the session time zone.

          Y

In TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.

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: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.

The implicit cursor (SQL) has additional attributes: %BULK_ROWCOUNT and %BULK_EXCEPTIONS.

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

See "Use of cursors in PL/SQL programs".

Cursor variables (REF CURSORs)

Act as handles to cursors over SQL result sets.

          Y

TimesTen supports OUT REF CURSORs, one per statement.

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.

DELETE statement

Deletes rows from a table.

          Y

See "DELETE" in Oracle TimesTen In-Memory Database SQL Reference.

DROP { PROCEDURE | FUNCTION | PACKAGE } statement

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

(This is self-explanatory.)

          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.

EXCEPTION_INIT pragma

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 RAISE statement. To handle raised exceptions, write separate routines called exception handlers.

See Chapter 4, "Errors and Exception Handling".

EXECUTE IMMEDIATE statement

Builds and executes a dynamic SQL statement.

          Y

TimesTen supports this as Oracle Database does to execute a SQL DML or DDL statement, execute a PL/SQL anonymous block, or call a PL/SQL stored procedure or function. See "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)".

In TimesTen, the EXECUTE IMMEDIATE statement can also be used to execute TimesTen built-in procedures and TimesTen-specific SQL features (such as SELECT FIRST).

Executing PL/SQL from client applications

(This is self-explanatory.)

          Y

TimesTen supports ODBC, OCI, Pro*C/C++, TTClasses (a set of TimesTen C++ classes), JDBC, and ODP.NET.

Executing PL/SQL from SQL

(This is self-explanatory.)

          N

In TimesTen, you cannot execute PL/SQL from a SQL statement (static or dynamic).

EXIT statement

Exits a loop and transfers control to the end of the loop.

          Y

See Example 6-2, "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

See "Expressions" in Oracle Database PL/SQL Language Reference.

FETCH statement

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

FORALL statement

Bulk-binds input collections before sending them to the SQL engine.

          Y

See "FORALL and BULK COLLECT operations".

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 CALL statement, but not from any other SQL statement. See "How to execute PL/SQL procedures and functions".

Use the CREATE FUNCTION statement in TimesTen SQL to create stored functions. See "PL/SQL procedures and functions". Also see "CREATE FUNCTION" in Oracle TimesTen In-Memory Database SQL Reference.

Also refer to the table entry below for "Procedure declaration and definition".

GOTO statement

Branches unconditionally to a statement label or block label.

          Y

See "GOTO Statement" in Oracle Database PL/SQL Language Reference.

IF statement

Executes or skips a sequence of statements depending on the value of the associated boolean expression.

          Y

See "Conditional control".

Initialization parameters

Initial parameter settings for an Oracle database.

          n/a

TimesTen connection attributes are equivalent. See that entry above.

INLINE pragma

Specifies whether a subprogram call is to be inline.

          Y

See "INLINE Pragma" in Oracle Database PL/SQL Language Reference.

INSERT statement

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'

LOCK TABLE statement

Locks database tables in a specified lock mode.

          N

TimesTen does not support the LOCK TABLE statement.

LOOP statement

Executes a sequence of statements multiple times. Can be used, for example, in implementing a FOR loop or WHILE loop.

          Y

See Example 2-8, "Using a WHILE loop". Also see "Basic LOOP Statement" in Oracle Database PL/SQL Language Reference.

MERGE statement

Allows you to select rows from one or more sources for update or insertion into a target table.

          Y

See "MERGE" in Oracle TimesTen In-Memory Database SQL Reference.

Native dynamic SQL execution

Processes most dynamic SQL statements through the EXECUTE IMMEDIATE statement.

          Y

See the EXECUTE IMMEDIATE entry above.

Non-ASCII names

Use of non-ASCII character sets in names of tables, columns, procedures, functions, and other database objects.

          N

This is not supported in TimesTen.

Non-uppercase names

Use of quoted non-uppercase names of tables, columns, procedures, functions, and other database objects.

          N

This is not supported in TimesTen (such as lowercase and MixedCase). For example, you cannot have the following:

create or replace procedure "MixedCase" as
begin
 ...
end;

NULL statement

This is a no-operation statement. Control is passed to the next statement without any action.

          Y

See "NULL Statement" in Oracle Database PL/SQL Language Reference.

Object type declaration

Specifies a custom object type, which is created in SQL and stored in the database.

          N

TimesTen does not support object types at the database level. For example, CREATE TYPE is not supported.

OPEN statement

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

OPEN-FOR statement

Executes the SELECT statement associated with a cursor variable (REF CURSOR). Positions the cursor variable before the first row in the result set.

          Y

  

Package declaration

Specifies a package, which is a database object that groups logically related PL/SQL types, items, and subprograms.

          Y

Use SQL statements CREATE PACKAGE and CREATE PACKAGE BODY.

See "PL/SQL packages". Also see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for information about the CREATE statements.

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 CALL statement, but not from any other SQL statement. See "How to execute PL/SQL procedures and functions".

Use the CREATE PROCEDURE statement in TimesTen SQL to create stored procedures. See "PL/SQL procedures and functions". Also see "CREATE PROCEDURE" in Oracle TimesTen In-Memory Database SQL Reference.

Also refer to the table entry above for "Function declaration and definition".

RAISE statement

Stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler.

          Y

See "Using the RAISE statement".

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 in PL/SQL".

RESTRICT_REFERENCES pragma

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

TimesTen does not support this.

RETURN statement

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.

RETURNING INTO clause

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

ROLLBACK statement

Undoes database changes made during the current transaction.

          Y

See "ROLLBACK" in Oracle TimesTen In-Memory Database SQL Reference.

Important: COMMIT and ROLLBACK statements close all cursors in TimesTen.

%ROWTYPE attribute

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

SAVEPOINT statement

Names and marks the current point in the processing of a transaction.

          N

TimesTen does not support savepoints.

SELECT INTO statement

Retrieves values from one row of a table (SELECT) and then stores the values in either variables or a record. With the BULK COLLECT clause (discussed above), this statement retrieves an entire result set at one time.

          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.

SERIALLY_REUSABLE pragma

Indicates that package state is required only for the duration of one call to the server.

          N

TimesTen does not support the SERIALLY_REUSABLE pragma.

SET TRANSACTION statement

Begins a read-only or read and write transaction.

          N

TimesTen does not support the SET TRANSACTION statement.

SOUNDEX SQL function

Returns a character string containing the phonetic representation of a char.

          Y

See "SOUNDEX" in Oracle TimesTen In-Memory Database SQL Reference.

SQL cursor

Either explicit or implicit, is used to handle the result set of a SELECT statement.

          Y

See "Use of cursors in PL/SQL programs".

SQLCODE function

Returns number code of the most recent exception.

          Y

Given the same error condition, error codes returned by the built-in function SQLCODE are the same in TimesTen as in Oracle Database, although the SQLERRM returns may be different.

This is also noted in "TimesTen error messages and SQL codes".

SQLERRM function

Returns the error message associated with the error-number argument.

          Y

Given the same error condition, error messages returned by the built-in function SQLERRM are not necessarily the same in TimesTen as in Oracle Database, although SQLCODE returns are the same.

This is also noted in "TimesTen error messages and SQL codes".

Supplied packages

These are PL/SQL packages supplied with the database.

          Y

TimesTen provides a subset of the Oracle Database PL/SQL supplied packages.

See Chapter 8, "TimesTen Supplied PL/SQL Packages".

System tables and views

These are 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 Views Reference.

SYSTIMESTAMP

Returns the system date, including fractional seconds and time zone, of the system on which the database resides.

          N

TimesTen cannot support this because the return type, TIMESTAMP WITH TIME ZONE, is not currently supported.

As an alternative, you can use SELECT tt_sysdate FROM dual using dynamic SQL:

declare
  ts timestamp;
begin
  execute immediate 
    'select tt_sysdate from dual'
     into ts;
-- ts has millisecond resolution
end;

Triggers

These are 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, but you can achieve similar functionality using XLA. See "XLA and TimesTen Event Management" in Oracle TimesTen In-Memory Database C Developer's Guide.

ttPLSQLMemoryStats built-in procedure

Returns statistics about library cache performance and activity.

          Y

See "ttPLSQLMemoryStats" in Oracle TimesTen In-Memory Database Reference.

Note: In Oracle Database, the V$LIBRARYCACHE system view provides the same statistical information.

%TYPE attribute

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

See "PL/SQL variables and constants".

UPDATE statement

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.

V$LIBRARYCACHE system view

In Oracle Database, use this system view to return statistics about library cache performance and activity.

          n/a

In TimesTen, use the ttPLSQLMemoryStats built-in procedure to retrieve the same statistical information. See that entry above.