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; /
Sunday, December 6, 2009
java_invalids_root_cause.sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment