Oracle package (module) text is damaged by 0xDBE

I have a dim memory of someone (here or in your bug tracker) saying they'd seen 0xDBE reorder the text of a procedure, function, or package. I have now seen that myself -- cost me a couple hours to find and fix the changed code. Fortunately         my database has flashback queries enabled, otherwise recovery would have been more difficult. I'll write a narrative that I hope will help you assess my claim:

* The package (module) is about 750 lines, including several procedures.

* Because modules open with all text flush left, I used Code/Reformat to make the text editable. I suspect it was this move that reorganized some lines but am not positive. Once I'd completed my edits, I "ran" the code to compile it.

* The two cases of text reordering are below. First, these lines:
   

...
(d.directory is null or d.directory = ''T'' ) and
|| block_where_clause
|| make_ethnic_clause(ethnic_in)
|| make_gender_clause(gender_in)
|| make_relatetype_clause('d.relation_to_school', relation_type_in)
|| make_degreetype_clause('d.degree', degree_type_in)
|| make_hidepeople_clause
|| make_degreeyear_clause(degree_year_in, degree_year_def_in)
|| joint_where_clause
|| prac_where_clause
|| member_where_clause
|| metro_where_clause
|| make_familyname_clause(familyname_in);
    

   
became these lines (one line moved):

   
...
(d.directory is null or d.directory = ''T'' ) and
'
|| block_where_clause
|| make_ethnic_clause(ethnic_in)
|| make_hidepeople_clause
|| make_gender_clause(gender_in)
|| make_relatetype_clause('d.relation_to_school', relation_type_in)
|| make_degreetype_clause('d.degree', degree_type_in)
|| make_degreeyear_clause(degree_year_in, degree_year_def_in)
|| joint_where_clause
|| prac_where_clause
|| member_where_clause
|| metro_where_clause
|| make_familyname_clause(familyname_in);
    
   

That case didn't damage the functioning of the procedure (but still). This case *did* break the code. This signature:

   
PROCEDURE get_addresslist( 
    degree_type_in IN INTEGER := 0,
    joint_degree_in IN VARCHAR2 := NULL,
    degree_year_in IN VARCHAR2 := NULL,
    degree_year_def_in IN INTEGER := 0,
    relation_type_in IN INTEGER := 0,
    report_mode_in IN INTEGER := 0,
    address_type_in IN INTEGER := 0,
    address_count_in IN INTEGER := 0,
    address_city_in IN VARCHAR2 := NULL,
    address_state_in IN VARCHAR2 := NULL,
    address_zip_in IN VARCHAR2 := NULL,
    address_metro_in IN VARCHAR2 := NULL,
    
   

became:

   
PROCEDURE get_addresslist(
    degree_type_in IN INTEGER := 0,
    joint_degree_in IN VARCHAR2 := NULL,
    degree_year_in IN VARCHAR2 := NULL,
    degree_year_def_in IN INTEGER := 0,
    relation_type_in IN INTEGER := 0,
    address_city_in IN VARCHAR2 := NULL,
    report_mode_in IN INTEGER := 0,
    address_type_in IN INTEGER := 0,
    address_count_in IN INTEGER := 0,
    address_state_in IN VARCHAR2 := NULL,
    address_zip_in IN VARCHAR2 := NULL,
    address_metro_in IN VARCHAR2 := NULL,
    
    Thank you.

Please sign in to leave a comment.