Debug Compile

The very main reason that I got to use and love Pl/Sql Developer years ago is it's debug capabilities. Back then I used Toad freeware like my colleagues while we had a PSD-license on the project. I found out that PSD had a debugger, a tool that I missed in writing Pl/Sql. Surprisingly I thought, because in my student-years I programmed Turbo Pascal under Dos and even that had a powerful debugger.

Before you can debug you have to add debug info to your pl/sql. By default Pl/Sql Developer compiles the packages with the debug option on. Until last week I did not know what the appropriate statement-clause was. But since PSD does it for me, I did not care.
It turns out to be:
alter package compile debug body

I'm currently working again with the Pl/Sql public api's of E-Business Suite. And I have to find out what particular values I have to pass. So being able to debug into the EBS packages turns out to be helpfull. However recompiling the ebs-packages invalidates referencing packages. Now that's not too much of a problem, but some of them fail with a "PLS-00123:program too large" error.
I was very surprised since I thought that was a pre-8i error (we're working with DB10gR2).

I did not dug to deep in it, but found it very unconvenient to find out what packages could and which couldn't be compiled with debug option. So today I created a script, actually a script that I created many times before, but now in a little smarter way.

It uses a cursor to list all the invalid packages and package-bodies that are invalid. In the loop it tries to compile them with the debug option. If that fails than it tries to compile them without the debug option. If that fails again, then there is something more problematic wrong.

Here is the script:

declare
  cursor c_obj 
  is select * from all_objects obj
  where obj.object_type in ('PACKAGE', 'PACKAGE BODY')
  and obj.status = 'INVALID';
  l_command varchar2(32767);
  procedure put_line(p_text in varchar2) is
    l_text varchar2(32767) := p_text;
    l_line varchar2(255);
  begin
    while length(l_text) > 0 loop
      l_line := substr(l_text, 1, 255);
      dbms_output.put_line(l_line);
      l_text := substr(l_text, 256);
    end loop;
  end put_line;
begin
  for r_obj in c_obj
  loop
    begin
      -- First try with debug
      l_command := 'alter package ' ||r_obj.object_name || ' compile debug';
      if r_obj.object_type = 'PACKAGE BODY' then
        l_command := l_command || ' body';
      end if;
      put_line('Executing: '||l_command);
      execute immediate l_command;
      put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name|| ' with debug option.'));
    exception
      when others then
      begin
        -- Then try without debug
        l_command := 'alter package ' ||r_obj.object_name || ' compile ';
        if r_obj.object_type = 'PACKAGE BODY' then
          l_command := l_command || ' body';
        end if;
        put_line('Executing: '||l_command);
        execute immediate l_command;
        put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name));
      exception
        when others then
          put_line('Error compiling '||lower(r_obj.object_type||' '||r_obj.object_name));
      end;
    end;
  end loop;
end;