It's at that stage you change your policy and switch to using a source control system to track changes - but what to do with code?!
I'm a big fan of the "start again" approach and so I wrote this little PL/SQL routine to find the source code of a package body in Oracle and remove *almost* all the comments. The comments it will keep are those that exist on a line of code. For example if we have the code block;
/* This is a standard block
-- prepare to increment loop counter
v_Int := v_Int + 1; -- increment loop counter
Then this routine will strip out the block comment and the point where the line starts with "--" but *not* the comment that comes after the line of code (the -- increment ...).
Of course you can tailor this to your heart's content.
Now, a little note on execution. I use PL/SQL Developer from All Round Automations to do my editing. This has a nice feature called a "Test Window". This allows you to pass parameters to/from a script. I've used this feature with this script to generate a script that populates two parameters, one with the original source code and the other with the "edited" version. If you don't use PL/SQL developer you'll need to find some other way of achieving this.
Anyway, here is the script, you'll need to replace &XXXXX with your package name:
declare -- Local variables here v_CharNo number := 1; v_CharCount number; v_InComment boolean; v_AddChar boolean; v_SourceCode clob; v_Chars varchar2(2); v_Text all_source.text%TYPE; cursor c_GetSource is select text from all_source where name = '&XXXXX' and type = 'PACKAGE BODY'; procedure addToCLOB(v_Text in varchar2) as begin dbms_lob.writeappend(v_SourceCode, length(v_Text), v_Text); end; begin -- Test statements here dbms_lob.createtemporary(lob_loc => v_SourceCode, cache => False); :old_data := ''; for v_Line in c_GetSource loop v_Text := trim(v_Line.Text); if substr(v_text, 1, 2) != '--' then addToCLOB(v_Line.Text); end if; end loop; :old_data := v_SourceCode; v_CharCount := length(:old_data); v_InComment := False; while v_CharNo <= v_CharCount loop if (not v_InComment) and (substr(:old_data, v_CharNo, 2) = '/*') then v_InComment := True; end if; v_AddChar := not v_InComment; if v_AddChar then :new_data := :new_data || substr(:old_data, v_CharNo, 1); end if; if (v_InComment) and (substr(:old_data, v_CharNo-2, 2) = '*/') then v_InComment := False; end if; v_CharNo := v_CharNo + 1; end loop; end;
Pretty simple stuff, if you have any questions drop me a comment ...