Rem  Copyright (c) Oracle Corporation 1999 - 2008. All Rights Reserved.
Rem
Rem    NAME
Rem      apxdbmig.sql
Rem
Rem    DESCRIPTION
Rem
Rem    NOTES
Rem      Assumes the SYS user is connected.
Rem
Rem    REQUIRENTS
Rem      - Oracle 11g
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      jstraub   06/29/2006 - Created
Rem      jstraub   07/24/2006 - Removed moving /i/ to new directory and moved it to apex_epg_config.sql
Rem      jstraub   08/14/2006 - Adapted for 11g upgrade install
Rem      jstraub   02/07/2007 - Added exiting if APPUN schema is detected (bug 5852905)
Rem      jstraub   02/08/2007 - Removed WHENEVER SQLERROR statements and changed to determine proper script to run, or null1.sql
Rem      jstraub   02/19/2007 - Added store set apxset.sql to save SQL*Plus setting prior to running APEX scripts, and added check to ensure EDITION is XE
Rem      jstraub   04/10/2007 - Added replace at the end of store command to avoid SP2-0605 error
Rem      jstraub   04/10/2007 - Added INSTALL_TYPE parameter for use with dynamically setting WHENEVER SQLERROR exit in coreins
Rem      jstraub   05/14/2007 - Added grants in case the DB that is upgraded is 9.2 (bug 6051833)
Rem      jstraub   06/19/2007 - Set allow-repository-anonymous-access back to false for XE upgrade (Bug 6140708)
Rem      jkallman  08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
Rem      jstraub   02/26/2008 - Removed reference to coreins2.sql, added check for FLOWS_020100 to normal prior version check
Rem      jstraub   03/03/2008 - Adapted for 11.2 upgrade/downgrade
Rem      jkallman  09/09/2008 - Change FLOWS_030100 references to APEX_030200
Rem      jstraub   09/09/2008 - Changed reference from apex_epg_config.sql to apex_epg_config_core.sql
Rem      jstraub   12/04/2008 - Added alter view statements for bug 7284151
Rem      jstraub   01/30/2009 - Added check for newer version
Rem      jstraub   02/03/2009 - Fixed typo in new version check
Rem

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
EXECUTE dbms_registry.check_server_instance;
WHENEVER SQLERROR CONTINUE

-- save session settings
store set apxset.sql replace

prompt .  ____   ____           ____        ____
prompt . /    \ |    \   /\    /     |     /
prompt .|      ||    /  /  \  |      |    |
prompt .|      ||---    ----  |      |    |---

prompt .|      ||   \  /    \ |      |    |
prompt . \____/ |    \/      \ \____ |____ \____
prompt .
prompt . Application Express Installation.
prompt ...................................

define UPGRADE = '2'

define INSTALL_TYPE = 'INTERNAL'

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

COLUMN :script_name NEW_VALUE comp_file NOPRINT
VARIABLE script_name VARCHAR2(50)

--
-- non - XE
--
begin
    -- intialize the script to do nothing
    :script_name := '?/apex/core/null1.sql';
    -- Check if there is a prior version, non-XE
    for c1 in (select null
                 from dba_users
                where username in ('FLOWS_010500','FLOWS_010600','FLOWS_020000','FLOWS_020100','FLOWS_020200','FLOWS_030000','FLOWS_030100') ) loop
        :script_name := '?/apex/coreins.sql';
        exit;
    end loop;
end;
/


--
-- check if this version is already installed, if so set script back to null1.sql
--
begin
    for c1 in (select null
                 from dba_users
                where username = '&APPUN' ) loop
        :script_name := '?/apex/core/null1.sql';
        exit;
    end loop;
end;
/

--
-- check if newer version is already installed, if so set script back to null1.sql
--
begin
    for c1 in (select to_number(replace(nvl(dbms_registry.version('APEX'),0),'.',null)) v from dual) loop
        if c1.v > 3200001 then
            :script_name := '?/apex/core/null1.sql';
        end if;
        exit;
    end loop;
exception when others then
    :script_name := '?/apex/core/null1.sql';
end;
/

set termout off
define UFROM     = 'FLOWS_010500'
column foo_usr new_val UFROM
select username foo_usr from (select username from dba_users where username in ('FLOWS_010500','FLOWS_010600','FLOWS_020000','FLOWS_020100','FLOWS_020200','FLOWS_030000','FLOWS_030100') order by username desc) x where rownum = 1;

set termout on

column :xe_tbs new_value DATTS NOPRINT
column :xe_ff_tbs new_value FF_TBLS NOPRINT
column :xe_tmp_tbs new_value TEMPTBL NOPRINT
column :xe_pwd new_value ADM_PWD NOPRINT
column :xe_home new_value OH_HOME NOPRINT

variable xe_tbs varchar2(30)
variable xe_ff_tbs varchar2(30)
variable xe_tmp_tbs varchar2(30)
variable xe_pwd varchar2(30)
variable xe_home varchar2(255)

begin
    for c1 in (select default_tablespace dt, temporary_tablespace tt
                 from dba_users
                where username = '&UFROM' ) loop

        :xe_tbs := c1.dt;
        :xe_tmp_tbs := c1.tt;

    end loop;

    for c1 in (select default_tablespace dt
                 from dba_users
                where username = 'FLOWS_FILES' ) loop

        :xe_ff_tbs := c1.dt;

    end loop;

    :xe_pwd := dbms_random.string('X',30);

end;
/

select :xe_tbs from dual;
select :xe_tmp_tbs from dual;
select :xe_ff_tbs from dual;
select :xe_pwd from dual;

define PREFIX     = '?/apex/'

select :script_name from dual;

@&comp_file x &UPGRADE &APPUN &TEMPTBL &IMGPR &DATTS &FF_TBLS &ADM_PWD &PREFIX

alter session set current_schema = SYS;

-- perform grants in case this is an upgrade from 9.2
begin
    -- Grant execute on SYS.dbms_crypto.  Silently fail if it does not exist
    execute immediate 'grant execute on SYS.dbms_crypto to ^APPUN';
exception
    when others then
        null;
end;
/

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

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

begin
-- get oracle_home
    dbms_system.get_env('ORACLE_HOME',:xe_home);
    if :script_name != '?/apex/core/null1.sql' then
        :script_name := '?/apex/apex_epg_config_core.sql';
    end if;
end;
/

select :xe_home from dual;
select :script_name from dual;

set define '&'

column :img_dir new_value IMGUPG NOPRINT
variable img_dir varchar2(4000)

begin
    for c1 in (select to_char(sysdate,'YYYYMMDDHH24MI') x from dual) loop
        :img_dir := '/images_'||c1.x||'/';
    end loop;
end;
/

select :img_dir from dual;

alter session set current_schema = &APPUN;

insert into wwv_flow_upgrade_progress values (0,sysdate,0,'&UFROM',null,'&IMGUPG');

alter view wwv_flow_months_month_temp compile;
alter view wwv_flow_months_month compile;
alter package wwv_flow_utilities compile body;

alter session set current_schema = SYS;

@&comp_file &OH_HOME

-- Restore session variables
@apxset.sql