Oracle PL/SQL: Stripping Comments From PL/SQL Packages

Now I've worked in several places with different coding policies. Some have said "comment your code where the meaning isn't clear" (which makes sense) and others have said that your code should include a (commented out) complete history of changes. Clearly if you adopt the second approach after a few years and several changes your code is going to go from being 90% code 10% comments to 90% comments 10% code.

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
comment */
-- 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 ...