Rem  Copyright (c) Oracle Corporation 1999 - 2008. All Rights Reserved.
Rem
Rem    NAME
Rem      apxrelod.sql
Rem
Rem    DESCRIPTION
Rem      This reload script should be used placed in the Oracle Home that is downgraded to
Rem      under a directory named apex.
Rem
Rem    NOTES
Rem      Assumes the SYS user is connected.
Rem
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      jstraub   01/03/2008 - Created
Rem      jstraub   01/03/2008 - Updated owner to FLOWS_030100
Rem      jstraub   03/03/2008 - Adapted for downgrades to versions prior to 11.1.0.6
Rem      jstraub   03/17/2009 - Added grant to ctx_ddl for downgrade/upgrade reload
Rem      jstraub   03/19/2009 - Made grant dynamic so it could be run by multiple versions
Rem      jstraub   10/27/2009 - Moved storing of apxset.sql to $ORACLE_HOME/rdbms/log (Bug 7422107)

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

column :xe_home new_value OH_HOME NOPRINT
variable xe_home varchar2(255)

begin
-- get oracle_home
    dbms_system.get_env('ORACLE_HOME',:xe_home);
end;
/

select :xe_home from dual;

-- save session settings
store set &OH_HOME/rdbms/log/apxset.sql replace

declare
    l_apex_schema varchar2(30);
begin
    l_apex_schema := dbms_registry.schema('APEX');
    execute immediate 'grant execute on ctx_ddl to '||l_apex_schema||' with grant option';
end;
/

EXECUTE validate_apex;

-- Restore session variables
@?/rdbms/log/apxset.sql