In this guide, I'll walk you through the installation process of Oracle Application Express (APEX), Oracle REST Data Services (ORDS), and Tomcat in Windows.

Following a step-by-step approach, we'll ensure a clean setup for all components.

Prerequisites

Oracle Database Installation

Make sure Java is installed on your machine. if not install same (preferably >= jdk17)
Ensure Oracle DB is installed (Version: 19c in our case).

Check Oracle DB version:

  SELECT banner FROM v$version;

  Output

  BANNER
  --------------------------------------------------------------------------------
  Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Install Any IDE of your choice such as SQL Developer or TOAD.
Data modeling software (optional). SQL Developer can be used for ERD visualization.

Uninstall APEX (if already installed) :

  -- Check APEX Version from APEX_RELEASE Table:
  SELECT * FROM APEX_RELEASE;

  -- Check APEX Installation Status from DBA_REGISTRY Table:
  -- If APEX is installed, you should see a row with COMP_ID as 'APEX'

  SELECT * FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';

If you see any row indicating APEX is already installed, change the working directory to APEX using cd apex. Start SQL*Plus and connect to the pluggable database where APEX is installed as SYS, specifying the SYSDBA role, and execute @apxremov.sql. Then exit SQL*Plus and reconnect before attempting a new install using apexins.sql.

Fresh APEX Installation:

Download and unzip APEX from Oracle's official website.
This will create a folder named 'apex24_2', and you should navigate to the 'apex' folder using cd apex.

Start SQL*Plus and make sure you connect to your PDB, not the "root" of the container database, since APEX should not be installed there.


 sqlplus / as sysdba

 --create PDB named DEMO
 create pluggable database demo admin user demo_admin identified by <password for pdb> file_name_convert=('pdbseed', 'demo');

 -- Chnage container to created DEMP pdb
 ALTER SESSION SET CONTAINER = DEMO;

 -- Create APEX tablespace
 create tablespace apex datafile 'E:\app\oradata\ORCL\demo\apex.dbf' size 300m autoextend on next 10m;

 -- Install APEX lowcode environment
 @apexins.sql APEX APEX TEMP /i/

 Note only for runtime environment run @apxrtins.sql. usually in PROD environment.
 -- Alternatively, utilize the below command:
 sqlplus sys@//localhost:port/pdb as sysdba @apexins sysaux sysaux temp /i/

This will take some time.....

Upon execution of the apexins.sql or apxrtins.sql , a log file generated in the apex directory, adhering to the format installYYYY-MM-DD_HH24-MI-SS.log. In the event of a successful installation, the log file will display the message: "Thank you for installing Oracle APEX. Oracle APEX is installed in the APEX_240200 schema"

Note that few errors in log does not necessarily indicate a failed installation.

Unlocking the APEX_PUBLIC_USER Account:

 alter user apex_public_user identified by oracle account unlock;

Execute @apxchpwd.sql to configure and update the instance administrator username and password. Ensure to provide a robust password; otherwise, the script will prompt repeatedly until a strong password is provided and the process is successful.
To check instance administrator user (default ADMIN ) details -
```
 select * from APEX_240200.WWV_FLOW_FND_USER;
```
💡

Let's take a quick look at the "WWV_FLOW" tables:

The "WWV" prefix stands for "Web Warrior Virtual." It's a common naming style in Oracle APEX, used for internal components and objects.

When you install Oracle APEX, a user is created (like APEX_230200, which can change with different versions). This user holds the "WWV_FLOW" tables, which store important metadata and runtime data for APEX apps. For example:
- WWV_FLOW_USERS: Stores user information for APEX. Example query: SELECT * FROM APEX_230200.WWV_FLOW_USERS;
- WWV_FLOW_SESSION: Keeps track of session details for APEX apps. Example query: SELECT * FROM APEX_230200.WWV_FLOW_SESSION;
- WWV_FLOW_FILES: Manages files related to APEX apps. Example query: SELECT * FROM APEX_230200.WWV_FLOW_FILES;

APEX Installation Verification

Check APEX installation:

  SELECT * FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
  If APEX is installed, you should see a row with COMP_ID as 'APEX'.

Check APEX release version:
```
  SELECT * FROM APEX_RELEASE;
```

Verify instance administrator user:

  SELECT * FROM APEX_240200.WWV_FLOW_FND_USER;

Prevent ADMIN from changing the password on first login:

  UPDATE APEX_240200.WWV_FLOW_FND_USER SET CHANGE_PASSWORD_ON_FIRST_USE='N';

Now let's move to ORDS installation.

First Uninstall ORDS (if already installed):

ORDS Installation: (Official Getting Started)

Verify if ORDS already installed ords --version
To uninstall ORDS run : ords uninstall
Now download the latest ORDS, unzip it, and navigate to the ords directory using cd E:\ords\.
Add ords bin to the environment PATH; otherwise, you will need to use the absolute path to run ords.
Create a system environment variable ORDS_CONFIG and assign it a folder that will be used for ORDS configuration (ensure this folder is not inside ords directory). for use ORDS_CONFIG = E:\ord_config
Lets install in standalone mode ( interactively) : ords install
Note : during installation in option #9 for apex static file path give //(double slash in path separator) insted of /(single slash) else / will not be interpreted correctly and merged to a single string.
After installation validate the version -ords --version
SELECT * FROM DBA_USERS WHERE USERNAME LIKE '%ORDS%'

ORDS_PUBLIC_USER
ORDS_METADATA
ORDSYS
To start ords serve
That's it!! You should now be able to login http://localhost:8080/ords/

The structure of the link to the Oracle APEX Administration Services is as follows: http://localhost:8080/ords/apex_admin

The structure of the link to the Oracle APEX development environment is as follows: http://localhost:8080/ords/apex

Workspace: internal
Username: admin
Password: <passwd you had provided>
Now, the challenge is that we need to manually start ORDS using the "ords serve" command every time we want to use APEX. To solve this, we should deploy the ords.war file into Tomcat, so ORDS starts automatically when Tomcat initializes during a system reboot. Let's explore the process of installing and configuring Tomcat for ORDS deployment.
Download and set up Tomcat, configuring it to utilize port 8080
Visit http://localhost:8080/ to ensure Apache Tomcat is up and running.
Copy apex images folder as i (~\apex\images) to Tomcat webapps folder (C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\i)
Copy the ords.war file to the Tomcat webapps directory (~\webapps), and note that you can rename it to any name you choose, but you must use the same name after host:port.
Restart the ORDS application from the Apache server .
Set the system environment variable JAVA_TOOL_OPTIONS to -Dconfig.url=E:\app\config_ords -Xms1024M -Xmx1024M (representing the ORDS Config path).
Modify the Apache Tomcat service startup type to Automatic and reboot the machine. Following this, everything should be operational.
If a 503 error (Service Unavailable) or ORA-28000 error occurs, unlock the following accounts:
- APEX_REST_PUBLIC_USER
- APEX_INSTANCE_ADMIN_USER (ignore if an error occurs for this user)
- APEX_PUBLIC_USER
- APEX_LISTENER
Now you can always access Apex at http://localhost:8080/ords

Some valuable links for additional information:

To delve deeper into ORDS installation and configuration, consult the Oracle Oracle ORDS official Quick Start Guide
For further exploration of ORDS details and resources, visit the official Oracle Page dedicated to ORDS.
If you're interested in installing ORDS on Tomcat for Linux systems (ORDS Version 22.1 onward), you can find guidance here.
Similarly, for Windows users looking to install ORDS on Tomcat, refer to this resource here.
For older version ords install you can refer to this or watch this short video.

FAQ

💡

How to create a pdb?

Example : create pluggable database demo admin user demo_admin identified by <password for pdb> file_name_convert=('pdbseed', 'demo');

SQL> show user con_name
USER is "SYS"

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL> create pluggable database demo admin user demo_admin identified by <password for pdb> file_name_convert=('pdbseed', 'demo');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 DEMO                           READ WRITE NO

💡

How to create a tablespace?

Before creating tablespace - check if OMF is enabled for your database,
SELECT name, value FROM v$parameter WHERE name = 'db_create_file_dest';

If the query returns a not null value for 'db_create_file_dest', OMF is enabled,
create tablespace apex datafile size 200M autoextend on next 10m maxsize unlimited;

If it returns a null value for 'db_create_file_dest', OMF is not enabled,
create tablespace apex datafile 'E:\app\oradata\ORCL\demo\apex.dbf' size 300m autoextend on next 10m;

that parameter names and values may vary based on the Oracle Database version and configuration.

💡

Difference between a Full and runtime apex environment installation?

Full Development Environment:

Comprehensive Toolset: Equipped with Application Builder, SQL Workshop, and Team Development, empowering developers with robust capabilities for application creation, customization, and collaborative development.

Development Lifecycle Hub: Serves as the central workspace for iterative prototyping, extensive testing, and meticulous debugging, facilitating efficient application development from inception to deployment readiness.

To install the Full Development Environment, execute the following command:

@apexins.sql tablespace_apex tablespace_files tablespace_temp images

Parameters:

tablespace_apex: Name of the tablespace for the Oracle APEX application user.
tablespace_files: Name of the tablespace for the Oracle APEX files user.
tablespace_temp: Name of the temporary tablespace or tablespace group.
images: Virtual directory for Oracle APEX images.

Example:

@apexins.sql SYSAUX SYSAUX TEMP /i/

If encountering the error SP2-0310: unable to open file "apexins.sql", exit SQL*Plus and ensure the working directory is correctly set to the installation file location.

Runtime Environment:

Production Deployment Hub: Exclusively focused on deploying and serving APEX applications to end-users, ensuring seamless user experiences and optimal application performance.

Secure Execution Realm: Stripped of development tools, it prioritizes application security, scalability, and resource optimization, safeguarding production environments while granting controlled access to application functionalities and underlying database assets.

To install the Runtime Environment, execute the following command:

@apxrtins.sql tablespace_apex tablespace_files tablespace_temp images

Parameters:

tablespace_apex: Name of the tablespace for the Oracle APEX application user.
tablespace_files: Name of the tablespace for the Oracle APEX files user.
tablespace_temp: Name of the temporary tablespace or tablespace group.
images: Virtual directory for Oracle APEX images.

Example:

@apxrtins.sql SYSAUX SYSAUX TEMP /i/

💡

Which users were created during apex installations?

During Oracle APEX installation, several database accounts are created to manage different aspects of the application:

APEX_240200: Owns the Oracle APEX schema and metadata.
FLOWS_FILES: Owns the uploaded files within Oracle APEX.
APEX_PUBLIC_USER: A minimally privileged account utilized for Oracle APEX configuration with ORDS. In upgrades, it is created if absent.
Additional accounts:
- APEX_REST_PUBLIC_USER: Used for invoking RESTful Services definitions stored in Oracle APEX.
- APEX_LISTENER: Used to query RESTful Services definitions stored in Oracle APEX.

APEX, ORDS, and TOMCAT INSTALLATION