Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Packages Reference
Release 11.2.1

Part Number E14000-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

1 Introduction to TimesTen-Supplied PL/SQL Packages and Types

A set of PL/SQL packages is installed when you enable PL/SQL for the TimesTen In-Memory Database. These packages extend database functionality and allow PL/SQL access to SQL features. To display the list of packages provided with TimesTen, use the system view ALL_PROCEDURES for objects owned by SYS. The following example shows this. As with other ALL_* system views, all users have SELECT privilege for the ALL_PROCEDURES system view.

Command> select distinct object_name from all_procedures where owner='SYS';
16 rows found.

This is the list of TimesTen-supplied packages currently installed in the database. It includes internal-use packages and public packages. This manual documents only the public packages, as summarized in "Summary of TimesTen-supplied PL/SQL packages".


The following packages are for internal use only and are not documented in this manual:

This chapter contains these topics:

Package overview

A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.

This section covers the following topics:

Package components

PL/SQL packages have two parts, the specification and the body, although sometimes the body is unnecessary. The specification is the interface to your application; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.

Unlike subprograms, packages cannot be called, parameterized, or nested. However, the formats of a package and a subprogram are similar:

CREATE PACKAGE name AS  -- specification (visible part)
   -- public type and item declarations
   -- subprogram specifications
END [name];

CREATE PACKAGE BODY name AS  -- body (hidden part)
   -- private type and item declarations
   -- subprogram bodies
   -- initialization statements]
END [name];

The specification holds public declarations that are visible to your application. The body holds implementation details and private declarations that are hidden from your application. You can debug, enhance, or replace a package body without changing the specification. You can change a package body without recompiling calling programs because the implementation details in the body are hidden from your application.

Using TimesTen-supplied packages

TimesTen-supplied packages are automatically installed when the database is created.

All users have EXECUTE privilege for packages described in this document, other than for UTL_RECOMP and UTL_FILE, as noted in those chapters. To use this package, you must be the instance administrator.

To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view. Instructions on special requirements for packages are documented in the individual chapters.


In TimesTen, running as the instance administrator is comparable to running as the Oracle SYSDBA. Running as the ADMIN user is comparable to running as the Oracle DBA.

Referencing package contents

To reference the types, items, and subprograms declared in a package specification, use the dot notation. For example:


Running package examples

In order to see the output from the package examples in this document, first execute the following command in ttIsql:

Command> set serveroutput on

Summary of TimesTen-supplied PL/SQL packages

Table 1-1 lists the PL/SQL server packages supplied with TimesTen for public use. These packages run as the invoking user, rather than the package owner.


  • The procedures and functions provided in these packages and their external interfaces are reserved by Oracle and are subject to change.

  • Modifying supplied packages can cause internal errors and database security violations. Do not modify supplied packages.

Table 1-1 Summary of TimesTen-supplied PL/SQL packages

Package Name Description


Provides an interface to Lock Management services. TimesTen supports only the SLEEP procedure, to suspend the session for a specified period of time.


Enables you to send messages from stored procedures and packages.


Provides an interface to print or retrieve the source text of a PL/SQL unit in its post-processed form.


Provides a built-in random number generator.


Lets you use dynamic SQL to access the database.


Provides various utility routines.


Indicates the TimesTen major and minor version numbers.


Enables your PL/SQL programs to read and write operating system text files and provides a restricted version of standard operating system stream file I/O.


Indicates which database or client PL/SQL is running in, such as TimesTen versus Oracle, and server versus client. (Each database or client running PL/SQL has its own copy of this package.)


Provides SQL functions for manipulating RAW data types.


Recompiles invalid PL/SQL modules.


Notes on data types:
  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses "BINARY_INTEGER" to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

  • The INTEGER and NUMBER(38) data types are also identical. This document uses "INTEGER" throughout.