Oracle | DBMS_PREPROCESSOR

Oracle DBMS_PREPROCESSOR Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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: Packages and Types Reference

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 10.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmspp.sql

Subprograms

GET_POST_PROCESSED_SOURCE

PRINT_POST_PROCESSED_SOURCE

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 uwclass.test_func RETURN NUMBER 
      AUTHID DEFINER 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;

      /

      FUNCTION uwclass.test_func RETURN NUMBER AUTHID 
      DEFINER IS

      

      i user_tables.blocks%TYPE := 1;

      

      BEGIN

      

      -- my demo comment in the code

      

      i := i + 1;

      

      RETURN i;

      

      END test_func;

      

      PL/SQL procedure successfully completed.

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 IN 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 IN 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;

      /