Solution confusing
Bob Maggio, January 07, 2002 - 11:50 am UTC
I am not sure I understand the solution. It looks like I wrap the source code file? as in wrap iname=/dir/func1.sql oname=/dir/func1_wrap.sql
But I am still able to view both files. Do I then run those files in oracle and does it create the functions a different way? It didn't seem to.
January 07, 2002 - 12:45 pm UTC
you run func1_wrap.sql, you deliver func1_wrap.sql.
You keep func1.sql in case you ever need to modify the source code.
Take this for example:
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p
2 as
3 begin
4 dbms_output.put_line( 'Hello world' );
5 end;
6 /
Procedure created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select text from user_source where name= 'P';
TEXT
-----------------------------------------------------------------------------------------------------------------------------------
procedure p
as
begin
dbms_output.put_line( 'Hello world' );
end;
<b>now you see it...</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> host wrap iname=test.sql oname=test_wrap.sql
PL/SQL Wrapper: Release 8.1.7.2.0 - Production on Mon Jan 07 12:44:21 2002
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing test.sql to test_wrap.sql
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test_wrap.sql
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p wrapped
2 0
3 abcd
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 3
19 7
20 8106000
21 1
22 4
23 0
24 4
25 2 :e:
26 1P:
27 1DBMS_OUTPUT:
28 1PUT_LINE:
29 1Hello world:
30 0
31
...
86 /
Procedure created.
<b>and now you don't</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select text from user_source where name = 'P';
TEXT
-----------------------------------------------------------------------------------------------------------------------------------
procedure p wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
7
8106000
1
4
0
4
2 :e:
1P:
1DBMS_OUTPUT:
1PUT_LINE:
1Hello world:
0
...
ops$tkyte@ORA817DEV.US.ORACLE.COM>
Error on wrap
Bob Maggio, January 08, 2002 - 9:21 am UTC
I must have missed the error msg the first time, since it created the function, abeit with errors.
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Jan 08 09:20:37 2002
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing encrypt.sql to encrypt.plb
PSU(103,1,1,56):Encountered the symbol "" when expecting one of the following:
. @ % ; is authid deterministic parallel_enable as character
PL/SQL Wrapper error: Compilation error(s) for:
CREATE OR REPLACE function encrypt
Outputting source and continuing.
It seems I created the source on my NT machine, FTP'd the source to the UNIX box, and ran it there. The wrap program seemd to choke on the windows return chars. I removed them via VI and all is well. Thanks.
Nice example
Daryl, January 08, 2002 - 9:52 am UTC
Bob, when ftp-ing, set your transfer type to text using the "asc" command (if using command line ftp) or checking the correct box if using wsftp or Coffee Cup ftp. The default transfer mode is usually binary, which preserves end-of-line characters exactly. The text mode will transfer the source using the correct end-of-line translation. From Windows to UNIX, this means the carriage returns (^M) at the end of each line will be removed.
This is great
Mohammed Ahmed, January 09, 2002 - 6:19 am UTC
Can we encrypt "sqlplus" scripts also?
January 09, 2002 - 8:55 am UTC
create a procedure, wrap it, call that from sqlplus.
No, sqlplus scripts are "clear text", only packages/procedures/functions can be wrapped.
Wrapping Procedure
Anil, March 13, 2003 - 10:26 pm UTC
I created a procedure , i was able to wrap the code. I logged in thru OEM as sysdba , i am not able to see the code. Upto this level it is fine.
Now i executed the procedure, the procedure takes around 15 minutes to complete. My problem is that if i login thru OEM , go to Database-->Instance-->sessions. Now if i go to the particular user who is executing the procedure , and click on tab SQL, the full code is visible.
Is there any wayout to hide the code when the procedure is executing.
Thanks and regards,
March 14, 2003 - 5:53 pm UTC
the full PLSQL or just the SQL query?
there is no way to hide SQL -- no way.
??? PSU(103,1,211,46) ???
j., July 12, 2003 - 8:42 am UTC
hi tom, I tried to wrap a package for the very first time (source extracted from db), but got an error.
it seems to me as if some of those SQL features integrated in PL/SQL since 9i cause this to happen (in my case the "over" of an analytical function in a SQL statement within the code).
do one have to avoid these new features of 9i in order to get source code wrapped, or did I just do anything wrong?
July 12, 2003 - 9:39 am UTC
please file a tar with support for something like that, it would be a "bug"
"there is no way to hide SQL -- no way"
John, July 12, 2003 - 1:23 pm UTC
I've always wondered about the effects of wrapping.
Apparently the reuse of Sql is safe, but what about performance? (I know I should try it an see but I can't even generate a stored procedure where I'm at, no v$'s nothing)
Does wrapping create "p-code" so it actually runs faster?
or just scrambles it in some way causing delay in unscrabbling?
Would 9i's feature of compiling source make this feature obsolete?
10i?
July 12, 2003 - 2:44 pm UTC
wrapping code will not affect performance one way or the other -- it simply obscures the procedural code from prying eyes.
I still see the code
Reader, July 12, 2003 - 4:03 pm UTC
Tom,
SQL> select text from user_source where name = 'P';
TEXT
---------------------------------------------------------------------
procedure p wrapped
0
abcd
abcd
abcd
abcd
abcd
9200000
1
4
0
4
2 :e:
1P:
1DBMS_OUTPUT: <--------------------
1PUT_LINE: <--------------------
1Hello world: <--------------------
0
0
0
14
2
0 9a b4 55 6a :2 a0 6b 6e
....
....
July 12, 2003 - 6:40 pm UTC
so, do a non-trival function. do something real world. See if you can tell me what this does not by running it -- but by reading the wrapped code. it is all of 3 lines of procedural code:
create or replace function f wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0
9
2 :e:
1FUNCTION:
1F:
1P_NUMBER:
1NUMBER:
1RETURN:
1=:
11:
1*:
1-:
0
0
0
31
2
0 a0 8d 8f a0 b0 3d b4
:2 a0 2c 6a a0 7e 51 b4 2e
5a a0 51 65 b7 :2 a0 7e :2 a0
7e 51 b4 2e a5 b b4 2e
65 b7 :2 19 3c b7 a4 b1 11
68 4f 1d 17 b5
31
2
0 3 7 23 1f 1e 2b 1b
30 34 38 3c 40 44 47 4a
4b 50 53 57 5a 5e 60 64
68 6b 6f 73 76 79 7a 7f
80 82 83 88 8c 8e 92 96
99 9b 9f a1 ad b1 b3 b4
bd
31
2
0 1 a e 1a :2 e c 23
2a :2 1 7 10 12 :2 10 5 1b
22 1b 16 7 e 17 19 1b
23 24 :2 1b :2 19 :2 e 7 :4 2 :9 1
31
4
0 :b 1 :a 4 :f 5
:3 4 :2 3 :7 1
bf
4
:3 0 1 :3 0 2
:a 0 2c 1 :7 0
5 :2 0 3 4
:3 0 3 :7 0 5
4 :3 0 5 :3 0
4 :3 0 7 9
0 2c 2 a
:2 0 3 :3 0 6
:2 0 7 :2 0 9
d f :3 0 10
:2 0 5 :3 0 7
:2 0 13 :2 0 15
c 25 5 :3 0
3 :3 0 8 :2 0
2 :3 0 3 :3 0
9 :2 0 7 :2 0
e 1b 1d :3 0
11 19 1f 13
18 21 :3 0 22
:2 0 24 16 26
11 15 0 27
0 24 0 27
18 0 28 1b
2b :3 0 2b 0
2b 2a 28 29
:6 0 2c :2 0 2
a 2b 2f :3 0
2e 2c 30 :8 0
1d
4
:3 0 1 3 1
6 1 e 2
c e 1 14
2 1a 1c 1
1e 2 17 20
1 23 2 25
26 1 27
1
4
0
2f
0
1
14
1
2
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0
2 0 1
3 1 0
0
/
Decrypt Procedure
vinodhps, July 13, 2003 - 10:53 am UTC
Hi Tom,
Is it possible to decrypt the encrypted procedure.
In our software we got a recuirment like.
Tender process
--------------
Qutoation file will be sealed and submitted to purchase department, after some day tender will be opened in front of the vendors Department head, Accounts Head.. as the tender value may be of several millions. Tender mostly will be for L1(low amount ).So the data here is crucial even DBA or developer or no one should open that or should not see that. only at that particualr day it has to be opend and then it should not be hiden.
Is there any provision in Oracle as i tried to know about that but still now i could not able to get any solution for that , If you have experianced any of this kind of situation , that will be Appriciated.
Thank you tom,
July 13, 2003 - 1:34 pm UTC
nope, once wrapped -- always wrapped.
but I don't know what that has to do with a "quotation file" -- whatever that is.
Decrypt Data
vinodhps, July 14, 2003 - 2:58 am UTC
Hi Tom,
Thanks for you quick responce , my requirement regarding Quotation is that... what the manual process is, physically they get quotation form which will be sealed. Few day tender will be as a open day.. until that time they use to get sealed quotation form from the vendor , then finally they will open in front of every one.Then the lowest quoated person will be given the contract.
Is it possible to automat this process using oracle Tom.
Thanking you
July 14, 2003 - 9:20 am UTC
then the quotation needs to be encrypted -- not the plsql code. dbms_obfuscation_toolkit can help you maybe
thanks
vinodhps, July 16, 2003 - 12:15 am UTC
Hi tom,
i have not yet read dbms_obfuscation_toolkit in your book,
i will try that.
thanks
Find unwrapped pkg...
Robert, September 10, 2003 - 12:16 pm UTC
I am exploring the guts of Marvel (HTML DB)...
Can I use SQL to show whick packages are NOT wrapped ?
Thanks
September 10, 2003 - 8:04 pm UTC
i would not bother, you are looking at the demo, not the real thing. it'll be different and wrapped in real life.
you just have to peek at the first 1 or 2 lines in dba_source to see if something is wrapped.
Unwrapped Source
Mark A. Williams, September 10, 2003 - 10:13 pm UTC
Robert,
Here is an ad hoc query that you may be able to use to find unwrapped source.
select owner,
name,
type
from all_source
where type in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
and line = 1
and instr(text,'wrapped') < 1
order by owner, name, type;
I have not tested the query or examined what impact it may have on the system. Probably needs tweaked as well.
HTH,
- Mark
HTML DB is wrapped for a reason
Tyler D. Muth, September 23, 2003 - 10:06 am UTC
HTML DB (formerly Project Marvel) is wrapped to protect Oracle's intellectual property. All of the package bodies are wrapped.
If you would like to talk about how it works in a conceptual manner I would be happy to answer any questions you may have as it helps in developing HTML DB applications.
I liken this to asking how to get the source for Multi-Version Read Consistency. Of course Oracle is not going to distribute the source, but the idea is covered in the concepts guide. I would go as far as saying that unless you work for a company trying to build a competing technology, the source would be a meaningless waste of time. Tom has NEVER viewed the source code for any aspect of the database, as it doesn't really matter how it is written, but more how it functions.
Anyway, as I stated before, I would be happy to answer any conceptual questions on this topic. I am a developer on the HTML DB team and familiar with every aspect of it's architecture.
Why I can still see the source code in all_source?
A reader, October 14, 2003 - 3:38 pm UTC
The wrapped script is generated all right, but ALL_SOURCE is still showing the unwrapped code!
Sorry, please ignore above question. I forgot to recompile using the wrapped code.
A reader, October 14, 2003 - 3:48 pm UTC
Comparing wrapped functions
A reader, October 29, 2003 - 1:53 pm UTC
Hi Tom,
If I generete a wrapped function now, and
in 8 month after, I generate again that function.
1. Should both be the same?
2. Even if I migrate from a previous release for example 9.01 to 9.2, and both were generate by different releases?
Thank you.
October 29, 2003 - 2:27 pm UTC
1) if you are using the same release -- sure.
2) no, you could expect the generated wrapped code to be different, no reason why it could not/would not be.
:) Thanks
A reader, October 29, 2003 - 4:30 pm UTC
How to See USER-2 's Package Body being in User-1
BIJAY K Pusty, October 29, 2003 - 5:13 pm UTC
Hi Tom
I am asking here another question.
There are 2 Users[ USER1, USER2] in one INstance WHSP.
USER1 has a package U1_PKG1
USER2 wanted to just see the Package Body i.e. U1_PKG1 not connecting to USER1 rather than being in USER2.
Is it possible ??
Thanks
--Bijay
October 29, 2003 - 5:21 pm UTC
only if user2 has
o execute any procedure
o access to dba_source
otherwise, all_source says "don't show the body, just the spec", thats the way it was designed.
got error when wrapping types
oleg, February 23, 2004 - 6:34 am UTC
in our app. We use plsql in types extensivly. But when wrapping it, we got compilation errors. is there any known problem with wrapping types? Any workaround? any suggestion?
February 23, 2004 - 7:52 am UTC
not that I am aware of personally. if you have a test case, contact support.
A reader, April 17, 2004 - 2:42 pm UTC
Can you please take a look at this. Heres the script.
$ more p1.sql
create or replace procedure p1 is
cursor c1
is
select * from (
select empno, ename,hiredate, RANK() OVER(ORDER BY HIREDATE DESC) latest_employee
from emp
where deptno = 10
)
where latest_employee = 1;
begin
for i in c1
loop
dbms_output.put_line(i.ename||' is a newbie hired on '||i.latest_employee);
end loop;
end p1;
/
$ wrap iname=p1.sql oname=p1.plb
PL/SQL Wrapper: Release 9.2.0.1.0- Production on Sat Apr 17 13:30:08 2004
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing p1.sql to p1.plb
PSU(103,1,5,42):Encountered the symbol "(" when expecting one of the following:
, from
PL/SQL Wrapper error: Compilation error(s) for:
create or replace procedure p1
Outputting source and continuing.
If i execute this in plsql works without any errors
scott@TEST>create or replace procedure p1 is
2 cursor c1
3 is
4 select * from (
5 select empno, ename,hiredate, RANK() OVER(ORDER BY HIREDATE DESC) latest_employee
6 from emp
7 where deptno = 10
8 )
9 where latest_employee = 1;
10
11 begin
12 for i in c1
13 loop
14 dbms_output.put_line(i.ename||' is a newbie hired on '||i.latest_employee);
15 end loop;
16 end p1;
17 /
Procedure created.
Elapsed: 00:00:00.07
Are there any issues with wrapping analytical functions.
Thanks.
April 17, 2004 - 2:54 pm UTC
Looks like there is in 9i
[tkyte@tkyte-pc-isdn tkyte]$ cat > pl.sql
create or replace procedure p1 is
cursor c1
is
select * from (
select empno, ename,hiredate, RANK() OVER(ORDER BY HIREDATE DESC)
latest_employee
from emp
where deptno = 10
)
where latest_employee = 1;
begin
for i in c1
loop
dbms_output.put_line(i.ename||' is a newbie hired on '||i.latest_employee);
end loop;
end p1;
/
[tkyte@tkyte-pc-isdn tkyte]$ wrap iname=pl.sql
PL/SQL Wrapper: Release 10.1.0.2.0- Production on Sat Apr 17 14:48:40 2004
Copyright (c) 1993, 2004, Oracle. All rights reserved.
Processing pl.sql to pl.plb
[tkyte@tkyte-pc-isdn tkyte]$ ora9ir2
[tkyte@tkyte-pc-isdn tkyte]$ wrap iname=pl.sql
PL/SQL Wrapper: Release 9.2.0.4.0- Production on Sat Apr 17 14:48:47 2004
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing pl.sql to pl.plb
PSU(103,1,5,42):Encountered the symbol "(" when expecting one of the following:
, from
PL/SQL Wrapper error: Compilation error(s) for:
create or replace procedure p1
Outputting source and continuing.
[tkyte@tkyte-pc-isdn tkyte]$
However:
BugTag: Support notes on <Bug:2372013> - DDR info <BugDesc:2372013>
Affects: PLSQL (817-920)
NB: FIXED
Abstract: WRAP does not handle some SQL syntax (PLS-103 etc..)
Fixed-Releases: 9014 9201
Tags: ERROR PLSQL
Details:
PLS-103 syntax errors (typically) or semantic errors
raised by WRAP when the input file contains PL/SQL
blocks with SQL statements that use syntax that was
introduced in 9i or some of the analytic processing
syntax introduced in 8.1.7.
This fix introduces a WRAP parameter "edebug=wrap_new_sql"
to allow WRAP to understand such SQL.
this edebug is not neccesary in 10g -- note that if you use this, in 9i, the sql will be in the clear in the wrapped plsql (which if you ask me is probably ok since the sql will be in the clear in the v$sql view anyhow)
Other options are
a) views (hide the construct from wrap)
b) dynamic sql (same side as a)
Wrap Util Not Wrapping Still
A reader, April 22, 2004 - 9:53 pm UTC
Hi Tom,
I've tried differennt versions that shipped with Developer and 8i Database server but it's not been successful. All seem to do the same thing - stripping off the comments, leaving /* */ intact, and the source code READABLE (by human).
Is there any environment variable need to be set for the wrap utility to wrap properly (ie encrypt)?
Thanks for you help
Hien
April 23, 2004 - 11:15 am UTC
sorry -- but I cannot reproduce your findings -- can you give me a small, simple, short test case that does?
C:\DOCUME~1\ADMINI~1>type test.sql
create or replace procedure p
as
/* hello world */
x int;
begin
for y in ( select * from dual )
loop
dbms_output.put_line( y.dummy );
end loop;
end;
/
C:\DOCUME~1\ADMINI~1>wrap iname=test.sql
PL/SQL Wrapper: Release 8.1.7.4.0 - Production on Fri Apr 23 11:15:34 2004
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing test.sql to test.plb
C:\DOCUME~1\ADMINI~1>type test.plb
create or replace procedure p wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
7
8106000
1
4
0
9
2 :e:
1P:
1X:
1INT:
1Y:
1DUAL:
1LOOP:
1DBMS_OUTPUT:
1PUT_LINE:
1DUMMY:
0
0
0
29
2
0 1d 9a b4 55 6a a3 a0
1c 81 b0 91 ac a0 b2 ee
ac d0 e5 e9 37 :3 a0 6b :2 a0
6b a5 57 b7 a0 47 b7 a4
b1 11 68 4f 17 b5
Eureka!
A reader, April 26, 2004 - 8:07 pm UTC
Hi Tom,
I got it to work. It didn't work before just because, no it's too embarrassed to mention, just some confusion. If I only read the doco carefully in the first place ....
Thanks for your help. You've been great!
Cheers
Hien
"Is it possible to encrypt PL/SQL procedure?" - Review
David, May 23, 2004 - 4:35 am UTC
Good Stuff.
Keep it up.
wrapped code line numbers
Dave Pelham, June 15, 2004 - 6:51 am UTC
We have a development environment with unwrapped code, we wrap for the test/customer environment.
In the case of an untrapped error, is the wrapped line number the same as the unwrapped?
June 15, 2004 - 3:42 pm UTC
as of 9ir2/10gr1 -- yes, i do not see it documented, so it could change but I doubt it.
Fail running wrap
Douglas Kei, June 16, 2004 - 5:17 am UTC
I have try to use the wrap function on the simple script file and it fails with message "kgepop: no error frame to pop to for error 1801". Is there any special environment or system settings in order to run the wrap utility? Thanks!
# My system configuration:
- W2K Pro SP3 (Traditional Chinese)
- Oracle 9i
# C:\test.sql
create or replace procedure p
as
begin
dbms_output.put_line( 'Hello world' );
end;
/
# screen dump
C:\>wrap iname=test.sql oname=test_wrap.sql
kgepop: no error frame to pop to for error 1801
June 16, 2004 - 12:40 pm UTC
contact support, there is nothing special you should need to do.
" Source code could not be displayed because it is wrapped".
Dawar, September 16, 2004 - 8:49 am UTC
OS: SuSE Linux Enterprise Server 8.
Oracle DB version: 10.1.0.2.0
Hello All,
I have installed Oracle database 10.1.0.2.0 on SLES 8.
Under EXFSYS Schema my package body DBMS_EXPFIL_IR suddenly become invalid is giving me following error on compile.
Line # = 490 Column # = 38 Error Text = PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-201: identifier 'UNDEFINED' must be declared
Line # = 490 Column # = 5 Error Text = PL/SQL: SQL Statement ignored
I can not edit the data.
It says:
" Source code could not be displayed because it is wrapped".
I ran @catproc.sql and @utlrp.sql scripts they fixed some other objects but not this one.
I have only this object invalid in this schema.
Regards,
Dawar
September 16, 2004 - 9:22 am UTC
not much i can do sitting here without a method to reproduce.
get the unwrapped code and see what the issue might be.
something change -- you did SOMETHING -- for code just doesn't "go stale sitting on the shelf". You change the privs of this user (revoked something) or altered an object it accesses. that caused the invalidation and the fact that it cannot recompile is directly tied to whatever you did.
Oracle Developer
Dawar, September 16, 2004 - 3:05 pm UTC
Tom,
Its work for me.
On my resarch I got Generic note on metalik.
Fix
Installating the EXFSYS schema :
==========================
1. Login as SYSDBA
2. @$ORACLE_HOME/rdbms/admin/catexf.sql
I'd like to share with you and other readers.
Note 258618.1 - How To Install and Uninstall Expression Filter Feature or EXFSYS schema
Regards,
Dawar
I had recently similar problem with WRAP for 9i
Mark, February 09, 2005 - 12:41 pm UTC
I had recently similar problem with WRAP for 9i.
Thanks
Dictionary view that tags wrapped code?
ht, October 27, 2005 - 9:57 pm UTC
Tom,
I have a routine that wraps my unwrapped stored procs using info from user_source. Is there a way I can wrap only unwrapped source because wrapping it 2x makes the code unusable?
In other words, is there a dictionary view that allows one to select either only unwrapped code (user_source.wrapped=N)? I'm using 10.2.
ht
October 28, 2005 - 1:57 am UTC
Not sure that is exposed, however, just looking to see if line 1 of anything contained the word 'wrapped' would find them.
A reader, November 01, 2005 - 5:47 pm UTC
Code comparison of wrapped code
Rich, November 30, 2005 - 1:37 pm UTC
We have several environments setup at the client site (e.g. Test, QA, PROD). We use some CVS utility to maintain the versioning of our PL/SQL codes between Test and QA environments.
We then wrap the PL/SQL code before deploying to PROD.
Between Test and QA, we can use "diff" utilities (e.g. OEM Change Mgmt) to check for differences in codes between environments.
How can we check if we have the same code for QA (unwrapped) and PROD (wrapped)?
Many thanks for your site.
November 30, 2005 - 8:44 pm UTC
you would have to wrapped qa and do a compare there.
wrap table names
Houman, May 23, 2006 - 5:09 pm UTC
Hi Tom,
I wrap a function and check the wrapped code but I see the table names. is there a way to hide the tables names when
using PL/SQl wrapper. is there other Oracle utility that it's level of decodong extends to table names inside the source function?
May 24, 2006 - 7:05 am UTC
why do you care, I'll just go to dba_dependencies to see what you rely on, or I'll run your procedure and query v$sql to see the sql it uses - the exact sql.
in 10g, the wrapping obfuscates the code totally, but again - in your case "so what". I still know what tables each and every module is dependent on and I'll see your SQL.
A reader, May 24, 2006 - 10:07 am UTC
No way to hide the SQL!
A reader, May 25, 2006 - 5:07 am UTC
/*
There is no way to hide the SQL!
*/
Oracle Apex (HTML DB) is wrapped and contained in database packages. How Oracle protects their intellectual property then?
1. If one can see the executing SQL then what makes Oracle to wrap the Apex code in packages? What Oracle needs to hide other than SQL?
2. How could an ISV protect the code in database packages, when deploying to their clients?
/*
in 10g, the wrapping obfuscates the code totally
*/
1. Explain how obfuscation makes difference then before.
2. Is the code get more secure? How?
3. What we should expect from the utility 'wrap'?
May 25, 2006 - 1:46 pm UTC
you can see our sql
you cannot see our logic.
all you can see is OUR SQL. You cannot see what we do with the output of the sql.
Also, we don't want you MODIFYING the code - that is the main goal of wrapping.
1) you don't see strings anymore.
2) no more or less "secure", don't understand "secure" in this aspect.
3) the same thing you always expected? the code is obscured.
The sql - that'll be visible.
The code - not visible.
Great explanation -- as always!
A reader, May 26, 2006 - 5:54 am UTC
/*
you can see our sql
you cannot see our logic.
The sql - that'll be visible.
The code - not visible.
*/
Although it conveys the sense but I need to clarify using the following terms (plain english as plain vanilla):
A database package (having functions and procedures) contains code written in PL/SQL or Java. One can see the SQL statements used in the PL/SQL or Java code but cann't get the PL/SQL or Java code.
- Is it what you say logic/code -- the invisible portion?
- Is Oracle Apex written in PL/SQL or Java?
May 26, 2006 - 8:55 am UTC
Apex is most definitely written in PLSQL.
wrapping PLSQL hides/obscures the plsql source code.
The sql will ALWAYS be visible in v$sql, always.
Executing the wrapped file (.plb)
A reader, November 05, 2007 - 8:12 pm UTC
Tom,
I use the WRAP utility and get a .plb file
C:\>wrap iname=C:\myproc.sql oname=myproc.plb
PL/SQL Wrapper: Release 10.2.0.1.0- Production on Mon Nov 05 16:46:59 2007
Copyright (c) 1993, 2004, Oracle. All rights reserved.
Processing C:\myproc.sql to myproc.plb
C:\>
But how do i execute this encrypted file(.plb) from SQL standalone?. Any idea?
Thanks.
I got it
A reader, November 05, 2007 - 8:22 pm UTC
Never mind Tom,
I got it...It was just like a regular .sql file execution.
SQL> @C:\myproc.plb
That did the magic.
Thank you anyways.
mask input parameter
A reader, March 26, 2010 - 11:34 am UTC
IS there any way we can mask input parameters passed to functions during calls to the function ?
The idea is for the password encrypting phrase - not to appear(or appear like '*****') even in views like v$sql
thx
Anto
March 26, 2010 - 3:42 pm UTC
why would you store an encrypted password? that is fraught with "10,000 ways to steal your password", don't store an encrypted password.
But the bottom line is - sounds like someone needs to mention the word "bind variables" to the developers.
If you pass passwords around, you will a) bind them, and b) use network encryption (sqlnet ASO - advanced security option) to encrypt the network traffic.
A reader, March 26, 2010 - 4:14 pm UTC
so the bottom line is - it is a stupid question and there is no functionality for masking input parameters :)
thanks for the response
Anto
March 26, 2010 - 5:27 pm UTC
But the bottom line is - sounds like someone needs to mention the word "bind variables" to the developers.
If you use bind variables, guess what you will never see in v$sql? But there are ways to dump bind variables - there will always be that.
Yes, you shouldn't be passing around things like that - they are visible to someone.
A reader, March 29, 2010 - 9:31 am UTC
thanks
Anto
Improvements to this in 12c
JD, January 21, 2014 - 7:22 am UTC
Hi Tom, Have you introduced any improvements/changes to this aspect in 12c?
Wrapping works :)
Prateek Tandon, April 18, 2015 - 2:51 am UTC
Thanks Tom this really helped
Unwrapping methods exist
Richard Rocker, January 21, 2017 - 9:49 am UTC
Just to update the original response: There are many many PL/SQL unwrapping tools that exist.
wrap and dbms_ddl.wrap are not in any way secure as at Oracle 12c (and I fail to understand why Oracle doesn't offer the ability to properly secure code, but that's another issue I suppose)