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

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:

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.

33 Comments

  1. Michal says:

    Thanks a lot, saves a lot of time!

  2. 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. 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. 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. 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. Alvaro Ramos says:

    thank you very  much

  8. Lucas says:

    Great tool! Thank you!

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

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

         

  10. Banu says:

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

  11. 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. 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. 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:

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

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


          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\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

          • 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. 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?

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.