Sunday, December 6, 2009

java_invalids_root_cause.sql

set serveroutput on
set feedback off

declare

    cr varchar2(2) := '
';
      tab varchar2(8) := '+-----> ';

    cursor messages1 is
    select b.text, count(*) total
    from dba_objects a, user_errors b
    where a.status = 'INVALID'
    and a.object_type like 'JAVA%'
    and b.text like 'ORA-29521:%'
    and a.object_name = b.name
    and a.owner='APPS'
    group by b.text
    order by 2 desc;

    cursor messages2 is
    select b.text, count(*) total
    from dba_objects a, user_errors b
    where a.status = 'INVALID'
    and a.object_type like 'JAVA%'
    and b.text like 'ORA-29535:%'
    and a.object_name = b.name
    and a.owner='APPS'
    group by b.text
    order by 2 desc;


    counter number := 0;
    
    cursor objects(message varchar2) is
    select name
    from user_errors
    where text = message;

    function get_object_name(msg varchar2) return varchar2 is
    
       tmpstr varchar2(200);
    
    begin
    
      tmpstr := substr(msg,27);
   tmpstr := replace(tmpstr,'could not be found','');
   return trim(tmpstr);     
    end;
    
    
begin

    dbms_output.enable(100000);    
    dbms_output.put_line('Executing');
    for i in messages1 loop
        counter := counter + 1;
     for o in objects(i.text) loop
         dbms_output.put_line(tab || dbms_java.longname(o.name));
         --fnd_aolj_util.getClassVersionFromDB(dbms_java.longname(o.name));
     end loop;
        dbms_output.put_line('Number of invalid objects : ' || i.total);
        dbms_output.put_line('Dependency : ' || get_object_name(i.text));
     dbms_output.put_line(cr);    
    end loop;

    for i in messages2 loop
        counter := counter + 1;
     for o in objects(i.text) loop
         dbms_output.put_line(tab || dbms_java.longname(o.name));
     end loop;
        dbms_output.put_line('Number of invalid objects : ' || i.total);
        dbms_output.put_line('Cause : ' || i.text);
     dbms_output.put_line(cr);
    end loop;
    dbms_output.put_line('Done. Processed ' || to_char(counter) || ' invalid java objects.');
end;
/

No comments: