C.3 Cleaning Up After a Failed Installation

Learn about troubleshooting after failed installation.

In a successful installation the following banner displays near the end of the installation:

Thank you for installing Oracle Application Express.
Oracle Application Express is installed in the APEX_190100 schema.

To reinstall, you must either drop the Oracle Application Express database schemas, or run a script to completely remove Application Express from the database, depending upon the installation type.

• Reverting to a Previous Release After a Failed Upgrade Installation
  Learn about reverting to Oracle Application Express to a previous release in the case of a failed upgrade installation.
• Removing Oracle Application Express from the Database
  Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role and execute the SQL> @apxremov.sql command.

C.3.1 Reverting to a Previous Release After a Failed Upgrade Installation

Learn about reverting to Oracle Application Express to a previous release in the case of a failed upgrade installation.

In the case of a failed upgrade installation, you may want to revert Oracle Application Express to a previous release and then remove the schemas associated with the current release.

• Verifying If You Have a Previous Release of Oracle Application Express
  
• Reverting the Images Directory
  
• Reverting to a Previous Release
  
• Removing the Oracle Application Express Release 19.1 Schema
  

C.3.1.1 Verifying If You Have a Previous Release of Oracle Application Express

To verify whether you have a previous release of Application Express:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

2. Execute the following command in SQL*Plus:

   select username from dba_users
       where regexp_like(username,'(FLOWS|APEX)_\d{6}')
           and username <> (select table_owner from all_synonyms
           where synonym_name = 'WWV_FLOW'
           and owner = 'PUBLIC')
   

   If the query above returns any rows, the database contains a previous release of Oracle Application Express.

C.3.1.2 Reverting the Images Directory

If you altered your images directory, revert it back to the release you want to revert to using the following methods:

• For Oracle REST Data Services or mod_plsql :

  If you altered your images directory, you must point the text alias /i/ back to images directory for the release you want to revert to.

• For Embedded PL/SQL Gateway :

  Run apex_epg_config.sql from the release directory you want to revert to, to restore the images of that release. See the Oracle Application Installation Guide for the release you want to revert to and find the section "Updating the Images Directory When Upgrading from a Previous Release."

See Also:

"Copying the Images Directory"

C.3.1.3 Reverting to a Previous Release

This section describes how to revert to a Oracle Application Express previous release.

• Reverting to Release 1.5
  
• Reverting to Release 1.6
  
• Reverting to Release 2.0
  
• Reverting to Release 2.2
  
• Reverting to Release 3.0
  
• Reverting to Release 3.1
  
• Reverting to Release 3.2
  
• Reverting to Release 4.0
  
• Reverting to Release 4.1
  
• Reverting to Release 4.2 in a non-CDB
  
• Reverting to Release 4.2 in a CDB
  
• Reverting to Release 5.0 in a non-CDB
  
• Reverting to Release 5.0 in a CDB
  
• Reverting to Release 5.1 in a non-CDB
  
• Reverting to Release 5.1 in a CDB
  
• Reverting to Release 18.1 in a non-CDB
  
• Reverting to Release 18.1 in a CDB
  
• Reverting to Release 18.2 in a non-CDB
  
• Reverting to Release 18.2 in a CDB
  

C.3.1.3.1 Reverting to Release 1.5

To revert to a previous Oracle Application Express release 1.5:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

3. Execute the following:

   ALTER SESSION SET CURRENT_SCHEMA = FLOWS_010500;
   exec flows_010500.wwv_flow_upgrade.switch_schemas('APEX_190100','FLOWS_010500');
   

4. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.2 Reverting to Release 1.6

To revert to a Oracle Application Express release 1.6:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

3. Execute the following:

   ALTER SESSION SET CURRENT_SCHEMA = FLOWS_010600;
   exec flows_010600.wwv_flow_upgrade.switch_schemas('APEX_190100','FLOWS_010600');
   

4. Depending upon the release you are reverting to, execute the appropriate command in SQL*Plus.
5. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.3 Reverting to Release 2.0

To revert to a previous Oracle Application Express release 2.0:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

3. Execute the following:

   ALTER SESSION SET CURRENT_SCHEMA = FLOWS_020000;
   exec flows_020000.wwv_flow_upgrade.switch_schemas('APEX_190100','FLOWS_020000');
   

4. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.4 Reverting to Release 2.2

To revert to a previous Oracle Application Express release 2.2:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

3. Execute the following:

   ALTER SESSION SET CURRENT_SCHEMA = FLOWS_020200;
   exec flows_020200.wwv_flow_upgrade.switch_schemas('APEX_190100','FLOWS_020200');
   

4. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.5 Reverting to Release 3.0

To revert to a previous Oracle Application Express release 3.0:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex in the 3.0 source.
3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

4. Execute the following:

   set define '^'
   
   @apexvalidate x x FLOWS_030000
   ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030000;
   exec flows_030000.wwv_flow_upgrade.switch_schemas('APEX_190100','FLOWS_030000');
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
   declare
       l_apex_version varchar2(30);
   begin
       l_apex_version := flows_030000.wwv_flows_release;
       dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','FLOWS_030000');
       dbms_registry.downgraded('APEX',l_apex_version);
       validate_apex;
   end;
   /
   

5. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.6 Reverting to Release 3.1

To revert to a previous Oracle Application Express release 3.1:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 3.1 source.
3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

4. Execute the following commands:

   @wwv_flow_val.plb
   @wwv_dbms_sql.sql
   @wwv_dbms_sql.plb
   

5. Change your working directory to apex in the 3.1 source.
6. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

7. Execute the following:

   set define '^'
    
   @apexvalidate x x FLOWS_030100
   ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030100;
   exec flows_030100.wwv_flow_upgrade.switch_schemas('APEX_190100','FLOWS_030100');
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
   declare
       l_apex_version varchar2(30);
   begin
       l_apex_version := flows_030100.wwv_flows_release;
       dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','FLOWS_030100');
       dbms_registry.downgraded('APEX',l_apex_version);
       validate_apex;
   end;
   /
   

8. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.7 Reverting to Release 3.2

To revert to a previous Oracle Application Express release 3.2:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 3.2 source.
3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

4. Execute the following commands:

   @wwv_flow_val.plb
   @wwv_dbms_sql.sql
   @wwv_dbms_sql.plb
   

5. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

6. Execute the following:

   set define '^'
    
   @apexvalidate x x APEX_030200
   ALTER SESSION SET CURRENT_SCHEMA = APEX_030200;
   exec apex_030200.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_030200');
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
   declare
       l_apex_version varchar2(30);
   begin
       l_apex_version := apex_030200.wwv_flows_release;
       dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_030200')
       dbms_registry.downgraded('APEX',l_apex_version);
       validate_apex;
   end;
   /
   

7. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.8 Reverting to Release 4.0

To revert to a previous Oracle Application Express release 4.0:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

3. Execute the following commands:

   @wwv_flow_val.sql
   @wwv_flow_val.plb
   @wwv_dbms_sql.sql
   @wwv_dbms_sql.plb
   

4. Change your working directory to apex in the 4.0 source.
5. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

6. Execute the following:

   set define '^'
   
   @apexvalidate x x APEX_040000
   ALTER SESSION SET CURRENT_SCHEMA = APEX_040000;
   exec apex_040000.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_040000');
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
   declare
       l_apex_version varchar2(30);
   begin
       l_apex_version := apex_040000.wwv_flows_release;
       dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_040000');
       dbms_registry.downgraded('APEX',l_apex_version);
       validate_apex;
   end;
   /
   

7. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.9 Reverting to Release 4.1

To revert to a previous Oracle Application Express release 4.1:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 4.1 source.
3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

4. Execute the following commands:

   @wwv_flow_val.sql
   @wwv_flow_val.plb
   @wwv_dbms_sql.sql
   @wwv_dbms_sql.plb
   

5. Change your working directory to apex in the 4.1 source.
6. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

7. Execute the following:

   set define '^'
    
   @apexvalidate x x APEX_040100
   ALTER SESSION SET CURRENT_SCHEMA = APEX_040100;
   exec apex_040100.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_040100');
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
   declare
       l_apex_version varchar2(30);
   begin
       l_apex_version := apex_040100.wwv_flows_release;
       dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_040100');
       dbms_registry.downgraded('APEX',l_apex_version);
       validate_apex;
   end;
   /
   

8. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.10 Reverting to Release 4.2 in a non-CDB

To revert to a previous Oracle Application Express release 4.2 in a non-CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 4.2 source.
3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

4. Execute the following commands:

   alter session set current_schema = SYS;
    
   @core_sys_views.sql
    
   grant select on sys.wwv_flow_gv$session to APEX_040200;
    
   @wwv_flow_val.sql
   @wwv_flow_val.plb
   @wwv_dbms_sql.sql
   grant execute on wwv_dbms_sql to APEX_040200;
   @wwv_dbms_sql.plb
    
   begin
       dbms_utility.compile_schema('APEX_040200');
   end;
   /
   

5. Change your working directory to apex in the 4.2 source.
6. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

7. Execute the following:

   set define '^'
    
   @apexvalidate x x APEX_040200
   ALTER SESSION SET CURRENT_SCHEMA = APEX_040200;
   exec apex_040200.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_040200');
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
   declare
       l_apex_version varchar2(30);
   begin
       l_apex_version := apex_040200.wwv_flows_release;
       dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_040200');
       dbms_registry.downgraded('APEX',l_apex_version);
       validate_apex;
   end;
   /
   

8. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.11 Reverting to Release 4.2 in a CDB

To revert to a previous Oracle Application Express release 4.2 in a CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 4.2 source.
3. Create a new text file in that directory named apx42dgrd1.sql consisting of the following:

   alter session set current_schema = SYS;
    
   @core_sys_views.sql
    
   grant select on sys.wwv_flow_gv$session to APEX_040200;
    
   @wwv_flow_val.sql
   @wwv_flow_val.plb
   @wwv_dbms_sql.sql
   grant execute on wwv_dbms_sql to APEX_040200;
   @wwv_dbms_sql.plb
    
   begin
       dbms_utility.compile_schema('APEX_040200');
   end;
   /
   

4. Create a second new text file in that directory named apx42dgrd.sql consisting of the following:

   set define '^'
    
   whenever sqlerror exit
    
   column :xe_home new_value OH_HOME NOPRINT
   variable xe_home varchar2(255)
    
   set serverout on
   begin
   -- get oracle_home
       sys.dbms_system.get_env('ORACLE_HOME',:xe_home);
       if length(:xe_home) = 0 then
           sys.dbms_output.put_line(lpad('-',80,'-'));
           raise_application_error (
               -20001,
               'Oracle Home environment variable not set' );
       end if;
   end;
   /
   whenever sqlerror continue
    
   set termout off
   select :xe_home from sys.dual;
   set termout on
    
   host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx42dgrd apx42dgrd1.sql
   

5. Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

6. Execute the following commands:

   @apx42dgrd.sql
   

7. Change your working directory to apex in the 4.2 source.
8. Create a new text file in that directory name apx42dgrd1.sql with the following contents:

   set define '^'
    
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
    
   @apexvalidate x x APEX_040200
    
   ALTER SESSION SET CURRENT_SCHEMA = APEX_040200;
   exec apex_040200.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_040200');
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
   declare
       l_apex_version varchar2(30);
   begin
       l_apex_version := apex_040200.wwv_flows_release;
       dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_040200');
       dbms_registry.downgraded('APEX',l_apex_version);
       validate_apex;
   end;
   /
   

9. Create a second new text file in that directory named apx42dgrd.sql consisting of the following:

   set define '^'
    
   whenever sqlerror exit
    
   column :xe_home new_value OH_HOME NOPRINT
   variable xe_home varchar2(255)
    
   set serverout on
   begin
   -- get oracle_home
       sys.dbms_system.get_env('ORACLE_HOME',:xe_home);
       if length(:xe_home) = 0 then
           sys.dbms_output.put_line(lpad('-',80,'-'));
           raise_application_error (
               -20001,
               'Oracle Home environment variable not set' );
       end if;
   end;
   /
   whenever sqlerror continue
    
   set termout off
   select :xe_home from sys.dual;
   set termout on
    
   host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx42dgrd apx42dgrd1.sql
   

10. Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

    On Windows:

    SYSTEM_DRIVE:\ sqlplus /nolog
    SQL> SQL> CONNECT SYS as SYSDBA
    Enter password: SYS_password
    

    On UNIX and Linux:

    $ sqlplus /nolog
    SQL> SQL> CONNECT SYS as SYSDBA
    Enter password: SYS_password
    

11. Execute the following:

    @apx42dgrd.sql
    

12. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.12 Reverting to Release 5.0 in a non-CDB

To revert to a previous Oracle Application Express release 5.0 in a non-CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 5.0 source.
3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

4. Execute the following commands:

   alter session set current_schema = SYS;   
   
   @wwv_flow_val.sql 
   @wwv_flow_val.plb 
   
   begin
        dbms_utility.compile_schema('APEX_050000');
   end;
   / 
   
   set define '^'   
   @validate_apex x x APEX_050000
   
   begin
     for i in ( select owner, trigger_name
                  from sys.dba_triggers
                 where owner        =    'APEX_050000'
                   and trigger_name like 'WWV_FLOW_UPGRADE_%'
                 order by 1 )
     loop
       sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
       execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
     end loop;
   end;
   /
   
   ALTER SESSION SET CURRENT_SCHEMA = APEX_050000; 
   exec apex_050000.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_050000');
   ALTER SESSION SET CURRENT_SCHEMA = SYS; 
   drop context APEX$SESSION;
   create context APEX$SESSION using APEX_050000.WWV_FLOW_SESSION_CONTEXT;
   declare
        l_apex_version varchar2(30); 
        l_schemas sys.dbms_registry.schema_list_t;
   begin
        l_apex_version := apex_050000.wwv_flows_release;
        dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050000');
        dbms_registry.downgraded('APEX',l_apex_version);
        select username
          bulk collect into l_schemas
          from all_users
         where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
         order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        validate_apex;
   end;
   /

5. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.13 Reverting to Release 5.0 in a CDB

To revert to a previous Oracle Application Express release 5.0 in a CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 5.0 source.
3. Create a new text file in that directory named apx50dgrd1.sql consisting of the following:

   alter session set current_schema = SYS;   
   
   @wwv_flow_val.sql 
   @wwv_flow_val.plb 
   
   begin
        dbms_utility.compile_schema('APEX_050000');
   end;
   / 
   
   set define '^'   
   @validate_apex x x APEX_050000
   
   begin
     for i in ( select owner, trigger_name
                  from sys.dba_triggers
                 where owner        =    'APEX_050000'
                   and trigger_name like 'WWV_FLOW_UPGRADE_%'
                 order by 1 )
     loop
       sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
       execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
     end loop;
   end;
   /
   
   ALTER SESSION SET CURRENT_SCHEMA = APEX_050000; 
   exec apex_050000.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_050000');
   ALTER SESSION SET CURRENT_SCHEMA = SYS; 
   drop context APEX$SESSION;
   create context APEX$SESSION using APEX_050000.WWV_FLOW_SESSION_CONTEXT;
   declare
        l_apex_version varchar2(30); 
        l_schemas sys.dbms_registry.schema_list_t;
   begin
        l_apex_version := apex_050000.wwv_flows_release;
        dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050000');
        dbms_registry.downgraded('APEX',l_apex_version);
        select username
          bulk collect into l_schemas
          from all_users
         where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
         order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        validate_apex;
   end;
   /

4. Create a second new text file in that directory named apx50dgrd.sql consisting of the following:

   set define '^'   
   whenever sqlerror exit   
   column :xe_home new_value OH_HOME NOPRINT 
   variable xe_home varchar2(255)   
   set serverout on 
   begin 
   -- get oracle_home     
   sys.dbms_system.get_env('ORACLE_HOME',:xe_home);     
   if length(:xe_home) = 0 then
       sys.dbms_output.put_line(lpad('-',80,'-'));         
       raise_application_error (-20001,'Oracle Home environment variable not set' );  
   end if; 
   end; 
   / 
   whenever sqlerror continue   
   set termout off 
   select :xe_home from sys.dual; 
   set termout on   
   host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx50dgrd apx50dgrd1.sql
   

5. Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

   On UNIX and Linux:

   $ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

6. Execute the following commands:

   @apx50dgrd.sql
   

7. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.14 Reverting to Release 5.1 in a non-CDB

To revert to a previous Oracle Application Express release 5.1 in a non-CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 5.1 source.
3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

4. Execute the following commands:

   alter session set current_schema = SYS;   
   
   @wwv_flow_val.sql 
   @wwv_flow_val.plb 
   
   begin
        dbms_utility.compile_schema('APEX_050100');
   end;
   / 
   
   set define '^'   
   @validate_apex x x APEX_050100
   
   begin
     for i in ( select owner, trigger_name
                  from sys.dba_triggers
                 where owner        =    'APEX_050100'
                   and trigger_name like 'WWV_FLOW_UPGRADE_%'
                 order by 1 )
     loop
       sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
       execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
     end loop;
   end;
   /
   
   ALTER SESSION SET CURRENT_SCHEMA = APEX_050100; 
   exec apex_050100.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_050100');
   ALTER SESSION SET CURRENT_SCHEMA = SYS; 
   drop context APEX$SESSION;
   create context APEX$SESSION using APEX_050100.WWV_FLOW_SESSION_CONTEXT;
   declare
        l_apex_version varchar2(30); 
        l_schemas sys.dbms_registry.schema_list_t;
   begin
        l_apex_version := apex_050100.wwv_flows_release;
        dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050100');
        dbms_registry.downgraded('APEX',l_apex_version);
        select username
          bulk collect into l_schemas
          from all_users
         where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
         order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        validate_apex;
   end;
   /

5. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.15 Reverting to Release 5.1 in a CDB

To revert to a previous Oracle Application Express release 5.1 in a CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex/core in the 5.1 source.
3. Create a new text file in that directory named apx51dgrd1.sql consisting of the following:

   alter session set current_schema = SYS;   
   
   @wwv_flow_val.sql 
   @wwv_flow_val.plb 
   
   begin
        dbms_utility.compile_schema('APEX_050100');
   end;
   / 
   
   set define '^'   
   @validate_apex x x APEX_050100
   
   begin
     for i in ( select owner, trigger_name
                  from sys.dba_triggers
                 where owner        =    'APEX_050100'
                   and trigger_name like 'WWV_FLOW_UPGRADE_%'
                 order by 1 )
     loop
       sys.dbms_output.put_line('Dropping trigger '||i.owner||'.'||i.trigger_name);
       execute immediate 'drop trigger '||i.owner||'.'||i.trigger_name;
     end loop;
   end;
   /
   
   ALTER SESSION SET CURRENT_SCHEMA = APEX_050100; 
   exec apex_050100.wwv_flow_upgrade.switch_schemas('APEX_190100','APEX_050100');
   ALTER SESSION SET CURRENT_SCHEMA = SYS; 
   drop context APEX$SESSION;
   create context APEX$SESSION using APEX_050100.WWV_FLOW_SESSION_CONTEXT;
   declare
        l_apex_version varchar2(30); 
        l_schemas sys.dbms_registry.schema_list_t;
   begin
        l_apex_version := apex_050100.wwv_flows_release;
        dbms_registry.downgrading('APEX','Oracle Application Express','validate_apex','APEX_050100');
        dbms_registry.downgraded('APEX',l_apex_version);
        select username
          bulk collect into l_schemas
          from all_users
         where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
         order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        validate_apex;
   end;
   /

4. Create a second new text file in that directory named apx51dgrd.sql consisting of the following:

   set define '^'   
   whenever sqlerror exit   
   column :xe_home new_value OH_HOME NOPRINT 
   variable xe_home varchar2(255)   
   set serverout on 
   begin 
   -- get oracle_home     
   sys.dbms_system.get_env('ORACLE_HOME',:xe_home);     
   if length(:xe_home) = 0 then
       sys.dbms_output.put_line(lpad('-',80,'-'));         
       raise_application_error (-20001,'Oracle Home environment variable not set' );  
   end if; 
   end; 
   / 
   whenever sqlerror continue   
   set termout off 
   select :xe_home from sys.dual; 
   set termout on   
   host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx51dgrd apx51dgrd1.sql
   

5. Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

   On UNIX and Linux:

   $ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

6. Execute the following commands:

   @apx51dgrd.sql
   

7. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.16 Reverting to Release 18.1 in a non-CDB

To revert to a previous Oracle Application Express release 18.1 in a non-CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex in the 18.1 source.
3. Create a new text file in that directory named apxdwngrd.sql consisting of the following:

   set define '^'
   set concat on
   set concat .
   set verify off
    
   set termout off
   column foo new_val LOG
   select 'apxdwngrd_' ||to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as foo
     from sys.dual;
   set termout on
   spool ^LOG
    
   @@core/scripts/set_appun.sql
    
   whenever sqlerror exit
   set serveroutput on size unlimited
    
   declare
       l_cnt    number := 0;
   begin
       select count(*) into l_cnt from sys.dba_users where username = '^APPUN';
       if l_cnt = 0 then
           dbms_output.put_line('^APPUN not found in this database.');
           raise program_error;
       end if;
   end;
   /
   whenever sqlerror continue
    
    
   prompt ...Create validate procedure in SYS schema and start registration
   @@core/validate_apex.sql x x ^APPUN
    
   grant inherit any privileges to ^APPUN;
    
   prompt Installing SYS views
    
   @@core/sys_core_views.sql
    
   @@core/wwv_flow_val.sql
   grant execute on sys.wwv_flow_val to ^APPUN.;
    
   @@core/wwv_flow_val.plb
    
   ALTER SESSION SET CURRENT_SCHEMA = ^APPUN;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   begin
       ^APPUN..wwv_flow_upgrade.remove_jobs();
       ^APPUN..wwv_flow_upgrade.create_jobs('^APPUN');
       ^APPUN..wwv_flow_upgrade.create_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.grant_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_remove('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_create('^APPUN');
   end;
   /
    
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
    
   drop context APEX$SESSION;
   create context APEX$SESSION using ^APPUN..WWV_FLOW_SESSION_CONTEXT;
    
   alter package sys.wwv_dbms_sql_^APPUN. compile;
   alter package sys.wwv_dbms_sql_^APPUN. compile body;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   set serveroutput on size unlimited
    
   declare
        l_apex_version varchar2(30);
        l_schemas sys.dbms_registry.schema_list_t;
   begin
       execute immediate 'drop package ^APPUN..WWV_FLOW_DB_VERSION';
       l_apex_version := ^APPUN..wwv_flows_release;
       sys.dbms_registry.loading('APEX','Oracle Application Express','validate_apex', '^APPUN');
       select username
         bulk collect into l_schemas
         from sys.all_users
        where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
        order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        sys.dbms_registry.loaded('APEX',l_apex_version);
        commit;
        sys.validate_apex;
   end;
   /

4. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

5. Run the apxdwngrd.sql script:

   SQL> @apxdwngrd.sql 

6. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.17 Reverting to Release 18.1 in a CDB

To revert to a previous Oracle Application Express release 18.1 in a CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex in the 18.1 source.
3. Create a new text file in that directory named apxdwngrd.sql consisting of the following:

   set define '^'
   set concat on
   set concat .
   set verify off
    
   set termout off
   column foo new_val LOG
   select 'apxdwngrd_' ||to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as foo
     from sys.dual;
   set termout on
   spool ^LOG
    
   @@core/scripts/set_appun.sql
    
   whenever sqlerror exit
   set serveroutput on size unlimited
    
   declare
       l_cnt    number := 0;
   begin
       select count(*) into l_cnt from sys.dba_users where username = '^APPUN';
       if l_cnt = 0 then
           dbms_output.put_line('^APPUN not found in this database.');
           raise program_error;
       end if;
   end;
   /
   whenever sqlerror continue
    
    
   prompt ...Create validate procedure in SYS schema and start registration
   @@core/validate_apex.sql x x ^APPUN
    
   grant inherit any privileges to ^APPUN;
    
   prompt Installing SYS views
    
   @@core/sys_core_views.sql
    
   @@core/wwv_flow_val.sql
   grant execute on sys.wwv_flow_val to ^APPUN.;
    
   @@core/wwv_flow_val.plb
    
   ALTER SESSION SET CURRENT_SCHEMA = ^APPUN;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   begin
       ^APPUN..wwv_flow_upgrade.remove_jobs();
       ^APPUN..wwv_flow_upgrade.create_jobs('^APPUN');
       ^APPUN..wwv_flow_upgrade.create_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.grant_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_remove('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_create('^APPUN');
   end;
   /
    
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
    
   drop context APEX$SESSION;
   create context APEX$SESSION using ^APPUN..WWV_FLOW_SESSION_CONTEXT;
    
   alter package sys.wwv_dbms_sql_^APPUN. compile;
   alter package sys.wwv_dbms_sql_^APPUN. compile body;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   set serveroutput on size unlimited
    
   declare
        l_apex_version varchar2(30);
        l_schemas sys.dbms_registry.schema_list_t;
   begin
       execute immediate 'drop package ^APPUN..WWV_FLOW_DB_VERSION';
       l_apex_version := ^APPUN..wwv_flows_release;
       sys.dbms_registry.loading('APEX','Oracle Application Express','validate_apex', '^APPUN');
       select username
         bulk collect into l_schemas
         from sys.all_users
        where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
        order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        sys.dbms_registry.loaded('APEX',l_apex_version);
        commit;
        sys.validate_apex;
   end;
   /
   

4. Create a second new text file in that directory named apxdwngrd_cdb.sql consisting of the following:

   set define '^'  
   whenever sqlerror exit  
   column :xe_home new_value OH_HOME NOPRINT
   variable xe_home varchar2(255)  
   set serverout on
   begin
   -- get oracle_home    
   sys.dbms_system.get_env('ORACLE_HOME',:xe_home);    
   if length(:xe_home) = 0 then
       sys.dbms_output.put_line(lpad('-',80,'-'));        
       raise_application_error (-20001,'Oracle Home environment variable not set' ); 
   end if;
   end;
   /
   whenever sqlerror continue  
   set termout off
   select :xe_home from sys.dual;
   set termout on  
   host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx181dgrd apxdwngrd.sql

5. Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

   On UNIX and Linux:

   $ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

6. Run the apxdwngrd_cdb.sql script:

   SQL> @apxdwngrd_cdb.sql
   

7. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.18 Reverting to Release 18.2 in a non-CDB

To revert to a previous Oracle Application Express release 18.2 in a non-CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex in the 18.2 source.
3. Create a new text file in that directory named apxdwngrd.sql consisting of the following:

   set define '^'
   set concat on
   set concat .
   set verify off
    
   set termout off
   column foo new_val LOG
   select 'apxdwngrd_' ||to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as foo
     from sys.dual;
   set termout on
   spool ^LOG
    
   @@core/scripts/set_appun.sql
    
   whenever sqlerror exit
   set serveroutput on size unlimited
    
   declare
       l_cnt    number := 0;
   begin
       select count(*) into l_cnt from sys.dba_users where username = '^APPUN';
       if l_cnt = 0 then
           dbms_output.put_line('^APPUN not found in this database.');
           raise program_error;
       end if;
   end;
   /
   whenever sqlerror continue
    
    
   prompt ...Create validate procedure in SYS schema and start registration
   @@core/validate_apex.sql x x ^APPUN
    
   grant inherit any privileges to ^APPUN;
    
   prompt Installing SYS views
    
   @@core/sys_core_views.sql
    
   @@core/wwv_flow_val.sql
   grant execute on sys.wwv_flow_val to ^APPUN.;
    
   @@core/wwv_flow_val.plb
    
   ALTER SESSION SET CURRENT_SCHEMA = ^APPUN;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   begin
       ^APPUN..wwv_flow_upgrade.remove_jobs();
       ^APPUN..wwv_flow_upgrade.create_jobs('^APPUN');
       ^APPUN..wwv_flow_upgrade.create_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.grant_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_remove('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_create('^APPUN');
   end;
   /
    
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
    
   drop context APEX$SESSION;
   create context APEX$SESSION using ^APPUN..WWV_FLOW_SESSION_CONTEXT;
    
   alter package sys.wwv_dbms_sql_^APPUN. compile;
   alter package sys.wwv_dbms_sql_^APPUN. compile body;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   set serveroutput on size unlimited
    
   declare
        l_apex_version varchar2(30);
        l_schemas sys.dbms_registry.schema_list_t;
   begin
       execute immediate 'drop package ^APPUN..WWV_FLOW_DB_VERSION';
       l_apex_version := ^APPUN..wwv_flows_release;
       sys.dbms_registry.loading('APEX','Oracle Application Express','validate_apex', '^APPUN');
       select username
         bulk collect into l_schemas
         from sys.all_users
        where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
        order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        sys.dbms_registry.loaded('APEX',l_apex_version);
        commit;
        sys.validate_apex;
   end;
   /

4. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

   On UNIX and Linux:

   $ sqlplus /nolog
   SQL> CONNECT SYS as SYSDBA
   Enter password: SYS_password
   

5. Run the apxdwngrd.sql script:

   SQL> @apxdwngrd.sql 

6. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.3.19 Reverting to Release 18.2 in a CDB

To revert to a previous Oracle Application Express release 18.2 in a CDB:

1. If you altered your images directory, revert it back to the release you want to revert to.
2. Change your working directory to apex in the 18.2 source.
3. Create a new text file in that directory named apxdwngrd.sql consisting of the following:

   set define '^'
   set concat on
   set concat .
   set verify off
    
   set termout off
   column foo new_val LOG
   select 'apxdwngrd_' ||to_char(sysdate, 'YYYY-MM-DD_HH24-MI-SS') || '.log' as foo
     from sys.dual;
   set termout on
   spool ^LOG
    
   @@core/scripts/set_appun.sql
    
   whenever sqlerror exit
   set serveroutput on size unlimited
    
   declare
       l_cnt    number := 0;
   begin
       select count(*) into l_cnt from sys.dba_users where username = '^APPUN';
       if l_cnt = 0 then
           dbms_output.put_line('^APPUN not found in this database.');
           raise program_error;
       end if;
   end;
   /
   whenever sqlerror continue
    
    
   prompt ...Create validate procedure in SYS schema and start registration
   @@core/validate_apex.sql x x ^APPUN
    
   grant inherit any privileges to ^APPUN;
    
   prompt Installing SYS views
    
   @@core/sys_core_views.sql
    
   @@core/wwv_flow_val.sql
   grant execute on sys.wwv_flow_val to ^APPUN.;
    
   @@core/wwv_flow_val.plb
    
   ALTER SESSION SET CURRENT_SCHEMA = ^APPUN;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   begin
       ^APPUN..wwv_flow_upgrade.remove_jobs();
       ^APPUN..wwv_flow_upgrade.create_jobs('^APPUN');
       ^APPUN..wwv_flow_upgrade.create_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.grant_public_synonyms('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_remove('^APPUN');
       ^APPUN..wwv_flow_upgrade.flows_files_objects_create('^APPUN');
   end;
   /
    
   ALTER SESSION SET CURRENT_SCHEMA = SYS;
    
   drop context APEX$SESSION;
   create context APEX$SESSION using ^APPUN..WWV_FLOW_SESSION_CONTEXT;
    
   alter package sys.wwv_dbms_sql_^APPUN. compile;
   alter package sys.wwv_dbms_sql_^APPUN. compile body;
    
   exec sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize);
    
   set serveroutput on size unlimited
    
   declare
        l_apex_version varchar2(30);
        l_schemas sys.dbms_registry.schema_list_t;
   begin
       execute immediate 'drop package ^APPUN..WWV_FLOW_DB_VERSION';
       l_apex_version := ^APPUN..wwv_flows_release;
       sys.dbms_registry.loading('APEX','Oracle Application Express','validate_apex', '^APPUN');
       select username
         bulk collect into l_schemas
         from sys.all_users
        where username in ('FLOWS_FILES','APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER','APEX_INSTANCE_ADMIN_USER')
        order by 1;
        sys.dbms_registry.update_schema_list('APEX', l_schemas);
        sys.dbms_registry.loaded('APEX',l_apex_version);
        commit;
        sys.validate_apex;
   end;
   /
   

4. Create a second new text file in that directory named apxdwngrd_cdb.sql consisting of the following:

   set define '^'  
   whenever sqlerror exit  
   column :xe_home new_value OH_HOME NOPRINT
   variable xe_home varchar2(255)  
   set serverout on
   begin
   -- get oracle_home    
   sys.dbms_system.get_env('ORACLE_HOME',:xe_home);    
   if length(:xe_home) = 0 then
       sys.dbms_output.put_line(lpad('-',80,'-'));        
       raise_application_error (-20001,'Oracle Home environment variable not set' ); 
   end if;
   end;
   /
   whenever sqlerror continue  
   set termout off
   select :xe_home from sys.dual;
   set termout on  
   host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b apx182dgrd apxdwngrd.sql

5. Start SQL*Plus and connect to CDB$ROOT of the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   On Windows:

   SYSTEM_DRIVE:\ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

   On UNIX and Linux:

   $ sqlplus /nolog 
   SQL> CONNECT SYS as SYSDBA 
   Enter password: SYS_password 
   

6. Run the apxdwngrd_cdb.sql script:

   SQL> @apxdwngrd_cdb.sql
   

7. See the next section, Removing the Oracle Application Express Release 19.1 Schema.

See Also:

• "Reverting the Images Directory"

• "Removing the Oracle Application Express Release 19.1 Schema"

C.3.1.4 Removing the Oracle Application Express Release 19.1 Schema

After you have reverted to the prior release you can remove the Oracle Application Express 19.1 schema.

• Removing the Oracle Application Express Release 19.1 Schema from a Non-CDB
  Start SQL*Plus and connect to the database and execute DROP USER APEX_190100 CASCADE; .
• Removing the Oracle Application Express Release 19.1 Schema from a CDB
  Create text files, start SQL*Plus and connect to the database execute @remove_apx191_usr.sql.

C.3.1.4.1 Removing the Oracle Application Express Release 19.1 Schema from a Non-CDB

Start SQL*Plus and connect to the database and execute DROP USER APEX_190100 CASCADE; .

To remove the release 19.1 schema from a non-CDB:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

2. Execute the following command:

   DROP USER APEX_190100 CASCADE;
   

   Once you have removed the Oracle Application Express 19.1 schema, you can now attempt the upgrade again.

C.3.1.4.2 Removing the Oracle Application Express Release 19.1 Schema from a CDB

Create text files, start SQL*Plus and connect to the database execute @remove_apx191_usr.sql.

To remove the release 19.1 schema from a CDB:

1. Create a new text file named remove_apx191_usr1.sql with the following contents:

   alter session set current_schema = SYS;
   drop user APEX_190100 cascade;
   

2. Create a second new text file named remove_apx191_usr.sql with the following contents:

   set define '^'
   whenever sqlerror exit
   column :xe_home new_value OH_HOME NOPRINT
   variable xe_home varchar2(255)
    
   set serverout on
   begin
   -- get oracle_home
       sys.dbms_system.get_env('ORACLE_HOME',:xe_home);
       if length(:xe_home) = 0 then
           sys.dbms_output.put_line(lpad('-',80,'-'));
           raise_application_error (
               -20001,
               'Oracle Home environment variable not set' );
       end if;
   end;
   /
   whenever sqlerror continue
    
   set termout off
   select :xe_home from sys.dual;
   set termout on
    
   host ^OH_HOME/perl/bin/perl -I ^OH_HOME/rdbms/admin ^OH_HOME/rdbms/admin/catcon.pl -b remove_apx51_usr remove_apx51_usr1.sql
   

3. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

4. Execute the following command:

   @remove_apx191_usr.sql
   

   Once you have removed the Oracle Application Express 19.1 schema, you can now attempt the upgrade again.

C.3.2 Removing Oracle Application Express from the Database

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role and execute the SQL> @apxremov.sql command.

This section describes how to remove the Oracle Application Express schema, synonyms, and users from the database without deleting the database.

Note:

Do not follow these steps if you have upgraded your database from a prior release, and still want to use the prior release of Oracle Application Express. For information about reverting to a prior release, see Reverting to a Previous Release. If you are not sure whether you have completed a new installation or an upgrade installation, follow the steps in Cleaning Up After a Failed Installation to verify if a previous release of Application Express exists in the database.

To remove Oracle Application Express from the database:

1. Change your working directory to the apex directory where you unzipped the Oracle Application Express software.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

3. Execute the following command:

   SQL> @apxremov.sql
   

4. After successfully removing Application Express using apxremov.sql, you must exit your current SQL*Plus session and reconnect before attempting another install using apexins.sql.

See Also:

• "Reverting to a Previous Release"

• "Cleaning Up After a Failed Installation"