Introduction
Today I released a PL/SQL Unwrapper for VS Code. You can find it in the VS Code Marketplace, along with instructions on how to use it. It’s super easy and works the same way as the extension I wrote 10 years ago for SQL Developer in Java. If you’re curious about why I created an Unwrapper in the first place, then read this blog post . The post also includes the original Python code by Niels Teusink. I basically translated that code to the target language and added a bit of UI sugar.
And Code Wrapped with the 9i Wrap Utility?
I often get asked whether I plan to enhance the Unwrapper to support code that was wrapped with a wrap utility from Oracle Database version 7, 8, or 9. The answer is no. I don’t plan on doing that. Why? – Well, there are a few reasons.
- Different Algorithm
The way the wrapping algorithm works in 9i and later versions is totally different. The 9i algorithm is a bit complicated. If you want the full story, check out Pete Finnigan’s How to Unwrap PL/SQL. In other words, It’ll take a lot of effort to create a comprehensive 9i Unwrapper. If you need one, Pete’s your guy. - Not widely used
Since unwrapping >=10g wrapped code is super easy, a few companies decided to use the 9i algorithm with or without a minifier to protect their intellectual property. However, I do not find that very often in the wild. - For Legacy Applications Only
IMO the 9i algorithm is only feasible for legacy applications. Because modern PL/SQL and SQL cannot be processed by the 9i wrap utility. As a result, legit requests for an Unwrapper are applications to be migrated that contain wrapped code without matching source code. In those cases, it is a good idea to contact Pete for help.
Let’s explore this last point a little further.
- Wrap Modern SQL with OracleDB 23ai
- Wrap Modern SQL with OracleDB 9iR2
- Wrap Modern PL/SQL with OracleDB 9iR2
- Conclusion
Wrap Modern SQL with OracleDB 23ai
The following procedure uses a table value constructor. That is a 23ai feature.
create or replace procedure print_emp (in_deptno in number default null) is
begin
-- print column headers
sys.dbms_output.put_line('DEPTNO EMPNO ENAME SAL');
sys.dbms_output.put_line('------ ----- ------ ----');
<<print_emps_of_selected_dept>>
for r in (
with
-- table value constructor is a 23ai feature
emp (empno, ename, sal, deptno) as (values
(7839, 'KING', 5000, 10),
(7566, 'JONES', 2975, 20),
(7788, 'SCOTT', 3000, 20)
)
select deptno, empno, ename, sal
from emp
where deptno = in_deptno or in_deptno is null
order by deptno, sal desc
) loop
sys.dbms_output.put(lpad(r.deptno, 6));
sys.dbms_output.put(lpad(r.empno, 6));
sys.dbms_output.put(' ');
sys.dbms_output.put(rpad(r.ename, 7));
sys.dbms_output.put_line(lpad(r.sal, 4));
end loop print_emps_of_selected_dept;
end print_emp;
/We can wrap this code with the wrap utility of the Oracle Database.
wrap iname=print_emp_modern_sql.sql oname=print_emp_modern_sql_wrapped.sqlPL/SQL Wrapper: Release 23.0.0.0.0 - Production on Sat Mar 8 13:20:22 2025
Version 23.6.0.24.10
Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved.
Processing print_emp_modern_sql.sql to print_emp_modern_sql_wrapped.sqlAnd the resulting file looks like this:
create or replace procedure print_emp wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
357 207
ayazvHL/mg/US3DdDyNYwTqa68EwgwLxTK5qyo5A7RlkV79jvdE9vjLZi/E/UPQJEalFiuXq
hzBrk1+1JByeVW8X/KEOaUHHMK6a36OU2H4q1oax+MG//jNSNhUB6sLU8kRxrH4ebt4Wk40N
FQYr4wRTWF1+xkM2pmh8W4JiToP15Q0u9rBXe69s78wW2/zU12UKWGqC85UeCNsFIo/gM9DI
UzEh7AwFODhZ4ntqNtVW1RJDBTuExWM1mG/jBTiKvhCe2Q4FWzymJdUah2Yynj4wzX+ROYX2
G6PJ/60EFEz/K45RH9G/80R1SaHm7tH0KZZ+vFTKM9gUgoVVZCWt/7FURXFmyZ4BYuADvKvg
FydUkofWep7ql66IjSsN2hyHik8Ee6RWQkYDcvIPflMpTBzxidGzteS4RSgg3Q0+di/WaSdN
tQdYoiGTodL1hUzbGbxSe/XSKHY1ISPihH+1TCWz4PmwmbH+iyQ2QfrCu/Ng+cSB28xJcBBO
HZoylZ0=
/Let’s unwrap the code. BTW: the highlighted lines 1 to 18 and 29 to 30 are not required to unwrap the code.
create or replace PROCEDURE print_emp (IN_DEPTNO IN NUMBER DEFAULT NULL) IS
BEGIN
SYS.DBMS_OUTPUT.PUT_LINE('DEPTNO EMPNO ENAME SAL');
SYS.DBMS_OUTPUT.PUT_LINE('------ ----- ------ ----');
<<PRINT_EMPS_OF_SELECTED_DEPT>>
FOR R IN (
WITH
EMP (EMPNO, ENAME, SAL, DEPTNO) AS (VALUES
(7839, 'KING', 5000, 10),
(7566, 'JONES', 2975, 20),
(7788, 'SCOTT', 3000, 20)
)
SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = IN_DEPTNO OR IN_DEPTNO IS NULL
ORDER BY DEPTNO, SAL DESC
) LOOP
SYS.DBMS_OUTPUT.PUT(LPAD(R.DEPTNO, 6));
SYS.DBMS_OUTPUT.PUT(LPAD(R.EMPNO, 6));
SYS.DBMS_OUTPUT.PUT(' ');
SYS.DBMS_OUTPUT.PUT(RPAD(R.ENAME, 7));
SYS.DBMS_OUTPUT.PUT_LINE(LPAD(R.SAL, 4));
END LOOP PRINT_EMPS_OF_SELECTED_DEPT;
END PRINT_EMP;Interesting are the empty lines 3 and 9. The original comments are lost. Furthermore, all keywords and identifiers are in uppercase. The only exception is the name of the procedure. The Unwrapper added the create or replace clause to make the statement executable.
Wrap Modern SQL with OracleDB 9iR2
Now let’s try to wrap print_emp_modern_sql.sql with the wrap utility in the Oracle Database 9.2.0.8.
wrap iname=print_emp_modern_sql.sql oname=print_emp_modern_sql_wrapped9i.sqlPL/SQL Wrapper: Release 9.2.0.8.0- 64bit Production on Sat Mar 08 14:55:28 2025
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing print_emp_modern_sql.sql to print_emp_modern_sql_wrapped9i.sql
PSU(103,1,8,7):Encountered the symbol "WITH" when expecting one of the following:
( - + case mod new not null others select <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "WITH" was ignored.
PSU(103,1,10,42):Encountered the symbol "AS" when expecting one of the following:
. ( ) , * % & | = - + < / > at in is mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
PL/SQL Wrapper error: Compilation error(s) for:
create or replace procedure print_emp
Outputting source and continuing.We got two errors due to the use of modern SQL. However, we can use the parameter edebug=wrap_new_sql to support newer SQL grammar.
wrap iname=print_emp_modern_sql.sql oname=print_emp_modern_sql_wrapped9i.sql edebug=wrap_new_sqlPL/SQL Wrapper: Release 9.2.0.8.0- 64bit Production on Sat Mar 08 14:56:01 2025
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing print_emp_modern_sql.sql to print_emp_modern_sql_wrapped9i.sqlAnd the resulting file looks like this:
create or replace procedure print_emp wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
7
9200000
1
4
0
18
2 :e:
1PRINT_EMP:
1IN_DEPTNO:
1NUMBER:
1SYS:
1DBMS_OUTPUT:
1PUT_LINE:
1DEPTNO EMPNO ENAME SAL:
1------ ----- ------ ----:
1PRINT_EMPS_OF_SELECTED_DEPT:
1R:
1EMP:
1EMPNO:
1ENAME:
1SAL:
1DEPTNO:
1LOOP:
1with:n -- table value constructor is a 23ai feature:n emp (em+
1pno, ename, sal, deptno) as (values:n (7839, 'KING', 5000, 10),:n+
1 (7566, 'JONES', 2975, 20),:n (7788, 'SCOTT', 3000, 20)+
1:n ):n select deptno, empno, ename, sal:n from emp:n +
1 where deptno = in_deptno or in_deptno is null:n order by deptno, sal +
1desc:n :
1PUT:
1LPAD:
16:
1 :
1RPAD:
17:
14:
0
0
0
7e
2
0 9a 8f a0 4d b0 3d b4
55 6a :2 a0 6b a0 6b 6e a5
57 :2 a0 6b a0 6b 6e a5 57
93 91 :10 a0 12a 37 :2 a0 6b a0
6b :3 a0 6b 51 a5 b a5 57
:2 a0 6b a0 6b :3 a0 6b 51 a5
b a5 57 :2 a0 6b a0 6b 6e
a5 57 :2 a0 6b a0 6b :3 a0 6b
51 a5 b a5 57 :2 a0 6b a0
6b :3 a0 6b 51 a5 b a5 57
b7 :2 a0 47 b0 46 b7 a4 a0
b1 11 68 4f 1d 17 b5
7e
2
0 3 20 1b 1f 1a 28 17
2d 31 35 39 3d 40 44 47
4c 4d 52 56 5a 5d 61 64
69 6a 6f 77 7b 7f 83 87
8b 8f 93 97 9b 9f a3 a7
ab af b3 b7 bb c7 c9 cd
d1 d4 d8 db df e3 e7 ea
ed ee f0 f1 f6 fa fe 101
105 108 10c 110 114 117 11a 11b
11d 11e 123 127 12b 12e 132 135
13a 13b 140 144 148 14b 14f 152
156 15a 15e 161 164 165 167 168
16d 171 175 178 17c 17f 183 187
18b 18e 191 192 194 195 19a 19c
1a0 1a4 1ab 1ac 1af 1b1 1b5 1b9
1bb 1c7 1cb 1cd 1ce 1d7
7e
2
0 b 16 23 32 :2 16 15 :2 1
4 :2 8 :2 14 1d :3 4 :2 8 :2 14 1d
:2 4 6 8 a f 16 1d 22
e 16 1d 24 :2 e 17 24 11
19 6 d 4 7 :2 b :2 17 1b
20 :2 22 2a :2 1b :3 7 :2 b :2 17 1b
20 :2 22 29 :2 1b :3 7 :2 b :2 17 1b
:3 7 :2 b :2 17 1b 20 :2 22 29 :2 1b
:3 7 :2 b :2 17 20 25 :2 27 2c :2 20
:2 7 6 8 d 4 :4 1 5 :7 1
7e
4
0 :9 1 :8 4 :8 5
6 7 :5 a :4 f
10 :3 11 :2 12 13
:2 7 :e 14 :e 15 :8 16
:e 17 :e 18 13 :2 19
7 :4 2 1a :7 1
1d9
4
:3 0 1 :a 0 79
1 :7 0 5 :2 0
:2 3 :4 0 2 :7 0
5 3 4 :2 0
7 :2 0 79 1
8 :2 0 4 :3 0
5 :3 0 a b
0 6 :3 0 c
d 0 7 :4 0
7 e 10 :2 0
74 4 :3 0 5
:3 0 12 13 0
6 :3 0 14 15
0 8 :4 0 9
16 18 :2 0 74
9 :5 0 72 2
a :3 0 b :3 0
c :3 0 d :3 0
e :3 0 f :3 0
f :3 0 c :3 0
d :3 0 e :3 0
b :3 0 f :3 0
2 :3 0 2 :3 0
f :3 0 e :3 0
10 :4 0 11 1
:8 0 2d 1b 2c
4 :3 0 5 :3 0
2e 2f 0 12
:3 0 30 31 0
13 :3 0 a :3 0
f :3 0 34 35
0 14 :2 0 b
33 38 e 32
3a :2 0 6e 4
:3 0 5 :3 0 3c
3d 0 12 :3 0
3e 3f 0 13
:3 0 a :3 0 c
:3 0 42 43 0
14 :2 0 10 41
46 13 40 48
:2 0 6e 4 :3 0
5 :3 0 4a 4b
0 12 :3 0 4c
4d 0 15 :4 0
15 4e 50 :2 0
6e 4 :3 0 5
:3 0 52 53 0
12 :3 0 54 55
0 16 :3 0 a
:3 0 d :3 0 58
59 0 17 :2 0
17 57 5c 1a
56 5e :2 0 6e
4 :3 0 5 :3 0
60 61 0 6
:3 0 62 63 0
13 :3 0 a :3 0
e :3 0 66 67
0 18 :2 0 1c
65 6a 1f 64
6c :2 0 6e 21
71 10 :3 0 9
:3 0 2d 6e :4 0
73 27 72 71
74 29 78 :3 0
78 1 :4 0 78
77 74 75 :6 0
79 :2 0 1 8
78 7c :3 0 7b
79 7d :8 0
2d
4
:3 0 1 2 1
6 1 f 1
17 2 36 37
1 39 2 44
45 1 47 1
4f 2 5a 5b
1 5d 2 68
69 1 6b 5
3b 49 51 5f
6d 1 1a 3
11 19 73
1
4
0
7c
0
1
14
2
4
0 1 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0
2 1 0
1a 1 2
1 0 1
1b 2 0
0
/The query is visible in plain text on lines 42 to 47.
When we try to install this wrapped package in the OracleDB 23ai we get the following error:
Procedure PRINT_EMP compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
0/0 PLS-01918: 9.2 and earlier wrap formats are not permitted
Errors: check compiler logWe have to enable permit_92_wrap_format to overcome this issue. This is not possible on session or PDB level. So we have to change the settings as follows:
alter session set container=cdb$root;
alter system set permit_92_wrap_format=true scope=spfile;Session altered.
System altered.After a restart of the database, we can install print_emp_modern_sql_wrapped9i.sql successfully.
System parameters that you can’t set at the PDB level are very limiting. In fact, it makes installing PL/SQL code wrapped with the 9i wrap utility in an Autonomous Database pretty much impossible.
Wrap Modern PL/SQL with OracleDB 9iR2
We have seen that it is possible to process arbitrary SQL within PL/SQL with the wrap utility in OracleDB 9.2.
Now let’s add some PL/SQL constructs that were introduced in later versions of the Oracle Database.
The highlighted lines show the changes to print_emp_modern_sql. We use a PL/SQL identifier print_employees_of_a_selected_department that is longer than 30 bytes. Also, we use the continue statement, which wasn’t available in version 9.2.0.8.
create or replace procedure print_emp (in_deptno in number default null) is
begin
-- print column headers
sys.dbms_output.put_line('DEPTNO EMPNO ENAME SAL');
sys.dbms_output.put_line('------ ----- ------ ----');
<<print_employees_of_a_selected_department>>
for r in (
with
-- table value constructor is a 23ai feature
emp (empno, ename, sal, deptno) as (values
(7839, 'KING', 5000, 10),
(7566, 'JONES', 2975, 20),
(7788, 'SCOTT', 3000, 20)
)
select deptno, empno, ename, sal
from emp
where deptno = in_deptno or in_deptno is null
order by deptno, sal desc
) loop
-- continue is a 11g feature
continue when r.sal < 1000;
sys.dbms_output.put(lpad(r.deptno, 6));
sys.dbms_output.put(lpad(r.empno, 6));
sys.dbms_output.put(' ');
sys.dbms_output.put(rpad(r.ename, 7));
sys.dbms_output.put_line(lpad(r.sal, 4));
end loop print_employees_of_a_selected_department;
end print_emp;
/Let’s try to wrap this code with the wrap utility of the Oracle Database 9.2.0.8.
wrap iname=print_emp_modern_plsql.sql oname=print_emp_modern_plsql_wrapped9i.sql edebug=wrap_new_sqlPL/SQL Wrapper: Release 9.2.0.8.0- 64bit Production on Sat Mar 08 16:11:45 2025
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing print_emp_modern_plsql.sql to print_emp_modern_plsql_wrapped9i.sql
PSU(114,1,6,6):identifier 'PRINT_EMPLOYEES_OF_A_SELECTED_' too long
PSU(103,1,21,16):Encountered the symbol "WHEN" when expecting one of the following:
:= . ( @ % ;
PSU(114,1,27,13):identifier 'PRINT_EMPLOYEES_OF_A_SELECTED_' too long
PL/SQL Wrapper error: Compilation error(s) for:
create or replace procedure print_emp
Outputting source and continuing.It is impossible to wrap PL/SQL code with grammar constructs that are missing in the Oracle database version of the wrap utility. This is only true for Oracle Database versions before 10g, though.
In case of an error, the wrap utility writes the original code unchanged to the target file. So technically, the resulting file can be installed successfully.
Conclusion
Still wrapping PL/SQL with Oracle 9i Release 2? It’s time to move on. Staying tied to 2007’s feature set means restricting your application’s potential and compatibility.
And if you’re absolutely sure you need a 9i Unwrapper, I’m not the person to ask – Pete is.
2 Comments
Interesting article.
Maybe you can answer a question that really disappoints me of Oracle. What’s the point of wrapping if it can be so easily unwrapped? It’s useless except to keep the code “safe” from the curious eye of no-developers.
In PL/SQL Language Reference 11gR2 the following reason was provided: “Wrapping is the process of hiding PL/SQL source code. Wrapping helps to protect your source code by making it more difficult for others to view it.”
In PL/SQL Language Reference 23ai there is no such reason provided anymore. Instead the following note is provided: “Note:Wrapping text is low-assurance security. For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator’s Guide.”
I see the following reasons for still using wrapped code:
So, I see it more like a legal or compliance feature. ;-)