Home arrow Oracle arrow Page 4 - Tuning PL/SQL Code

Dynamic Wrapping with DBMS_DDL - Oracle

In this conclusion to a nine-part series on managing PL/SQL code, you will learn how to analyze and optimize your code's performance. 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). Copyright 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Tuning PL/SQL Code
  2. Optimizing PL/SQL Performance
  3. Protecting Stored Code
  4. Dynamic Wrapping with DBMS_DDL
  5. Guidelines for Working with Wrapped Code
By: O'Reilly Media
Rating: starstarstarstarstar / 22
December 20, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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;



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: