DEV Community

Franck Pachot for AWS Heroes

Posted on • Edited on

Unwrap Oracle Home .plb

When you get an error with an Oracle DBMS_ package, there's a line number given to be able to troubleshoot. However, most of them are wrapped to have their code compact, with comments stripped, when loading it in the dictionary. Opening the file with a CREATE OR REPLACE PACKAGE BODY shows only random printable characters:

Image description

To troubleshoot an error, you can get the PL/SQL code back easily in order to check the line where you got an error.

There's a plugin for SQL Developer:

GitHub logo Trivadis / plsql-unwrapper-sqldev

PL/SQL Unwrapper for SQL Developer

Discontinuation Notice

@PhilippSalvisberg no longer work for Trivadis - Part of Accenture and no one at Trivadis/Accenture will continue this project. Therefore, this GitHub repository was archived on 30 August 2024.

PL/SQL Unwrapper for SQL Developer

Introduction

PL/SQL Unwrapper for SQL Developer is a free extension to unwrap PL/SQL code wrapped with the wrap utility of the Oracle Database Server version 10g, 11g, 12c, 18c or 19c.

Example

Open a wrapped PL/SQL unit (procedure, function, package specification, package body, type specification or type body) in a SQL Developer editor

Wrapped

Right-click within editor to show the pop-up-menu. Select Unwrap or simply press Ctrl-Shift-U to unwrap the code.

Unwrap

The editor content is replaced by the unwrapped code.

Unwrapped

By default the unwrapped code does not contain a valid DDL statement. In this case the CREATE is missing. You may change this behaviour in in the preferences.

Preferences

Check the Add ‘CREATE OR REPLACE' option…

An online site can also do it online: https://www.codecrete.net/UnwrapIt/

Here is a small script that writes the code from all *.plb in the $ORACLE_HOME to a .unwrapped one:

export PATH=$PATH:$ORACLE_HOME/python/bin
pip install html2text
for f in $(grep -l ' wrapped *$' $(find $ORACLE_HOME -name "*.plb"))
do
 curl -s -F "file=@$f" -F "ShowLineNumbers=false" -F "UnwrapFile=Unwrap File" https://www.codecrete.net/UnwrapIt/ |
 html2text | awk '
/Unwrap More Code/{p="Y";next}
/Bleichenbacher/{print "--",$0;next}
p=="Y"{gsub(/ʼ/,qq);print}
' qq="'" > ${f}.unwrapped && wc ${f}.unwrapped
done
Enter fullscreen mode Exit fullscreen mode

This is to run on only in a lab, why not a container started from Oracle 23c Free edition (docker run container-registry.oracle.com/database/free), and is for educational purpose only. Nothing is encrypted when wrapped, but that doesn't give you any right on the code. This is not Open Source!

Top comments (0)