-- 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 jvmdwgrd.ql 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 jvmdwgrd.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');

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(:jvmpatchset) as jvmuscript from dual;

select '&jvmuscript',initjvmaux.registrystatus,:jvmdownto from dual;

begin
  if initjvmaux.registrystatus = 'DOWNGRADING' and
     initjvmaux.startstep('SET_REGISTRY_DOWNGRADED') then
    dbms_registry.downgraded('JAVAVM',:jvmdownto);
    initjvmaux.endstep;
  end if;
end;
/

begin
  if initjvmaux.startstep('DROP_SROS') then
    initjvmaux.drop_sros;
    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 unique(obj#) from obj$,dependency$ 
        where type#=29 and d_obj#=obj# and bitand(flags,64)=64);
    commit;
    initjvmaux.rollbackset;
    declare
      obn number;
      ftflags number;
      cursor c is select obj# from java$rmjvm$aux;
    begin
      open c;
      loop
        fetch c into obn;
        exit when c%NOTFOUND;
        begin
        select bitand(joxftflags,96) into ftflags from x$joxfc where joxftobn=obn;
        exception when no_data_found then
           --- set xtflags to 1 so that the row is removed below
           --- this is a dependency row that is a dangling MDL object
           --- that should be removed.
          ftflags := 1;
        when others then
          raise;                
        end;
        if ftflags != 0 then
          delete from dependency$ where d_obj#=obn;
        end if;
      end loop;
      close c;
    end;
    commit;
    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;
      cname varchar2(200);
      sname varchar2(200);
      cowner varchar2(200);
      cursor c(prev_obj number) is
        select o.name,u.name,obj# from obj$ o,user$ u,javasnm$
            where obj#>prev_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 cname,cowner,last_obj;
            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;
          begin
            select name into cname from obj$ where obj#=last_obj and type#=29;
            select JOXFTDERIVEDFROM into sname from x$joxfc
              where joxftobn=last_obj;
            if sname is null then
              initjvmaux.drp('drop java class "'||cowner||'"."'||cname||'"');
            else
              initjvmaux.drp('drop java source "'||cowner||'"."'||sname||'"');
            end if;
          exception when no_data_found then null;
          end;
      end loop;
        close c;
        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;
/
