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

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

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

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.