5 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:
-
-
Overview
-
Operational notes
-
-
-
Table types
-
Using DBMS_PREPROCESSOR
Overview
There are three styles of subprograms:
-
Subprograms that take a schema name, a unit type name, and the unit name
-
Subprograms that take a
VARCHAR2
string that contains the source text of an arbitrary PL/SQL compilation unit -
Subprograms that take a
VARCHAR2
associative array (index-by table) that 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.
Operational Notes
-
For subprograms of the first style, the status of the stored PL/SQL unit is not required to be
VALID
. Likewise, the source text passed in as aVARCHAR2
string or aVARCHAR2
associative array may contain compile time errors. If errors are found when generating the post-processed source, the error message text also appears at the end of the post-processed source text. In some cases, the preprocessing can terminate because of errors. When this happens, the post-processed source text appears to be incomplete and the associated error message can help indicate that an error has occurred during preprocessing. -
For subprograms of the second or third style, the source text can represent any arbitrary PL/SQL compilation unit. However, the source text of a valid PL/SQL compilation unit cannot include commonly used prefixes such as
CREATE OR REPLACE
. In general, the input source should be syntactically prepared in a way as if it were obtained from theALL_SOURCE
view. The following list gives some examples of valid initial syntax for some PL/SQL compilation units.anonymous block (BEGIN | DECLARE) ... package PACKAGE name ... package body PACKAGE BODY name ... procedure PROCEDURE name ... function FUNCTION name ...
If the source text represents a named PL/SQL unit that is valid, that unit is not created after its post-processed source text is generated.
-
If the text of a wrapped PL/SQL unit is obtained from the
ALL_SOURCE
view, the keywordWRAPPED
always immediately follows the name of the unit, as in this example:PROCEDURE "some proc" WRAPPED a000000 b2 ...
If such source text is presented to a GET_POST_PROCESSED_SOURCE Function or a PRINT_POST_PROCESSED_SOURCE Procedure, the exception
WRAPPED_INPUT
is raised.
Data Structures
The DBMS_PREPROCESSOR
package defines a table type.
Note:
-
The
PLS_INTEGER
andBINARY_INTEGER
data types are identical. This document usesBINARY_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
andNUMBER(38)
data types are also identical. This document usesINTEGER
throughout.
Table types
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 32 KB.
Syntax
TYPE source_lines_t IS
TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
DBMS_PREPROCESSOR Subprograms
Table 5-1 summarizes the DBMS_PREPROCESSOR
subprograms, followed by a full description of each subprogram.
Table 5-1 DBMS_PREPROCESSOR Package Subprograms
Subprogram | Description |
---|---|
Returns the post-processed source text. |
|
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 5-2 GET_POST_PROCESSED_SOURCE Function Parameters
Parameter | Description |
---|---|
|
One of |
|
Schema name (case insensitive unless a quoted identifier is used) If |
|
Name of the object (case insensitive unless a quoted identifier is used) |
|
Source text of the compilation unit |
|
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
An associative array containing the lines of the post-processed source text starting from index 1
Usage Notes
-
Newline characters are not removed.
-
Each line in the post-processed source text is mapped to a row in the associative array.
-
In the post-processed source, unselected text has blank lines.
Exceptions
Table 5-3 GET_POST_PROCESSED_SOURCE Function Exceptions
Exception | Description |
---|---|
|
Insufficient privileges or non-existent object |
|
Bad value for object type (neither |
|
Empty source text |
|
Missing identifier The |
|
Numeric or value error:
|
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 5-4 PRINT_POST_PROCESSED_SOURCE Procedure Parameters
Parameter | Description |
---|---|
|
One of |
|
Schema name (case insensitive unless a quoted identifier is used) If |
|
Name of the object (case insensitive unless a quoted identifier is used) |
|
Source text of the compilation unit |
|
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 5-5 PRINT_POST_PROCESSED_SOURCE Procedure Exceptions
Exception | Description |
---|---|
|
Insufficient privileges or non-existent object |
|
Bad value for object type (neither |
|
Empty source text |
|
Missing identifier The |
|
Numeric or value error:
|