-- Complete Java downgrade of 11.2.0 database to 8.1.6
-- This script is the part of the downgrade that must be done once 8.1.6 is
-- running.  It loads system classes and support packages without
-- affecting user definitions.

whenever sqlerror exit;

variable test_for_1335603 varchar2(30)

/*
#######################################################################
#######################################################################
  If the following   :test_for_1335603 := 'x'   statement causes
  a PLS-00553 error, and thereby terminates the sqlplus connection,
  simply restart the script in a new sqlplus session.
  This error can occur if the script is run in the same session where
  the database was started.  To avoid this problem, always start a new
  session to run scripts after starting the database.
#######################################################################
#######################################################################
*/

execute :test_for_1335603 := 'x';

whenever sqlerror continue;

-- reload initjvmaux to remove dependencies on dbms_registry
@@initjvmaux

-- get rid of 9.2.0 java status tables.  Old values in these tables will
-- be confusing if the database is ever upgraded back to 9.2.0
drop table java$jvm$status;
drop table java$jvm$steps$done;

-- Load all the Java classes
create or replace java system;
/

-- Java Sanity check for installation
-- If the following query returns 0, then the Java installation
-- did not suceed
select count(*) from all_objects where object_type like 'JAVA%';

@@initdbj

-- Java Sanity Check

-- Check for any Java classes that are invalid.
-- The following query should return 0 rows.  This query uses a
-- Java stored procedure, so if the query bombs, the reason
-- is that something's wrong running the Java VM.  If it runs,
-- and the query result is 0 rows, there's a very good chance
-- that Java is running at this point

select dbms_java.longname(name) from obj$ where
  type#=29 and status != 1 and owner#=0 and obj# in
  (select joxftobn from x$joxfc where bitand(joxftflags,96)!=0);

-- Find all the error messages.  The following query returns the errors
-- that caused the invalid classes (if any) from the above query.  If you
-- get to this point the the Java VM is running, and there are enough classes
-- to run the longname() jsp.

select dbms_java.longname(o.name), text from error$ e,obj$ o where e.obj#=o.obj# and o.owner#=0 and o.type#=29;

-- There should be zero results from the above query in a correct installation
-- of the Java VM

-- Create the get error package for LoadJava
create or replace package get_error$ as 
  type myrec is record (errormsg varchar(4000));
  type myrctype is ref cursor return myrec;
  function error_lines (classname varchar2) return myrctype; 
end get_error$;
/
create or replace package body get_error$ as 
  function error_lines (classname varchar2) return myrctype is 
    rc myrctype; short_name varchar2(50); n number;
  begin 
    open rc for select text from user_errors
       where name = dbms_java.shortname(classname);
    return rc; 
  end; 
end get_error$;
/
drop public synonym get_error$;
/
create public synonym get_error$ for get_error$;
/
grant execute on get_error$ to public;
/

-- Triggers to run startup and shutdown Java code

create or replace trigger aurora$server$startup after startup on database call dbms_java.server_startup
/
create or replace trigger aurora$server$shutdown before shutdown on database call dbms_java.server_shutdown
/

-- SQLJ initialization
@@initsqlj

-- XA JSP initialization
@@initxa