-- Remove some portion of the Java related data dictionary objects

-- This script must be run as a subscript of a script which sets the
-- variable jvmrmaction.
-- Possible values are
--  FULL_REMOVAL:        remove all java related objects
--  DOWNGRADE_8.1.7_TO_8.1.6: remove or massage system objects as appropriate
--                       when downgrading from 8.1.7 to 8.1.6
--  DOWNGRADE_8.1.6_TO_8.1.5: remove or massage system objects as appropriate
--                       when downgrading from 8.1.6 to 8.1.5 (after having
--                       earlier downgraded from 8.1.7 to 8.1.6)
--  DOWNGRADE_x.y.z_TO_a.b.c: analogous to the above for all supported
--                       downgrade from/to consecutive release pairs
--  GRADE_REMOVAL:       remove java related objects as appropriate during
--                       upgrade or downgrade
--  NONE:                do nothing

-- 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 -
jvmrm.sql must be run with SQLPLUS, not SVRMGRL

-- This script may only be called as a subscript.  
-- 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, 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;

print jvmrmaction

begin
  if :jvmrmaction = 'FULL_REMOVAL' then
    dbms_registry.removing('JAVAVM');
    initjvmaux.startaction('UNLOAD');
  end if;
  if :jvmudaction = 'DOWNGRADE' and
     initjvmaux.registrystatus = 'LOADED' and 
     initjvmaux.startstep('SET_REGISTRY_GRADING') then
    dbms_registry.downgrading('JAVAVM');
    initjvmaux.endstep;
  end if;
end;
/

begin if :jvmrmaction = 'FULL_REMOVAL' then

if initjvmaux.startstep('RMJVM_REMOVE_ROLES') then
--
initjvmaux.drp('drop role javauserpriv');
initjvmaux.drp('drop role javaidpriv');
initjvmaux.drp('drop role javasyspriv');
initjvmaux.drp('drop role javadebugpriv');
initjvmaux.drp('drop role ejbclient');

--
initjvmaux.drp('drop view javasnm');
initjvmaux.drp('drop public synonym javasnm');

--
initjvmaux.drp('drop table aurora$startup$classes$');
initjvmaux.drp('drop table aurora$shutdown$classes$');

initjvmaux.endstep; end if;
end if;end;
/

begin if :jvmrmaction = 'DOWNGRADE_8.1.6_TO_8.1.5' then

if initjvmaux.startstep('RMJVM_STRIP') then

--- Strip 8.1.6 bytecode optimizations
rmjvm.strip();

initjvmaux.endstep; end if;

end if;end;
/

begin if :jvmrmaction = 'FULL_REMOVAL' or :jvmrmaction = 'DOWNGRADE_8.1.7_TO_8.1.6' then

if initjvmaux.startstep('RMJVM_REMOVE_DYNREG') then

--- remove DynamicRegistration
initjvmaux.drp('delete from aurora$startup$classes$ where classname = ''oracle.aurora.net.DynamicRegistration''');
initjvmaux.drp('delete from aurora$shutdown$classes$ where classname = ''oracle.aurora.net.DynamicRegistration''');
initjvmaux.drp('drop table AURORA$DYN$REG');
if :jvmrmaction = 'DOWNGRADE_8.1.7_TO_8.1.6' then
  initjvmaux.drp('begin dbms_java.aurora_shutdown();end;');
end if;
initjvmaux.drp('drop table AURORA$CURRENT$DYN$REG');

initjvmaux.endstep; end if;

end if;end;
/

begin if :jvmrmaction = 'FULL_REMOVAL' or :jvmrmaction = 'GRADE_REMOVAL' then

if initjvmaux.startstep('RMJVM_REMOVE_GET_ERROR') then

--
initjvmaux.drp('drop package get_error$');
initjvmaux.drp('drop public synonym get_error$');

--
initjvmaux.drp('drop trigger AURORA$SERVER$SHUTDOWN');
initjvmaux.drp('drop trigger AURORA$SERVER$STARTUP');

-- initsql
initjvmaux.drp('drop package sqljutl');


-- Remove any occurrences of LOADLOBS package
DECLARE 
max_iterations number;
loop_count number;
iterations number;
drop_statement varchar2(50);
my_err number;
cursor C1 is select unique 'DROP PACKAGE ' || u.name || '.LOADLOBS' 
             into drop_statement from obj$ o, user$ u where
             (o.name = 'LOADLOBS' and 
             (o.type# = 9 or o.type# = 11)) and 
             u.user#=o.owner#;
BEGIN
  max_iterations := 10000000;
  select count(*) into iterations from obj$ o, user$ u where
        (o.name = 'LOADLOBS' and (o.type# = 9 or o.type# = 11)) and
        u.user#=o.owner#;
  LOOP
    exit when iterations=0 or iterations >= max_iterations;
    max_iterations := iterations;
    loop_count := 0;
    open C1;
    LOOP -- outer loop for snap shot checking.
      BEGIN
        fetch c1 into drop_statement;
        exit when C1%notfound or loop_count > iterations;
      EXCEPTION
        when others then
          my_err := SQLCODE;
          if my_err = -1555 then 
            exit;
          else
            raise;
          end if;
       END;
     initjvmaux.drp(drop_statement);
     loop_count := loop_count + 1;    
   END LOOP;--inner
close C1;
commit;
end LOOP; --outer
end;--LOADLOBS

initjvmaux.endstep; end if;

end if;end;
/

@@jvmrmcat
@@jvmrmjis
@@jvmrmdbj
@@jvmrmsec
@@jvmrmxa
@@jvmrmprefs


begin if :jvmrmaction = 'FULL_REMOVAL' or :jvmrmaction = 'DOWNGRADE_10.1.0_TO_9.2.0' then
initjvmaux.drp('drop package sqljutl2');
initjvmaux.drp('drop package utl_dbws');
end if; end;
/

begin if :jvmrmaction = 'FULL_REMOVAL' or :jvmrmaction = 'DOWNGRADE_10.2.0_TO_10.1.0' then
initjvmaux.drp('drop package jvmfcb');
initjvmaux.drp('drop package dbms_jvm_exp_perms');
initjvmaux.drp('drop public synonym dbms_jvm_exp_perms');
end if; end;
/

begin if :jvmrmaction = 'FULL_REMOVAL' or :jvmrmaction = 'DOWNGRADE_11.1.0_TO_10.2.0' then
initjvmaux.drp('drop table javajar$');
initjvmaux.drp('drop table javajarobjects$');
initjvmaux.drp('drop table java$jvm$runtime$parameters');
initjvmaux.drp('drop table java$mc$');
initjvmaux.drp('drop table java$compiler$options');
initjvmaux.drp('drop table java$jvm$rjbc');
initjvmaux.drp('drop package jvmrjbc');
end if; end;
/

begin if :jvmrmaction = 'FULL_REMOVAL' or :jvmrmaction = 'DOWNGRADE_11.2.0_TO_11.0.0' then
initjvmaux.drp('drop table java$method$metadata');
initjvmaux.drp('drop table java$mc$deps');
initjvmaux.drp('drop package dbms_java_definers');
initjvmaux.drp('drop table javanegdeps$');
end if; end;
/

/*
  The following statements containing calls to rmjvm may generate errors
  if java has never been installed.  Such errors are harmless and may be
  ignored.  If java has been installed, these statements may take tens
  of minutes to run.
*/

begin if :jvmrmaction = 'FULL_REMOVAL' then
  rmjvm.run(true);
  initjvmaux.endaction;
  dbms_registry.removed('JAVAVM');
end if;end;
/

begin if :jvmrmaction = 'GRADE_REMOVAL' and :jvmudaction = 'DOWNGRADE' then
  if initjvmaux.startstep('RMJVM_RUN') then
    rmjvm.run(false);
  initjvmaux.endstep; end if;
end if;end;
/

declare trash varchar2(30);
begin if :jvmrmaction = 'GRADE_REMOVAL' and
         :jvmudaction = 'DOWNGRADE' then
  if initjvmaux.startstep('DOWNGRADE_VERSION') then
  if :jvmudsubaction = 'DOWNGRADE_11.2.0_TO_11.1.0' or
     :jvmudsubaction = 'DOWNGRADE_11.2.0_TO_10.2.0' or
     :jvmudsubaction = 'DOWNGRADE_11.2.0_TO_10.1.0'
  then
    null;
  elsif :jvmudsubaction = 'DOWNGRADE_11.2.0_TO_9.2.0' or
        :jvmudsubaction = 'DOWNGRADE_11.2.0_TO_9.0.1' then
    trash := dbms_java_test.funcall('-downgrade_version',' ',28, 0);
    trash := dbms_java_test.funcall('-downgrade_version',' ',29, 6);
    trash := dbms_java_test.funcall('-downgrade_version',' ',30, 1);
  else
    trash := dbms_java_test.funcall('-downgrade_version',' ',29, 4);
  end if;
  initjvmaux.endstep; end if;
end if;end;
/