Oracle Experience: PL/SQL API of Wrap Utility

31 October 2011

PL/SQL API of Wrap Utility - DBMS_DDL package

Sometimes it is necessary to hide the source code because of some security concerns or copyright procedures. (but note that there are some tools to un-wrap a wrapped code, like de-compiling) I prefer wrapping pl/sql source codes. Because, it is safer, nobody cannot change and compile it. Testers also cannot see the code, so they can concentrate the business rules.

To wrap(encrypt, compress) a pl/sql source code, there is a utility executable -named wrap- that is shiped with Oracle software. (for more information please read this…. )

Now, this wrap utility has a PL/SQL API via DBMS_DDL package. It is possible to get a wrapped code or directly compile it in the database easily. Please see demonstration below:

p.s. : i use chr(56) instead of “;” my IDE has some problems with “;” J

A sample function that returns 1:

CREATE OR REPLACE FUNCTION GET_1 RETURN NUMBER IS

BEGIN

RETURN 1;

END;

Test it:

SELECT get_1 FROM dual;

Check dba_source:

SELECT LINE, TEXT FROM DBA_SOURCE WHERE NAME = 'GET_1' ORDER BY LINE;

LINE TEXT

---------- ----------------------------------

1 FUNCTION GET_1 RETURN NUMBER IS

2 BEGIN

3 RETURN 1;

4 END;

Get wrapped source

select SYS.DBMS_DDL.wrap( '

create or replace function get_1 return number

begin

return 1' || chr(59)||'

end' || chr(59)||'

' ) wrapped_source from DUAL;

WRAPPED_SOURCE

--------------------------------------------------------------------------------

create or replace function get_1 wrapped

a000000

abcd

38 69

xzk0ly8PFjTNqoXGivso2L4+wvYwg8eZgcfLCNL+XlquYvR4w+fAsr2ym16lmYEywLIGm3SL

wMAy/tKGCamhBKPHvpK+VMd/pqa+9ct/

Compile wrapped source in the database:

BEGIN

SYS.DBMS_DDL.create_wrapped( '

create or replace function get_1 return number

begin

return 1' || chr(59)||'

end' || chr(59)||'

' );

END;

Check dba_source again:

SELECT LINE, TEXT FROM DBA_SOURCE WHERE NAME = 'GET_1' ORDER BY LINE;

LINE TEXT

---------- --------------------------------------------------------------------------------

1 function get_1 wrapped