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.

PLS-00801 internal error and ORA-06544 pl/sql internal error

After giving the finishing touches to a PL/SQL procedure that had been haunting me for the last days, consisting primarily of one BIG implicit cursor with several subqueries and a convoluted logic, i was finally ready to compile the program when i got this terrifying error message:

Line: 1 Column: 3  Error: PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
Line: 7 Column: 12  Error: PL/SQL: ORA-06544: PL/SQL: internal error, arguments: [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []
Line: 7 Column: 5  Error: PL/SQL: SQL Statement ignored

Once i recovered from the shock, i soon realized that there must be some junk i left in the SQL statement that i was converting from the sqlplus format (where i developed and tested it) into a programmatic format, that is replacing substitution variables with procedure parameters and variables.

From my past experience i know that when Oracle 9i complaints with a PL/SQL parser internal error, although the manual suggests to call Oracle Support and report a supposed bug, usually it's much ado about nothing, it's just a matter of a construct that the parser doesn't like.

Note that the parser doesn't point us to the precise spot where it found a problem, but it reports it as a generic problem regarding the whole SQL statement (line 7, where my UPDATE begins) , so it's up to us to find out where the offending bit is located.

...
begin
...
UPDATE                        -- at line 7
...
AND L1.TYPE_CODE = 'MK'       -- at line 25
AND L2.TYPE_CODE = 'RE'
AND AM.MD_NBR = &num_model_nbr
AND L1.LOC_CODE = L2.LOC_CODE
AND AM.MD_CODE = 'WK'
AND MOG.USER_ID = i_var_user
AND MOG.CONFIG_FLAG = 'X'
AND MOG.MKT_NBR = L2.LOC_NBR
AND MOG.PL_NBR = AM.PL_NBR
...
end;

In my case, it was just a sqlplus substitution variable that i forgot to replace with its corresponding parameter, however i remember i saw this happen more than once with certain SQL statements involving function THE (aka TABLE function) inside triggers.

If you want to artificially recreate this problem, try to execute the following anonymous PL/SQL block:

begin
update dual
   set k = k
 where k = &test;
end;

In conclusions there are situations where the PL/SQL parser seems to say: "hey look, i know there is a problem with your SQL but i can't make out exactly what it is, i give up, sorry!"

The good news is that at least Oracle doesn't freeze and doesn't force you to press CTRL-ALT-DEL to restart!

See message translations for ORA-06544 and search additional resources.