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:
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:
Trivadis
/
plsql-unwrapper-sqldev
PL/SQL Unwrapper for SQL Developer
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
Right-click within editor to show the pop-up-menu. Select Unwrap or simply press Ctrl-Shift-U to unwrap the code.
The editor content is replaced by the unwrapped code.
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.
Check the Add ‘CREATE OR REPLACE' option to get a complete DDL statement.
Bulk Unwrap
If you install oddgen for SQL Developer you will see a PL/SQL Unwrapper node in the Generators window:
Open the PL/SQL Unwrapper node…
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
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)