Tuning PL/SQL Code - Dynamic Wrapping with DBMS_DDL

(Page 4 of 5 )

Oracle Database 10g Release 2 provides a way to wrap code that is generated dynamically: the WRAP and CREATE_WRAPPED programs of the DBMS_DDL package:

DBMS_DDL.WRAP
   Returns a string containing an obfuscated version of
   your code

DBMS_DDL.CREATE_WRAPPED
   Compiles an obfuscated version of your code into the
   database

Both programs are overloaded to work with a single string and with arrays of strings based on the DBMS_SQL.VARCHAR2A and DBMS_SQL.VARCHAR2S collection types. Here are two examples that use these programs:

• Obfuscate and display a string that creates a tiny procedure:
  
    SQL> DECLARE
      2     l_program   VARCHAR2 (32767);
      3  BEGIN
      4     l_program := 'CREATE OR REPLACE PROCEDURE dont_look IS BEGIN NULL; END;';
      5     DBMS_OUTPUT.put_line (SYS.DBMS_DDL.wrap (l_program));
      6  END;
      7 /
    CREATE OR REPLACE PROCEDURE dont_look wrapped
  
    a000000
    369
    abcd
    ....  
    XtQ19EnOI8a6hBSJmk2NebMgPHswg5nnm7+ fMr2ywFy4CP6Z9P4I/v4rpXQruMAy/tJepZmB 
    CC0r
    uIHHLcmmpkOCnm4= 
  
• Read a PL/SQL program definition from a file, obfuscate it, and compile it into the database:
  
    /* File on web: obfuscate_from_file.sql */
    CEATE OR REPLACE PROCEDURE obfuscate_from_file (
      dir_in   IN   VARCHAR2
    , file_in  IN   VARCHAR2
    )
    IS
       l_file   UTL_FILE.file_type;
       l_lines  DBMS_SQL.varchar2s;
  
       PROCEDURE read_file (lines_out IN OUT NOCOPY DBMS_SQL.varchar2s)
       IS BEGIN ... not critical to the example ... END read_file;
    BEGIN
       read_file (l_lines);
       SYS.DBMS_DDL.create_wrapped (l_lines, l_lines.FIRST, l_lines.LAST);
    END obfuscate_from_file;

Next: Guidelines for Working with Wrapped Code >>
 

More Oracle Articles
More By O'Reilly Media

Comments On: Tuning PL/SQL Code

· This article is an excerpt from the book "Oracle PL/SQL Programming, Fourth...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.