Rem  Copyright (c) Oracle Corporation 1999 - 2006. All Rights Reserved.
Rem
Rem    NAME
Rem      apxremov.sql
Rem
Rem    DESCRIPTION
Rem      Removes Application Express
Rem
Rem    NOTES
Rem      Assumes the SYS user is connected.
Rem
Rem    REQUIRENTS
Rem      Application Express
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      jstraub   08/14/2006 - Created
Rem      jkallman  09/29/2006 - Adjusted APPUN to FLOWS_030000, add FLOWS_020200 to upgrade query
Rem      jstraub   02/14/2007 - Added call to wwv_flow_upgrade.drop_public_synonyms, and dropping APEX_PUBLIC_USER if not upgraded
Rem      jkallman  08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
Rem      jstraub   08/29/2007 - Altered to completely remove Application Express per bug 6086891
Rem      jstraub   11/27/2007 - Added removing APEX_ADMINISTRATOR_ROLE if not an upgrade installation
Rem      jkallman  09/09/2008 - Change FLOWS_030100 references to APEX_030200
Rem      jstraub   12/19/2008 - Added removal of SYS owned objects specific to Application Express
Rem      jstraub   01/21/2009 - Moved XDB cleanup to block that only executes if not an upgrade from prior release
Rem      jstraub   01/30/2009 - Added dropping WWV_FLOW_KEY and WWV_FLOW_VAL_LIB
Rem

prompt ...Removing Application Express

define UPGRADE = '1'
define APPUN = 'APEX_030200'
define IMGPR = '/i/'

alter session set current_schema = &APPUN;

begin
    wwv_flow_upgrade.drop_public_synonyms;
end;
/

column foo2 new_val UPGRADE

select '2' foo2 from dba_users where username in ('FLOWS_010500','FLOWS_010600','FLOWS_020000','FLOWS_020100','FLOWS_020200','FLOWS_030000','FLOWS_030100');

begin
    if '&UPGRADE' = '1' then
        wwv_flow_upgrade.flows_files_objects_remove('^APPUN');
    end if;
end;
/

alter session set current_schema = SYS;

set serveroutput on
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
begin
    if dbms_registry.status('APEX') is null then
        dbms_output.put_line(chr(13)||chr(10));
        dbms_output.put_line('Error:');
        dbms_output.put_line('You can only use this script to remove Application Express');
        dbms_output.put_line(chr(13)||chr(10)||chr(13)||chr(10));
        execute immediate('invalid sql stmnt to force exit');
    end if;
end;
/
WHENEVER SQLERROR CONTINUE

-- Remove FLOWS SCHEMA
drop user &APPUN cascade;

-- Remove FLOWS_FILES and APEX_PUBLIC_USER SCHEMA if no other versions exist

begin
    if '&UPGRADE' = '1' then
        execute immediate 'drop user FLOWS_FILES cascade';
        execute immediate 'drop user APEX_PUBLIC_USER cascade';
        execute immediate 'drop role APEX_ADMINISTRATOR_ROLE';
    end if;
end;
/

-- XDB Cleanup
declare
    cfg XMLType;
begin

    if '&UPGRADE' = '1' then

        if dbms_xdb.existsresource('/i/') then
          dbms_xdb.deleteresource('/i/',4);
        end if;

        if dbms_xdb.existsresource('/images/') then
          dbms_xdb.deleteresource('/images/',4);
        end if;

        dbms_epg.drop_dad('APEX');

        cfg := dbms_xdb.cfg_get();

        if cfg.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings/servlet-mapping/servlet-name[text()="PublishedContentServlet"]') = 1 then
            cfg := cfg.deleteXML('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings/servlet-mapping/servlet-name[text()="PublishedContentServlet"]/..');
        end if;

        if cfg.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet/servlet-name[text()="PublishedContentServlet"]') = 1 then
            cfg := cfg.deleteXML('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet/servlet-name[text()="PublishedContentServlet"]/..');
        end if;

        dbms_xdb.cfg_update(cfg);
        commit;
        dbms_xdb.cfg_refresh;

    end if;

end;
/

-- Remove SYS owned objects

begin
    if '&UPGRADE' = '1' then
        execute immediate 'drop procedure validate_apex';
        execute immediate 'drop package WWV_FLOW_VAL';
        execute immediate 'drop package WWV_DBMS_SQL';
        execute immediate 'drop package WWV_FLOW_KEY';
        execute immediate 'drop library WWV_FLOW_VAL_LIB';
    end if;
end;
/

prompt ...Application Express Removed