Rem  Copyright (c) Oracle Corporation 1999 - 2007. All Rights Reserved.
Rem
Rem    NAME
Rem      apxdevrm.sql
Rem
Rem    DESCRIPTION
Rem      This is the development environment removal script for Oracle Application Express.
Rem
Rem
Rem    REQUIREMENTS
Rem      - Oracle database 9.2.0.3 or better
Rem      - PL/SQL Web Toolkit
Rem
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      jstraub   06/26/2007 - Created
Rem      jkallman  08/02/2007 - Change FLOWS_030000 references to FLOWS_030100,
Rem                             augmented application removal to include translated versions
Rem      jstraub   09/18/2007 - Removed 4155 from internal application removal
Rem      jkallman  11/21/2007 - Add drop of wwv_flow_theme_19 and wwv_flow_theme_20
Rem      jstraub   11/26/2007 - Added running runtime_grant_revoke.sql
Rem      jstraub   12/14/2007 - Removed dropping theme packages since they moved to coreins.sql
Rem      jkallman  01/30/2008 - Remove revoke of v_$sesstat, v_$sql_plan
Rem      jkallman  09/08/2008 - Delete removal of package wwv_flow_create_flow_api (made obsolete)
Rem      jkallman  09/09/2008 - Change FLOWS_030100 references to APEX_030200
Rem      hfarrell  10/23/2008 - Added drop of Application Migrations packages:wwv_mig_acc_load,
Rem                             wwv_mig_frm_update_apx_app, wwv_mig_frm_utilities,wwv_mig_frm_load_xml,
Rem                             wwv_mig_frmmenu_load_xml,wwv_mig_rpt_load_xml
Rem      hfarrell  10/23/2008 - Removed drop statements for Application Migrations and placed in wwv_mig_drop_ddl.sql
Rem      jkallman  07/23/2009 - Add drop of package wwv_flow_install_wizard as well as delete incorrect revocation of select on dba_triggers and dba_dependencies
Rem      jkallman  07/23/2009 - Remove revoke of execute any procedure


set define '^'
set concat on
set concat .
set verify off

define APPUN = 'APEX_030200'
define PREFIX = '@'

column foo3 new_val LOG1

select 'removedev'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' foo3 from dual;
spool ^LOG1

set termout on

timing start "Development Removal"

alter session set current_schema = SYS;

prompt I.    O R A C L E   S Y S   D E - I N S T A L L   P R O C E S S

revoke alter system                   from ^APPUN;
revoke select on sys.dba_tab_comments from ^APPUN;
revoke select on sys.dba_synonyms from ^APPUN;
revoke select on sys.dba_db_links from ^APPUN;
revoke select on sys.dba_profiles from ^APPUN;
revoke select on sys.dba_trigger_cols from ^APPUN;
revoke select on sys.dba_indexes from ^APPUN;
revoke select on sys.dba_free_space from ^APPUN;
revoke select on sys.dba_col_privs from ^APPUN;
revoke select on sys.dba_snapshots from ^APPUN;
revoke select on sys.dba_types from ^APPUN;
revoke select on sys.dba_rsrc_plan_directives from ^APPUN;
revoke select on sys.seg$ from ^APPUN;
revoke select on sys.argument$ from ^APPUN;
revoke select on sys.obj$ from ^APPUN;
revoke select on sys.v_$statname from ^APPUN;
revoke select on sys.v_$sysstat from ^APPUN;
revoke select on sys.v_$sql from ^APPUN;
revoke select on sys.v_$mystat from ^APPUN;
revoke select on sys.v_$session from ^APPUN;
revoke select on sys.v_$locked_object from ^APPUN;
revoke select on sys.v_$session_wait from ^APPUN;
revoke select on sys.v_$sess_io from ^APPUN;
revoke select on sys.v_$open_cursor from ^APPUN;
revoke select on sys.v_$process from ^APPUN;

@^PREFIX.core/runtime_grant_revoke.sql


begin
    -- revoke select on sys.dba_recyclebin.  Silently fail if it does not exist
    execute immediate 'revoke select on sys.dba_recyclebin from ^APPUN';
exception
    when others then
        null;
end;
/

begin
    -- revoke select on sys.dba_feature_usage_statistics.  Silently fail if it does not exist
    execute immediate 'revoke select on sys.dba_feature_usage_statistics from ^APPUN';
exception
    when others then
        null;
end;
/

revoke select on dba_rollback_segs from ^APPUN;


prompt ...CONNECT as the Oracle user who owns the APEX engine

alter session set current_schema = ^APPUN;

timing start "Remove Development Packages"
prompt  II.   D R O P   A P E X   P A C K A G E S

drop package wwv_flow_plsql_editor;
drop package wwv_flow_model_api;
drop package wwv_flow_f4000_util;
drop package wwv_flow_image_generator;
drop function wwv_flow_item_comps;
drop procedure wwv_flow_copy;
drop procedure wwv_flow_copy_lov;
drop procedure wwv_flow_copy_page_item;
drop procedure wwv_flow_copy_button;
drop package wwv_flow_translation_utilities;
drop procedure wwv_flow_seed_translations;
drop procedure wwv_flow_sync_translations;
drop package wwv_flow_region_layout;
drop function wwv_flow_lov_used_on_pages;
drop package wwv_flow_query_builder;
drop package wwv_flow_sw_object_feed;
drop package wwv_flow_load_data;
drop package wwv_flow_load_excel_data;
drop package wwv_flow_copy_metadata;
drop package wwv_flow_copy_util;
drop package wwv_flow_tab_mgr;
drop package wwv_flow_generate_ddl;
drop package wwv_flow_table_drill;
drop package wwv_flow_download;
drop package wwv_flow_copy_page;
drop package wwv_flow_generate_table_api;
drop package wwv_flow_gen_hint;
drop package wwv_flow_xliff;
drop package wwv_flow_create_model_app;
drop procedure apex_admin;
drop procedure htmldb_admin;
drop package wwv_flow_help;
drop package wwv_flow_data_quick_flow;
drop package wwv_flow_theme_files;
drop package wwv_flow_session_mon;
drop package wwv_flow_sw_page_calls;
drop package wwv_flow_wiz_confirm;
drop package wwv_flow_page_map;
drop package wwv_flow_drag_layout;
drop package wwv_flow_dataload_xml;
drop package wwv_flow_collections_showcase;
drop package wwv_flow_install_wizard;

timing stop

timing start "Remove Migration Workbench"
prompt ...Remove Application Migration Workshop package and tables
@@core/wwv_mig_drop_ddl.sql
timing stop

timing start "Validate Application Express"
prompt ...Validating Application Express Component, this may take a few minutes

alter session set current_schema = SYS;

set serveroutput on
exec validate_apex;

timing stop

alter session set current_schema = ^APPUN;

timing start "Remove Internal Development Applications"
prompt  III.   R E M O V E   D E V E L O P M E N T    A P P L I C A T I O N S

declare
    l_app dbms_sql.number_table;
begin
    wwv_flow_security.g_security_group_id := 10;

    l_app(1) := 4000;
    l_app(2) := 4050;
    l_app(3) := 4300;
    l_app(4) := 4350;
    l_app(5) := 4400;
    l_app(6) := 4500;
    l_app(7) := 4550;
    l_app(8) := 4700;

    for i in 1..l_app.count loop
        for j in 0..8 loop
            wwv_flow_api.remove_flow( l_app(i)+j );
        end loop;
    end loop;
end;
/

timing stop


timing start "Verification Images Remove"
begin
    delete from wwv_flow_random_images;
end;
/
timing stop

spool off

timing stop

commit;

column global_name new_value gname
set termout off
select user global_name from dual;
set termout on
set heading on
set feedback on
set sqlprompt '^gname> '