In this article, I’ve included the easiest possible way to wrap any piece of existing Pl/SQL code, using an anonymous Pl/SQL block. If you’ve stumbled on this page wondering “what does it mean to wrap Pl/SQL?” just know that it means that we’re intentionally making code unreadable. A few things to note:
- WARNING: This could corrupt your code. Make sure you’ve backed your code up.
- WARNING: This will wrap your code. Really–make sure your code is backed up.
- Wrapping code isn’t a 100% foolproof method to prevent someone from reading your code. If someone wants to know is inside a wrapped package, there are ways for someone to figure that out, even if all they have is the wrapped code.
- WARNING: This code doesn’t come with any warranty, expressed or implied. Use at your own risk.
To wrap a Pl/SQL Package (Both Package Spec and Package Body)
- Set PACKAGE_OWNER to be whoever owns the package.
- Set PACKAGE_NAME to whatever the name of the package is.
- Double check that you’ve backed up the “unwrapped” version of the code, in case you need to modify it later (do this even if you *think* you won’t need to modify it later).
- Triple check that you’ve backed the code up. Both your package spec and your package body. Seriously.
- Once you’re sure you’ve backed the code up, set IVE_BACKED_UP_UNWRAPPED_CODE to true, and run the below block.
Don’t try to wrap an already-wrapped package–it won’t work out very well for you. Also, for this method, you have to make sure that none of the lines in your package exceed 32,767 characters.
declare
PACKAGE_OWNER constant varchar2(128) := 'OWNER_OF_MY_PACKAGE';
PACKAGE_NAME constant varchar2(128) := 'MY_PACKAGE_NAME';
IVE_BACKED_UP_UNWRAPPED_CODE constant boolean := false;
v_clob_source clob;
procedure remove_leading_whitespace(p_clob_source in out clob)
is
SEARCH_STRING constant varchar2(100) := 'CREATE';
v_saved_index number;
begin
<<character_loop>>
for v_index in 1 .. 99 loop
if substr(p_clob_source, v_index, length(SEARCH_STRING)) = SEARCH_STRING then
v_saved_index := v_index;
exit;
end if;
end loop character_loop;
p_clob_source := substr(p_clob_source, v_saved_index);
end remove_leading_whitespace;
procedure get_rid_of_editionable_keyword(p_clob_source in out clob)
is
START_DEFINITION constant varchar2(100) := 'CREATE OR REPLACE EDITIONABLE ';
REPLACE_DEFINITION constant varchar2(100) := 'CREATE OR REPLACE ';
begin
if substr(p_clob_source, 1, length(START_DEFINITION)) = START_DEFINITION then
p_clob_source := REPLACE_DEFINITION || substr(p_clob_source, length(START_DEFINITION) + 1);
end if;
end get_rid_of_editionable_keyword;
procedure replace_windows_newlines(p_clob_source in out clob)
is
begin
p_clob_source := replace(p_clob_source, chr(13)||chr(10), chr(10));
end replace_windows_newlines;
function get_next_line(p_clob_source in out clob)
return varchar2
is
v_chr10 number;
MAXIMUM_VARCHAR2A_SIZE pls_integer := 32767;
v_return_str varchar2(32767);
begin
v_chr10 := instr(p_clob_source, chr(10));
if v_chr10 = 0 and dbms_lob.getlength(p_clob_source) <= MAXIMUM_VARCHAR2A_SIZE then
v_chr10 := dbms_lob.getlength(p_clob_source);
end if;
if v_chr10 <= MAXIMUM_VARCHAR2A_SIZE then
v_return_str := substr(p_clob_source, 1, v_chr10);
p_clob_source := substr(p_clob_source, v_chr10 + 1);
return v_return_str;
end if;
raise_application_error(-20101, 'Code cannot contain a single line longer than ' || MAXIMUM_VARCHAR2A_SIZE || ' bytes!');
end get_next_line;
begin
if IVE_BACKED_UP_UNWRAPPED_CODE = false or IVE_BACKED_UP_UNWRAPPED_CODE is null then
raise_application_error(-20100, 'First, back up the orignal package (both the spec and the body) to someplace safe. Then set IVE_BACKED_UP_UNWRAPPED_CODE to true.');
end if;
-- Get the package spec
select replace(dbms_metadata.get_ddl('PACKAGE_SPEC', PACKAGE_NAME, PACKAGE_OWNER), chr(13)) into v_clob_source
from dual;
remove_leading_whitespace(v_clob_source);
get_rid_of_editionable_keyword(v_clob_source);
replace_windows_newlines(v_clob_source);
<<convert_clob_to_array>>
declare
v_loop_index number := 1;
INFINITE_LOOP_THRESHOLD constant number := 9999999;
v_array dbms_sql.varchar2a;
v_wrap dbms_sql.varchar2a;
begin
<<loop_line_by_line>>
loop
if v_loop_index > INFINITE_LOOP_THRESHOLD then
raise_application_error(-20102, 'Either code has more than ' || INFINITE_LOOP_THRESHOLD || ' lines, or we have detected an infinite loop');
end if;
v_array(v_loop_index) := get_next_line(v_clob_source);
exit when length(v_clob_source) < 1 or length(v_clob_source) is null;
v_loop_index := v_loop_index + 1;
end loop loop_line_by_line;
sys.dbms_ddl.create_wrapped( ddl => v_array, lb => 1, ub => v_loop_index );
end convert_clob_to_array;
------------------------------------------------------
-- Do the same thing, but with the package body now
------------------------------------------------------
-- Get the package body
select replace(dbms_metadata.get_ddl('PACKAGE_BODY', PACKAGE_NAME, PACKAGE_OWNER), chr(13)) into v_clob_source
from dual;
remove_leading_whitespace(v_clob_source);
get_rid_of_editionable_keyword(v_clob_source);
replace_windows_newlines(v_clob_source);
<<convert_clob_to_array>>
declare
v_loop_index number := 1;
INFINITE_LOOP_THRESHOLD constant number := 9999999;
v_array dbms_sql.varchar2a;
v_wrap dbms_sql.varchar2a;
begin
<<loop_line_by_line>>
loop
if v_loop_index > INFINITE_LOOP_THRESHOLD then
raise_application_error(-20103, 'Either code has more than ' || INFINITE_LOOP_THRESHOLD || ' lines, or we have detected an infinite loop');
end if;
v_array(v_loop_index) := get_next_line(v_clob_source);
exit when length(v_clob_source) < 1 or length(v_clob_source) is null;
v_loop_index := v_loop_index + 1;
end loop loop_line_by_line;
sys.dbms_ddl.create_wrapped( ddl => v_array, lb => 1, ub => v_loop_index );
end convert_clob_to_array;
end;
/To Wrap a Pl/SQL Procedure
- Set PROCEDURE_OWNER to be whoever owns the procedure.
- Set PROCEDURE_NAME to whatever the name of the procedure is.
- Double check that you’ve backed up the “unwrapped” version of the code, in case you need to modify it later (do this even if you *think* you won’t need to modify it later).
- Triple check that you’ve backed the code up. Seriously.
- Once you’re sure you’ve backed the code up, set IVE_BACKED_UP_UNWRAPPED_CODE to true, and run the below block.
Don’t try to wrap an already-wrapped procedure–it won’t work out very well for you. Also, for this method, you have to make sure that none of the lines in your procedure exceed 32,767 characters.
declare
PROCEDURE_OWNER constant varchar2(128) := 'OWNER_OF_MY_PROCEDURE';
PROCEDURE_NAME constant varchar2(128) := 'MY_PROCEDURE_NAME';
IVE_BACKED_UP_UNWRAPPED_CODE constant boolean := false;
v_clob_source clob;
procedure remove_leading_whitespace(p_clob_source in out clob)
is
SEARCH_STRING constant varchar2(100) := 'CREATE';
v_saved_index number;
begin
<<character_loop>>
for v_index in 1 .. 99 loop
if substr(p_clob_source, v_index, length(SEARCH_STRING)) = SEARCH_STRING then
v_saved_index := v_index;
exit;
end if;
end loop character_loop;
p_clob_source := substr(p_clob_source, v_saved_index);
end remove_leading_whitespace;
procedure get_rid_of_editionable_keyword(p_clob_source in out clob)
is
START_DEFINITION constant varchar2(100) := 'CREATE OR REPLACE EDITIONABLE ';
REPLACE_DEFINITION constant varchar2(100) := 'CREATE OR REPLACE ';
begin
if substr(p_clob_source, 1, length(START_DEFINITION)) = START_DEFINITION then
p_clob_source := REPLACE_DEFINITION || substr(p_clob_source, length(START_DEFINITION) + 1);
end if;
end get_rid_of_editionable_keyword;
procedure replace_windows_newlines(p_clob_source in out clob)
is
begin
p_clob_source := replace(p_clob_source, chr(13)||chr(10), chr(10));
end replace_windows_newlines;
function get_next_line(p_clob_source in out clob)
return varchar2
is
v_chr10 number;
MAXIMUM_VARCHAR2A_SIZE pls_integer := 32767;
v_return_str varchar2(32767);
begin
v_chr10 := instr(p_clob_source, chr(10));
if v_chr10 <= MAXIMUM_VARCHAR2A_SIZE then
v_return_str := substr(p_clob_source, 1, v_chr10);
if v_chr10 = 0 and dbms_lob.getlength(p_clob_source) <= MAXIMUM_VARCHAR2A_SIZE then
v_chr10 := dbms_lob.getlength(p_clob_source);
end if;
if v_chr10 <= MAXIMUM_VARCHAR2A_SIZE then
v_return_str := substr(p_clob_source, 1, v_chr10);
p_clob_source := substr(p_clob_source, v_chr10 + 1);
return v_return_str;
end if;
raise_application_error(-20101, 'Code cannot contain a single line longer than ' || MAXIMUM_VARCHAR2A_SIZE || ' bytes!');
end get_next_line;
begin
if IVE_BACKED_UP_UNWRAPPED_CODE = false or IVE_BACKED_UP_UNWRAPPED_CODE is null then
raise_application_error(-20100, 'First, back up the orignal procedure to someplace safe. Then set IVE_BACKED_UP_UNWRAPPED_CODE to true.');
end if;
-- Get the package spec
select replace(dbms_metadata.get_ddl('PROCEDURE', PROCEDURE_NAME, PROCEDURE_OWNER), chr(13)) into v_clob_source
from dual;
remove_leading_whitespace(v_clob_source);
get_rid_of_editionable_keyword(v_clob_source);
replace_windows_newlines(v_clob_source);
<<convert_clob_to_array>>
declare
v_loop_index number := 1;
INFINITE_LOOP_THRESHOLD constant number := 9999999;
v_array dbms_sql.varchar2a;
v_wrap dbms_sql.varchar2a;
begin
<<loop_line_by_line>>
loop
if v_loop_index > INFINITE_LOOP_THRESHOLD then
raise_application_error(-20102, 'Either code has more than ' || INFINITE_LOOP_THRESHOLD || ' lines, or we have detected an infinite loop');
end if;
v_array(v_loop_index) := get_next_line(v_clob_source);
exit when length(v_clob_source) < 1 or length(v_clob_source) is null;
v_loop_index := v_loop_index + 1;
end loop loop_line_by_line;
sys.dbms_ddl.create_wrapped( ddl => v_array, lb => 1, ub => v_loop_index );
end convert_clob_to_array;
end;
/To Wrap a Pl/SQL Function
- Set FUNCTION_OWNER to be whoever owns the function.
- Set FUNCTION_NAME to whatever the name of the function is.
- Double check that you’ve backed up the “unwrapped” version of the code, in case you need to modify it later (do this even if you *think* you won’t need to modify it later).
- Triple check that you’ve backed the code up. Seriously.
- Once you’re sure you’ve backed the code up, set IVE_BACKED_UP_UNWRAPPED_CODE to true, and run the below block.
Don’t try to wrap an already-wrapped function–it won’t work out very well for you. Also, for this method, you have to make sure that none of the lines in your procedure exceed 32,767 characters.
declare
FUNCTION_OWNER constant varchar2(128) := 'OWNER_OF_MY_FUNCTION';
FUNCTION_NAME constant varchar2(128) := 'MY_FUNCTION_NAME';
IVE_BACKED_UP_UNWRAPPED_CODE constant boolean := false;
v_clob_source clob;
procedure remove_leading_whitespace(p_clob_source in out clob)
is
SEARCH_STRING constant varchar2(100) := 'CREATE';
v_saved_index number;
begin
<<character_loop>>
for v_index in 1 .. 99 loop
if substr(p_clob_source, v_index, length(SEARCH_STRING)) = SEARCH_STRING then
v_saved_index := v_index;
exit;
end if;
end loop character_loop;
p_clob_source := substr(p_clob_source, v_saved_index);
end remove_leading_whitespace;
procedure get_rid_of_editionable_keyword(p_clob_source in out clob)
is
START_DEFINITION constant varchar2(100) := 'CREATE OR REPLACE EDITIONABLE ';
REPLACE_DEFINITION constant varchar2(100) := 'CREATE OR REPLACE ';
begin
if substr(p_clob_source, 1, length(START_DEFINITION)) = START_DEFINITION then
p_clob_source := REPLACE_DEFINITION || substr(p_clob_source, length(START_DEFINITION) + 1);
end if;
end get_rid_of_editionable_keyword;
procedure replace_windows_newlines(p_clob_source in out clob)
is
begin
p_clob_source := replace(p_clob_source, chr(13)||chr(10), chr(10));
end replace_windows_newlines;
function get_next_line(p_clob_source in out clob)
return varchar2
is
v_chr10 number;
MAXIMUM_VARCHAR2A_SIZE pls_integer := 32767;
v_return_str varchar2(32767);
begin
v_chr10 := instr(p_clob_source, chr(10));
if v_chr10 = 0 and dbms_lob.getlength(p_clob_source) <= MAXIMUM_VARCHAR2A_SIZE then
v_chr10 := dbms_lob.getlength(p_clob_source);
end if;
if v_chr10 <= MAXIMUM_VARCHAR2A_SIZE then
v_return_str := substr(p_clob_source, 1, v_chr10);
p_clob_source := substr(p_clob_source, v_chr10 + 1);
return v_return_str;
end if;
raise_application_error(-20101, 'Code cannot contain a single line longer than ' || MAXIMUM_VARCHAR2A_SIZE || ' bytes!');
end get_next_line;
begin
if IVE_BACKED_UP_UNWRAPPED_CODE = false or IVE_BACKED_UP_UNWRAPPED_CODE is null then
raise_application_error(-20100, 'First, back up the orignal procedure to someplace safe. Then set IVE_BACKED_UP_UNWRAPPED_CODE to true.');
end if;
-- Get the package spec
select replace(dbms_metadata.get_ddl('FUNCTION', FUNCTION_NAME, FUNCTION_OWNER), chr(13)) into v_clob_source
from dual;
remove_leading_whitespace(v_clob_source);
get_rid_of_editionable_keyword(v_clob_source);
replace_windows_newlines(v_clob_source);
<<convert_clob_to_array>>
declare
v_loop_index number := 1;
INFINITE_LOOP_THRESHOLD constant number := 9999999;
v_array dbms_sql.varchar2a;
v_wrap dbms_sql.varchar2a;
begin
<<loop_line_by_line>>
loop
if v_loop_index > INFINITE_LOOP_THRESHOLD then
raise_application_error(-20102, 'Either code has more than ' || INFINITE_LOOP_THRESHOLD || ' lines, or we have detected an infinite loop');
end if;
v_array(v_loop_index) := get_next_line(v_clob_source);
exit when length(v_clob_source) < 1 or length(v_clob_source) is null;
v_loop_index := v_loop_index + 1;
end loop loop_line_by_line;
sys.dbms_ddl.create_wrapped( ddl => v_array, lb => 1, ub => v_loop_index );
end convert_clob_to_array;
end;
/Free Oracle SQL Tuning Guide
Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
hi,
when using these tools to wrap code, is it possible to password protect so un-wrap utilities will not work?
regards
Alan
Hi Alan!
Great question, there’s a lot to unpack with it. There’s no way to password-encrypt your code using Oracle’s wrap utility, and Oracle’s wrap utility is easily broken. You can easily find sites and utilities that help you “unwrap” code.
There are other things you can do.
1.) You might try googling Pete Finnigan’s PFCLObfuscate (I’ve never used it myself, I’ve only heard about it).
2.) *Theoretically* you a way to write code that, when provided with an appropriate password, will self-decipher and run…but even then, there might be a chance that the user could acquire your deciphered code from the shared pool…so there are some considerations there. The key to remember here is that you would need the password to run the app, so if you’re talking about distributing Pl/SQL programs to others to have them run them, then your back to square one.
3.) Many of the Oracle packages do call-outs to binary files. You might try googling something similar to “native compilation pl/sql” for information surrounding how to set up Oracle with makefiles, etc to see how you can compile your code into a C file.
Hope this helps.
Thanks Kaley,
its for our own code that we run on client sites. i will have to hope that the client isnt savvy enough to unwrap the code
regards
Alan
Muchas gracias. ?Como puedo iniciar sesion?
how to setup this oracle wrap utility ? i was trying to wrap a sql but saying ‘wrap command not found’