A set of PL/SQL packages is supplied with TimesTen. These packages extend database functionality and allow PL/SQL access to SQL features. To display the list of packages currently installed in TimesTen, use the system view ALL_PROCEDURES
for objects owned by SYS
. The following example, using ttIsql
, 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'; < DBMS_LOB > < DBMS_LOCK > < DBMS_OUTPUT > < DBMS_PREPROCESSOR > < DBMS_RANDOM > < DBMS_SQL > ... < DBMS_UTILITY > ... < TT_STATS > < UTL_FILE > < UTL_RAW > < UTL_RECOMP > < UTL_IDENT > < TT_DB_VERSION > 19 rows found.
This manual documents these public packages, listed with brief descriptions in "Summary of TimesTen-supplied PL/SQL packages". Packages that are part of the PL/SQL language itself or are for TimesTen or Oracle Database internal use only are not shown here or described in this manual.
The rest of this chapter contains these topics:
For additional information about PL/SQL and PL/SQL packages, you can refer to the following:
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:
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 [BEGIN -- 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.
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 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.
Note:
In TimesTen, running as the instance administrator is comparable to running as the Oracle Database userSYSDBA
. Running as the ADMIN
user is comparable to running as the Oracle Database user DBA
.Table 1-1 lists the PL/SQL packages supplied with TimesTen for public use. These packages run as the invoking user, rather than the package owner.
Important:
The procedures and functions provided in these packages and their external interfaces are reserved by Oracle Database and are subject to change.
Do not modify supplied packages. Modifying supplied packages may cause internal errors and database security violations.
Table 1-1 Summary of TimesTen-supplied PL/SQL packages
Package Name | Description |
---|---|
Provides subprograms to operate on binary and character large objects: BLOBs, CLOBs, and NCLOBs. |
|
Provides an interface to Lock Management services. TimesTen supports only the |
|
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. |
|
Collects snapshots of database metrics and generates reports based on comparisons between snapshots. |
|
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 in which database or client PL/SQL is running, such as TimesTen or Oracle Database, and server versus client. (Each database or client running PL/SQL has its own copy of this package.) |
|
Provides SQL functions for manipulating |
|
Recompiles invalid PL/SQL modules. |
Notes:
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.