8 TimesTen Supplied PL/SQL Packages

Oracle TimesTen In-Memory Database supplies PL/SQL packages, listed immediately below, to extend database functionality and provide PL/SQL access to SQL features. TimesTen installs these packages automatically for your use.

This chapter lists and briefly describes the subprograms that comprise each package. For details on these PL/SQL packages, refer to Oracle TimesTen In-Memory Database PL/SQL Packages Reference.

DBMS_LOCK
DBMS_OUTPUT
DBMS_PREPROCESSOR
DBMS_RANDOM
DBMS_SQL
DBMS_UTILITY
TT_DB_VERSION
UTL_FILE
UTL_IDENT
UTL_RAW
UTL_RECOMP

Notes:

The packages STANDARD, DBMS_STANDARD, and PLITBLM are not documented here. Subprograms belonging to these packages are part of the PL/SQL language.
All users have EXECUTE privilege for packages described in this chapter, except as noted for UTL_FILE and UTL_RECOMP in those sections.

DBMS_LOCK

The DBMS_LOCK package provides an interface to lock-management services. In the current release, TimesTen supports only the sleep feature.

Table 8-1 describes the supported DBMS_LOCK subprogram.

Table 8-1 DBMS_OUTPUT Subprograms

Subprogram Description

Subprogram	Description
`SLEEP` procedure	This procedure suspends the session for a given duration. Specify the amount of time in seconds. The smallest supported increment is a hundredth of a second. For example: DBMS_LOCK.SLEEP(1.95); Notes: The actual sleep time may be somewhat longer than specified, depending on system activity. If `PLSQL_TIMEOUT` is set to a positive value that is less than this sleep time, then the timeout will take effect first. Be sure that either the sleep value is less than the timeout value, or `PLSQL_TIMEOUT=0` (no timeout). See "PL/SQL connection attributes" for information about `PLSQL_TIMEOUT`.

SLEEP procedure

This procedure suspends the session for a given duration. Specify the amount of time in seconds. The smallest supported increment is a hundredth of a second. For example:

DBMS_LOCK.SLEEP(1.95);

Notes:

The actual sleep time may be somewhat longer than specified, depending on system activity.
If PLSQL_TIMEOUT is set to a positive value that is less than this sleep time, then the timeout will take effect first. Be sure that either the sleep value is less than the timeout value, or PLSQL_TIMEOUT=0 (no timeout). See "PL/SQL connection attributes" for information about PLSQL_TIMEOUT.

DBMS_OUTPUT

The DBMS_OUTPUT package enables you to send messages from stored procedures and packages. The package is useful for displaying PL/SQL debugging information.

Table 8-2 describes the DBMS_OUTPUT subprograms.

Table 8-2 DBMS_OUTPUT Subprograms

Subprogram	Description
`DISABLE` procedure	Disables message output.
`ENABLE` procedure	Enables message output.
`GET_LINE` procedure	Retrieves one line from the buffer.
`GET_LINES` procedure	Retrieves an array of lines from the buffer.
`NEW_LINE` procedure	Terminates a line created with `PUT`.
`PUT` procedure	Places a line in the buffer.
`PUT_LINE` procedure	Places a partial line in the buffer.

DBMS_PREPROCESSOR

The DBMS_PREPROCESSOR package provides an interface to print or retrieve the source text of a PL/SQL unit after processing of conditional compilation directives.

Table 8-3 describes the DBMS_PREPROCESSOR subprograms.

Table 8-3 DBMS_PREPROCESSOR Subprograms

Subprogram	Description
`GET_POST_PROCESSED_SOURCE` function	Returns post-processed source text.
`PRINT_POST_PROCESSED_SOURCE` procedure	Prints post-processed source text.

DBMS_RANDOM

The DBMS_RANDOM package provides a built-in random number generator.

Table 8-4 describes the DBMS_RANDOM subprograms.

Table 8-4 DBMS_RANDOM Subprograms

Subprogram	Description
`INITIALIZE` procedure	Initializes the package with a seed value (deprecated).
`NORMAL` function	Returns random numbers in a normal distribution.
`RANDOM` procedure	Generates a random number (deprecated).
`SEED` procedure	Resets the seed.
`STRING` function	Gets a random string.
`TERMINATE` procedure	Terminates the package (deprecated).
`VALUE` function	The `VALUE` function gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal point (38-digit precision). The overload function gets a random `NUMBER` `x`, where `x` is greater than or equal to `LOW` and less than `HIGH`.

DBMS_SQL

The DBMS_SQL package provides an interface for using dynamic SQL to parse data manipulation language (DML) or data definition language (DDL) statements using PL/SQL.

This package does not support pre-defined data types and overloads with data types that are not supported in TimesTen, such as LOBs, UROWID, time zone features, ADT, database-level collections, and edition overloads. For more information on the supported data types in TimesTen PL/SQL, see "Understanding the data type environments".

Table 8-5 describes the DBMS_SQL subprograms.

Table 8-5 DBMS_SQL Subprograms

Subprogram	Description
`BIND_ARRAY` procedure	Binds a given value to a given collection.
`BIND_VARIABLE` procedure	Binds a given value to a given variable.
`CLOSE_CURSOR` procedure	Closes a given cursor and frees memory.
`COLUMN_VALUE` procedure	Returns the value of the cursor element for a given position in a cursor.
`COLUMN_VALUE_LONG` procedure	Returns a selected part of a `LONG` column that has been defined using `DEFINE_COLUMN_LONG`. Important: Because TimesTen does not support the `LONG` data type, attempting to use this procedure in TimesTen will result in an `ORA-01018` error at runtime.
`DEFINE_ARRAY` procedure	Defines a collection to be selected from the given cursor. Use with `SELECT` statements.
`DEFINE_COLUMN` procedure	Defines a column to be selected from the given cursor. Use with `SELECT` statements.
`DEFINE_COLUMN_LONG` procedure	Defines a `LONG` column to be selected from the given cursor. Use with `SELECT` statements. Important: Because TimesTen does not support the `LONG` data type, attempting to use the `COLUMN_VALUE_LONG` procedure in TimesTen will result in an `ORA-01018` error at runtime.
`DESCRIBE_COLUMNS` procedure	Describes the columns for a cursor opened and parsed through the `DBMS_SQL` package.
`DESCRIBE_COLUMNS2` procedure	Describes the specified column. Use as an alternative to `DESCRIBE_COLUMNS` procedure.
`DESCRIBE_COLUMNS3` procedure	Describes the specified column. Use as an alternative to `DESCRIBE_COLUMNS` procedure.
`EXECUTE` function	Executes a given cursor.
`EXECUTE_AND_FETCH` function	Executes a given cursor and fetches rows.
`FETCH_ROWS` function	Fetches a row from a given cursor.
`IS_OPEN` function	Returns `TRUE` if a given cursor is open.
`LAST_ERROR_POSITION` function	Returns the byte offset in the SQL statement text where the error occurred.
`LAST_ROW_COUNT` function	Returns cumulative count of the number of rows fetched.
`LAST_ROW_ID` function	TimesTen does not support `ROWID` of the last row operated on by a DML statement. This function returns `NULL`.
`LAST_SQL_FUNCTION_CODE` function	Returns the SQL function code for the statement.
`OPEN_CURSOR` function	Returns the cursor ID number of a new cursor.
`PARSE` procedures	Parses a given statement.
`TO_CURSOR_NUMBER` function	Takes an opened (by `OPEN`) strongly or weakly typed REF CURSOR and transforms it into a `DBMS_SQL` cursor number.
`TO_REFCURSOR` function	Takes an opened, parsed, and executed cursor (by `OPEN`, `PARSE`, and `EXECUTE`) and transforms or migrates it into a PL/SQL manageable REF CURSOR (a weakly typed cursor) that can be consumed by PL/SQL native dynamic SQL and switched to use native dynamic SQL.
`VARIABLE_VALUE` procedures	Returns value of a named variable for a given cursor.

DBMS_UTILITY

The DBMS_UTILITY package provides a variety of utility subprograms.

Subprograms are not supported (and not listed here) for features that TimesTen does not support.

Table 8-6 describes DBMS_UTILITY subprograms.

Table 8-6 DBMS_UTILITY Subprograms

Subprogram	Description
`CANONICALIZE` procedure	Canonicalizes a given string.
`COMMA_TO_TABLE` procedure	Converts a comma-delimited list of names into an associative array (index-by table) of names.
`COMPILE_SCHEMA`	Compiles all procedures, functions, packages, and views in the specified database schema.
`DB_VERSION` procedure	Returns version information for the database. The procedure returns `NULL` for the compatibility setting because TimesTen does not support the system parameter `COMPATIBLE`.
`FORMAT_CALL_STACK` function	Formats the current call stack.
`FORMAT_ERROR_BACKTRACE` function	Formats the backtrace from the point of the current error to the exception handler where the error is caught.
`FORMAT_ERROR_STACK` function	Formats the current error stack.
`GET_CPU_TIME` function	Returns the current CPU time in hundredths of a second.
`GET_DEPENDENCY` procedure	Shows the dependencies on the objects passed in.
`GET_ENDIANNESS` function	Returns the endianness of your database platform.
`GET_HASH_VALUE` function	Computes a hash value for a given string.
`GET_SQL_HASH` function	Computes the hash value for a given string using the MD5 algorithm.
`GET_TIME` function	Returns the current time in hundredths of a second.
`INVALIDATE` procedure	Invalidates a database object and optionally modifies the PL/SQL compiler parameter settings for the object.
`IS_BIT_SET` function	Returns bit setting.
`NAME_RESOLVE` procedure	Resolves the given name of the form: [[a.]b.]c[@dblink] Where `a`, `b`, and `c` are SQL identifiers and `dblink` is a dblink (database link). Do not use `@dblink`. TimesTen does not support dblinks.
`NAME_TOKENIZE` procedure	Calls the parser to parse the given name: "a [.b [.c ]][@dblink]" Strips double quotes or converts to uppercase if there are no quotes. Ignores comments and does not perform semantic analysis. Missing values are `NULL`. Do not use `@dblink`. TimesTen does not support dblinks.
`TABLE_TO_COMMA` procedures	Converts an associative array (index-by table) of names into a comma-delimited list of names.
`VALIDATE` procedure	Validates the object described by either owner, name and namespace or object ID.

TT_DB_VERSION

The TT_DB_VERSION package is a TimesTen-specific package that indicates the version number and release number for the Oracle TimesTen In-Memory Database.

Table 8-7 describes the TT_DB_VERSION constants.

The primary use case for the TT_DB_VERSION and UTL_IDENT packages is for conditional compilation. See "UTL_IDENT" for an example.

Table 8-7 TT_DB_VERSION Constants

Name Description

Name	Description
`VERSION`	Equals the major release number of the Oracle TimesTen In-Memory Database. `VERSION` is of type `PLS_INTEGER`. For example, for the Oracle TimesTen In-Memory Database, Release 11.2.1.0, `TT_DB_VERSION.VERSION` equals 1121.
`RELEASE`	Equals the minor release number of the Oracle TimesTen In-Memory Database product. `RELEASE` is of type `PLS_INTEGER`. For example, for the Oracle TimesTen In-Memory Database, Release 11.2.1.0, `TT_DB_VERSION.RELEASE` equals 0.

VERSION

Equals the major release number of the Oracle TimesTen In-Memory Database. VERSION is of type PLS_INTEGER.

For example, for the Oracle TimesTen In-Memory Database, Release 11.2.1.0, TT_DB_VERSION.VERSION equals 1121.

RELEASE

Equals the minor release number of the Oracle TimesTen In-Memory Database product. RELEASE is of type PLS_INTEGER.

For example, for the Oracle TimesTen In-Memory Database, Release 11.2.1.0, TT_DB_VERSION.RELEASE equals 0.

UTL_FILE

The UTL_FILE package enables PL/SQL programs the ability to read and write operating system text files.

In the current release, this package is restricted to access of a pre-defined temporary directory only. Refer to the Oracle TimesTen In-Memory Database Release Notes for details.

Note:

Users do not have execute permission on UTL_FILE by default. To use UTL_FILE in TimesTen, an ADMIN user or instance administrator must explicitly grant EXECUTE permission on it, such as in the following example:

GRANT EXECUTE ON SYS.UTL_FILE TO scott;

Table 8-8 describes the UTL_FILE subprograms.

Table 8-8 UTL_FILE Subprograms

Subprogram	Description
`FCLOSE` procedure	Closes a file.
`FCLOSE_ALL` procedure	Closes all file handles.
`FCOPY` procedure	Copies a contiguous portion of a file to a newly created file.
`FFLUSH` procedure	Physically writes all pending output to a file.
`FGETATTR` procedure	Reads and returns the attributes of a disk file.
`FGETPOS` procedure	Returns the current relative offset position (in bytes) within a file.
`FOPEN` function	Opens a file for input or output.
`FOPEN_NCHAR` function	Opens a file in Unicode for input or output.
`FREMOVE` procedure	With sufficient privilege, deletes a disk file.
`FRENAME` procedure	Renames an existing file to a new name (similar to the UNIX `mv` command).
`FSEEK` procedure	Adjusts the file pointer forward or backward within the file by the number of bytes specified.
`GET_LINE` procedure	Reads text from an open file.
`GET_LINE_NCHAR` procedure	Reads text in Unicode from an open file.
`GET_RAW` function	Reads a `RAW` string value from a file and adjusts the file pointer ahead by the number of bytes read.
`IS_OPEN` function	Determines if a file handle refers to an open file.
`NEW_LINE` procedure	Writes one or more operating system-specific line terminators to a file.
`PUT` procedure	Writes a string to a file.
`PUT_LINE` procedure	Writes a line to a file and appends an operating system-specific line terminator.
`PUT_LINE_NCHAR` procedure	Writes a Unicode line to a file.
`PUT_NCHAR` procedure	Writes a Unicode string to a file.
`PUT_RAW` function	Accepts as input a `RAW` data value and writes the value to the output buffer.
`PUTF` procedure	This is similar to the `PUT` procedure, but with formatting.
`PUTF_NCHAR` procedure	This is similar to the `PUT_NCHAR` procedure, but with formatting. Writes a Unicode string to a file with formatting.

UTL_IDENT

The UTL_IDENT package indicates whether PL/SQL is running on TimesTen, an Oracle client, an Oracle server, or Oracle Forms. Each of these has its own version of UTL_IDENT with appropriate settings for the constants.

Table 8-9 shows the UTL_IDENT settings for TimesTen.

The primary use case for the UTL_IDENT package is for conditional compilation, resembling the following:

$if utl_ident.is_oracle_server $then
    [...Run code supported for Oracle Database...]
$elsif utl_ident.is_timesten $then
    [...code supported for TimesTen Database...]
$end

See Example 8-1 below.

Table 8-9 UTL_IDENT Constants

Name	Description
`IS_ORACLE_CLIENT`	`BOOLEAN` set to `FALSE`.
`IS_ORACLE_SERVER`	`BOOLEAN` set to `FALSE`.
`IS_ORACLE_FORMS`	`BOOLEAN` set to `FALSE`.
`IS_TIMESTEN`	`BOOLEAN` set to `TRUE`.

Example 8-1 Using UTL_IDENT and TT_DB_VERSION

This example uses the UTL_IDENT and TT_DB_VERSION packages to show information about the database being used. For the current release, it displays either "Oracle Database 11.2" or "TimesTen 11.2.1". The conditional compilation trigger character, $, identifies code that is processed before the application is compiled.

Command> run what_db.sql
 
create or replace function what_db
return varchar2
as
 dbname varchar2(100);
 version varchar2(100);
begin
$if utl_ident.is_timesten
$then
 dbname := 'TimesTen';
 version := substr(tt_db_version.version, 1, 2) ||
            '.' ||
            substr(tt_db_version.version, 3, 1) ||
            '.' ||
            substr(tt_db_version.version, 4, 1);
$elsif utl_ident.is_oracle_server
$then
 dbname := 'Oracle Database';
 version := dbms_db_version.version || '.' || dbms_db_version.release;
$else
 dbname := 'Non-database environment';
 version := '';
$end
 return dbname || ' ' || version;
end;
/
 
Function created.
 
set serveroutput on;
 
begin
dbms_output.put_line(what_db());
end;
/
 
TimesTen 11.2.1
 
PL/SQL procedure successfully completed.

UTL_RAW

The UTL_RAW package provides SQL functions for manipulating RAW data types.

Table 8-10 describes the UTL_RAW subprograms.

Table 8-10 UTL_RAW Subprograms

Subprogram	Description
`BIT_AND` function	Performs bitwise logical "and" of two `RAW` values and returns the resulting `RAW`.
`BIT_COMPLEMENT` function	Performs bitwise logical "complement" of a `RAW` value and returns the resulting `RAW`.
`BIT_OR` function	Performs bitwise logical "or" of two `RAW` values and returns the resulting `RAW`.
`BIT_XOR` function	Performs bitwise logical "exclusive or" of two `RAW` values and returns the resulting `RAW`.
`CAST_FROM_BINARY_DOUBLE` function	Returns the `RAW` binary representation of a `BINARY_DOUBLE` value.
`CAST_FROM_BINARY_FLOAT` function	Returns the `RAW` binary representation of a `BINARY_FLOAT` value.
`CAST_FROM_BINARY_INTEGER` function	Returns the `RAW` binary representation of a `BINARY_INTEGER` value.
`CAST_FROM_NUMBER` function	Returns the `RAW` binary representation of a `NUMBER` value.
`CAST_TO_BINARY_DOUBLE` function	Casts the `RAW` binary representation of a `BINARY_DOUBLE` value into a `BINARY_DOUBLE`.
`CAST_TO_BINARY_FLOAT` function	Casts the `RAW` binary representation of a `BINARY_FLOAT` value into a `BINARY_FLOAT`.
`CAST_TO_BINARY_INTEGER` function	Casts the `RAW` binary representation of a `BINARY_INTEGER` value into a `BINARY_INTEGER`.
`CAST_TO_NUMBER` function	Casts the `RAW` binary representation of a `NUMBER` value into a `NUMBER`.
`CAST_TO_NVARCHAR2` function	Converts a `RAW` value represented using `n` data bytes into an `NVARCHAR2` value with `n` data bytes.
`CAST_TO_RAW` function	Converts a `VARCHAR2` value represented using `n` data bytes into a `RAW` with `n` data bytes.
`CAST_TO_VARCHAR2` function	Converts a `RAW` value represented using `n` data bytes into a `VARCHAR2` value with `n` data bytes.
`COMPARE` function	Compares two `RAW` values.
`CONCAT` function	Concatenates up to 12 `RAW` values into a single `RAW` value.
`CONVERT` function	Converts a `RAW` value from one character set to another and returns the resulting `RAW`.
`COPIES` function	Copies a `RAW` value a specified number of times and returns the concatenated `RAW` value.
`LENGTH` function	Returns the length in bytes of a `RAW` value.
`OVERLAY` function	Overlays the specified portion of a target `RAW` value with an overlay `RAW` value, starting from a specified byte position and proceeding for a specified number of bytes.
`REVERSE` function	Reverses a byte-sequence in a `RAW` value.
`SUBSTR` function	Returns a substring of a `RAW` value for a specified number of bytes from a specified starting position.
`TRANSLATE` function	Translates the specified bytes from an input `RAW` value according to the bytes in a specified translation `RAW` value.
`TRANSLITERATE` function	Converts the specified bytes from an input `RAW` value according to the bytes in a specified transliteration `RAW` value.
`XRANGE` function	Returns a `RAW` value containing the succession of one-byte encodings beginning and ending with the specified byte-codes.

UTL_RECOMP

The UTL_RECOMP package recompiles invalid PL/SQL modules. This is particularly useful after a major-version upgrade that typically invalidates all PL/SQL objects.

Table 8-11 describes the UTL_RECOMP subprograms.

Important:

To use this package, you must be the instance administrator and specify SYS.UTL_RECOMP.

Table 8-11 UTL_RECOMP Subprograms

Name Description

Name	Description
`RECOMP_PARALLEL` procedure	Recompiles invalid objects in a given schema, or all invalid objects in the database, in parallel. Note: Because TimesTen does not support `DBMS_SCHEDULER`, the number of recompile threads to run in parallel is always 1, regardless of what the user specifies. Therefore there is no effective difference between `RECOMP_PARALLEL` and `RECOMP_SERIAL` in TimesTen.
`RECOMP_SERIAL` procedure	Recompiles invalid objects in a given schema, or all invalid objects in the database, serially.

RECOMP_PARALLEL procedure

Recompiles invalid objects in a given schema, or all invalid objects in the database, in parallel.

Note: Because TimesTen does not support DBMS_SCHEDULER, the number of recompile threads to run in parallel is always 1, regardless of what the user specifies. Therefore there is no effective difference between RECOMP_PARALLEL and RECOMP_SERIAL in TimesTen.

RECOMP_SERIAL procedure

Recompiles invalid objects in a given schema, or all invalid objects in the database, serially.