Language Elements and Features Supported by TimesTen

This section describes language elements and features supported by TimesTen, with any special notes along with references for additional information and examples.

Table 8-1 PL/SQL Language Element and Feature Support in TimesTen

Feature Name	Description	Example/Comment
ALTER {PROCEDURE| FUNCTION | PACKAGE} statements	Recompiles a PL/SQL procedure, function, or package.	Syntax and semantics are the same as in Oracle Database. See SQL Statements in Oracle TimesTen In-Memory Database SQL Reference.
ALTER SESSION statement	Changes session parameters dynamically.	In TimesTen, you can use ALTER SESSION to set some PL/SQL connection attributes as discussed in PL/SQL Connection Attributes. See ALTER SESSION in Oracle TimesTen In-Memory Database SQL Reference.
Assignment statement	Sets current value of a variable, parameter, or element.	See PL/SQL Variables and Constants.
Block declaration	Declares a block, the basic unit of a PL/SQL source program.	See PL/SQL Blocks.
BULK COLLECT clause	Select multiple rows.	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.	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 Executing Procedures and Functions and the example in Using EXECUTE IMMEDIATE to Call a TimesTen Built-In Procedure.
CASE statement	Evaluates an expression, compares it against several values, and takes action according to the comparison that is true.	See PL/SQL Control Structures.
CLOSE statement	Closes a cursor or cursor variable.	See the example in Use of Cursors in PL/SQL Programs (among others).
Collection definition	Specifies a collection, which is an ordered group of elements that are all of the same type.	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.	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.	Single-line and multi-line comments are supported.
COMMIT statement	Ends the current transaction and makes permanent all changes performed in the transaction.	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.	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.	See PL/SQL Variables and Constants.
CONTINUE statement	Exits the current iteration of a loop and transfers control to the next iteration.	See CONTINUE Statement.
CREATE FUNCTION statement	Creates a PL/SQL function.	CREATE FUNCTION is supported in TimesTen Classic, but the AS LANGUAGE, AS EXTERNAL, and PIPELINED clauses are not supported. The ACCESSIBLE BY clause is 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 PACKAGE statement CREATE PACKAGE BODY statement	These statements are used together to create a PL/SQL package definition and package body.	CREATE PACKAGE is supported in TimesTen Classic. Syntax and semantics are the same as in Oracle Database. The ACCESSIBLE BY clause is supported. 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.	CREATE PROCEDURE is supported in TimesTen Classic, but the AS LANGUAGE and AS EXTERNAL clauses are not supported. The ACCESSIBLE BY clause is 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.
CURRENT_DATE function	Returns the current date in the session time zone.	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.	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.	See Use of Cursors in PL/SQL Programs.
Cursor variables (REF CURSORs)	Act as handles to cursors over SQL result sets.	TimesTen supports OUT REF CURSORs. See PL/SQL REF CURSORs.
DELETE statement	Deletes rows from a table.	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.	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.)	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.	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.	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 Errors and Exception Handling.
EXECUTE IMMEDIATE statement	Builds and executes a dynamic SQL statement.	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).
EXIT statement	Exits a loop and transfers control to the end of the loop.	See the example in Fetching Values (among other examples in that chapter).
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.	See Expressions in Oracle Database PL/SQL Language Reference.
FETCH statement	Retrieves rows of data from the result set of a multi-row query.	See the example in Use of Cursors in PL/SQL Programs (among others).
FORALL statement	Bulk-binds input collections before sending them to the SQL engine.	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.	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 Executing Procedures and Functions. In TimesTen Classic, 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.	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.	See Conditional Control.
INLINE pragma	Specifies whether a subprogram call is to be inline.	See INLINE Pragma in Oracle Database PL/SQL Language Reference.
INSERT statement	Inserts one or more rows of data into a table.	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.	Examples: Numeric literal: 135 String literal: 'TimesTen'
LOOP statement	Executes a sequence of statements multiple times. Can be used, for example, in implementing a FOR loop or WHILE loop.	See the example in Iterative Control. 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.	See MERGE in Oracle TimesTen In-Memory Database SQL Reference.
Native dynamic SQL execution	Processes most dynamic SQL statements through the EXECUTE IMMEDIATE statement.	See the EXECUTE IMMEDIATE entry above.
NULL statement	This is a no-operation statement. Control is passed to the next statement without any action.	See NULL Statement in Oracle Database PL/SQL Language Reference.
OPEN statement	Executes the query associated with a cursor. Allocates database resources to process the query, and identifies the result set.	See the example in Use of Cursors in PL/SQL Programs.
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.	See OPEN FOR Statement in Oracle Database PL/SQL Language Reference.
Package declaration	Specifies a package, which is a database object that groups logically related PL/SQL types, items, and subprograms.	In TimesTen Classic, 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.	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 Executing Procedures and Functions. In TimesTen Classic, 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 execution of a PL/SQL block or subprogram and transfers control to an exception handler.	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).	See Using Records in PL/SQL.
RETURN statement	Immediately completes the execution of a subprogram and returns control to the invoker. Execution resumes with the statement following the subprogram call.	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.	See RETURNING INTO Clause and Examples Using RETURNING INTO.
ROLLBACK statement	Undoes database changes made during the current transaction.	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.	See the example in PL/SQL Variables and Constants.
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.	See the SELECT INTO example in PL/SQL Variables and Constants. Also see Processing Query Result Sets in Oracle Database PL/SQL Language Reference.
SOUNDEX SQL function	Returns a character string containing the phonetic representation of a char.	See SOUNDEX in Oracle TimesTen In-Memory Database SQL Reference.
SQL cursor	Either explicit or implicit, handles the result set of a SELECT statement.	See Use of Cursors in PL/SQL Programs.
SQLCODE function	Returns number code of the most recent exception.	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.	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.	TimesTen provides a subset of the Oracle Database PL/SQL supplied packages. See TimesTen Supplied PL/SQL Packages.
System tables and views	These are tables and views provided with the database for administrative purposes.	TimesTen supports a subset of the Oracle Database system tables and views. See System Tables and Views in Oracle TimesTen In-Memory Database System Tables and Views Reference.
ttPLSQLMemoryStats built-in procedure	Returns statistics about library cache performance and activity.	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.	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.	See UPDATE in Oracle TimesTen In-Memory Database SQL Reference.