Yesterday I wrote about how you can (re)compile an object with the debug option. Now it might be handy to recompile an object with the debug option off. But to do that automatically you need to know which objects are compiled with the debug option. For you don't want to recompile your whole schema do you? I don't want it, since my schema is the APPS schema of EBS and it contains a billion packages...

So how would you do that? I was looking for a neat view that gives me this information. And browsing through all the 'ALL_%' views I stumbled on the nice view: all_stored_settings. It contains all kinds of parameters about the stored pl/sql objects.
The one I wanted to query on is the plsql_debug parameter. Mark that the parameter names are lower-case while the True/False values are Upper-case.

Besides Owner, Object_Name, Object_Type columns it also has the Object_id column. So to getall the packages with the debug option on simply join the view with the all_objects view:

select obj.* 
from all_objects obj
, all_stored_settings ssg
where ssg.object_id = obj.object_id
and ssg.param_name = 'plsql_debug'
and ssg.param_value = 'TRUE'
and obj.object_type in ('PACKAGE', 'PACKAGE BODY')

So to recompile all these packages I changed the script of yesterday:

declare
  cursor c_obj 
  is select obj.* 
  from all_objects obj
  , all_stored_settings ssg
  where ssg.object_id = obj.object_id
  and ssg.param_name = 'plsql_debug'
  and ssg.param_value = 'TRUE'
  and obj.object_type in ('PACKAGE', 'PACKAGE BODY');
  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
      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 loop;
end;

Of course this script can also easily be changed to add debug info for those packages that haven't yet...