8 TimesTen Supplied PL/SQL Packages

TimesTen supplies public 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. Packages that are part of the PL/SQL language itself or are otherwise for Oracle Database internal use only are not shown here or described in this chapter.

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_LOB
DBMS_LOCK
DBMS_OUTPUT
DBMS_PREPROCESSOR
DBMS_RANDOM
DBMS_SQL
DBMS_UTILITY
TT_DB_VERSION
TT_STATS
UTL_FILE
UTL_IDENT
UTL_RAW
UTL_RECOMP

DBMS_LOB

The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, and NCLOBs, including temporary LOBs.

Notes:

TimesTen does not support DBMS_LOB subprograms intended specifically for BFILEs, SecureFiles (including Database File System features), or inserting or deleting data fragments in the middle of a LOB.
DBMS_LOB procedures and functions are supported for both TimesTen LOBs and passthrough LOBs, which are LOBs in Oracle Database accessed through TimesTen and exposed as TimesTen LOBs. Note, however, that CREATETEMPORARY can only be used to create a temporary LOB in TimesTen. If a temporary passthrough LOB is created using some other mechanism, such as SQL, ISTEMPORARY and FREETEMPORARY can be used on that LOB.

As with TimesTen local LOBs, a locator for a passthrough LOB does not remain valid past the end of the transaction.

In addition to copying from one TimesTen LOB to another, COPY can copy from a TimesTen LOB to a passthrough LOB, from a passthrough LOB to a TimesTen LOB, or from one passthrough LOB to another passthrough LOB. An attempt to copy a passthrough LOB to a TimesTen LOB when the passthrough LOB is larger than the TimesTen LOB size limit results in an error. ("COPY procedures" in Oracle TimesTen In-Memory Database PL/SQL Packages Reference provides examples for copying LOBs.)

See "Passthrough LOBs" for related information.

Table 8-1 describes the supported DBMS_LOB subprograms.

Table 8-1 DBMS_LOB subprograms

Subprogram	Description
`APPEND` procedures	Appends the contents of the source LOB to the destination LOB.
`CLOSE` procedures	Closes a previously opened LOB.
`COMPARE` functions	Compares two entire LOBs or parts of two LOBs.
`CONVERTTOBLOB` procedure	Reads character data from a source `CLOB` or `NCLOB` instance, converts the character data to the specified character set, writes the converted data to a destination `BLOB` instance in binary format, and returns the new offsets.
`CONVERTTOCLOB` procedure	Takes a source `BLOB` instance, converts the binary data in the source instance to character data using the specified character set, writes the character data to a destination `CLOB` or `NCLOB` instance, and returns the new offsets.
`COPY` procedures	Copies all or part of the source LOB to the destination LOB.
`CREATETEMPORARY` procedures	Creates a temporary LOB in the temporary data region. Any of the durations supported by Oracle Database is permitted (`SESSION`, `TRANSACTION`, or `CALL`), but in TimesTen LOB duration cannot extend past the end of the transaction.
`ERASE` procedures	Erases all or part of a LOB.
`FREETEMPORARY` procedures	Frees a temporary LOB in the temporary data region.
`GET_STORAGE_LIMIT` functions	Returns the storage limit for the LOB type of the specified LOB.
`GETCHUNKSIZE` functions	In TimesTen, this simply returns the value 32 KB for interoperability. Do not rely on this value for performance tuning.
`GETLENGTH` functions	Returns the length of the LOB value, in bytes for a BLOB or characters for a CLOB or NCLOB.
`INSTR` functions	Returns the matching position of the nth occurrence of the pattern in the LOB.
`ISOPEN` functions	Checks to see if the LOB was already opened using the input locator.
`ISTEMPORARY` functions	Checks whether the locator is pointing to a temporary LOB.
`OPEN` procedures	Opens a LOB (persistent or temporary) in the indicated mode, read/write or read-only. Note: Opening a LOB is similar conceptually, but not technically, to opening a file. Opening a LOB is more like a hint regarding resources to be required.
`READ` procedures	Reads data from the LOB starting at the specified offset.
`SUBSTR` functions	Returns part of the LOB value starting at the specified offset.
`TRIM` procedures	Trims the LOB value to the specified shorter length.
`WRITE` procedures	Writes data to the LOB from a specified offset.
`WRITEAPPEND` procedures	Writes a buffer to the end of a LOB.

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-2 describes the supported DBMS_LOCK subprogram.

Table 8-2 DBMS_LOCK 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, the timeout takes 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, the timeout takes 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-3 describes the DBMS_OUTPUT subprograms.

Table 8-3 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-4 describes the DBMS_PREPROCESSOR subprograms.

Table 8-4 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-5 describes the DBMS_RANDOM subprograms.

Table 8-5 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	There are two overloaded versions. In the first, it 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). In the second, it gets a random number within specified low and high limits.

DBMS_SQL

The DBMS_SQL package provides an interface for using dynamic SQL to accomplish any of the following:

Execute data manipulation language (DML) and data definition language (DDL) statements.
Execute PL/SQL anonymous blocks.
Call PL/SQL stored procedures and functions.

This package does not support pre-defined data types and overloads with data types that are not supported in TimesTen, such as 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-6 describes the DBMS_SQL subprograms.

Table 8-6 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 results 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 results in an `ORA-01018` error at runtime. `DEFINE_COLUMN_LONG` would be used in conjunction with `COLUMN_VALUE_LONG`.
`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 a cumulative count of the number of rows fetched.
`LAST_ROW_ID` function	Returns `NULL`. TimesTen does not support `ROWID` of the last row operated on by a DML statement.
`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-7 describes DBMS_UTILITY subprograms.

Table 8-7 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	Checks the setting of a specified bit in a `RAW` value.
`NAME_RESOLVE` procedure	Resolves the given name of the following 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 of the following form: "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 contains boolean constants indicating the current TimesTen release.

Table 8-8 describes the TT_DB_VERSION constants.

The primary use case for the TT_DB_VERSION and UTL_IDENT packages is for conditional compilation.

Table 8-8 TT_DB_VERSION constants

Name	Description
`VER_LE_1121`	Boolean that is `TRUE` if the TimesTen version this package ships with is 11.2.1 or prior. `FALSE` for TimesTen 11g Release 2 (11.2.2) versions.
`VER_LE_1122`	Boolean that is `TRUE` if the TimesTen version this package ships with is 11.2.2 or prior. `TRUE` for TimesTen 11g Release 2 (11.2.2) versions.

See "Examples" in Oracle TimesTen In-Memory Database PL/SQL Packages Reference for an example that uses TT_DB_VERSION and UTL_IDENT.

TT_STATS

The TT_STATS package provides features for collecting and comparing snapshots of TimesTen system metrics, according to the capture level. Each snapshot can consist of what TimesTen considers to be basic metrics, typical metrics, or all available metrics.

For those familiar with Oracle Database performance analysis tools, these reports are similar in nature to Oracle Automatic Workload Repository (AWR) reports.

Table 8-9 TT_STATS subprograms

Subprogram	Description
`CAPTURE_SNAPSHOT` procedure and function	Takes a snapshot of TimesTen metrics. The function also returns the snapshot ID.
`DROP_SNAPSHOTS_RANGE` function	Deletes snapshots according to a specified range of snapshot IDs or timestamps.
`GENERATE_REPORT_HTML` procedure	Produces a report in HTML format based on the data from two specified snapshots.
`GENERATE_REPORT_TEXT` procedure	Produces a report in plain text format based on the data from two specified snapshots.
`GET_CONFIG` function	Retrieves the value of a specified `TT_STATS` configuration parameter or the values of all configuration parameters.
`SET_CONFIG` procedure	Sets a specified value for a specified `TT_STATS` configuration parameter.
`SHOW_SNAPSHOTS` function	Shows the snapshot IDs and timestamps of all snapshots currently stored in the database.

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-10 describes the UTL_FILE subprograms.

Table 8-10 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 database client, an Oracle database server, or Oracle Forms. Each of these has its own version of UTL_IDENT with appropriate settings for the constants.

Table 8-11 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

Table 8-11 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`

See "Examples" in Oracle TimesTen In-Memory Database PL/SQL Packages Reference for an example that uses TT_DB_VERSION and UTL_IDENT.

UTL_RAW

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

Table 8-12 describes the UTL_RAW subprograms.

Table 8-12 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	Casts a `RAW` value represented using `n` data bytes into an `NVARCHAR2` value with `n` data bytes.
`CAST_TO_RAW` function	Casts a `VARCHAR2` value represented using `n` data bytes into a `RAW` with `n` data bytes.
`CAST_TO_VARCHAR2` function	Casts 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-13 describes the UTL_RECOMP subprograms.

Important:

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

Table 8-13 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.