I’m using from time to time the free service Unwrap it! or Niels Teusink’s Python script unwrap.py to unwrap PL/SQL code. Recently I’m confronted more with wrapped code since a customer is about to migrate to a new banking platform which is using wrapped PL/SQL code extensively. While investigating migration errors we experienced that unwrapping the called PL/SQL packages helped us a lot to identify the root cause faster. But since the unwrapping and debugging process is still a bit cumbersome for a series of PL/SQL packages a colleague asked me: “Wouldn’t it be nice if we could unwrap PL/SQL packages directly in SQL Developer?” and I answered “This should be simple. I’ve already written an extension for SQL Developer and the code in unwrap.py does not look too complicated.”
And on a rainy weekend I analyzed Niels Teusink’s public domain Phyton script unwrap.py and used it as a starting point for the development of a PL/SQL Unwrapper for SQL Developer.
# This script unwraps Oracle wrapped plb packages, does not support 9g
# Contact: niels at teusink net / blog.teusink.net
# License: Public domain
# simple substitution table
charmap = [0x3d, 0x65, 0x85, 0xb3, 0x18, 0xdb, 0xe2, 0x87, 0xf1, 0x52, 0xab, 0x63, 0x4b, 0xb5, 0xa0, 0x5f, 0x7d, 0x68, 0x7b, 0x9b, 0x24, 0xc2, 0x28, 0x67, 0x8a, 0xde, 0xa4, 0x26, 0x1e, 0x03, 0xeb, 0x17, 0x6f, 0x34, 0x3e, 0x7a, 0x3f, 0xd2, 0xa9, 0x6a, 0x0f, 0xe9, 0x35, 0x56, 0x1f, 0xb1, 0x4d, 0x10, 0x78, 0xd9, 0x75, 0xf6, 0xbc, 0x41, 0x04, 0x81, 0x61, 0x06, 0xf9, 0xad, 0xd6, 0xd5, 0x29, 0x7e, 0x86, 0x9e, 0x79, 0xe5, 0x05, 0xba, 0x84, 0xcc, 0x6e, 0x27, 0x8e, 0xb0, 0x5d, 0xa8, 0xf3, 0x9f, 0xd0, 0xa2, 0x71, 0xb8, 0x58, 0xdd, 0x2c, 0x38, 0x99, 0x4c, 0x48, 0x07, 0x55, 0xe4, 0x53, 0x8c, 0x46, 0xb6, 0x2d, 0xa5, 0xaf, 0x32, 0x22, 0x40, 0xdc, 0x50, 0xc3, 0xa1, 0x25, 0x8b, 0x9c, 0x16, 0x60, 0x5c, 0xcf, 0xfd, 0x0c, 0x98, 0x1c, 0xd4, 0x37, 0x6d, 0x3c, 0x3a, 0x30, 0xe8, 0x6c, 0x31, 0x47, 0xf5, 0x33, 0xda, 0x43, 0xc8, 0xe3, 0x5e, 0x19, 0x94, 0xec, 0xe6, 0xa3, 0x95, 0x14, 0xe0, 0x9d, 0x64, 0xfa, 0x59, 0x15, 0xc5, 0x2f, 0xca, 0xbb, 0x0b, 0xdf, 0xf2, 0x97, 0xbf, 0x0a, 0x76, 0xb4, 0x49, 0x44, 0x5a, 0x1d, 0xf0, 0x00, 0x96, 0x21, 0x80, 0x7f, 0x1a, 0x82, 0x39, 0x4f, 0xc1, 0xa7, 0xd7, 0x0d, 0xd1, 0xd8, 0xff, 0x13, 0x93, 0x70, 0xee, 0x5b, 0xef, 0xbe, 0x09, 0xb9, 0x77, 0x72, 0xe7, 0xb2, 0x54, 0xb7, 0x2a, 0xc7, 0x73, 0x90, 0x66, 0x20, 0x0e, 0x51, 0xed, 0xf8, 0x7c, 0x8f, 0x2e, 0xf4, 0x12, 0xc6, 0x2b, 0x83, 0xcd, 0xac, 0xcb, 0x3b, 0xc4, 0x4e, 0xc0, 0x69, 0x36, 0x62, 0x02, 0xae, 0x88, 0xfc, 0xaa, 0x42, 0x08, 0xa6, 0x45, 0x57, 0xd3, 0x9a, 0xbd, 0xe1, 0x23, 0x8d, 0x92, 0x4a, 0x11, 0x89, 0x74, 0x6b, 0x91, 0xfb, 0xfe, 0xc9, 0x01, 0xea, 0x1b, 0xf7, 0xce]
base64dec = base64.decodestring(base64str)[20:] # we strip the first 20 chars (SHA1 hash, I don't bother checking it at the moment)
decoded = ''
for byte in range(0, len(base64dec)):
decoded += chr(charmap[ord(base64dec[byte])])
sys.stderr.write("=== Oracle 10g/11g PL/SQL unwrapper 0.2 - by Niels Teusink - blog.teusink.net ===\n\n" )
if len(sys.argv) < 2:
sys.stderr.write("Usage: %s infile.plb [outfile]\n" % sys.argv)
infile = open(sys.argv)
outfile = None
if len(sys.argv) == 3:
outfile = open(sys.argv, 'w')
lines = infile.readlines()
for i in range(0, len(lines)):
# this is really naive parsing, but works on every package I've thrown at it
matches = re.compile(r"^[0-9a-f]+ ([0-9a-f]+)$").match(lines[i])
base64len = int(matches.groups(), 16)
base64str = ''
j = 0
while len(base64str) < base64len:
base64str += lines[i+j]
base64str = base64str.replace("\n","")
outfile.write(decode_base64_package(base64str) + "\n")
Even if this code looked straight forward on the first sight, it took me a moment or two to understand it. In fact I googled and found the following information helpful:
After flipping through all these pages I had some second thoughts about publishing an unwrapper, especially since David, Pete and Anton were a bit secretive about certain details such as the substitution table. Obviously I decided to publish it nonetheless. Is this really harmful? There are already a couple of other 10g unwrapper available, such as:
In the end this is just another PL/SQL Unwrapper. However, I believe it delivers some additional value, if Oracle’s SQL Developer is the IDE of your choice. This is how it looks like on Windows:
The wrapped code will be replaced in the editor by the unwrapped code…
…you have to pay attention to not save the unwrapped code by accident.
Grab your copy of Trivadis PL/SQL Unwrapper from the download area. I hope it is useful.
Thanks a lot, saves a lot of time!
Awesome. I don’t need to go other places to unwrap the packages. I have all that I need in my SQL Developer tool.
Thanks a lot for this post.
Hi Phillip – Thanks a ton for this unwrap extension to SQL Developer , saved me so much time and effort! Kudos to your efforts!
Phillip, I’ve downloaded .jar file. What I need to do further in order to get the final result: “Unwrap” submenu in “right click”-menu of SQLDeveloper’s editor. Sorry, I’m not a Java developer. Thanks in advance. Oleg
You have to start SQL Developer and install the downloaded Unwrapper_for_SQLDev_0.1.1.zip file. Please follow the instructions under “Installation” at the bottom of https://www.salvis.com/blog/downloads/plsql-unwrapper-for-sql-developer/. See also the FAQ section, specifically https://www.salvis.com/blog/faq/i-cannot-install-any-extension-on-windows/ if you are using Windows.
There don’t seem to be any installation instructions on the linked page.
In the download area is a link. See https://github.com/Trivadis/plsql-unwrapper-sqldev#configure-update-center
Oh, got it. You have to add it to the “Check for Updates…” menu. Not obvious if you’re not that familiar with SQL Developer. Thanks.
Update center is the preferred way. The other option is to download the zip and use the
Install From Local Fileoption. It’s also mentioned there.
Great tool, thank you.
Do you plan to develop Oracle 9i unwrapper?
You’re welcome. No, I do not plan to provide a pre 10g unwrapper in the near future.
[…] days ago I’ve released PL/SQL Unwrapper for SQL Developer version 0.1.1 and blogged about it. With this extension you can unwrap the content of a SQL Developer window. Time for an update. With […]
thank you very much
Great tool! Thank you!
HI, I have downloaded the jar file, but unable to configure/install the unwrap utility into sqldeveloper. I am unable to find the procedure to do that as you specified in Installation section. Can you please help me on this?
You should get a zip file and not a jar after download. Please configure your browser to suppress unzipping files after download. Regarding the installation instructions, see section “Configure Update Center” in PL/SQL Unwrapper for SQL Developer.
I am not able to unwrap a package in SQL Developer after adding your extension.
I am using SQL Developer 4.1.1.
When i am select the package body and clicking in the unwrap it is showing
“Could unwrap this code . Most probably it was not wrapped with Oracle 10g , 11g and 12c wrap Utility.
I assume that the error message is accurate. Unwrapping is not supported by this extension when the code was wrapped with an Oracle 9i wrap utility.
Thank You. I could able to install using the last section for “Check for Updates”
I have installed the packages and updated them from “Check for updates’ option. But how can i get options for “Check ” and “Beautify”
For “Check” you have to install PL/SQL Cop for SQL Developer. “Beautify” is another SQL Developer plugin (a formatter) which is not yet released.
Thank you so Much for your Efforts. It really helped.
Just one question: How did you take that snapshot of screen? I mean Cutting Edges on the Bottom (0r any side of the image) ;-) What tool/utility you used? Please tell me the way. I googled it but could not able to find out.
You’re welcome. I’m using Snagit from TechSmith.
Thank You Philipp. Appreciate your efforts :)
I tried to install your extension into my Oracle SQL Developer v. 22.214.171.124 by referencing the URL http://update.salvis.com/ as noted in your instructions. The extension downloaded, but the extension functionality doesn’t show up.
I noticed an error displayed in the Extensions Log that states:
Error: ESDK version 2.0 is too high. Maximum supported version is 1.0.
Error: Exception processing manifest: java.lang.NullPointerException:null
Double-clicking on the first error displays a file named “extension.xml” which contains:
I’m not sure what to try next in order to resolve this issue. I would appreciate any suggestions you might have.
SQL Developer 1.x, 2.x, 3.x are based on the Extension Software Development Kit (ESDK) version 1. SQL Developer 4.x requires the use of ESDK version 2. Unfortunately these versions are not compatible. So I decided to support the newest SQL Developer version only. I suggest to update to the latest SQL Developer version (currently 4.1.5) since this is a prerequisite for this extension.
Thank you for your assistance, Philipp!
How can i come to know about logic behind of unwrap
Look at the Nils Teusink’s Python script unwrap.py and the links mentioned above. The work from David Litchfield, Pete Finnigan and Anton Scheffler are very helpful.
I,ve installed the Unwrapper from local file Unwrapper_for_SQLDev_1.0.0.zip onto SQL Developer 126.96.36.199 Build MAIN-21.78,
but don’t see any effect after restarting neither messages in extensions log.
Thank you in advance for help.
The extension should work on 4.1.5. In fact I’m successful using it on Windows and MacOS. Which OS are you using? How have you installed the extension? Have you followed the instructions at the bottom of Configure Update Center? If you are on Windows, please have a look at the FAQ I cannot install any extension on Windows.
Windows Server 2012 R2. Check For Updates -> Install From Local File. Then New Installs/Other/Trivadis PL/SQL Unwrapper for SQL Developer, Finish, Restart and… nothing.
SQL Developer seats on C:\sqldeveloper, I’m administrator myself and tried to “Run As Administrator”.
C:\sqldeveloper is ok, no need to run as administrator. Installation procedure looks good as well. Do you see the extension in the About or features dialog as shown below?
If not, do you see some related logging output under “Views->Log” after the installation/restart?
In “C:\sqldeveloper\configuration” you should find the files cfu_bundles.info and cfu_bundles.extras. These are CSV files, containing a line for each non-standard extension. They do not exist on a fresh SQLDev installation. Here’s the content required for version 1.0.0:
In “C:\sqldeveloper\sqldeveloper\extensions” you should find the file “com.trivadis.unwrapper.sqldev.jar”. It should have been extracted from the ZIP file during installation. That’s all what is required for a successful installation.
However, there are cases when SQL Developer’s System cache is corrupt. You may either delete your settings completely (folder C:\Users\YOURNAME\AppData\Roaming\SQL Developer\system188.8.131.52.78) or the cache (folder C:\Users\YOURNAME\AppData\Roaming\SQL Developer\system184.108.40.206.78\system_cache). Please export connections and other settings before you do so.
Hi Philipp, thanks a lot.
I don’t see Unwrapper on both screens above :(
Checked configuration, cfu_bundles.info was the same as cfu_bundles.extras, fixed it.
Jar file on its place.
Deleted whole \AppData\Roaming\SQL Developer\system220.127.116.11.78, restarted, reinstalled, restarted again. Nothing.
One suggestion: my Oracle box isn’t connected to the net, even have no net card. Can it affect?
Hi Eugene, strange. I’ve contacted you via E-Mail. Cheers, Philipp
this tool gives me an error message that ‘NOT A VALID CODE OR INCOMPLETE’,but it runs on my system.
please anyone unwrapp this procedure and send it my mail(email@example.com)….
CREATE OR REPLACE PROCEDURE FTP_UPLOADFILE wrapped
(removed trailing code)
The full error message is with version 1.0.0 is:
The error message is correct. This coded has been wrapped with a pre Oracle 10g wrap utility. See also FAQ Is unwrapping code processed by the PL/SQL Wrapper of Oracle7, Oracle8, Oracle8i or Oracle9i supported?
I am using SQL Developer in my Linux Virtual Box and I am not able to see the option Unwrap on right clicking, Also I couldn’t use ur zip file as its for windows
Could you please help me in this regard !
The ZIP file is platform independent.
I suggest to install the extension via the update center. See https://github.com/Trivadis/plsql-unwrapper-sqldev#configure-update-center . If it does not work, please have a look at the the FAQs. If the FAQs do not help then please open a GitHub issue and provide the version of SQL Developer, the OS (of the VM) and error messages you’ve got during the installation process. Thanks.
I am using SqlDev Version 18.104.22.168 now. I have tried and installed the Unwrapper utility under Check for Updates using Install from Local file option
(Path: C:\Users\Unwrapper_for_SQLDev_1.0.0.zip). Unfortunately, there is no unwrap option listed while right click on SQL window after restarted.
A possible reason for the issue you are describing is that you have installed SQL Developer in a protected folder, e.g. under
C:\Program Files. Installations have to be done as Administrator. See also https://github.com/Trivadis/plsql-unwrapper-sqldev/blob/main/FAQ.md#i-cannot-install-any-extension-on-windows for more information.
If this does not solve your issue then please open a GitHub issue https://github.com/trivadis/plsql-unwrapper-sqldev/issues/new with further details such as path where SQLDev is installed and steps to reproduce the issue.
Very good and useful tool.
I read your initial post and the articles you have mentioned. I am searching for a feature that is not covered by any of the tools I have seen. I want to be able to unwrap the code in the database and not in a file. Would it be possible to have some pl/sql code that allows to wrap/unwrap the code in the internal structures of Oracle?
Thank you for your thoughts
You can use this SQL Developer extension to read the wrapped code from the database and unwrap it. It’s even possible to do that for a complete schema as shown in this blog post.
If you are looking for an Unwrapper that runs in the Oracle Database, then have a look at plsqlunwrapper. I’ve mentioned in the blog post.
Thanks for your tool, Phil!
What do you suggest for this error? I was given a Package Spec & Body together in one file. Even if I took the spec off & tried unwrapping the body by itself, same error (slightly different character counts). Did the source file get corrupted somehow during the extraction?
Wrapped code seems to be truncated. Expected length of 4857 characters but got only 4028.
SQL Developer 23.1.0.097 (64-bit)
Unwrapper For SQL Developer 1.0.0
indeed, it appears to be a corrupted file. Other Database object unwrapped ok using your tool, but for this package, even pasting it online at https://www.codecrete.net/UnwrapIt/ declared:
The PL/SQL code cannot be unwrapped. The data seems to be corrupted.
So, there’s nothing you can do… unless! You build an uncorrupter! jus’ kidding. Your tool is great as is :)