8 TimesTen Supplied PL/SQL Packages

Oracle TimesTen In-Memory Database supplies a number of 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 period of time. 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 period of time. 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.
NORMAL function	Returns random numbers in a normal distribution.
RANDOM procedure	Generates a random number.
SEED procedure	Resets the seed.
STRING function	Gets a random string.
TERMINATE procedure	Terminates the package.
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.

The DBMS_SQL 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 the OPEN, PARSE, and EXECUTE subprograms), 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 TimesTen 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[@d] Where `a`, `b`, and `c` are SQL identifiers and `d` is a dblink (database link). Do not use `@d`. 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
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.

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.

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
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.

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.