Thursday, August 10, 2017

Set up an APEX development environment

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

A development environment can mean different things to different people. You can read the definitions on Wikipedia, TechTarget, Techopedia to name a few.

For me, it means on one side you need an environment where you can develop in. In case of an Oracle APEX project this means, you need at least an Oracle schema and an APEX workspace linked to the Oracle schema in which you can create your APEX application. Now if we take this a step further, it means you need an Oracle Database and APEX installed, which includes a webserver and ORDS (Oracle REST Data Services). And again one level higher it means you need a machine (that is most likely connected to the internet).

On the other side you have everything around it: some tools you use, something to plan and track the development, something to store your code (version control), something where you can collaborate with other team members. If we include this we talk more about software development in general which touches on application life cycle management (moving to Test, QA, Production).

Lets first focus om the first part; the infrastructure. Unless we get millions of concurrent users, I don't think our multiplication table project needs much infrastructure :)

I will walk over the different options we have when doing an APEX project.


You basically have your own machines and installed all software on there and you manage everything yourself. Many of my customers still have an on-premise infrastructure. Unless you see our laptops as on-premise, at my own company we never had on-premise, we've always been in the cloud. While running in the cloud you still have different options of the level of responsibility and flexibility you have yourself. We use cloud services from Amazon, Microsoft Azure, Digital Ocean and the Oracle Cloud. More on that further on.

This is the fastest way to get started, we just sign up for an account on and get an APEX workspace and underlying Oracle schema. also contains the latest version of Oracle Application Express (APEX) as the development team uses this service to roll-out and test new features and versions first. If you don't have an account yet, I recommend to create one. I've requested a workspace for this project too (takes less than 2 minutes), which you can see here:

Now we can use this service to develop and test, but we can't keep our application here forever, as we only have 25MB of space and you're not supposed to run production applications here. It's also not possible to connect to this service from SQL Developer or other tools, so it's a bit limiting. But in a future post "Build the Oracle APEX application: the framework" you will see why I still setup this workspace and I recommend everybody to have at least one workspace on :)

Oracle pre-built Developer VM

Another fast way of being up and running is to download the Oracle pre-built Developer VM for Virtual Box. Everything is already setup for us, but we would need to put it somewhere on a server where it can be accessible through the internet by more people. I typically don't use this solution to do my development, only to test something locally.

Free Oracle Database Cloud Service

This service from Oracle has been announced a few months ago, but is not yet available at the time of writing. The rumours are you get 1GB of data, have APEX 5.1 or higher, you're running in the Oracle Cloud and can run production applications there. This solution would have been ideal for our multiplication table project!

Cloud with OXAR

If you're searching to build a low cost infrastructure based on Oracle XE (the free Oracle database), you really should use one of OraOpenSource projects, called OXAR (read "Oscar"). It sets up an entire machine by itself, it's completely scripted. The only thing you have to do is download the Oracle software (XE, ORDS, APEX), get a low cost virtual machine (for example at Digital Ocean or Amazon), clone the OXAR git repository on that machine, edit the config file to point to the downloaded files and run the script. That is it! Even the most popular print engine for Oracle APEX, APEX Office Print (AOP), comes installed with it :)

The biggest benefit of an Oracle XE infrastructure, it's a very cheap solution, the downside is that Oracle XE is still Oracle DB 11g with many restrictions, so we can't use some 12c features (like JSON in DB, ...). Rumours are there will be an Oracle XE 12c version coming out in the future. Maybe even more important to know; there's no Oracle support for this database. Although at first sight you might not need it, it also implies you can't download Oracle APEX patches, as you don't have a CSI number. If you have a CSI number for another infrastructure and downloaded the APEX patches, you can obviously patch your APEX in Oracle XE. If not you would need to download and reinstall the full version of APEX every time, which becomes time consuming.

So lets look at our multiplication table project, we can definitely use OXAR for this as it fits within the XE limits. I subscribed at Digital Ocean for a CentOS Droplet for 10 USD/month and ran OXAR on there. It took me about 1 hour to be up and running (mostly the OXAR script was running by itself).


Docker has gained a lot of popularity in the last years (since 2013/2014). Docker is a tool designed to make it easier to create, deploy, and run applications by using containers. Oracle provides official Docker Images for many of their products, see also the Docker store. If you're new to Docker, you can read more here. I also like this article which covers the main benefits of Docker. You can also watch this video how to build and deploy an Oracle Database Docker Image to the Oracle Container Cloud Service. So far I've used Docker only for test instances.

I typically use the docker image of Daniel Hochleitner (aka Mr. APEX Plugin). Daniel itself says his docker image isn't very "dockerish" because installing all components in one container is not the concept how it should work, so the benefits of microservices, security, single components and things like that are lost in a cloud environment with this particular docker image. But the nice thing about this image, just like OXAR, it includes all you need: ORDS, APEX, Tomcat, AOP, ... For me this image is ideal to test something quickly. A docker container has less overhead than a full VM and you still have some benefits of Docker.

Some pictures while building and creating the container:

For the multiplication table project I won't use this option. But I did want to cover it, as it might be a good solution for you, especially if your company is already into containers. Oh and did you know Docker has a competitor too? It's called rkt, we will probably hear more about that in the coming years.

Hosting companies

Next to Oracle, there're a number of hosting companies that provide Oracle APEX hosting, but I've no experience using them for my own projects, but it might be an option for yours. I know some AOP cloud customers that connect from their SkillBuilders, Revion and AppsHosting account.


As previously mentioned in the on-premise section, there're a number of options you have. You can just go with a (virtual) machine, or a machine that is pre-configured, a complete managed machine and database etc. They also call it infrastructure as a service (IaaS), platform as a service (PaaS) or software as a service (SaaS) or data as a service (DaaS). For an Oracle APEX project it's most important you have the Oracle database. Oracle put the last few years a lot of focus on the cloud, here you find the Oracle Database Cloud offering. There're also alternatives by many other providers for example Amazon AWS: EC2 (virtual machine), RDS (managed database) etc. In the next section we go in more detail with one of the Oracle's offering.

Oracle Exadata Express Cloud Service

I'm a real fan of one of Oracle's cloud solutions named Oracle Exadata Express Cloud. I find this a great solution for many of my Oracle APEX projects. For 152 euro (excl. vat) you get an Oracle 12c pluggable database with many Enterprise Edition features turned on and ORDS and APEX 5.1 installed. This service is also fully managed by Oracle, so Oracle is taking care of all the patching, keeping it up and running, performant and secure. They let you know when a maintenance will happen and let you know when it's complete. Here's an example of a few days ago:

On top of this, it's running on Exadata hardware. To build this solution myself would require a lot of money. Oh and it can connect to our APEX Office Print cloud service, so you can do PDF printing and exporting to Excel too :)

But that is not all... you get more, you get a complete development infrastructure, meaning part 1 and part 2 I touched on at the start of this blog. So you can manage the full development life cycle with this service. You have a Git repository, can do the planning, capture issues, do automated builds etc.

Here's a quick screen cast logging in to Exadata Express and looking at the different pieces we covered:

On the management part of the development process; doing the builds, tracking the issues etc. you can definitely set this up yourself, and there are many options you have, from open source to probably most known, the Atlassian stack with Jira, Confluence, Bamboo etc. but this comes with a cost too. At APEX R&D we use different tools depending the project and customer. Two years ago, for AOP we started with Team Development which is part of Oracle APEX to define the features, gather feedback and define the releases. But we extended with other tools now like Trello and Bitbucket which stores our Git repository and we started to use the issue features there too. In some other projects we use Jira or Redmine.

There're so many tools to manage your development life cycle, at some point you just have to make a choice. At the end of the day most tools are good, it comes down to personal preference. Whenever our project involves Oracle Exadata Express, my choice will be to use the tools that come with the Oracle Developer Cloud Service, as that is included and I don't need to setup anything else anymore. From a maintenance and cost perspective I find it really appealing.

On different Oracle conferences this year, I've given a presentation how to move your APEX app to the Oracle Exadata Express Cloud.

Is this solution perfect yet? No, there's still room for improvement. For example I would love to have some more EE features available (RAS, Flashback data archive for example), an easier way to point to a custom URL and a customizable backup strategy. Oracle is improving every month and for many projects the current feature set is more than enough.

As the multiplication project has both an educational side to show you how I do APEX projects and is also a real use case, I'll use the Oracle Exadata Express service.

Ok, this post is getting close to 2000 words... if you read till here, great! I hope I gave you an insight in the different options you have to build your APEX infrastructure.

If you have any questions or remarks, don't hesitate to add them as comments to this post.


Anonymous said...

Dimitry, we are evaluating APEX as a development tool for our future projects.

We want to convert our existing .Net C# application (Windows desktop Application) to a web-application.

Our data model is an EAV.

So, to put a long story short, can we create a "dynamic" web page like this. Not in the traditional "dynamic" sense but like this:

So, we have a table (this is the simplified version) with all the screen names. This is called SCREEN_TABLE. This has one row for each screen we see in the Windows application.

So, for example for the customer screen we have like this:
Pk, screen_id, description
100, cus_screen, "Customer Details".

All the fields for this screen is stored in the detail to this table called SCREEN_FIELDS.
It has
PK, screen_table_pk, field, data_type, label, seq
1 , cus_screen, cus_name, 'C', 'Customer Name', 1
2 , cus_screen, cus_age, 'N', 'Age', 2
3 , cus_screen, dob, 'D', 'Date of Birth', 3

Now, when the user clicks the link to the customer details screen, APEX should read these 2 tables and construct the screen and show it to the user. If we want to add a new field, all you have to do is add the data (row) to the SCREEN_FIELDS table. Then APEX will show the new field in the screen at the right place, WITHOUT ANY CODING. I think these kinds of screens are also called "flex fields".

Is this type of development possible using APEX?

If so how can we do it?? (At a high level explanation is OK).


Juan Lopez said...

It would be very bad from our side if we don't show our appreciation feedback to you!! You're doing such a great work by sharing your experience through this project!! Thanks a lot!

Dimitri Gielis said...

Thank you Juan, appreciate your comment.

Dimitri Gielis said...

Hi Mike,

What you describe is what APEX is doing behind the scenes for you.
I would not add another layer on top of that.

Note that you can build that layer in APEX, so after that there's no coding anymore as you describe, but I believe it would make it overly complex without reason.

Just my 2 cents,

Anonymous said...

Thanks Dimitri for the reply.

In our current .Net application if we want to add a new field to a screen, what we do is create a .SQL script (Oracle, our DB is Oracle). In this script we have SQL inserts to insert the row to the SCREEN_FIELDS table. Then this script is run in all environments (system testing, QA & UAT) by our Jenkins build tool. This includes finally in Production.

In APEX if we want to add a new field, can we create .SQL script like above or is the procedure different? Like we have to open the APEX Web IDE and add it manually? Then how do we deploy the new added field to production??


Dimitri Gielis said...

Hi Mike,

The procedure is different, but the rumours are an upcoming version will include a feature to build pages with JSON files.

The way you do it in APEX, you open the APEX APP Builder in Dev, add the field and you can then use Jenkins to run the export command and deployment to the other versions (TEST, QA, PROD etc). An Oracle APEX app is just a SQL file.

Hope that clarifies,

Unknown said...

Dimitri, thanks for all your posts, comments, solutions, tips and tricks. I've been following your work for 3 years (the same i have worked with APEX), and in short words, we have an altar for you here in the office.

Thanks for all your hard work, and we hope you continue to provide us with excellent APEX material.

Jeffy Dwitra said...

Hi Dimitri,

I'm Jeff, and I'm interasting with your multiplication project. I want to ask If this okay if I don't use Oracle database cloud service and just use Apex database ?

Jeffy Dwitra

Dimitri Gielis said...

Hi Jeffy,

Sure you can do it in any Oracle APEX environment.

Best Regards,