I want to write an wrapped procedure in Oracle which is invisible so that no one can drop it.

Posted on 2009-04-25
Last Modified: 2012-05-06
I want to write an wrapped procedure in Oracle.
That should not  list in the procedure names so that no one will be able to drop it.

Presume  GIVE_RAISE is an wrapped procuedue.

SQL> select text from user_source where name = 'GIVE_RAISE';
The above statment should give a result
"no rows selected"
instead of displaying the content in the encryted format.

Normally wrapped procedure is like below .

C:\oracle9i\bin>wrap iname=giveraise.sql oname=give_raise_encrypted.sql

SQL> @c:\oracle9i\bin\give_raise_encrypted.sql

Procedure Created. 

Now, another query against user_source shows the newly wrapped code: 

SQL> select text from user_source where name = 'GIVE_RAISE';



procedure give_raise wrapped











0 9a 8f :2 a0 6b :2 a0 f b0

3d 8f a0 b0 3d b4 55 6a

a0 7e 51 b4 2e :3 a0 7e 51

b4 2e e7 :2 a0 7e b4 2e ef

f9 e9 b7 19 3c b7 a4 b1

11 68 4f 1d 17 b5


Open in new window

Question by:bethel777

    Author Comment

    Appreciate an example is listed.
    LVL 142

    Accepted Solution

    you cannot do that.
    you can obfuscate the code, you can encrypt it, you can grant/deny permissions so only the owner can drop/alter it, but you cannot hide it completely from the database.

    Author Comment

    Can I wrap a view ?

    When I write a sql
    Create or replace view efgh wrapped
    as select * from abcd

    it throws out an error  " missing keyword"
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >Can I wrap a view ?
    can you clarify?

    the error you get is the "wrapped" that comes although "as" is expected...

    Author Comment

    Like wrapping a procedure  I want to create a wrapped view so that the source code is protected.
    for eg below

    SQL> Create or replace view efgh as select * from abcd
                       creates a view 'efgh' of the table 'abcd'
    I want to modify the above source code so that  so that user will not know what is that 'efgh'
    if I do wrap iname=ab.sql  oname=ef.sql the resulting query shows the code if it is not wrapped.

    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    I found the tool "wrap" that you are looking for:

    so, you do 2 things:
    § wrap the function/view/package so the code is unreadable (remember to keep a copy of the code in a (protected) .sql file so that you still have the code as such!!!!
    § use access control so that views can only be selected, but not be altered/dropped.

    Author Comment

    I had gone thru the same.I know to write the code for wrapping procedures and creating wrapped sqls for the same,
    But writing code to wrap views is confusing.

    Pls help me by converting the below example to protect the source code.

    SQL> Create or replace view efgh as select * from abcd;

    where 'abcd' is the table and 'efgh' is the view.

    LVL 7

    Assisted Solution

    I think you can't wrap the views and triggers. Only procedures, functions and packages can be wrapped. If you want to wrap a trigger, you can call a wrapped procedure inside the trigger.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Query with "like" last 7 digits 3 71
    Get a single value from sqlplus Oracle query 3 55
    Oracle to_char 21 52
    UNIX SCP 5 27
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now