(For more resources related to this topic, see here.)

Let's take a look at the history and background of APEX.

History and background

APEX is a very powerful development tool, which is used to create web-based database-centric applications. The tool itself consists of a schema in the database with a lot of tables, views, and PL/SQL code. It's available for every edition of the database. The techniques that are used with this tool are PL/SQL, HTML, CSS, and JavaScript.

Before APEX there was WebDB, which was based on the same techniques. WebDB became part of Oracle Portal and disappeared in silence. The difference between APEX and WebDB is that WebDB generates packages that generate the HTML pages, while APEX generates the HTML pages at runtime from the repository. Despite this approach APEX is amazingly fast.

Because the database is doing all the hard work, the architecture is fairly simple. We only have to add a web server. We can choose one of the following web servers:

Oracle HTTP Server (OHS)
Embedded PL/SQL Gateway (EPG)
APEX Listener

APEX became available to the public in 2004 and then it was part of version 10g of the database. At that time it was called HTMLDB and the first version was 1.5. Before HTMLDB, it was called Oracle Flows , Oracle Platform, and Project Marvel. Throughout the years many versions have come out and at the time of writing the current version is 4.1.1. These many versions prove that Oracle has continuously invested in the development and support of APEX. This is important for the developers and companies who have to make a decision about which techniques to use in the future. According to Oracle, as written in their statement of direction, new versions of APEX will be released at least annually. The following screenshot shows the home screen of the current version of APEX:

prepare-and-build-img-0

Home screen of APEX

For the last few years, there is an increasing interest in the use of APEX from developers. The popularity came mainly from developers who found themselves comfortable with PL/SQL and wanted to easily enter the world of web-based applications. Oracle gave ADF a higher priority, because APEX was a no cost option of the database and with ADF (and all the related techniques and frameworks from Java), additional licenses could be sold.

Especially now Oracle has pointed out APEX as one of the important tools for building applications in their Oracle Database Cloud Service, this interest will only grow. APEX shared a lot of the characteristics of cloud computing, even before cloud computing became popular. These characteristics include:

Elasticity
Roles and authorization
Browser-based development and runtime
RESTful web services (REST stands for Representational State Transfer)
Multi-tenant
Simple and fast to join

APEX has outstanding community support, witnessed by the number of posts and threads on the Oracle forum. This forum is the most popular after the database and PL/SQL.

Oracle itself has some websites, based on APEX. Among others there are the following:

Oracle uses quite a few internal APEX applications.

Oracle also provides a hosted version of APEX at http://apex.oracle.com. Users can sign up for free for a workspace to evaluate and experiment with the latest version of APEX. This environment is for evaluations and demonstrations only, there are no guarantees! Apex.oracle.com is a very popular service—more than 16,000 workspaces are active. To give an idea of the performance of APEX, the server used for this service used to be a Dell Poweredge 1950 with two Dual Core Xeon processors with 16 GB.

Installing APEX

In this section, we will discuss some additional considerations to take care of while installing APEX. The best source for the installation process is the Installation Guide of APEX.

Runtime or full development environment

On a production database, the runtime environment of APEX should be installed. This installation lacks the Application Builder and the SQL Workshop. Users can run applications, but the applications cannot be modified. The runtime environment of APEX can be administered using SQL*Plus and SQL Developer. The (web interface) options for importing an application, which are only available in a full development environment, can be used manually with the APEX_INSTANCE_ADMIN API. Using a runtime environment for production is recommended for security purposes, so that we can be certain that installed applications cannot be modified by anyone.

On a development environment the full development environment can be installed with all the features available to the developers.

Build status

Besides the environment of APEX itself, the applications can also be installed in a similar way. When importing or exporting an application the Run Application Only or Run and Build Application options can be selected.

Changing an application to Run Application Only can be done in the Application Builder by choosing Edit Application Properties. Changing the Build Status to Run and Build Application can only be done as the admin user of the workspace internal. In the APEX Administration Services, choose Manage Workspaces and then select Manage Applications | Build Status.

Another setting related to the Runtime Only option could be used in the APEX Administration Services at instance level. Select Manage Instance and then select Security. Setting the property Disable Workspace Login to yes, acts as setting a Runtime Only environment, while still allowing instance administrators to log in to the APEX Administration Services.

Tablespaces

Following the install guide for the full development environment, at a certain moment, we have to run the following command, when logged in as SYS with the SYSDBA role, on the command line:

@apexins tablespace_apex tablespace_files tablespace_temp
images

The command is explained as follows:

tablespace_apex is the name of the tablespace that contains all the objects for the APEX application user.
tablespace_files is the name of the tablespace that contains all the objects for the APEX files user.
tablespace_temp is the name of the temporary tablespace of the database.
images will be the virtual directory for APEX images. Oracle recommends using /i/ to support the future APEX upgrades.

For the runtime environment, the command is as follows:

@apxrtins tablespace_apex tablespace_files tablespace_temp
images

In the documentation, SYSAUX is given as an example for both tablespace_apex and tablespace_files. There are several reasons for not using SYSAUX for these tablespaces, but to use our own instead:

SYSAUX is an important tablespace of the database itself
We have more control over sizing and growth
It is easier for a DBA to manage tablespace placement
Contention in the SYSAUX tablespace is less occurring
It's easier to clean-up older versions of APEX
And last but not least, it's only an example

Converting runtime environment into a full development environment and vice versa

It's always possible to switch from a runtime to a production environment and vice versa. If you want to convert a runtime to a full development environment log in as SYS with the SYSDBA role and on the command line type @apxdvins.sql. For converting a full development to a runtime environment, type @apxdevrm—but export websheet applications first.

Another way to restrict user access can be accomplished by logging in to the APEX Administration Services, where we can (among others) manage the APEX instance settings and all the workspaces. We can do that in two ways:

http://server:port/apex/apex_admin: Log in with the administrator credentials
http://server:port/apex/: Log in to the workspace internal, with the administrator credentials

After logging in, perform the following steps:

Go to Manage Instance.
Select Security.
Select the appropriate settings for Disable Administrator Login and Disable Workspace Login. These settings can also be set manually with the APEX_INSTANCE_ADMIN API.

Choosing a web server

When using a web-based development and runtime environment, we have to use a web server.

prepare-and-build-img-1

Architecture of APEX

The choice of a web server and the underlying architecture of the system has a direct impact on performance and scalability. Oracle provides us with three choices:

Oracle HTTP Server (OHS)
Embedded PL/SQL Gateway (EPG)
APEX Listener

Simply put, the web server maps the URL in a web browser to a procedure in the database. Everything the procedure prints with sys.htp package, is sent to the browser of the user. This is the concept used by tools such as WebDB and APEX.

OHS

The OHS is the oldest of the three. It's based on the Apache HTTP Server and uses a custom Apache Module named as mod_plsql:

prepare-and-build-img-2

Oracle HTTP Server

In release 10g of the database, OHS was installed with the database on the same machine. Upward to the release 11g, this is not the case anymore. If you want to install the OHS, you have to install the web tier part of WebLogic. If you install it on the same machine as the database, it's free of extra licence costs. This installation takes up a lot of space and is rather complex, compared with the other two. On the other hand, it's very fl exible and it has a proven track record. Configuration is done with the text files.

EPG

The EPG is part of XML DB and lives inside the database. Because everything is in the database, we have to use the dbms_xdb and dbms_epg PL/SQL packages to configure the EPG. Another implication is that all images and other files are stored inside the database, which can be accessed with PL/SQL or FTP, for example:

prepare-and-build-img-3

Embedded PL/SQL gateway

The architecture is very simple. It's not possible to install the EPG on a different machine than the database. From a security point of view, this is not the recommended architecture for real-life Internet applications and in most cases the EPG is used in development, test, or other internal environments with few users.

APEX Listener

APEX Listener is the newest of the three, it's still in development and with every new release more features are added to it. In the latest version, RESTful APIs can be created by configuring resource templates. APEX Listener is a Java application with a very small footprint. APEX Listener can be installed in a standalone mode, which is ideal for development and testing purposes. For production environments, the APEX Listener can be deployed by using a J2EE compliant Application Server such as Glassfish, WebLogic, or Oracle Containers for J2EE:

prepare-and-build-img-4

APEX Listener

Configuration of the APEX Listener is done in a browser. With some extra configuration, uploading of Excel into APEX collections can be achieved. In future release, other functionalities, such as OAuth 2.0 and ICAP virus scanner integration, have been announced.

prepare-and-build-img-5

Configuration options of the APEX Listener

Like OHS, an architectural choice can be made if we want to install APEX Listener on the same machine as the database. For large public applications, it's better to use a separate web server.

Many documents and articles have been written about choosing the right web server. If you read between the lines, you'll see that Oracle more or less recommends the use of APEX Listener. Given the functionality, enhanced security, file caching, fl exibility of deployment possibilities, and feature announcements makes it the best choice.

Creating a second administrator

When installing APEX, by default the workspace Internal with the administrator user Admin is created. Some users know more than the average end user. Also, developers have more knowledge than the average user. Imagine that such users try to log in to either the APEX Administration Services or the normal login page with the workspace Internal and administrator Admin, and consequently use the wrong password. As a consequence, the Admin account would be locked after a number of login attempts. This is a very annoying situation, especially when it happens often. Big companies and APEX Hosting companies with many workspaces and a lot of anonymous users or developers may suffer from this. Fortunately there is an easy solution, creating a second administrator account.

prepare-and-build-img-6

If the account is already locked, we have to unlock it first. This can be easily done by running the apxchpwd.sql script, which can be found in the main Apex directory of the unzipped installation file of APEX:

Start SQL*Plus and connect as sys with the sysdba role
Run the script by entering @apxchpwd.sql.
Follow the instructions and enter a new password.

Now we are ready to create a second administrator account. This can be done in two ways, using the web interface or the command line.

APEX web interface

Follow these steps to create a new administrator, using the browser.

First, we need to log in to the APEX Administrator Services at http://server:port/apex/. Log in to the workspace Internal, with the administrator credentials

After logging in, perform the following steps:

Go to Manage Workspaces.
Select Existing Workspaces.
You can also select the edit icon of the workspace Internal to inspect the settings. You cannot change them. Select Cancel to return to the previous screen.
Select the workspace Internal by clicking on the name.
Select Manage Users. Here you can see the user Admin.
You can also select the user Admin to change the password. Other settings cannot be changed. Select Cancel or Apply Changes to return to the previous screen.
Select Create User. Make sure that Internal is selected in the Workspace field and APEX_xxxxxx is selected in Default Schema, and that the new user is an administrator. xxxxxx has to match your APEX scheme version in the database, for instance, APEX_040100.
Click on Create to finish.

Settings for the new administrator

Command line

When we still have access, we can use the web interface of APEX. If not we can use the command line:

Start SQL*Plus and connect as SYS with the SYSDBA role.
Unlock the APEX_xxxxxx account by issuing the following command:
```
alter user APEX_xxxxxx account unlock;
```
Connect to the APEX_xxxxxx account. If you don't remember your password, you can just reset it, without impacting the APEX instance.

Execute the following (use your own username, e-mail, and password):

BEGIN
wwv_flow_api.set_security_group_id
(p_security_group_id=>10);
wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'second_admin',
p_email_address => 'email@company.com',
p_web_password => 'second_admin_password') ;
END;
/
COMMIT
/

The new administrator is created. Connect again as SYS with the SYSDBA role and lock the account again with the following command:
```
alter user APEX_xxxxxx account lock;
```
Now you can log in to the Internal workspace with your newly created account and you'll be asked to change your password.

Other accounts

When an administrator of a developer workspace loses his/her password or has a locked account, you can bring that account back to life by following these steps: