Monday, August 20, 2007

Loading wrapped PL/SQL source code into Apex

You may want to know that it is perfectly possible to load wrapped PL/SQL code into Apex.

Wrapped source code is the source code that has been obfuscated using the wrap utility, documented in the PL/SQL User's Guide and Reference for Oracle 10G (or similar document for earlier or future versions).

Basically you can get wrapped source by executing the following statement from the O/S command line (I did this on a Windows box):

wrap iname=source.pls oname=source.plb
where iname is the parameter for the input file and oname for the output file.
Please refer to the official documentation for restrictions and/or additional information.

Note also that using these file extensions is not mandatory, they just represent the usual ones.

By wrapping the source code you achieve the following two goals:

  1. to make the source code unmodifiable;
  2. to make the source code difficult to read.

I say difficult but not impossible, because it seems that there is some way to reverse engineer wrapped code, if you are interested in doing this, you can certainly find some link where the this matter is investigated.

Once the source code has been wrapped, it can be easily loaded as a Simple Script or as a Supporting Object Installation Script.

In order to do so, i recommend using the "Load from file" option instead of the copy-and-paste into the editor window technique, but either of the two should be perfectly working.

For some unknown reason that i unsuccessfully tried to recreate, i ended up once with a corrupted package body and basically you will find out that something has gone wrong if you see either of these symptoms:

1. attempting to compile the wrapped object you get :
PLS-00801: internal error [pkg_read: corrupted wrap 3]

2. attempting to compile the wrapped object you get:
PLS-00753: malformed or corrupted wrapped unit

3. attempting to run a wrapped packaged function or procedure you get:
ORA-04063: package body "OWNER.PACKAGE_NAME" has errors

Whatever the error is, the only practical workaround is to reinstall the corrupted object from a valid source file.

As i said earlier, i was unable to reproduce this problem without explicitly editing the wrapped code (which led to PLS-00753 only whereas PLS-00801 remains a mystery), which is a good thing in the end, because it means that the whole process is quite robust.

In pursue of PLS-00801 i also checked for an encoding issue because i was suspecting that specifying the wrong file encoding at load time could cause the problem, but the wrapped portion of the code is encoded using the so-called BASE64 character set (that is the basic ASCII 7-bit character set) and changing from UTF8 to WINDOWS1252 or vice versa didn't affect the result.

6 comments:

Tonguç said...

> Whatever the error is, the only practical workaround is to reinstall the corrupted object from a valid source file.

Some research advice other ways around :)

Byte64 said...

Tonguç,
do you mean that you can recover a working PL/SQL source even if you are missing half of the wrapped code?


Bye,
Flavio

Phil said...

I get this error and it is a real problem as I need to provide an APEX build with the packages wrapped. I can run the wrapped code in using SQL*Plus fine but loaded in via APEX to automate the install fails for some reason.

Byte64 said...

Phil,
i had the same problem for some time, but when i decided to create a test case for reporting the problem to the Apex team, the installation scripts started working fine.
It was really frustrating because the error disappeared altogether and i never understood why.
I thought it could be related to a bad character set encoding that i changed afterwards, but i never succeeded in reproducing the error condition again, except when i deliberately removed some characters from the source code, so it looked like a corrupted file problem, but the same source code would compile perfectly using SQLPlus!
In conclusion, sometimes it's really hard to build a reproducible
test case, because there are so many boundary conditions that play a role.

Bye,
Flavio

yelkarama said...

there is a package called DBMS_DDL which contains a procedure called CREATE_WRAPPED. I think This can help you create wrapped procs in APEX.

PROCEDURE create_wrapped(ddl VARCHAR2);
PROCEDURE create_wrapped(ddl dbms_sql.varchar2s, lb PLS_INTEGER,
ub PLS_INTEGER);
PROCEDURE create_wrapped(ddl dbms_sql.varchar2a, lb PLS_INTEGER,
ub PLS_INTEGER);

Byte64 said...

Hello Yelkarama,
this is true for Oracle 10R2 or better, although i haven't tried to use these packaged functions yet.

Thanks for pointing this out.

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles