Search This Blog

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.

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;

Popular posts from this blog

Attend the free webinar by the Oracle ACE Director Martin Widlake and learn how Oracle works under the hood. About the Webinar:
Despite the complexity and capability of the Oracle database, at it's heart there are a relatively small number of key activities and processes. If you understand them, you will get a lot more out of the database and advanced topics make a lot more sense. In this presentation I explain the key things the database does and how they work together. If you know what a SELECT statement is, you will understand this talk. And by the end of it, you will know some things that even many experienced developers and DBAs don't know. In this webinar, you will learn: The two ways Oracle reads data from disc and how it writes it back.That REDO is actually the most important part of the database. What happens when you COMMIT.Why Oracle data blocks are key to the buffer cache.What a "Consistent Get" really is.Why indexes are so powerful and what "balanced B…