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
Explanation to the concept is very good, simple to understand and very useful…
Thankyou very much… for providing such a valuable information…
LikeLike
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/
LikeLike