Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Packages Reference
Release 11.2.1

Part Number E14000-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 DBMS_PREPROCESSOR

The DBMS_PREPROCESSOR package provides an interface to print or retrieve the source text of a PL/SQL unit in its post-processed form.

This package contains the following topics:


Using DBMS_PREPROCESSOR


Overview

There are three styles of subprograms:

  1. Subprograms that take a schema name, a unit type name, and the unit name.

  2. Subprograms that take a VARCHAR2 string which contains the source text of an arbitrary PL/SQL compilation unit.

  3. Subprograms that take a VARCHAR2 associative array (index-by table) which contains the segmented source text of an arbitrary PL/SQL compilation unit.

Subprograms of the first style are used to print or retrieve the post-processed source text of a stored PL/SQL unit. The user must have the privileges necessary to view the original source text of this unit. The user must also specify the schema in which the unit is defined, the type of the unit, and the name of the unit. If the schema is null, then the current user schema is used. If the status of the stored unit is VALID and the user has the required privilege, then the post-processed source text is guaranteed to be the same as that of the unit the last time it was compiled. Subprograms of the second or third style are used to generate post-processed source text in the current user schema. The source text is passed in as a single VARCHAR2 string in the second style, or as a VARCHAR2 associative array in the third style. The source text can represent an arbitrary PL/SQL compilation unit. A typical usage is to pass the source text of an anonymous block and generate its post-processed source text in the current user schema. The third style can be useful when the source text exceeds the VARCHAR2 length limit.


Operating notes


Data structures

The DBMS_PREPROCESSOR package defines a table type.

Notes:

  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

  • The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

Table types

SOURCE_LINES_T table type


SOURCE_LINES_T table type

This table type stores lines of post-processed source text. It is used to hold PL/SQL source text both before and after it is processed. It is especially useful in cases in which the amount of text exceeds 32K.

Syntax

TYPE source_lines_t IS
    TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

Summary of DBMS_PREPROCESSOR subprograms

Table 4-1 DBMS_PREPROCESSOR package subprograms

Subprogram Description

GET_POST_PROCESSED_SOURCE function

Returns the post-processed source text.

PRINT_POST_PROCESSED_SOURCE procedure

Prints post-processed source text.



GET_POST_PROCESSED_SOURCE function

This overloaded function returns the post-processed source text. The different functionality of each form of syntax is presented along with the definition.

Syntax

Returns post-processed source text of a stored PL/SQL unit:

DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (
   object_type    IN VARCHAR2,
   schema_name    IN VARCHAR2,
   object_name    IN VARCHAR2)
  RETURN dbms_preprocessor.source_lines_t;

Returns post-processed source text of a compilation unit:

DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (
   source        IN VARCHAR2)
  RETURN dbms_preprocessor.source_lines_t;

Returns post-processed source text of an associative array (index-by table) containing the source text of the compilation unit:

DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (
   source        IN dbms_preprocessor.source_lines_t)
  RETURN dbms_preprocessor.source_lines_t;

Parameters

Table 4-2 GET_POST_PROCESSED_SOURCE function parameters

Parameter Description

object_type

Must be one of PACKAGE, PACKAGE BODY, PROCEDURE, or FUNCTION. Case sensitive.

schema_name

The schema name. Case insensitive unless a quoted identifier is used. If NULL, use current schema.

object_name

The name of the object. Case insensitive unless a quoted identifier is used.

source

The source text of the compilation unit.

source_lines_t

Associative array containing the source text of the compilation unit. The source text is a concatenation of all the non-null associative array elements in ascending index order.


Return values

The function returns an associative array containing the lines of the post-processed source text starting from index 1.

Usage notes

Exceptions

Table 4-3 GET_POST_PROCESSED_SOURCE function exceptions

Exception Description

ORA-24234

Insufficient privileges or object does not exist.

ORA-24235

Bad value for object type. Must be one of PACKAGE, PACKAGE BODY, PROCEDURE, or FUNCTION.

ORA-24236

The source text is empty.

ORA-00931

Missing identifier. The object_name value cannot be NULL.

ORA-06502

Numeric or value error:

  • Character string buffer too small.

  • A line is too long (more than 32767 bytes).



PRINT_POST_PROCESSED_SOURCE procedure

This overloaded procedure calls DBMS_OUTPUT.PUT_LINE to let you view post-processed source text. The different functionality of each form of syntax is presented along with the definition.

Syntax

Prints post-processed source text of a stored PL/SQL unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   object_type    IN VARCHAR2,
   schema_name    IN VARCHAR2,
   object_name    IN VARCHAR2);

Prints post-processed source text of a compilation unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   source        IN VARCHAR2);

Prints post-processed source text of an associative array containing the source text of the compilation unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   source        IN dbms_preprocessor.source_lines_t);

Parameters

Table 4-4 PRINT_POST_PROCESSED_SOURCE procedure parameters

Parameter Description

object_type

Must be one of PACKAGE, PACKAGE BODY, PROCEDURE, or FUNCTION. Case sensitive.

schema_name

The schema name. Case insensitive unless a quoted identifier is used. If NULL, use current schema.

object_name

The name of the object. Case insensitive unless a quoted identifier is used.

source

The source text of the compilation unit.

source_lines_t

Associative array containing the source text of the compilation unit. The source text is a concatenation of all the non-null associative array elements in ascending index order.


Usage notes

The associative array may contain holes. Null elements are ignored when doing the concatenation.

Exceptions

Table 4-5 PRINT_POST_PROCESSED_SOURCE procedure exceptions

Exception Description

ORA-24234

Insufficient privileges or object does not exist.

ORA-24235

Bad value for object type. Must be PACKAGE, PACKAGE BODY, PROCEDURE, or FUNCTION.

ORA-24236

The source text is empty.

ORA-00931

Missing identifier. The object_name value cannot be NULL.

ORA-06502

Numeric or value error:

  • Character string buffer too small.

  • A line is too long (more than 32767 bytes).