There are circumstances where you whish to have a look at the source of an wrapped PL/SQL package, procedure or function. The circumstances I want to have peek at the source is when some PL/SQL code becomes invalidated because an object the code depends on disapears or grants on such objects gets lost. It would/should of course be nice if the support department of an software company have a direct answer on what the dependencies exactly are, but I have found that in a lot of cases they don’t know and it will cost a lot of time to get things sorted out.
Wouldn’t it be nice that you actually can take a wrappped piece of PL/SQL and take a look at the line of code where Oracle is complaning about when you try to recompile?
This post will show the way wrapped PLSQL is wrapped and describes the method to unwrap wrapped PLSQL code in 10g and 11g Oracle databases.
The basic steps for unwrapping wrapped PL/SQL code in 10g Release 1 and higher (tested it on 10gR2,11g and 12c) is:
- Decode (base64) the wrapped code
- Decode the result by using a codetable
- Unzip the decoded byte array to get to the PL/SQL source
Components of a wrapped PL/SQL object
All wrapped PL/SQL objects can be broken down in a header and a body (BASE64). The header contains information about the version of the database it was wrapped on, the type of object and the length of both wrapped and unwrapped text. The body contains the actual wrapped code that is BASE64 coded (to be able to copy/past it).
CREATE OR REPLACE PROCEDURE test_proc wrapped
This first 20 lines can/should be ignored when unwrapping the PL/SQL code. The header of the wrapped code will give you some information about the type of PL/SQL code like the type of object that is wrapped. Below are some interesting fields of the header (you don’t actually need them to unwrap the PL/SQL object however!).
Hex value that looked like to be database server dependent, but most of the time just equals 1. So there is no real value using this header record.
A hex value specifying the type of PL/SQL object (all type of PL/SQL objects that can be wrapped):
The last line of the header contains two hex values separated by a space. These values contain length information. The first value contains the length of the unwrapped text (without the CREATE OR REPLACE part). The second value contains the length of the wrapped body without the header and without the ending LF (0x0A) and “/” sign.
As mentioned before, the wrapped PL/SQL text is BASE64 coded and needs to be decoded before you can actually start unwrapping (decrypting). The first 20 bytes of the BASE64 decoded body contains a SHA1 hash value for the wrapped (encrypted) body. The remaining of the body is a coded (using a codetable) compressed stream of bytes that contains the source text.
BASE64 coded body:
Unwrap wrapped PL/SQL code online at my tools page.