Oracle DBMS_PREPROCESSOR
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_SYS_ERROR PLITBLM
DBMS_OUTPUT    
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

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-2014 Daniel A. Morgan All Rights Reserved