Introducing PL/SQL Unwrapper for SQL Developer

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’ve been 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 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.”

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.


Niels Teusink’s Python script unwrap.py (copy)

#!/usr/bin/python
#
# This script unwraps Oracle wrapped plb packages, does not support 9g
# Contact: niels at teusink net / blog.teusink.net
#
# License: Public domain
#
import re
import base64
import zlib
import sys

# 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]

def decode_base64_package(base64str):
	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])])
	return zlib.decompress(decoded)
	

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[0])
	sys.exit(1)

infile = open(sys.argv[1])
outfile = None
if len(sys.argv) == 3:
	outfile = open(sys.argv[2], '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])
	if matches:
		base64len = int(matches.groups()[0], 16)
		base64str = ''
		j = 0
		while len(base64str) < base64len:
			j+=1
			base64str += lines[i+j]
		base64str = base64str.replace("\n","")
		if outfile:
			outfile.write(decode_base64_package(base64str) + "\n")
		else:
			print decode_base64_package(base64str)


Even if this code looked straightforward on 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 unwrappers 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 what it looks like on Windows:

unwrapper-windows

The wrapped code will be replaced in the editor by the unwrapped code…

unwrapper-windows-2

…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.

46 Comments

  1. blank Michal says:

    Thanks a lot, saves a lot of time!

  2. blank Luis A says:

    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.

     

     

  3. blank Ashly says:

    Hi Phillip – Thanks a ton for this unwrap extension to SQL Developer , saved me so much time and effort! Kudos to your efforts!

     

    Thanks,

    Ashly.

  4. blank Oleg says:

    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

  5. blank Nick says:

    Great tool, thank you.

    Do you plan to develop Oracle 9i unwrapper?

  6. […] 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 […]

  7. blank Alvaro Ramos says:

    thank you very  much

  8. blank Lucas says:

    Great tool! Thank you!

  9. blank Banu says:

    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?

     

    Regards,

    Banu

    • Hello Banu,

      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.

      Regards Philipp

      • blank Kalpataru Rath says:

        Hi Philipp,

        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.

  10. blank Banu says:

    Thank You. I could able to install using the last section for “Check for Updates”

  11. blank Banu says:

    I have installed the packages and updated them from “Check for updates’ option. But how can i get options for “Check ” and “Beautify”

  12. blank Aashish Suneja says:

    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.

  13. blank Chad says:

    Hi Philipp,

    I tried to install your extension into my Oracle SQL Developer v. 3.2.20.10 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:

    <extension id="com.trivadis.unwrapper.sqldev" version="1.0.0.1878"
        esdk-version="2.0" rsbundle-class="com.trivadis.unwrapper.sqldev.resources"
        xmlns="http://jcp.org/jsr/198/extension-manifest">
    
    

    I’m not sure what to try next in order to resolve this issue. I would appreciate any suggestions you might have.

    Thanks!

    • Hi Chad,

      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.
      Philipp

  14. blank Naresh Kumawat says:

    How can i come to know about logic behind of unwrap

    • Hi Naresh,

      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.

      HTH
      Philipp

  15. blank Eugene says:

    Hi Philipp,
    I,ve installed the Unwrapper from local file Unwrapper_for_SQLDev_1.0.0.zip onto SQL Developer 4.1.5.21 Build MAIN-21.78,
    but don’t see any effect after restarting neither messages in extensions log.
    Thank you in advance for help.

    • Hi Eugene,
      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.
      HTH
      Philipp

      • blank Eugene says:

        Hi Philipp,
        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”.
        Thanks,
        Eugene.

        • Hi Eugene,

          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?
          blank

          blank
          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:

          com.trivadis.unwrapper.sqldev,1.0.0,../sqldeveloper/extensions/com.trivadis.unwrapper.sqldev.jar,4,false
          
          com.trivadis.unwrapper.sqldev,EXTENSION,,1.0.0.1878,Trivadis PL/SQL Unwrapper for SQL Developer,,Other,Trivadis PL/SQL Unwrapper for SQL Developer is an extension to unwrap an editor content.,Trivadis AG,http://www.trivadis.com,Trivadis PL/SQL Unwrapper for SQL Developer
          

          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\system4.1.5.21.78) or the cache (folder C:\Users\YOURNAME\AppData\Roaming\SQL Developer\system4.1.5.21.78\system_cache). Please export connections and other settings before you do so.

          Cheers,
          Philipp

        • blank Eugene says:

          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\system4.1.5.21.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?

          Best regards,
          Eugene.

        • Hi Eugene, strange. I’ve contacted you via E-Mail. Cheers, Philipp

  16. blank Rupesh Kumar sah says:

    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(sahrupesh12@gmail.com)….
    please…………………..

    CREATE OR REPLACE PROCEDURE FTP_UPLOADFILE wrapped
    0
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    abcd
    7
    8003000
    1
    4
    0
    30
    e FTP_UPLOADFILE:
    (removed trailing code)

  17. The full error message is with version 1.0.0 is:

    Could not unwrap this code. Most probably it was not wrapped with the Oracle 10g, 11g or 12c wrap utility.

    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?

  18. blank Eduardo Candido says:

    Perfect, congrats!

    Perfeito, parabéns!

  19. blank Nandhini says:

    Hi Philipp,

    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 !

    Thanks,
    Nandhini.

  20. blank Rajadeepan says:

    Hi,

    I am using SqlDev Version 19.2.1.247 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.
    Please help.

    Thanks

  21. blank Adrian Boangiu says:

    Hello Philipp,

    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.

  22. blank Kelly says:

    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?

    Unwrap failed
    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
    Windows 10

  23. blank Kelly says:

    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 :)
    ⭐⭐⭐⭐⭐

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.