Oracle Wrapped Package Unwrap

[Mirtha Hinrichs]

Viewed100K+ times! This question is You Asked Hi Tom,

I am analyzing a database using OEM (Database Control) console with ADDM.

Some Huge SQL statemente are procedures used by the application running on this database.

Unfortunately some packages and procedures are encripted (wrapped).

So I would to know if it is possible to look on this code (unwrapping it) obviously?

Any information about that will be really appreciated.

And just another one question, is it possible to monitor 9i databases using 10g Grid Control?

I would like to take advantage of ADDM to monitor 9i databases.

Thanks in advance.

Kind regards,

Francisco Mtz.

and Tom said...We don't provide a native mechanism for unwrapping PL/SQL, but there are tools on the internet. We don't support or guarantee the correctness of validity of such tools.

So there is no way to see the code out of the box, just the SQL.

You cannot use ADDM on 9i since most of ADDM is actually "in the database". So, while you can use 10g Grid control on 9i, you would still be using the 9i tuning pack against 9i for tuning.

Most all of the functionality (true functionality - not the GUI stuff you see, but the real work) is in the database.

Rating (18 ratings)

Is this answer out of date? If it is, please let us know via a Comment Comments Comment Thanks for your answer.Francisco Mtz., June 08, 2006 - 4:15 pm UTC

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account. More to Explore PL/SQL demos Check out more PL/SQL tutorials on our LiveSQL tool.

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:

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!

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

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.

To produce a wrapped file, use either the PL/SQL Wrapper utility or a DBMS_DDL subprogram. The PL/SQL Wrapper utility wraps the source text of every wrappable PL/SQL unit created by a specified SQL file. The DBMS_DDL subprograms wrap the source text of single dynamically generated wrappable PL/SQL units.

By default, the 12.2 PL/SQL compiler can use wrapped packages that were compiled with the 9.2 PL/SQL compiler. To prevent the 12.2 PL/SQL compiler from using wrapped packages that were compiled with the 9.2 PL/SQL compiler, set the PL/SQL compilation parameter PERMIT_92_WRAP_FORMAT to FALSE. For more information about PERMIT_92_WRAP_FORMAT, see Oracle Database Reference. For more information about PL/SQL compilation parameters, see "PL/SQL Units and Compilation Parameters".

For example, you cannot load files produced by the version n.1 PL/SQL Wrapper utility into a version (n-1).2 Oracle Database. Nor can you load files produced by the version n.2 PL/SQL Wrapper utility into a version n.1 Oracle Database. Wrapped files are both upward- and downward-compatible across patch sets.

Leaving the specification unwrapped allows other developers to see the information needed to use the package or type (see Example A-5). Wrapping the body prevents them from seeing the package or type implementation.

This example uses the PL/SQL Wrapper utility to wrap wraptest2.sql and shows the wrapped file, wraptest2.plb. The wrapped file shows that the utility deleted the comments inside the code and wrapped (made unreadable) the PL/SQL source text of the procedure wraptest and the function fibonacci, but kept the comments outside the wrapped source.

In SQL*Plus, this example runs the wrapped file wraptest.plb, creating the procedure wraptest and the function fibonacci; selects the text of the subprograms (which is wrapped and therefore unreadable), and then invokes the subprograms.

The DBMS_DDL package provides WRAP functions and CREATE_WRAPPED procedures, each of which wraps the PL/SQL source text of a single dynamically generated wrappable PL/SQL unit. The DBMS_DDL package also provides the exception MALFORMED_WRAP_INPUT (ORA-24230), which is raised if the input to WRAP or CREATE_WRAPPED is not a valid wrappable PL/SQL unit. (For the list of wrappable PL/SQL units, see the introduction to "PL/SQL Source Text Wrapping".)

Each WRAP function takes as input a single CREATE statement that creates a wrappable PL/SQL unit and returns an equivalent CREATE statement in which the PL/SQL source text is wrapped. For more information about the WRAP functions, see Oracle Database PL/SQL Packages and Types Reference.

If you pass the statement that DBMS_DDL.WRAP returns to the DBMS_SQL.PARSE procedure whose formal parameter statement has data type VARCHAR2A, then you must set the lfflg parameter of DBMS_SQL.PARSE to FALSE. Otherwise, DBMS_SQL.PARSE adds lines to the wrapped PL/SQL unit, corrupting it. (For the syntax of DBMS_SQL.PARSE, see Oracle Database PL/SQL Packages and Types Reference.)

Each CREATE_WRAPPED procedure does what its corresponding WRAP function does and then runs the returned CREATE statement, creating the specified PL/SQL unit. For more information about the CREATE_WRAPPED procedures, see Oracle Database PL/SQL Packages and Types Reference.

When invoking a DBMS_DDL subprogram, use the fully qualified package name, SYS.DBMS_DDL, to avoid name conflict if someone creates a local package named DBMS_DDL or defines the public synonym DBMS_DDL.

Example A-5 selects the text of the package that Example A-4 created, emp_actions, and then invokes the procedure emp_actions.raise_salary. If the package specification were wrapped, then the information needed to invoke the procedure would be unreadable, like the PL/SQL source text of the package body.

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:

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?

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.

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.

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?

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.

A possible reason for the issue you are describing is that you have installed SQL Developer in a protected folder, e.g. under C:\Program Files. Installations have to be done as Administrator. See also -unwrapper-sqldev/blob/main/FAQ.md#i-cannot-install-any-extension-on-windows for more information.

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?

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?

Hi:

We have a requirement to upgrade a custom application built on Oracle7 to 10g. There are some wrapped procedures which could not be migrated. Is there any toll available to unwarp the same and reqrap after migration to 10g?

Thanks

Murali

Hi,

I came across your blog and I am trying to unwrap a PL/SQL package in 9i, But it fails. If I am not mistaken, Base 64 encoding is in 10g not in 9i. I tried unwrap utility online but it fails saying not a valid wrapped code.

Thanks

Renuka

3a8082e126