Rem
Rem $Header: imclnjav.sql 09-jun-2007.20:46:45 smavris Exp $
Rem
Rem imclnjav.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      imclnjav.sql - Oracle Multimedia java cleanup script
Rem
Rem    DESCRIPTION
Rem     Clean up previous Java objects  and synonyms belonging to ORDSYS
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    smavris     06/09/07 - interMedia to Multimedia
Rem    dolin       02/27/07 - bug 5439195
Rem    dolin       02/23/07 - cleanup dicom java, bug 5597325
Rem    rabbott     06/29/05 - bug 4460896 
Rem    alamb       05/05/04 - bug 3587451 (fix rollback errors)
Rem    alamb       04/02/04 - bug 3551052 (drop java synonyms -> ORDSYS)
Rem    alamb       02/24/04 - bug 3230212 (increase speed)
Rem    smavris     06/20/03 - 
Rem    trowell     05/14/03 - creation
Rem


--
-- drop OrdSourceServer class in ORDPLUGINS schema if present
--
declare
 cnt number;
begin
 select count(object_name) into cnt
   from all_objects,javasnm
   where owner = 'ORDPLUGINS' and
   object_type = 'JAVA CLASS' and
   short(+) = object_name and
   nvl(longname, object_name) = 'oracle/ord/media/source/OrdSourceSvr';
 if cnt > 0 then
   execute immediate 'drop java class ORDPLUGINS."oracle/ord/media/source/OrdSourceSvr"';
 end if;
end;
/

declare
-- drop all public synonyms to java objects that point to ORDSYS schema.
  cursor syn_curs is 
   select a.object_name
   from all_objects  a, 
        javasnm      j, 
        all_synonyms s
   where j.short(+) = a.object_name and 
         a.object_name = s.synonym_name and
         a.owner = 'ORDSYS' and
         s.owner = 'PUBLIC' and
         s.table_owner = 'ORDSYS' and
         (a.object_type = 'JAVA CLASS' or
          a.object_type = 'JAVA SOURCE') 
         and
         (nvl(j.longname, a.object_name) like 'sun/awt/image%' or
          nvl(j.longname, a.object_name) like 'com/sun/image%' or
          nvl(j.longname, a.object_name) like 'javax/media/jai%' or
          nvl(j.longname, a.object_name) like 'com/sun/media/jai%' or
          nvl(j.longname, a.object_name) like 'com/sun/medialib/mlib%' or
          nvl(j.longname, a.object_name) like 'oracle/ord/media%' or
          nvl(j.longname, a.object_name) like 'oracle/ord/dicom%'
          );

   -- Java sources are dropped first to avoid dropping derived java classes
  cursor src_curs is 
   select object_name 
   from all_objects,javasnm
   where owner = 'ORDSYS' and 
         object_type = 'JAVA SOURCE' and
         short(+) = object_name and
         (nvl(longname, object_name) like 'sun/awt/image%' or
          nvl(longname, object_name) like 'com/sun/image%' or
          nvl(longname, object_name) like 'javax/media/jai%' or
          nvl(longname, object_name) like 'com/sun/media/jai%' or
          nvl(longname, object_name) like 'com/sun/medialib/mlib%' or
          nvl(longname, object_name) like 'oracle/ord/media%' or
          nvl(longname, object_name) like 'oracle/ord/dicom%'
          );

  cursor cls_curs is 
   select object_name
   from all_objects,javasnm
   where owner = 'ORDSYS' and 
         object_type = 'JAVA CLASS' and
         short(+) = object_name and
         (nvl(longname, object_name) like 'sun/awt/image%' or
          nvl(longname, object_name) like 'com/sun/image%' or
          nvl(longname, object_name) like 'javax/media/jai%' or
          nvl(longname, object_name) like 'com/sun/media/jai%' or
          nvl(longname, object_name) like 'com/sun/medialib/mlib%' or
          nvl(longname, object_name) like 'oracle/ord/media%' or
          nvl(longname, object_name) like 'oracle/ord/dicom%'
          );

  cursor res_curs is 
   select object_name
   from all_objects,javasnm
   where owner = 'ORDSYS' and 
         object_type = 'JAVA RESOURCE' and
         short(+) = object_name and
         (nvl(longname, object_name) like 'sun/awt/image%' or
          nvl(longname, object_name) like 'com/sun/image%' or
          nvl(longname, object_name) like 'javax/media/jai%' or
          nvl(longname, object_name) like 'com/sun/media/jai%' or
          nvl(longname, object_name) like 'com/sun/medialib/%' or
          nvl(longname, object_name) like 'META-INF/javax.media%' or
          nvl(longname, object_name) like 'META-INF/services/javax.media%' or
          nvl(longname, object_name) like 'META-INF/MANIFEST.MF' or
          nvl(longname, object_name) like 'oracle/ord/media%' or
          nvl(longname, object_name) like 'oracle/ord/dicom%'
          );

  objname     varchar2(30);
  undo_ex     exception;
  pragma exception_init( undo_ex, -1555 );
begin
  ----------------------------------------------
  -- Drop public synonyms to ORDSYS java class 
  ----------------------------------------------
  open syn_curs;
  loop
    begin
      fetch syn_curs into objname;
      exit when syn_curs%NOTFOUND;
      execute immediate 'drop public synonym "' || objname || '"';
    exception 
      -- extended processing sometimes results in a rollback error (ORA-01555)
      when undo_ex then
        close syn_curs;
        open syn_curs;
      -- avoid looping forever when other exceptions occur
      when others then
        exit;
    end;
  end loop;

  ----------------------------------------------
  -- Drop java source files
  ----------------------------------------------
  open src_curs;
  loop 
    begin
      fetch src_curs into objname;
      exit when src_curs%NOTFOUND;
      execute immediate 'drop java source "' || objname || '"';
    exception
      -- extended processing sometimes results in a rollback error (ORA-01555)
      when undo_ex then
        close src_curs;
        open src_curs;
      -- avoid looping forever when other exceptions occur
      when others then
        exit;
    end;
  end loop;
  
  close src_curs;
  commit;


  ----------------------------------------------
  -- Drop java class files
  ----------------------------------------------
  open cls_curs;
  loop 
    begin
      fetch cls_curs into objname;
      exit when cls_curs%NOTFOUND;
      execute immediate 'drop java class "' || objname || '"';
    exception
      --extended processing sometimes results in a rollback error
      when undo_ex then
        close cls_curs;
        open cls_curs;

      -- avoid looping forever when other exceptions occur
      when others then
        exit;
    end;
  end loop;

  close cls_curs;
  commit;

  ----------------------------------------------
  -- Drop java resources
  ----------------------------------------------
  open res_curs;
  loop 
    begin
      fetch res_curs into objname;
      exit when res_curs%NOTFOUND;
      execute immediate 'drop java resource "' || objname || '"';
    exception
      --extended processing sometimes results in a rollback error
      when undo_ex then
        close res_curs;
        open res_curs;

      -- avoid looping forever when other exceptions occur
      when others then
        exit;
    end;
  end loop;

  close res_curs;
  commit;

end;
/