Search This Blog

Wednesday, May 21, 2008

The PL/SQL Wrap Utility - Hide source code in Oracle

A company has a code(Package, Procedure, Function etc) with all the proprietary information and logic in it. If this information is leaked out in the market then the competitors can take advantage of it and this can affect the business. One of the way to deal with this is to hide the code from others.
This can be achieved using oracle's WRAP utility. The advantage of WRAP utility is that this converts the source code into some language that is not understood but the code can still be compiled like any other source code.
Using Wrap is very simple. In the bin directory of Oracle Home, the wrap utility is installed. The file name could be WRAP.exe or WRAP80.exe depending on the oracle version installed.
Syntax

 C:\orant\BIN>wrap.exe iname=[inputfilename] oname=[outputfilename] 


e.g.
 C:\orant\BIN>wrap.exe iname=wrap_test.sql oname=wrap_test.plb 


An example of using WRAP

Create a sample procedure wrap_test using following code

CREATE OR REPLACE PROCEDURE wrap_test
IS
BEGIN
dbms_output.put_line('Wrap test complete');
END;
/
then call the wrap utility using following
wrap.exe iname=wrap_test.sql oname=wrap_test.plb

Content of new file wrap_test.plb
CREATE OR REPLACE PROCEDURE wrap_test wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4f 8d
LPjE3qKQyH/yQRCK4+efvSyST50wg5nnm7+fMr2ywFznKMB04yhSssvum3SLwMAy/tKGBvVS
m7JK/iiyveeysx0GMCyuJOqygaVR2+EC8XcG0wJd5GisbnfxwzIu9tHqJB/2OabWTW+0

/

It is very clear from this that the new code is not readable and so is completely hidden from others.
Drop your procedure(if already created) and recreate using the the new file wrap_test.plb which can be compiled as any other package. Important point here is that the source code will be hidden and cannot be read.
Another important point to remember is that once wrapped, a code cannot be unwrapped.

Thanks please let me know your feedback.

15 Comments:

Lakki Reddy Sreehari Reddy said...

Hi Suresh,
this is very good artical.
But i have small issue here.
yot said once wrapped, a code cannot be unwrapped.

If we want to add some more extra info. to code,then how we add that.

Suresh Vaishya said...

You need to store the unwrap version somewhere and need to use that to make any changes.
After making changes to the original package follow same steps to wrap and compile in the database.

Lakki Reddy Sreehari Reddy said...
This comment has been removed by a blog administrator.
Lakki Reddy Sreehari Reddy said...

Hi Suresh,

I want to dispaly order line information(all open orders.).
If any order was closed(in order header section FLOW_STATUS_CODE='CLOSED'),
those order line info also i want to retrieve (upto 30 days after closing the order header).

Please help me on this query.

Thank U,
Sreehari.

Suresh Vaishya said...

Sreehari,
Where do you want this order info to be retrieved.

Lakki Reddy Sreehari Reddy said...

from oe_order_headers and oe_order_lines.

I want to retieve order line information(all open orders.).
If any order was closed(in order header section FLOW_STATUS_CODE='CLOSED'),
those order line info also i want to retrieve (upto 30 days after closing the order header).

Please help me on this query.

I want selct statement for this requirement to retrieve line information.

Suresh Vaishya said...

Sreehari,
check following query

select ooh.order_number, ool.line_number, ool.inventory_item_id , ooh.flow_status_code
from oe_order_lines_all ool, oe_order_headers_all ooh
where 1=1
and ool.header_id = ooh.header_id
and ooh.open_flag = 'Y'
union
select ooh.order_number, ool.line_number, ool.inventory_item_id, ooh.flow_status_code
from oe_order_lines_all ool, oe_order_headers_all ooh
where 1=1
and ool.header_id = ooh.header_id
and ooh.open_flag = 'N'
and trunc(ooh.last_update_date) >= sysdate-30

Lakki Reddy Sreehari Reddy said...

Hi suresh,

Thank U for your help. your query is working.
But I have one doubt.you are taking last _update_date.
What happened if they update after closing the header.
this queries is not work.

Is there any way to findout exact closing date of header.(may be from work flow table) ?.

Suresh Vaishya said...

The order cannot be updated once closed. It is kind of frozen.

Lakki Reddy Sreehari Reddy said...

Hi suresh,
Thanks for your help.Its working.

I have one more wuery.

I am using 2 valuesets(2 parameters).

I am passing 2 values for 1st parameter (ex: X and Y). If i pass X for 1st parameter,second parameter disable.

If i pass Y for 1st parameter, 2nd parameter enable and user will pass some text in 2 nd parameter.

How i achieve this requirement,what code i write in value sets?

Thank U,
Sreehari.

Suresh Vaishya said...

Created a new post
http://sureshvaishya.blogspot.com/2008/05/disable-concurrent-program-parameter.html

Let me know if you have any questions.

Admin said...

Suresh,

I believe the code can be un-wrapped and there are some articles on this by Pete Finnigan's,
http://www.petefinnigan.com/orasec.htm.

Do you know of any utility to wrap a views in Oracle 9,10,11 versions.

Thanks,
sanjay

blogje said...

If I read your wrapped code it looks like:

PROCEDURE WRAP_TEST
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Wrap test complete');
END;

Rob said...

PL/SQL wrapped code can easily be unwrapped. Please check out the following discussion.
http://forums.oracle.com/forums/thread.jspa?threadID=995596&tstart=15


--Rob

sap upgrade assessment said...

Wow! This is very interesting and creative feature. You can use Wrap Utility to hide the source code by writing in some other language but still can be compiled in the same way. The code for wrap utility is simple. I am going to try it for my application. You can also give a try.

Copyright (c) All rights reserved. Presented by Suresh Vaishya