Enable Real Application Security (RAS) in APEX 5.0

Oracle Database 12c introduced Oracle Real Application Security (RAS), the next generation Oracle Virtual Private Database (VPD). In APEX 5.0 RAS is declaratively build-in. Follow the below steps to enable it:

Login to the INTERNAL workspace and go to Manage Instance > Security:

In the Real Application Security section set Allow Real Application Security to Yes.

Next login to the Workspace your Application is build in and go to your Authentication Scheme.
You'll (more...)

JSON for APEX Developers (part 3): Querying JSON in Oracle DB 12c

In previous blog posts I talked about JSON for APEX Developers:
In this post I want to show how you store and query JSON data straight in the database.

To start, create a table to store the JSON object:

CREATE TABLE ssn_json
   (id          NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
    date_loaded (more...)

Oracle APEX 5.0 released today

After 2.5 years of development, today is the day APEX 5.0 is publicly released and ready to be downloaded to install on your own environment.

In my view it's the best release ever. Not so much of the new Page Designer - although that is definitely a piece of art and it increased productivity even further - but because it's the first time whole of APEX got refreshed and every piece was put (more...)

Working with multiple Trees on the same APEX Page (4.2/5.0)

Ever tried to put multiple Trees on the same page in APEX 4.2?

Here's my example:


On the left Tree the "Selected Node Link with" is set to hidden item P1_TREE1, on the right Tree the value that sets the tree is set to P1_TREE2. At least it should do that, but by default if you're values are not unique - in this case empno is the value behind both trees - it doesn't (more...)

APEX 5.0: pimping the Login page

When you create a new application in APEX 5.0, the login page probably looks like this:


I love the build-in login page of APEX itself - luckily it's easy enough to build that in our own apps too. Thank you APEX Dev team!

The first step is to change the region type to be of Login Region Template:


We want to add a nice icon on top of the Login text. You can use (more...)

Loading CSV files with special characters in Oracle DB

I often need to load the data of Excel or CSV files into the Oracle Database.

Ever got those annoying question marks when you try to load the data? or instead of question marks you just get empty blanks when the file is using special characters? Here's an example:


My database characterset is UTF-8, so ideally you want to load your data UTF-8 encoded.

With Excel I've not found an easy way to specify the (more...)

Oracle Database Tools updated – check out SQLcl

Today Oracle released new versions of:

Also Oracle REST Data Services 3 got a new EA2 version.
You may want to check Kris Rice's blog for new features.

I already blogged about all of the tools before, but not yet about SQLcl.
This is a command line tool, I call it "SQL*Plus on steroids" (or as Jeff calls (more...)

APEX 5.0: the way to use Theme Roller

Once you have your new application created using the Universal Theme it's time to customise your theme with Theme Roller.

Run your application and click the Theme Roller link in the APEX Developer Toolbar:


Theme Roller will open. I won't go in every section, but want to highlight the most important sections in Theme Roller in this post:
  1. Style: there're two styles that come with APEX 5.0: Blue and Gray. You can start (more...)

APEX 5.0 Fixed Headings for Interactive Reports

When you click on the Attributes of your Interactive Report there's a neath little new features included called "Heading - Fixed to" with as options: None, Region and Page


We've got this request from many customers and it has always been a pain to get it working in every condition with APEX 4.x, but no hacking anymore - it's there now in APEX 5.0.
When you start to scroll the column header will (more...)

APEX 5.0 EA3 – Universal Theme Styles – Gray!

In APEX 5.0 theme 42 is included - this is the Universal Theme. The theme comes with two styles: Blue and Gray.


The UI is so much improved in APEX 5, it's really a game changer. When you look at the details for example from such a style you will see it's using a Less file (Less is a CSS pre-processor) behind the scenes. So it's very easy to make changes to the CSS. (more...)

APEX 5.0 EA3 released with Universal Theme

Today Oracle released the latest Early Adopter release of APEX 5.0.


The release is packed with new features, but one of the biggest improvements you can find in the UI.

For me the Universal Theme with Theme Roller, Theme Styles and Template Options is the absolute killer feature of APEX 5.0. I can't better describe Universal Theme as how Oracle is describing it:

Universal Theme is an all-new user interface for your (more...)

Preparing architecture for APEX 5.0 upgrade

I doubted to set the title of this post to "Running APEX 4.2 and 5.0 in the same Oracle instance", but decided not to do that, but that is basically what I will do. Before going into details, I'll share my architecture.

In December 2013 I wrote it was time to update your APEX environment and I gave a quick overview of the architecture we're using. I thought it's time to review that (more...)

Announcement: APEX 5.0 UI Training – May 12th


APEX 5.0 will be released between now and the end of May. People who have already spent some time on the Early Adopter versions know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.

Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way.

The definition of user interface components (more...)

JSON for APEX Developers (part 2)

In the previous post we created a service that allowed us to give our data in JSON format.

Now let's focus on consuming that JSON. In this post I want to show how to use JSON data in the client (your browser), in a future post I'll show how to use JSON on the server (in the database).

If you want to play with JSON, open the console of your browser and create some text (more...)

JSON for APEX Developers (part 1)

After my post Generate nested JSON from SQL with ORDS and APEX 5 I got some requests to explain more about REST and JSON, so let me start with JSON. I'll go more into REST in some future posts.

JSON stands for JavaScript Object Notation, it's a text based format to store and transport data.

It all comes from exchanging data, and finding a format that can easily be used (more...)

Setting up Node and Oracle Database driver

Today Oracle introduced the node-oracledb driver, so you can easily connect from node to your Oracle database.
Previously I blogged about the Oracle VM you can download so you have your own local Oracle environment. Below are the steps to install the node driver and run a first example in that environment.
(Also read the official node-oracledb installation guide for a local database - 4. Installation with a local database).

First we need to install git (more...)

Next ORCLAPEX-BE Meetup – Feb 2nd

Roeland organised a new APEX Meetup in Belgium on February 2nd.


This time no specific topic, everybody can get on stage for maximum 5 minutes to show something they did or to ask some feedback or help on a specific problem.

If Belgium is to far, no worries, there're meetups planned all over the world.

The next meeting in the Netherlands is on February 18th organised by Alex, Christian and Roel.

But as I said, (more...)

Setting up your own local Oracle Development environment in less than 15 minutes

The fastest and easiest way to setup your own local Oracle Development environment is by using the "Oracle Technology Network Developer Day" Database Virtual Box Appliance.

It contains:

  • Oracle Linux 7
  • Oracle Database 12c (12.1.0.2) EE (CDB/PDB)
  • Oracle Rest Data Services (ORDS)
  • Oracle Application Express (APEX)
  • Oracle SQL Developer and Oracle SQL Developer Data Modeler
Step 1: Download and install Oracle VM VirtualBox

Step 2: Download the VM image that contains (more...)

Generate nested JSON from SQL with ORDS and APEX 5

When creating web applications, at some point you will need to interact with JSON. Either you consume JSON or you need to generate it to be able to use that cool widget you found.

A few years ago when I wrote about interacting and customising charts, XML was the data format. Today JSON is more common to use as it works so well with JavaScript. I needed to pass some nested JSON - here's an (more...)

Understanding the APEX url – passing variables to items and (interactive) reports

Did you know you can recognise an Oracle APEX application through the url?

Here's the syntax:

f?p=appid:pageid:session:request:debug:clearcache:params:paramvalues:printerfriendly&p_trace=YES&cs=...

Let me give some examples:

  • https://www.apexrnd.be/ords/f?p=209:12:12351378808570:::::

The first part: https://www.apexrnd.be/ords/ depends on your configuration. I'm using https, my domain name is apexrnd.be and I'm using the Oracle REST Data Services as connection to the database. This is a configuration in your webserver.

The next part is f?p= f is a (more...)