Installation of Oracle APEX in Windows/ Oracle XE and configuring ORDS in Tomcat

Oracle APEX 18.2 installation in Oracle XE and configuring ORDS in Tomcat 

               In this article, we are going to see the installation of Oracle APEX in Oracle DB (XE). Oracle APEX is a database driven application that is built using PL/SQL Web Toolkit. Installing Oracle APEX is essentially creating database schemas and database objects.

To start with installation, I have installed Oracle 11g XE and will be using it entirely in this article.

Oracle Application Express release 18.2 requires an Oracle Database release 11.2.0.4 or later, 

including Enterprise Edition and Express Edition (Oracle Database XE).

Oracle DB 9i or later comes with APEX installed in Oracle binaries. You can see the image below which shows the APEX schemas which exist after the installation of Oracle XE 11g

Here we can see 3 different users namely.

• APEX_040000
• APEX_PUBLIC_USER
• FLOWS_FILES

APEX_XXXXXX - This schema is popularly called as APEX Engine. The application pages are dynamically generated from the engine each time a request is made. Here XXXXXX varies based on the version of the APEX. Example: APEX_0500000 indicates version 5 of APEX

APEX_PUBLIC_USER - Schema which is used to connect to the Database and to execute the APEX application. This user has minimal DB privilege granted on creation. In APEX 5.0 these are as follows: Create Session and Select, Insert, Update, delete on WWV_FLOW_FILE_OBJECTS$.

FLOWS_FILES - This schema is the owner of the WWV_FLOW_FILE_OBJECTS$ table. This table is used as a temporary storage area when any file is uploaded in the APEX application.

Before we install the Oracle APEX 18.2 first I am going to uninstall the existing version which is Oracle 4.0.2

select version from dba_registry where comp_id = 'APEX';

To do this download the APEX from Oracle Website.

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

Perform the below steps to uninstall the existing Oracle APEX: 

1. Extract the downloaded zip file.
2. Open cmd prompt
3. Navigate to the extracted APEX folder (Let us call this folder as APEX Home. I will be using convention throughout this article to indicate this folder)
   
   
   
   Note: Once the zip file is extracted I renamed the folder as apex as shown in the pic above.
4. Connect to the SYS user. sqlplus SYS AS SYSDBA
5. Run the following command: @apxremov.sql
   This should take approximately 10mins to uninstall.
   You can also uninstall the APEX instance by running the file in apxremov.sql
   oraclexe\app\oracle\product\11.2.0\server\apex

Perform the below steps to install APEX 18.2

1. Open cmd prompt
2. Navigate to APEX Home folder
3. Connect to the SYS user. sqlplus SYS AS SYSDBA
4. Create a tablespace for APEX Engine
   
   
5. Run the following command
   @apexins.sql APEX_TS APEX_TS TEMP /i/
   
   
   
   @apexins.sql tablespace_apex tablespace_files tablespace_temp images
   Where:
   tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
   tablespace_files is the name of the tablespace for the Oracle Application Express files user.
   tablespace_temp is the name of the temporary tablespace or tablespace group.
   images is the virtual directory for Oracle Application Express images.
   Above step should take approximately 30 mins.

Once the installation of APEX is completed check the new users which were created in this process.

APEX_INSTANCE_ADMIN_USER - Ths user provides the REST admin interface. The REST Administration Interface enables Instance administrators to automatically fetch usage metrics for an Oracle Application Express instance with a REST client.

Now it is the time for configuring RESTful Services in Oracle APEX.

Perform the below steps to install to configure the RESTful services.

1. Open cmd prompt
2. Navigate to APEX Home folder
3. Connect to the SYS user. sqlplus SYS AS SYSDBA
Run the following command
@apex_rest_config.sql
                           

As shown in the image above you will be prompted to enter a password for the APEX_LISTENER and APEX_REST_PUBLIC_USER accounts.

APEX_LISTENER: Required only if using RESTful Services defined in Application Express.
The database user used to query RESTful Services definitions stored in Oracle Application Express if RESTful Services defined in Application Express workspaces are being accessed

APEX_REST_PUBLIC_USER: Required only if using 
4. RESTful Services defined in Application Express. 
5. The database user used when invoking Oracle Application Express RESTful Services if RESTful Services defined in Application Express workspaces are being accessed



6. Set the XDB port to 0
   EXEC DBMS_XDB.SETHTTPPORT(0);
   
   
7. Unlock the Users APEX_PUBLIC_USER and APEX_REST_PUBLIC_USER
   
   ALTER USER APEX_PUBLIC_USER IDENTIFIED BY ORACLE ACCOUNT UNLOCK;
   ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY ORACLE ACCOUNT UNLOCK;

Apache Tomcat Installation and Configuration:

Download the Apache Tomcat (I used version 9) from the website https://tomcat.apache.org/

Extract the zip file and install it by double-clicking on the .exe file.

You see the below screen. Please click on the components which I have selected in the image below and click Next.

I have used the port 8282 as shown in the image. You can use any port number as per your convenience.

Select the JRE path in the next step.

Once the installation is completed a tiny icon appears in the taskbar tray as shown below. If it is green it depicts that it is running, red for suspended.

Installing ORDS:

Oracle REST Data Services is the recommended web listener for APEX. It allows the creation of Web Services in APEX.

Download ORDS from the Oracle website http://www.oracle.com/technetwork/developer-tools/index.html

Note: ORDS 3 requires Java version 7 or higher.

1. Extract the downloaded zip file.
2. Go to the extracted ORDS folder (Let us call this folder as ORDS Home. I will be using convention throughout this article to indicate this folder).
   Create a new folder and name it as ordsconfig.(This is the location to store the configuration data for the ORDS)
   Here you can find a file named ords_params.properties inside params folder.
   
   Installing ORDS essentially means configuring the ORDS.war file which you can find in the ORDS Home with appropriate parameters
3. Edit ords_params.properties in the text editor  (Optional step). I have added localhost as my hostname with other details such as my Web server port etc.
   
   
4. Open cmd prompt.
5. Navigate to the ORDS Home directory where ords.war file is located.
   Run the following command
   
   java -jar ords.war configdir C:\ords_install\config-ords
   
6. Ensure you are still in the ORDS Home directory in cmd prompt and run the below command.
   
   java -jar ords.war
   
   You will be prompted for inputs throughout the process which you have to type.
   
   I have put the screenshot of the configuration steps below. Please ensure you remember the passwords for APEX_LISTENER and APEX_REST_PUBLIC_USER which you set earlier during configuration of the RESTful Services in Oracle APEX.
   
   
   
   You can notice the above steps creates 2 more new users ORDS_PUBLIC_USER and ORDS_METADATA
   
   ORDS_METADATA: Owner of the PL/SQL packages used for implementing many Oracle REST Data Services capabilities. ORDS_METADATA is where the metadata about Oracle REST Data Services-enabled schemas is stored.
   It is not accessed directly by Oracle REST Data Services; the Oracle REST Data Services application never creates a connection to the ORDS_METADATA schema. The schema password is set to a random string, connect privilege is revoked, and the password is expired.
   
   ORDS_PUBLIC_USER: User for invoking RESTful Services in the Oracle REST Data Services-enabled schemas.
7. To deploy ORDS to Apache Tomcat
   Copy the ords.war from ORDS Home to the webapps folder of Apache Tomcat installation directory which is  C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps in my case.
8. Create a new folder by name 'i' in the location C:\Program Files\Apache Software Foundation\Tomcat 9.0\
   Copy the contents of images folder from APEX_Home into the newly created 'i' folder in Tomcat Server
9. Stop and then Start the Tomcat Service as shown below from the taskbar.
   
10. Now you should be able to run the below URL in your browser
    http://localhost:8282/ords
    
    You should be able to see the below screen.
    
    
11. If you are wondering what password you should be entering here. We are still one step short to finish the installation.
    Open cmd prompt
    Navigate to APEX Home folder
    Connect to the SYS user. sqlplus SYS AS SYSDBARun the following command
    @apxchpwd.sql
    Enter the password when prompted, without changing the Username [ADMIN]
    Use this password to login in the above pic.