View source for Oracle protecting PL/SQL sourcecode

[[Category:Oracle]]
[[Category:201112]]

In Oracle, packages, procedures and functions can be encoded to prevent others reusing the sourcecode. For this you would ''Obfuscate'' the sourcecode to an unreadable form with the ''Wrap'' function.

More info:
* http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/wrap.htm

Note:
* you need keep a copy of the original sourcecode, the obfuscated code can not be reused
* it is not possible to use substitution variables like '&variable.' in the sourcecode
* triggers can not be obfuscated
* obfuscated PL/SQL can not be used for debugging

Example package:

<syntaxhighlight lang=sql>
-- package specification
CREATE OR REPLACE PACKAGE OBFUSCATE AS

  g_version varchar2(30) := '1.0';

  function get_version return varchar2;  
  procedure set_version(v varchar2);
END;
/

-- package body
CREATE OR REPLACE PACKAGE BODY OBFUSCATE AS

 function get_version return varchar2 AS
 BEGIN
   RETURN g_version;
 END;
 
 procedure set_version(v varchar2) IS
 BEGIN
   -- value remains only for the current session
   g_version:=v;
 END;
END;
/
</syntaxhighlight>

De package sourcecode can be retrieved with this function, when obfuscated you would see a set of meeningless characters:

<syntaxhighlight lang=sql>
select dbms_metadata.get_ddl('PACKAGE_BODY','OBFUSCATE','SCHEMANAME')
  from dual
  ;
</syntaxhighlight>

Note: the following CREATE_WRAPPED function uses the DBMS_SQL.VARCHAR2A type to enable it to encode larger-than 32Kb sourcecode:

<syntaxhighlight lang=sql>
DECLARE
  packagename varchar2(30):='OBFUSCATE';
  schemaname varchar2(30):='SCHEMANAME';
  packagebody CLOB;
  packagebody2a DBMS_SQL.VARCHAR2A;
  checkwrapped number(5);
  i number(5) ;
  len number(11);
BEGIN
  packagebody := dbms_metadata.get_ddl('PACKAGE_BODY',packagename,schemaname);
  checkwrapped := instr(packagebody, '" wrapped');
  IF checkwrapped>0 AND checkwrapped<60 THEN
    raise_application_error(-20000,'Package '||schemaname||'.'||packagename||' already obfuscated');
  ELSE
    i:=1;
    len:=length(packagebody);
    while (i-1)*32000+1<len loop
      packagebody2a(i):=substr(packagebody,(i-1)*32000+1,i*32000);
      i:=i+1;
    end loop;
    DBMS_DDL.CREATE_WRAPPED(packagebody2a,1,packagebody2a.count);
  END IF;
END;
/
</syntaxhighlight>