-- Subscript for upgrade/downgrade to call to remove system classes
-- and support packages on downgrade, or upgrade them on upgrade.

-- This script can not function properly in SVRMGR.  The following 
-- lines are intended to abort the script if it is being run with
-- SVRMGR, but will be interpreted simply as remarks by SQLPLUS.
rem -
set stoponerror on
rem -
udjvmrm.sql must be run with SQLPLUS, not SVRMGRL

-- This script may only be called from d0x0x0x0 or jvmdbmig.sql.  
-- Otherwise, jvmudaction will be undefined and the following will 
-- abort the script.
whenever sqlerror exit;

/*
/*
##  If the following sequence results in
##   SP2-0552: Bind variable "JVMUDACTION" not declared.
##  followed by
##   ORA-01476: divisor is equal to zero
##  thereby causing SQLPLUS to exit, it is because this script is 
##  not being run as a subscript of jvmdbmig.sql or d0x0x0x0.sql, as required.
*/
variable scratch varchar2(30)
execute :scratch := :jvmudaction; :scratch := 'not null';
declare z number := 0; begin if :scratch is null then z := 1/z;end if;end;
/

whenever sqlerror continue;


call initjvmaux.drp('drop table JACCELERATOR$DLLS');
call initjvmaux.drp('drop table JACCELERATOR$DLL_ERRORS');
call initjvmaux.drp('drop table JACCELERATOR$STATUS');

@@jisrm

variable jvmrmaction varchar2(30)
execute :jvmrmaction := 'GRADE_REMOVAL';

delete from java$rmjvm$aux;
commit;

@@jvmrm

call initjvmaux.drp('drop table JAVA$CLASS$MD5$TABLE');

column jvmuscript new_value jvmuscript noprint;
select initjvmaux.jvmuscript as jvmuscript from dual;

begin
  if initjvmaux.registrystatus = 'DOWNGRADING' and
     initjvmaux.startstep('SET_REGISTRY_DOWNGRADED') then
    dbms_registry.downgraded('JAVAVM',substr(:jvmudsubaction,instr(:jvmudsubaction,'_',-1)+1));
    initjvmaux.endstep;
  end if;
end;
/

begin
  if initjvmaux.registrystatus = 'UPGRADING' and
     initjvmaux.startstep('DROP_SROS') then
   update obj$ set status=5 where obj#=(select obj# from obj$,javasnm$ 
      where owner#=0 and type#=29 and short(+)=name and 
      nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
    commit;
    declare
      cursor C1 is select
         'DROP JAVA DATA "' || u.name || '"."' || o.name || '"'
         from obj$ o,user$ u where o.type#=56 and u.user#=o.owner#;
    
      ddl_statement varchar2(200);
      iterations number;
      previous_iterations number;
      loop_count number;
      my_err     number;
    begin
      previous_iterations := 10000000;
      loop
        -- To make sure we eventually stop, pick a max number of iterations
        select count(*) into iterations from obj$ where type#=56;
        exit when iterations=0 or iterations >= previous_iterations;
        previous_iterations := iterations;
        loop_count := 0;
        open C1;
        loop
          begin
            fetch C1 into ddl_statement;
            exit when C1%NOTFOUND or loop_count > iterations;
          exception when others then
             my_err := sqlcode;
             if my_err = -1555 then -- snapshot too old, re-execute fetch query
               exit;
             else
               raise;
             end if;
          end;
          initjvmaux.exec(ddl_statement);
          loop_count := loop_count + 1;
        end loop;
        close C1;
      end loop;
    end;
    commit;
    delete from java$policy$shared$table;
   update obj$ set status=1 where obj#=(select obj# from obj$,javasnm$ 
      where owner#=0 and type#=29 and short(+)=name and 
      nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
    commit;
    initjvmaux.endstep;
  end if;
end;
/

delete from java$rmjvm$aux;
commit;

begin
  if initjvmaux.registrystatus = 'UPGRADING' and
     initjvmaux.startstep('CLEAN_DEPENDENCIES') then
    initjvmaux.rollbacksetup;
    commit;
    initjvmaux.rollbackset;
    insert into java$rmjvm$aux (select joxftobn from x$joxfc where bitand(joxftflags,96)!=0);
    commit;
    initjvmaux.rollbackset;
    delete from dependency$ where d_obj# in (select obj# from java$rmjvm$aux);
    commit;
    if initjvmaux.jvmversion > '7' then
      initjvmaux.rollbackset;
      delete from dependency$ where d_obj# in
        (select obj# from obj$ where type#=29);
      commit;
      initjvmaux.rollbackset;
      update obj$ set status=5 where
        type#=29 and obj# not in (select obj# from java$rmjvm$aux);
      commit;
    end if;
    initjvmaux.rollbackcleanup;
    initjvmaux.endstep;
  end if;
end;
/

delete from java$rmjvm$aux;
commit;

begin
  if initjvmaux.registrystatus = 'UPGRADING' and
     initjvmaux.startstep('FIX_DEPENDENCY_TIMESTAMPS') then
    initjvmaux.rollbacksetup;
    commit;
    initjvmaux.rollbackset;
    execute immediate 'update dependency$ set p_timestamp=(select stime from obj$ where obj#=p_obj#) where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and (select type# from obj$ where obj#=p_obj#)=29  and (select owner# from obj$ where obj#=p_obj#)=0';
    commit;
    initjvmaux.rollbackcleanup;
    initjvmaux.endstep;
  end if;
end;
/

@@&&jvmuscript

begin
  if initjvmaux.registrystatus = 'UPGRADING' and
     initjvmaux.startstep('DROP_DEPLOY_DREGS') then
    declare
      last_obj number := 0;
      cmd varchar2(200);
      cursor c(prev_obj number) is
          select obj#,
                 'drop java source '||u.name||'."'||nvl(longdbcs,o.name)||'"'
            from obj$ o,user$ u,javasnm$ 
            where obj#>=prev_obj and u.user#=o.owner# and short(+)=o.name and 
                  obj# in (select /*+ no_unnest */unique(joxftobn) 
                           from x$joxfs,javasnm$ 
                           where short(+)=JOXFTDERIVEDCLASSNAME and 
                                 (nvl(longdbcs,JOXFTDERIVEDCLASSNAME) like
                                  'oracle/jaccelerator/server/%' or
                                  nvl(longdbcs,JOXFTDERIVEDCLASSNAME) like
                                  'oracle/aurora/deploy/%'))
            order by obj#;
      cursor c2(prev_obj number) is
          select obj#,
                 'drop java class '||u.name||'."'||nvl(longdbcs,o.name)||'"'
            from obj$ o,user$ u,javasnm$ 
            where obj#>=last_obj and u.user#=o.owner# and short(+)=o.name and
                  o.type#=29 and
                  (nvl(longdbcs,o.name) like 'oracle/jaccelerator/server/%' or
                   nvl(longdbcs,o.name) like 'oracle/aurora/deploy/%')
            order by obj#;
      my_err     number;
    begin
      loop
        open c(last_obj);
        my_err := 0;
        loop
          begin
            fetch c into last_obj, cmd;
            exit when c%NOTFOUND;
          exception when others then
            my_err := sqlcode;
            if my_err = -1555 then -- snapshot too old, re-execute fetch query
              exit;
            else
              raise;
            end if;
          end;
          initjvmaux.drp(cmd);
        end loop;
        close c;
        if my_err = 0 then exit; end if;
      end loop;
      last_obj := 0;
      loop
        open c2(last_obj);
        my_err := 0;
        loop
          begin
            fetch c2 into last_obj, cmd;
            exit when c2%NOTFOUND;
          exception when others then
            my_err := sqlcode;
            if my_err = -1555 then -- snapshot too old, re-execute fetch query
              exit;
            else
              raise;
            end if;
          end;
          initjvmaux.drp(cmd);
        end loop;
        close c2;
        if my_err = 0 then exit; end if;
      end loop;
    end;
    initjvmaux.endstep;
  end if;
end;
/

-- end of the line in the downgrade case
begin
  if :jvmudaction = 'DOWNGRADE' then
    initjvmaux.endaction();
  end if;
end;
/