Oracle DBMS_PREPROCESSOR
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Provides an interface to print or retrieve the source text of a PL/SQL unit in its post-processed form
AUTHID CURRENT_USER
Data Types TYPE source_lines_t IS TABLE OF VARCHAR2(32767)
INDEX BY BINARY_INTEGER;
Dependencies
ALL_SOURCE DBMS_STANDARD PLITBLM
DBMS_OUTPUT DBMS_SYS_ERROR  
Documented Yes
Exceptions
Error Code Reason
ORA-00931 Missing identifier. The object_name should not be NULL
ORA-06502 Numeric or value error
ORA-24234 Insufficient privileges or object does not exist
ORA-24235 Bad value for object type. Should be one of PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TYPE, TYPE, BODY or TRIGGER.
ORA-24236 Source text is empty
ORA-24241 Source text is in wrapped format
First Available 10gR2
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmspp.sql
Subprograms
 
GET_POST_PROCESSED_SOURCE
Returns post-processed source text

Overload 1
dbms_preprocessor.get_post_processed_source (
object_type IN VARCHAR2, 
schema_name IN VARCHAR2,
object_name IN VARCHAR2)
RETURN source_lines_t;
CREATE OR REPLACE FUNCTION test_func RETURN NUMBER IS
 i user_tables.blocks%TYPE := 1;
BEGIN
  -- my demo comment in the code
  i := i + 1;
  RETURN i;
END test_func;
/

set serveroutput on

DECLARE
 retval_t  dbms_preprocessor.source_lines_t;
BEGIN
  retval_t := dbms_preprocessor.get_post_processed_source(
  'FUNCTION', 'UWCLASS', 'TEST_FUNC');

  FOR i IN 1..retval_t.LAST
  LOOP
    dbms_output.put_line(retval_t(i));
  END LOOP;
END;
/
Overload 2 dbms_preprocessor(source IN VARCHAR2) RETURN source_lines_t;
set serveroutput on

DECLARE
 instring  VARCHAR2(32767);
 retval_t  dbms_preprocessor.source_lines_t;
BEGIN
  instring := 'BEGIN ' || 
              '  FOR i IN 1 .. 100 LOOP ' ||
              '    NULL; ' ||
              '  END LOOP; ' ||
              'END;';

  retval_t := dbms_preprocessor.get_post_processed_source(instring);

  FOR i IN 1..retval_t.LAST
  LOOP
    dbms_output.put_line(retval_t(i));
  END LOOP;
END;
/
Overload 3 dbms_preprocessor(source_lines_t IN VARCHAR2) RETURN source_lines_t;
set serveroutput on

DECLARE
 inval_t  dbms_preprocessor.source_lines_t;
 retval_t dbms_preprocessor.source_lines_t;
BEGIN
  inval_t(1) := 'BEGIN ';
  inval_t(2) := '  FOR i IN 1 .. 100 LOOP ';
  inval_t(3) := '    NULL; ';
  inval_t(4) := '  END LOOP; ';
  inval_t(5) := 'END;';

  retval_t := dbms_preprocessor.get_post_processed_source(inval_t);

  FOR i IN 1..retval_t.LAST
  LOOP
    dbms_output.put_line(retval_t(i));
  END LOOP;
END;
/
 
PRINT_POST_PROCESSED_SOURCE
Given a stored PL/SQL unit, print its post-processed source text

Overload 1
dbms_preprocessor.print_post_processed_source(
object_type IN VARCHAR2,
schema_name IN VARCHAR2,
object_name IN VARCHAR2);
set serveroutput on

BEGIN
  dbms_preprocessor.print_post_processed_source('FUNCTION', 'UWCLASS', 'TEST_FUNC');
END;
/
Overload 2 dbms_preprocessor.print_post_processed_source(source VARCHAR2);
set serveroutput on

DECLARE
 str VARCHAR2(32767) := 'BEGIN NULL; END;';
BEGIN
  dbms_preprocessor.print_post_processed_source(str);
END;
/
Overload 3 dbms_preprocessor.print_post_processed_source(source source_lines_t);
set serveroutput on

DECLARE
 inval_t dbms_preprocessor.source_lines_t;
BEGIN
  inval_t(1) := 'BEGIN ';
  inval_t(2) := 'FOR i IN 1 .. 100 LOOP ';
  inval_t(3) := 'NULL; ';
  inval_t(4) := 'END LOOP; ';
  inval_t(5) := 'END;';

  dbms_preprocessor.print_post_processed_source(inval_t);
END;
/

Related Topics
DBMS_METADATA
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved