Rem Copyright (c) 2008, Oracle. All rights reserved.
Rem
Rem    NAME
Rem      apxe111.sql
Rem
Rem    DESCRIPTION
Rem      Runs downgrade actions for 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   02/26/2008 - Created for 11.2 downgrade
Rem      jstraub   04/14/2008 - Changed logic to revert images directory to prior release, only if resource exists (lrg 3366866)
Rem      jstraub   05/08/2008 - Added call to validate_apex since there is no apxrelod script for 10.1 and 10.2 (lrg 3398351)
Rem      jkallman  09/09/2008 - Change FLOWS_030100 references to APEX_030200
Rem      jstraub   12/08/2008 - Changed current_schema to ^UFROM prior to wwv_flow_upgrade.switch_schemas (lrg 3702914)
Rem      jstraub   03/18/2009 - Removed call to validate_apex per component script guidelines, already in apxrelod.sql

-- save session settings
store set apxset.sql replace

set define '^'
set concat on
set concat .

define APPUN = 'APEX_030200'
define UFROM = 'APEX_030200'

EXECUTE dbms_registry.downgrading('APEX');

-- find the version upgraded from and the prior images directory

alter session set current_schema = ^APPUN;

column :foo_user new_val UFROM NOPRINT
variable foo_user varchar2(50)
column :image_dir new_val IMGDIR NOPRINT
variable image_dir varchar2(4000)

begin
    for c1 in (select upgrade_action, upgrade_command
                 from wwv_flow_upgrade_progress
                where upgrade_id = 0 and upgrade_sequence = 0 ) loop
        :foo_user := c1.upgrade_action;
        :image_dir := c1.upgrade_command;
    end loop;
end;
/

select :foo_user from dual;
select :image_dir from dual;

alter session set current_schema = ^UFROM;

begin
    if '^UFROM' != '^APPUN' then
        wwv_flow_upgrade.switch_schemas('^APPUN','^UFROM');
    end if;
end;
/

alter session set current_schema = SYS;

begin
    if '^UFROM' != '^APPUN' then
        if dbms_xdb.existsresource('/images/') and dbms_xdb.existsresource('^IMGDIR') then
            for c1 in (select to_char(sysdate,'YYYYMMDDHH24MI') x from dual) loop
                dbms_xdb.renameresource('/images/','/','/images_'||c1.x||'/');
            end loop;
            dbms_xdb.renameresource('^IMGDIR','/','/images/');
        end if;
    end if;
end;
/

column flow_version new_val version
set termout off
select ^UFROM..wwv_flows_release flow_version from dual where rownum = 1;
set termout on

EXECUTE dbms_registry.downgraded('APEX','^version');

@apxset.sql