Rem
Rem $Header: javavm/install/bug26451793.sql /main/2 2017/07/28 10:11:03 nneeluru Exp $
Rem
Rem bug26451793.sql
Rem
Rem Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      bug26451793.sql
Rem
Rem    DESCRIPTION
Rem      Post-install script to fix the bug 26451793
Rem
Rem    NOTES
Rem      To be run as SYS
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: javavm/install/bug26451793.sql
Rem    SQL_SHIPPED_FILE:
Rem    SQL_PHASE: PATCH
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nneeluru    07/19/17 - Post-install script for bug 26451793
Rem    nneeluru    07/19/17 - Created
Rem

-- Create temporary tables to store the details of affected sources and derivees
-- (classes and resources) that need to be dropped and re-created.
create table javavm_affected_sources (objno number, ownerno number, owner varchar2(128), name varchar2(128), text clob, flags number);

create table javavm_affected_derivees (objno number, ownerno number, objtype number, owner varchar2(128), name varchar2(128), flags number);

begin
  if (sys_context('userenv', 'con_id') >= 1) then   -- for CDB
    execute immediate 'alter session set "_ORACLE_SCRIPT"=true';
    execute immediate 'create view javavm_affected_sources_view sharing=object as select * from javavm_affected_sources';
    execute immediate 'alter session set "_ORACLE_SCRIPT"=false';
  end if;
end;
/

create table javavm_errors_during_creation (e number, message varchar2(400));

-- Identify the affected sources and derivees, and insert their details in the
-- temporary tables.  For sources, text will be populated later.
insert into javavm_affected_derivees select unique joxftobn, o.owner#, joxfttype, u.name, o.name, o.flags from x$joxft, obj$ o, user$ u where (joxfttype=29 or joxfttype=30) and joxftderivedfrom like '/%' and (select 1 from javasnm$ where short=joxftderivedfrom) is null and joxftobn=o.obj# and o.owner#=u.user#;

insert into javavm_affected_sources(objno, ownerno, owner, name, flags) select unique joxftobn, o.owner#, u.name, o.name, o.flags from x$joxft, obj$ o, user$ u where joxfttype=28 and ((joxftderivedclassname like '/%' and (select 1 from javasnm$ where short=joxftderivedclassname) is null) or (joxftderivedresourcename like '/%' and (select 1 from javasnm$ where short=joxftderivedresourcename) is null)) and joxftobn=o.obj# and o.owner#=u.user#;

insert into javavm_affected_sources(objno, ownerno, owner, name, flags) select unique joxftobn, o.owner#, u.name, o.name, o.flags from x$joxft, javavm_affected_derivees j, obj$ o, user$ u where joxfttype=28 and (joxftderivedclassname=j.name or joxftderivedresourcename=j.name) and joxftobn=o.obj# and o.owner#=j.ownerno and o.owner#=u.user#;

-- Identify if any MDL sources in a PDB are affected.
-- (We need to compare owner by name here, rather than by owner#, since
--  we are comparing across the containers.)
begin
  if sys_context('userenv', 'con_id') >= 2 then
    execute immediate 'insert into javavm_affected_sources(objno, ownerno, owner, name, flags) select o.obj#, o.owner#, u.name, o.name, o.flags from obj$ o, javavm_affected_sources_view j, user$ u where o.type#=28 and bitand(o.flags,65536)=65536 and o.name=j.name and o.owner#=u.user# and u.name=j.owner';
  end if;
end;
/

insert into javavm_affected_derivees select unique joxftobn, o.owner#, joxfttype, u.name, o.name, o.flags from x$joxft, javavm_affected_sources j, obj$ o, user$ u where (joxfttype=29 or joxfttype=30) and joxftderivedfrom=j.name and joxftobn=o.obj# and o.owner#=j.ownerno and o.owner#=u.user#;

commit;

-- Remove duplicates from the temporary tables
delete from javavm_affected_sources j1 where rowid > (select min(rowid) from javavm_affected_sources j2 where j1.objno=j2.objno);

delete from javavm_affected_derivees j1 where rowid > (select min(rowid) from javavm_affected_derivees j2 where j1.objno=j2.objno);

-- The object names in the above tables will be 128-based shortened names,
-- as they appear in obj$.

set serveroutput on

-- Now, populate the text for the sources by extracting it from dba_source
declare
  cursor c1 is select owner, name from javavm_affected_sources
                      where text is null;
  cursor c2(o varchar2, n varchar2, id number)
         is select text from dba_source where
            owner=o and name=n and type='JAVA SOURCE' and origin_con_id=id
            order by line;
  overall_text  CLOB;
  text          varchar2(4000);
  j_owner       varchar2(128);
  j_name        varchar2(128);
  first_iter    boolean;
  done          boolean := FALSE;
begin
  loop
    begin
      open c1;
      loop
        fetch c1 into j_owner, j_name; -- j_name as obtained from obj$
        exit when c1%NOTFOUND;
        dbms_output.put_line('Extracting text for JAVA SOURCE : ' ||
                              j_owner || '.' || j_name);
        open c2(j_owner, j_name, sys_context('userenv', 'con_id'));
        first_iter := TRUE;
        loop
          fetch c2 into text;
          exit when c2%NOTFOUND;
          if first_iter = TRUE then
            overall_text := text;
            first_iter := FALSE;
          else
            overall_text := overall_text || chr(10) || text;
          end if;
        end loop;
        close c2;
        update javavm_affected_sources set text=overall_text
               where owner=j_owner and name=j_name;
        commit;
      end loop;
      close c1;
      done := TRUE;
    exception when others then
      if sqlcode in (-1555) then
        if c1%ISOPEN then close c1; end if;
        if c2%ISOPEN then close c2; end if;
      else
        raise;
      end if;
    end;
    exit when done;
  end loop;
end;
/

-- List the details of the affected sources and derivees identified
select objno, owner, name, to_char(text), flags from javavm_affected_sources;

select objno, objtype, owner, name, flags from javavm_affected_derivees;

-- Set the event to allow/ignore certain special case drops
alter session set events '29578 trace name context forever, level 2';

-- Drop the affected sources
declare
  cursor c is select owner, name, flags from javavm_affected_sources;
  j_owner   varchar2(128);
  j_name    varchar2(4000);  -- need to be able to hold a Java long name
  j_flags   number;
  drop_stmt varchar2(4200);
  mdl       boolean;
  for_pdb   boolean := (sys_context('userenv', 'con_id') >= 2);
begin
  open c;
  loop
    fetch c into j_owner, j_name, j_flags;
    exit when c%NOTFOUND;
    j_name := dbms_java.longname(j_name);
    mdl := (bitand(j_flags, 65536) = 65536);
    dbms_output.put_line('Dropping JAVA SOURCE : ' || j_owner || '.' || j_name);
    drop_stmt := 'DROP JAVA SOURCE ' ||
                 sys.dbms_assert.enquote_name(j_owner, FALSE) || '.' ||
                 sys.dbms_assert.enquote_name(j_name, FALSE);
    if mdl = TRUE and for_pdb = TRUE then
      execute immediate 'alter session set "_ORACLE_SCRIPT"=true';
    end if;
    execute immediate drop_stmt;
    if mdl = TRUE and for_pdb = TRUE then
      execute immediate 'alter session set "_ORACLE_SCRIPT"=false';
    end if;
  end loop;
  close c;
end;
/

-- Drop the affected derivees; ignore the ORA-04043 that may occur
-- if any of them can't be found, as they may have been dropped already
-- as part of dropping their source above.
declare
  cursor c is select objtype, owner, name, flags from javavm_affected_derivees;
  j_owner     varchar2(128);
  j_name      varchar2(4000);  -- need to be able to hold a Java long name
  j_type      number;
  j_flags     number;
  drop_stmt   varchar2(4200);
  type_clause varchar2(20);
  mdl         boolean;
  for_pdb     boolean := (sys_context('userenv', 'con_id') >= 2);
begin
  open c;
  loop
    fetch c into j_type, j_owner, j_name, j_flags;
    exit when c%NOTFOUND;
    j_name := dbms_java.longname(j_name);
    if j_type = 29 then
      type_clause := 'CLASS ';
    elsif j_type = 30 then
      type_clause := 'RESOURCE ';
    end if;
    mdl := (bitand(j_flags, 65536) = 65536);
    dbms_output.put_line('Dropping JAVA ' || type_clause || ': ' ||
                         j_owner || '.' || j_name);
    drop_stmt := 'DROP JAVA ' || type_clause ||
                 sys.dbms_assert.enquote_name(j_owner, FALSE) || '.' ||
                 sys.dbms_assert.enquote_name(j_name, FALSE);
    if mdl = TRUE and for_pdb = TRUE then
      execute immediate 'alter session set "_ORACLE_SCRIPT"=true';
    end if;
    begin
      execute immediate drop_stmt;
    exception when others then
      if sqlcode not in (-4043) then
        if mdl = TRUE and for_pdb = TRUE then
          execute immediate 'alter session set "_ORACLE_SCRIPT"=false';
        end if;
        raise;
      else
        dbms_output.put_line('Ignoring ORA-04043 for this object...');
      end if;
    end;
    if mdl = TRUE and for_pdb = TRUE then
      execute immediate 'alter session set "_ORACLE_SCRIPT"=false';
    end if;
  end loop;
  close c;
end;
/

-- Unset the event
alter session set events '29578 trace name context off';

-- Re-create the sources in respective schemas using the source texts
-- in the temporary table
declare
  cursor c is select owner, name, flags from javavm_affected_sources;
  j_owner         varchar2(128);
  j_name          varchar2(4000);  -- need to be able to hold a Java long name
  j_flags         number;
  sharing_clause  varchar2(30);
  ddl_stmt        varchar2(4400);
  mdl             boolean;
  error           number;
  errmsg          varchar2(400);
begin
  open c;
  loop
    fetch c into j_owner, j_name, j_flags;
    exit when c%NOTFOUND;
    begin
      j_name := dbms_java.longname(j_name);
      dbms_output.put_line('Creating JAVA SOURCE : ' || j_owner || '.' || j_name);
      if bitand(j_flags, 65536) = 65536 then
        sharing_clause := 'sharing=metadata ';
        mdl := TRUE;
      else
        sharing_clause := 'sharing=none ';
        mdl := FALSE;
      end if;
      ddl_stmt := 'CREATE OR REPLACE JAVA SOURCE NAMED ' ||
                  sys.dbms_assert.enquote_name(j_owner, FALSE) || '.' ||
                  sys.dbms_assert.enquote_name(j_name, FALSE) || ' ' ||
                  sharing_clause ||
                  'USING CLOB select text from javavm_affected_sources ' ||
                              'where owner=''' || j_owner || '''' ||
                                     ' and name=''' || j_name || '''';
      if mdl = TRUE then
        execute immediate 'alter session set "_ORACLE_SCRIPT"=true';
      end if;
      execute immediate ddl_stmt;
      if mdl = TRUE then
        execute immediate 'alter session set "_ORACLE_SCRIPT"=false';
      end if;
    exception when others then
      error := -sqlcode;
      errmsg := substr(sqlerrm, 1, 400);
      dbms_output.put_line('Error: ORA-' || error);
      insert into javavm_errors_during_creation values (error, errmsg);
    end;
  end loop;
  close c;
end;
/

commit;

-- Compile the sources just created, that are not VALID
declare
  cursor c1 is select owner, name from javavm_affected_sources;
  cursor c2(o varchar2, n varchar2) is
         select object_id from dba_objects where owner=o and object_name=n
         and object_type='JAVA SOURCE' and status='VALID';
  j_owner  varchar2(128);
  j_name   varchar2(4000);  -- need to be able to hold a Java long name
  j_id     number;
  alt_stmt varchar2(4200);
begin
  open c1;
  loop
    fetch c1 into j_owner, j_name;
    exit when c1%NOTFOUND;
    open c2(j_owner, j_name);  -- j_name as obtained from obj$
    fetch c2 into j_id;
    if c2%FOUND then       -- source is already VALID, skip comiling it
      close c2;
      continue;
    end if;
    close c2;
    j_name := dbms_java.longname(j_name);
    dbms_output.put_line('Compiling JAVA SOURCE : ' || j_owner || '.' || j_name);
    alt_stmt := 'ALTER JAVA SOURCE ' ||
                sys.dbms_assert.enquote_name(j_owner, FALSE) || '.' ||
                sys.dbms_assert.enquote_name(j_name, FALSE) || ' ' ||
                'COMPILE';
    execute immediate alt_stmt;
  end loop;
  close c1;
end;
/

-- Check if things went fine, and if so, drop the temporary tables, except in 
-- the root, in case of CDB.  Preserve the tables in the root since they will
-- be needed while patching a PDB that is not open at the moment but is opened
-- and patched later.
declare
  cursor c1 is select joxftobn from x$joxft where (joxfttype=28 and ((joxftderivedclassname like '/%' and (select 1 from javasnm$ where short=joxftderivedclassname) is null) or (joxftderivedresourcename like '/%' and (select 1 from javasnm$ where short=joxftderivedresourcename) is null))) or ((joxfttype=29 or joxfttype=30) and joxftderivedfrom like '/%' and (select 1 from javasnm$ where short=joxftderivedfrom) is null);
  cursor c2 is select e from javavm_errors_during_creation;
  objno  number;
  err    number;
  ok     boolean;
  con_id number;
begin
  open c1;
  fetch c1 into objno;
  ok := c1%NOTFOUND;
  close c1;
  if ok = FALSE then
    dbms_output.put_line('Things are still not fixed.');
  else
    open c2;
    fetch c2 into err;
    ok := c2%NOTFOUND;
    close c2;
    if ok = FALSE then
      dbms_output.put_line('Something didn''t go well.');
    else
      dbms_output.put_line('All OK.');
      con_id := sys_context('userenv', 'con_id');
      if (con_id = 0) or (con_id >= 2) then  -- non-CDB, or a PDB in CDB
        dbms_output.put_line('Dropping the temp tables, etc. ... ');
        execute immediate 'drop table javavm_affected_sources';
        execute immediate 'drop table javavm_affected_derivees';
        execute immediate 'drop table javavm_errors_during_creation';
        if con_id >= 2 then 
          execute immediate 'alter session set "_ORACLE_SCRIPT"=true';
          execute immediate 'drop view javavm_affected_sources_view';
          execute immediate 'alter session set "_ORACLE_SCRIPT"=false';
        end if;
        dbms_output.put_line('Done.');
      end if;
    end if;
  end if;
end;
/

