-- initjvmaux: support package for conditional execution during jvm scripts
create or replace package initjvmaux is 
 -- exec: execute a statement
 procedure exec (x varchar2);
 -- drp: execute a statement
 -- with some errors typically seen in drop commands ignored
 procedure drp (x varchar2);
 -- rollbacksetup: find or allocate a big, online rollback segment
 -- return the set transaction command to use the segment in the current txn
 function rollbacksetup return varchar2;
 -- rollbackcleanup: deallocate any rollback segment allocated by rollbacksetup
 procedure rollbackcleanup;
end;
/

create or replace package body initjvmaux is

deallocate_rollback_segment boolean;

procedure exec (x varchar2) as
begin
dbms_output.put_line(substr(x, 1, 250));
execute immediate x;
end;

procedure drp (x varchar2) as
begin
exec(x);
exception
when others then
if sqlcode not in (-4080, -1418, -1919, -942, -1432, -4043, -1918, -2289,
                   -1598, -1534) then raise; end if;
end;

function rollbacksetup return varchar2 as
  rollback_segment_name varchar2(30);
  cursor C1 is select segment_name from dba_rollback_segs
    where tablespace_name='SYSTEM' and next_extent*max_extents>100000000 and
          status='ONLINE';
BEGIN

  deallocate_rollback_segment := FALSE;

  OPEN C1;
 
  FETCH C1 INTO rollback_segment_name;
  if C1%NOTFOUND then
    deallocate_rollback_segment := TRUE;
    rollback_segment_name := 'MONSTER';
    drp('alter rollback segment monster offline');
    drp('drop rollback segment monster');
    exec('create rollback segment monster ' ||
              'storage (initial 100 k next 100 k maxextents unlimited)');
    exec('alter rollback segment monster online');
  END IF;
  CLOSE C1;
  return
   'set transaction use rollback segment ' || rollback_segment_name;
END;

procedure rollbackcleanup as
begin
if deallocate_rollback_segment then
    drp('alter rollback segment monster offline');
    drp('drop rollback segment monster');
end if;
end;

end;
/

-- Package rmjvm: encapsulates undo logic for removing Java system objects
-- during upgrade/downgrade and for full removal of Java to back out of
-- the results of a failed initjvm
drop table java$rmjvm$aux;
drop table java$rmjvm$aux2;
drop index java$rmjvm$auxi;
drop index java$rmjvm$auxi2;
create table java$rmjvm$aux(obj# number);
create table java$rmjvm$aux2(name varchar2(30));
create unique index java$rmjvm$auxi on java$rmjvm$aux(obj#);
create unique index java$rmjvm$auxi2 on java$rmjvm$aux2(name);



create or replace package rmjvm is
 procedure run(remove_all boolean);
 procedure strip;
end;
/

create or replace package body rmjvm is

procedure exec (x varchar2) as
begin
 initjvmaux.exec(x);
end;

procedure drp (x varchar2) as
begin
 initjvmaux.drp(x);
end;

procedure run(remove_all boolean) as
rollback_segment_set_command varchar2(100) := initjvmaux.rollbacksetup;
begin
--    DESCRIPTION
--      This removes java related objects from the data dictionary.
--      If remove_all is true, it removes all java objects and java
--      related tables and packages, including user objects.
--      If remove all is false, it removes only the java objects, such
--      as system classes, that are considered to be a fixed part of a
--      given Oracle release.  It does not remove user objects.
--
--    NOTES
--      This procedure is destructive.  After it runs, System classes 
--      must be reloaded either by initjvm or in a subsequent 
--      upgraded/downgrade phase before Java is again usable.
--
--      This procedure requires a significant amount of rollback
--      to execute.
--

dbms_output.enable(10000000); -- biggest size we can get

commit;
exec(rollback_segment_set_command);

declare
c number;
begin
select count(*) into c from java$rmjvm$aux;
if c = 0 then
  commit;
  exec(rollback_segment_set_command);
  if remove_all then
  exec('insert into java$rmjvm$aux (select obj# from obj$ where ' ||
    'type#=28 or type#=29 or type#=30 or type#=56)');
  else
  exec('insert into java$rmjvm$aux (select joxftobn from x$joxfc ' ||
    'where (floor(joxftflags/64)*2)!=floor(joxftflags/32))');
  exec('insert into java$rmjvm$aux (select joxftobn from x$joxfr ' ||
    'where (floor(joxftflags/64)*2)!=floor(joxftflags/32))');
  exec('insert into java$rmjvm$aux (select obj# from obj$ where ' ||
    'type#=56 or namespace=32)');
  end if;
  commit;
end if;
end;

commit;
exec(rollback_segment_set_command);

dbms_output.put_line('drop synonyms with java targets');

DECLARE
  cursor C1 is select
     'DROP PUBLIC SYNONYM "' || name || '"' from java$rmjvm$aux2;

  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  iterations number;
  previous_iterations number;
  loop_count number;
  my_err     number;
BEGIN
 previous_iterations := 10000000;

 DDL_CURSOR := dbms_sql.open_cursor;

 loop
 
  exec('delete from java$rmjvm$aux2');
  if remove_all then
  exec('insert into  java$rmjvm$aux2 (select o1.name from ' ||
     'obj$ o1,obj$ o2 where o1.type#=5 and o1.name=o2.name and o2.type#=29)');
  else
  exec('insert into  java$rmjvm$aux2 (select o1.name ' ||
            'from obj$ o1,obj$ o2, java$rmjvm$aux j ' ||
            'where o1.type#=5 and o1.name=o2.name and o2.obj#=j.obj#)');
  end if;

 -- To make sure we eventually stop, pick a max number of iterations
  select count(*) into iterations from java$rmjvm$aux2;
 
  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
--        CLOSE C1;
        exit;
       ELSE
        RAISE;
       END IF;
    END;

    BEGIN
        -- Issue the Alter Statement  (Parse implicitly executes DDLs)
        dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
    EXCEPTION
        WHEN OTHERS THEN
        null; -- ignore, and proceed.
    END;
 
  <<continue>>
    loop_count := loop_count + 1;

  END LOOP;
  CLOSE C1;

 end loop;
 dbms_sql.close_cursor(DDL_CURSOR);

END;
commit;

dbms_output.put_line('flush shared_pool');
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';

declare
total_to_delete number;
deletions_per_iteration number := 1000;
begin

exec(rollback_segment_set_command);

dbms_output.put_line('delete from dependency$');

if remove_all then
select count(*) into total_to_delete from dependency$
  where p_obj# in (select obj# from java$rmjvm$aux);
else
select count(*) into total_to_delete from dependency$
  where p_obj# in (select obj# from obj$ where (type#=29 or type#=56));
end if;
commit;
loop
exec(rollback_segment_set_command);
if remove_all then
delete from dependency$ where p_obj# in
  (select obj# from java$rmjvm$aux)
  and rownum <= deletions_per_iteration;
else
delete from dependency$ where p_obj# in
  (select obj# from obj$ where (type#=29 or type#=56))
  and rownum <= deletions_per_iteration;
end if;
commit;
exit when total_to_delete <= deletions_per_iteration;
total_to_delete := total_to_delete - deletions_per_iteration;
end loop;

exec(rollback_segment_set_command);

dbms_output.put_line('delete from error$');

if remove_all then
select count(*) into total_to_delete from error$
  where obj# in (select obj# from java$rmjvm$aux);
else
select count(*) into total_to_delete from error$
  where obj# in (select obj# from obj$
                 where type#=28 or type#=29 or type#=30 or type#=56);
end if;
commit;
loop
exec(rollback_segment_set_command);
if remove_all then
delete from error$ where obj# in
  (select obj# from java$rmjvm$aux)
  and rownum <= deletions_per_iteration;
else
delete from error$ where obj# in
  (select obj# from obj$ where type#=28 or type#=29 or type#=30 or type#=56)
  and rownum <= deletions_per_iteration;
end if;
commit;
exit when total_to_delete <= deletions_per_iteration;
total_to_delete := total_to_delete - deletions_per_iteration;
end loop;

exec(rollback_segment_set_command);

dbms_output.put_line('delete from objauth$');

select count(*) into total_to_delete from objauth$
   where obj# in (select obj# from java$rmjvm$aux);
commit;
loop
exec(rollback_segment_set_command);
delete from objauth$ where obj# in (select obj# from java$rmjvm$aux)
  and rownum <= deletions_per_iteration;
commit;
exit when total_to_delete <= deletions_per_iteration;
total_to_delete := total_to_delete - deletions_per_iteration;
end loop;

if remove_all then
exec(rollback_segment_set_command);

dbms_output.put_line('delete from javasnm$');
delete from javasnm$;
commit;
end if;

exec(rollback_segment_set_command);

dbms_output.put_line('delete from idl_ub1$');

select count(*) into total_to_delete
 from idl_ub1$ where obj# in (select obj# from java$rmjvm$aux);
commit;
loop
exec(rollback_segment_set_command);
delete from idl_ub1$ where obj# in (select obj# from java$rmjvm$aux)
   and rownum <= deletions_per_iteration;
commit;
exit when total_to_delete <= deletions_per_iteration;
total_to_delete := total_to_delete - deletions_per_iteration;
end loop;

dbms_output.put_line('delete from idl_ub2$');

execute immediate
'select count(*) from idl_ub2$ ' ||
  'where obj# in (select obj# from java$rmjvm$aux)' into total_to_delete;
commit;
loop
exec(rollback_segment_set_command);
execute immediate
'delete from idl_ub2$ where obj# in (select obj# from java$rmjvm$aux) ' ||
   'and rownum <= :deletions_per_iteration' using deletions_per_iteration;
commit;
exit when total_to_delete <= deletions_per_iteration;
total_to_delete := total_to_delete - deletions_per_iteration;
end loop;

dbms_output.put_line('delete from idl_char$');

select count(*) into total_to_delete
 from idl_char$ where obj# in (select obj# from java$rmjvm$aux);
commit;
loop
exec(rollback_segment_set_command);
delete from idl_char$ where obj# in (select obj# from java$rmjvm$aux)
   and rownum <= deletions_per_iteration;
commit;
exit when total_to_delete <= deletions_per_iteration;
total_to_delete := total_to_delete - deletions_per_iteration;
end loop;

dbms_output.put_line('delete from idl_sb4$');

execute immediate
'select count(*) from idl_sb4$ ' ||
 'where obj# in (select obj# from java$rmjvm$aux)' into total_to_delete;
commit;
loop
exec(rollback_segment_set_command);
execute immediate
'delete from idl_sb4$ where obj# in (select obj# from java$rmjvm$aux) ' ||
   'and rownum <= :deletions_per_iteration' using deletions_per_iteration;
commit;
exit when total_to_delete <= deletions_per_iteration;
total_to_delete := total_to_delete - deletions_per_iteration;
end loop;

dbms_output.put_line('delete from obj$');
-- 
-- only delete from obj$ if all the java information was deleted
-- from the other tables correctly.  Once we run this delete
-- there is no going back to remove the information from 
-- syn$, objauth$ and dependency$ using this script.
--
DECLARE
 c1 number;
 c2 number;
 c3 number;
 c4 number; 
BEGIN
  if remove_all then
  select count(*) into c1 from syn$ where obj# in
        (select o1.obj# from obj$ o1,obj$ o2 
                where o1.name=o2.name and 
                o1.type#=5 and o2.type#=29);
  select count(*) into c2 from dependency$ where p_obj# in 
        (select obj# from java$rmjvm$aux);
  select count(*) into c3 from objauth$ where obj# in
        (select obj# from java$rmjvm$aux);
  select count(*) into c4 from javasnm$;
  else
  select count(*) into c1 from syn$ where obj# in
        (select o1.obj# from obj$ o1,obj$ o2,java$rmjvm$aux j
           where o1.name=o2.name and o1.type#=5 and o2.obj#=j.obj#);
  select count(*) into c2 from dependency$ where p_obj# in
        (select obj# from obj$ where
         type#=28 or type#=29 or type#=30 or type#=56);
  select count(*)into c3 from objauth$ where obj# in
        (select obj# from java$rmjvm$aux);
  c4 := 0;
  end if;

  IF c1 = 0 AND c2 = 0 AND c3 = 0 AND c4 = 0 THEN
        select count(*) into total_to_delete
         from obj$ where obj# in (select obj# from java$rmjvm$aux);
        commit;
        loop
        exec(rollback_segment_set_command);
        delete from obj$ where obj# in (select obj# from java$rmjvm$aux)
           and rownum <= deletions_per_iteration;
        commit;
        exit when total_to_delete <= deletions_per_iteration;
        total_to_delete := total_to_delete - deletions_per_iteration;
        end loop;

        if not remove_all then
        update obj$ set status=5 where type#=28 or type#=29;
        end if;

        delete from java$rmjvm$aux;

        insert into java$rmjvm$aux
           (select obj# from obj$ where type#=10 and owner#=1);
        delete from java$rmjvm$aux
            where obj# in (select p_obj# from dependency$);
        delete from obj$ where obj# in  (select obj# from java$rmjvm$aux);
        commit;

        dbms_output.put_line('All java objects removed');
  ELSE
        dbms_output.put_line('Java objects not completely removed. ' ||
                             'Rerun rmjvm.run');
  END IF;
END;

end;

commit;

initjvmaux.rollbackcleanup;

dbms_output.put_line('flush shared_pool');
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush shared_pool';
end;

procedure strip as
rollback_segment_set_command varchar2(100) := initjvmaux.rollbacksetup;
begin
--    DESCRIPTION
--      This strips bytecode optimizations from non-system java classes,
--      and sets the status of these classes to invalid (unresolved).
--      It is intended for use only prior to downgrade to 8.1.5, and is
--      present only because 8.1.5 resolution code incorrectly fails to
--      do such stripping, allowing 8.1.6 optimization codes that cannot
--      be correctly interpreted by 8.1.5 to remain in place.
--

dbms_output.enable(10000000); -- biggest size we can get

commit;
exec(rollback_segment_set_command);

delete from java$rmjvm$aux;

exec('insert into java$rmjvm$aux (select joxftobn from x$joxfc ' ||
    'where (floor(joxftflags/128)*2)=floor(joxftflags/64))');

commit;
exec(rollback_segment_set_command);

exec('create or replace java source named java$rmjvm$src as import java.lang.Object;');

commit;
exec(rollback_segment_set_command);

dbms_output.put_line('strip 8.1.6 bytecode optimizations');

DECLARE
  done boolean;
  already_done number := 0;
  cursor C1(above number) is select
     'ALTER JAVA CLASS "' || u.name || '"."' || o.name || '" RESOLVE',
     o.obj# from 
     obj$ o, user$ u, java$rmjvm$aux j where 
     o.obj#=j.obj# and u.user# = o.owner# and j.obj# > above
     order by j.obj#;

  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  my_err     number;
BEGIN

 DDL_CURSOR := dbms_sql.open_cursor;

 loop
  done := true; 
  OPEN C1(already_done);
 
  LOOP
 
    BEGIN
      FETCH C1 INTO ddl_statement, already_done;
      EXIT WHEN C1%NOTFOUND;
    EXCEPTION
     WHEN OTHERS THEN
       my_err := SQLCODE;
       IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
--        CLOSE C1;
        done := false;
        exit;
       ELSE
        RAISE;
       END IF;
    END;
 
    BEGIN
        -- Issue the Alter Statement  (Parse implicitly executes DDLs)
        dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
    EXCEPTION
        WHEN OTHERS THEN
        null; -- ignore, and proceed.
    END;
 
  END LOOP;
  CLOSE C1;
  exit when done;

 end loop;
 dbms_sql.close_cursor(DDL_CURSOR);

END;
commit;

exec(rollback_segment_set_command);

exec('drop java source java$rmjvm$src');
delete from java$rmjvm$aux;

commit;

initjvmaux.rollbackcleanup;

end;

end;
/
