PL/SQL Wrapper utility

As a application developer  we do not always want our code displayed to the outside world
when writing an application. In some cases, the features are proprietary and we
need to protect the intellectual capital that is invested in them. In other cases, we
simply want to prevent the code from being modified by the user in order to avoid
problems .

Oracle provides a way to hide code with the PL/SQL Wrapper utility. When
source code has been wrapped, not only is the file unreadable, but also when it
is loaded into the database, the code cannot be read in the data dictionary.

The wrapper utility does not encrypt the code. Instead, it converts it to
hexadecimal digits so that it cannot be read or edited.

example:

SQL> CREATE TABLE lecturer (
2       id               NUMBER(5) PRIMARY KEY,
3       first_name       VARCHAR2(20),
4       last_name        VARCHAR2(20),
5       major            VARCHAR2(30),
6       current_credits  NUMBER(3)
7       );

Table created.

SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10001, ‘Scott’, ‘Lawson’,’Computer Science’, 11);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2   VALUES (10002, ‘Mar’, ‘Wells’,’History’, 4);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10003, ‘Jone’, ‘Bliss’,’Computer Science’, 8);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10004, ‘Man’, ‘Kyte’,’Economics’, 8);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10005, ‘Pat’, ‘Poll’,’History’, 4);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10006, ‘Tim’, ‘Viper’,’History’, 4);

1 row created.
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2  VALUES (10007, ‘Barbara’, ‘Blues’,’Economics’, 7);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10008, ‘David’, ‘Large’,’Music’, 4);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10009, ‘Chris’, ‘Elegant’,’Nutrition’, 8);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10010, ‘Rose’, ‘Bond’,’Music’, 7);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10011, ‘Rita’, ‘Johnson’,’Nutrition’, 8);

1 row created.
SQL>  INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2   VALUES (10012, ‘Sharon’, ‘Clear’,’Computer Science’, 3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from lecturer;

ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
———- ——————– ——————– —————————— —————
10001 Scott                Lawson               Computer Science                            11
10002 Mar                  Wells                History                                      4
10003 Jone                 Bliss                Computer Science                             8
10004 Man                  Kyte                 Economics                                    8
10005 Pat                  Poll                 History                                      4
10006 Tim                  Viper                History                                      4
10007 Barbara              Blues                Economics                                    7
10008 David                Large                Music                                        4
10009 Chris                Elegant              Nutrition                                    8
10010 Rose                 Bond                 Music                                        7
10011 Rita                 Johnson              Nutrition                                    8

ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS
———- ——————– ——————– —————————— —————
10012 Sharon               Clear                Computer Science                             3

12 rows selected.

i have a file named newrec which has the following stored procedure,

D:\>type newrec.sql
CREATE OR REPLACE PROCEDURE AddNewStudent (
p_FirstName  lecturer.first_name%TYPE,
p_LastName   lecturer.last_name%TYPE,
p_Major      lecturer.major%TYPE) AS
BEGIN
INSERT INTO lecturer (ID, first_name, last_name,
major, current_credits)
VALUES (1, p_FirstName, p_LastName,p_Major, 0);
END AddNewStudent;
/

Now i am going to wrap this file by using the pl/sql wrapper utility.

D:\>wrap iname=newrec.sql oname=newrec_wrap.plb

PL/SQL Wrapper: Release 10.2.0.4.0- Production on Wed Mar 24 15:11:28 2010

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing newrec.sql to newrec_wrap.plb

Now the warped file looks like

D:\>type newrec_wrap.plb
CREATE OR REPLACE PROCEDURE AddNewStudent wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
13c 10b
Ud07xFlyvsFfWzUuUo/ECDS1uwMwgzLQf5kVfC8CkE7VSPOjV6LB3C2tbCWH2WzMbkWgCl6V
nSCroGKsIM/Pz7vijegUtkZn/gAolngi+O5/5ugOzdwrbhdMtmfpY9CJigmxLkM8mroV9ArS
H7pTJvivJxDeNgnJVjK9+jXaRIB7uiunpPRb2bxYzIXYNVSL84v0EbW3GoAW1uctu/KoC+3Z
PVzqN3aRW3Gd9AoPpceWFSKWUU+0qd9AKYvPfMwrfhnBkGlD

/

Now this wrapper file will get compiled without any errors.

SQL> @D:\newrec_wrap.plb

Procedure created.

Even in the data dictionary only your wrapped code exists.
SQL> select text from user_source where NAME=’ADDNEWSTUDENT’;

TEXT
——————————————————————————–
PROCEDURE AddNewStudent wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd

TEXT
——————————————————————————–
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
13c 10b
Ud07xFlyvsFfWzUuUo/ECDS1uwMwgzLQf5kVfC8CkE7VSPOjV6LB3C2tbCWH2WzMbkWgCl6V
nSCroGKsIM/Pz7vijegUtkZn/gAolngi+O5/5ugOzdwrbhdMtmfpY9CJigmxLkM8mroV9ArS

TEXT
——————————————————————————–
H7pTJvivJxDeNgnJVjK9+jXaRIB7uiunpPRb2bxYzIXYNVSL84v0EbW3GoAW1uctu/KoC+3Z
PVzqN3aRW3Gd9AoPpceWFSKWUU+0qd9AKYvPfMwrfhnBkGlD

About these ads
  1. #1 by Subrahmanyam on July 25, 2011 - 11:07 am

    Explanation to the concept is very good, simple to understand and very useful…
    Thankyou very much… for providing such a valuable information…

  2. #2 by appstier on April 30, 2013 - 5:08 pm

    Thanks for your post.this is some what use full

    And you can also check for any issues and tips and trouble shooting related to appsdba 11i and R12 on http://www.appstier.blogspot.in/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: