Wrapping PL/SQL Source Code and a Strange Bug (PLS-00753)


Wrapping process makes PL/SQL source code unreadable. Developers can wrap package specifications, package bodies, functions, procedures, type specifications and type bodies. Unfortunately triggers cannot be wrapped. To wrap the trigger code you need to place your source code to a package or procedure and call it from the trigger.

There are two ways to wrap PL/SQL source code.

1. Wrapping PL/SQL source code with wrap utility


Wrap utility takes two parameters. First one (iname) is the PL/SQL source code file and the second one (oname) is the output file that will hold the wrapped code.

wrap iname=example_input_file.sql oname=example_output_file.plb

This is an easy way to wrap source code because you do not need to handle DDLs bigger than 32767 bytes. But this way, you need to save DDLs into different files and execute the wrap command frmo the command prompt for each file.

2. Wrapping PL/SQL source code with DBMSL_DDL package


The DBMS_DDL package contains an overloaded wrap API to wrap PL/SQL source code.

dbms_ddl.wrap(ddl varchar2, lb pls_integer, ub, pls_integer) return varchar2;

dbms_ddl.wrap(ddl dbms_sql.varchar2s, lb pls_integer, ub, pls_integer) return dbms_sql.varchar2s;

dbms_ddl.wrap(ddl dbms_sql.varchar2a, lb pls_integer, ub, pls_integer) return dbms_sql.varchar2a;

In this method of wrapping, developers need to handle CLOB values that are bigger than 32767 bytes. They need to convert CLOB values to either dbms_sql.varchar2s or dbms_sql.varchar2a format.

We wrapped all our source code the second way in Oracle 10.0.2.1. We liked the ability ti generate all the encoded scripts automatically in PL/SQL. The encoded scripts run just fine from SQLPlus or similar tool. However, when we run the scripts using JDBC, we have faced a problem. Some packages started to give the following error mesagge:

PLS-00753: malformed or corrupted wrapped unit

First, we tried to solve the problem with changing character encoding but this did not help us. Then we started again to investigate the problem and we found a really interesting statement in the following forum thread. site.

This only happens if the last character of the wrapped code is at the end of a line.

We looked over all our uncompiled packages and verified the problem. Then we found an odd solution to this strange bug. We started to break down the last character of the wrapped code to next line. It does not mess up the wrapped code and works just fine in different oracle 10g versions.

Here is the function which extracts the DDL of the specified object in dbms_sql.varchar2a format and then wraps the source code using the dbms_ddl.wrap utility. We used dbms_sql.varchar2a due to the length of our object DDLs. At the end, there is a code block which converts dbms_sql.varchar2a to CLOB. It breaks down the last character of the wrapped code to the next line if the last character of the wrapped code is at the end of the line.

function extractCodeWrapped(in_objectType_cd varchar2, in_objectName_tx varchar2)
return  clob
is
 v_ddl_index_nr number;
 v_cur_pos_nr number;
 v_next_pos_nr number;
 
 v_ddl_2a dbms_sql.varchar2a;
 v_wrapped_ddl_2a dbms_sql.varchar2a;
 
 v_wrapped_ddl_cl clob;
 
 cursor cur_objectDDL is
   select -1 line, 'create or replace'||chr(10) text from dual
   union all
   select line, text
   from user_source
   where type = in_objectType_cd
   and name = in_objectName_tx
   order by line;
   
begin
 dbms_lob.createtemporary(v_wrapped_ddl_cl,true);

 -- extract DDL of the object
 v_ddl_index_nr := 1;
 for rec in cur_objectDDL loop
   v_ddl_2a(v_ddl_index_nr) := rec.text;
   v_ddl_index_nr := v_ddl_index_nr + 1;
 end loop;
 
 -- wrap source code of the object
 v_wrapped_ddl_2a := dbms_ddl.wrap(v_ddl_2a,1,v_ddl_2a.count);
 
 -- Convert dbms_sql.varchar2a to CLOB
 for i in 1..v_wrapped_ddl_2a.count loop
   
   -- if it is the last line check the last character. If the last character is
   -- at the end of line, break down it to next line.
   if i = v_wrapped_ddl_2a.count then
     -- find the positions of line feeds (chr(10)) at the line
     v_cur_pos_nr := 0;
     v_next_pos_nr := 0;
     loop
       v_cur_pos_nr := instr(v_wrapped_ddl_2a(i), chr(10), v_cur_pos_nr+1);
       v_next_pos_nr := instr(v_wrapped_ddl_2a(i), chr(10), v_cur_pos_nr+1);
       
       exit when v_cur_pos_nr = 0 or v_next_pos_nr = (length(v_wrapped_ddl_2a(i))-1) 
              or v_next_pos_nr = length(v_wrapped_ddl_2a(i));
     end loop;
     -- if the line length is 74, the last character is at the end of line.
     if (length(v_wrapped_ddl_2a(i)) - v_cur_pos_nr) = 74 then
       -- append the line as two lines; first one is until last character and 
       -- second one is only last character
       dbms_lob.writeappend(v_wrapped_ddl_cl, v_cur_pos_nr, substr(v_wrapped_ddl_2a(i), 1, v_cur_pos_nr));
       dbms_lob.writeappend(v_wrapped_ddl_cl, 71, substr(v_wrapped_ddl_2a(i), v_cur_pos_nr+1, 71));
       dbms_lob.writeappend(v_wrapped_ddl_cl, 1, chr(10));
       dbms_lob.writeappend(v_wrapped_ddl_cl, 2, substr(v_wrapped_ddl_2a(i), v_cur_pos_nr+72, 1)||chr(10));
     else
       -- otherwise append the line directly
       dbms_lob.writeappend(v_wrapped_ddl_cl, length(v_wrapped_ddl_2a(i)),v_wrapped_ddl_2a(i));
     end if;
   else
     -- if it is not last line append the line directly
     dbms_lob.writeappend(v_wrapped_ddl_cl, length(v_wrapped_ddl_2a(i)),v_wrapped_ddl_2a(i));
   end if;
 
 end loop;
 -- add '/' character end of object wrap
 dbms_lob.writeappend(v_wrapped_ddl_cl,3,'/'||chr(10)||chr(10));
 
 -- return wrapped source code
 return v_wrapped_ddl_cl;
end;
Here is sample code which calls the function:

declare
 v_objectType_cd varchar2(200) := 'PROCEDURE';
 v_objectName_tx varchar2(255) := 'TEST_PROCEDURE';
 
 v_wrapped_ddl_cl clob;
begin
 v_wrapped_ddl_cl := extractCodeWrapped(v_objectType_cd, v_objectName_tx);
 dbms_output.put_line(v_wrapped_ddl_cl);
end;
Samet Basaran

Comments

Ayhan Gungor said…
hi Samet, sites like this (www.codecheck.info/UnwrapIt) and other tools ruined all magic with wrapping plsql codes. i gave up wrapping.
Gerger said…
Ayhan, couldn't you tell this a week ago? :-) Yalim.
Ayhan Gungor said…
let's hope Oracle finds a new algorithm for 12g-13g...
William Bishop said…
This can be fixed by removing any unnecessary blank lines from your code before running the wrap.exe utility.
William Bishop said…
This can also be solved by removing any unnecessary blank lines from your code before running the wrap utility.

Popular posts from this blog

Monitoring Oracle Database with Zabbix

Powerful Free Webinar Network for Oracle Developers