CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_PREPROCESSOR
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmspp.sql
Defined Data Type TYPE source_lines_t IS TABLE OF VARCHAR2(32767)
INDEX BY BINARY_INTEGER;
Dependencies

ALL_SOURCE

DBMS_SYS_ERROR

PLITBLM

DBMS_OUTPUT

DUAL

 
Exceptions
Error Code Reason
ORA-00931 Missing identifier. The object_name should not be NULL
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 The source text is empty
ORA-24241 Wrapped input
ORA-06502 Numeric or value error
Security Model Execute is granted to PUBLIC during installation
 
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 Procedures

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
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [151 users online]    © 2010 psoug.org