[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-04-25
Medium Priority
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
  • 4
  • 3

Author Comment

ID: 24231398
Appreciate an example is listed.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1344 total points
ID: 24231812
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

ID: 24234833
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"
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 143

Expert Comment

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

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

Author Comment

ID: 24235405
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1344 total points
ID: 24235498
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

ID: 24235577
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.


Assisted Solution

Fayyaz earned 672 total points
ID: 24236660
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 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