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.

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

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

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.