Migrate an application to the APEX 5.0 Universal Theme

iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the second tutorial is presented.

This tutorial helps you step by step through the process of migrating an application to the Universal Theme of Oracle Application Express 5.0. In this tutorial we talk about Oracle Application Express when Oracle APEX is mentioned. The intention of this tutorial is to become familiar with APEX 5.0 and its features.

We assume you have an environment where you can work with APEX 5.0.

Step 1: Pre-migration step

The Oracle APEX team has created a bookmarklet to assist with mapping your older templates to new templates because there is not always an equivalent in the Univeral Theme. This bookmarklet
will be used further on this tutorial in the Theme Switch Wizard.
We have to add the Universal Theme Migration bookmarklet to our bookmarks bar. Go to https://apex.oracle.com/pls/apex/f?p=42:2000:::NO:RP::and scroll down until this appears on your screen:

Drag and drop ‘Universal Theme Migration Helper V1.0’ to your bookmarks bar. This bookmarklet actually is a JavaScript. By adding it to your bookmarks bar it will be easy to apply it during the tutorial.

Step 2: Import an Oracle APEX 4.2 application

Before we start the migration, import an application by using the file named ‘f_sample_db_app_42.sql’. Navigate to page https://goo.gl/cg2izy and click Tutorial 2. There you will find the SQL-script. Start the import by clicking the icon ‘Import’ in the Application Builder.


In the page that appears click the button ‘Choose File’ and select file ‘f_sample_db_app_42.sql’. Leave all other values as default by Oracle APEX and click ‘Next’.


In the following page, click ‘Next’.


At the next screen, leave all defaults and click ‘Install Application’.


Again, at the next page leave all defaults. Click ‘Next’.


At the next screen, click ‘Install’.


Finally we import is ready. Click ‘Edit Application’.


Now you are in the Application Builder for the imported application. If you should take a look in the Workspace where you see all applications, you will see the application you just imported.


For now, the Application Builder is the place to continue our migration. Click ‘Utilities’.


A page with lots of options appears. Click ‘Upgrade Application’ to do some preparing steps before the Universal Theme will be applied to this application.


The next page will appear. Some actions have to be performed. You can do this by clicking the numbers one by one under the header ‘Candidate Objects’. Oracle APEX will do some actions for you in your application to upgrade some aspects to typical Oracle APEX 5.0 ‘things’.
For example, when we click the number corresponding with ‘Upgrade Flash Charts to HTML5 Charts’, a window with all affected pages, regions and objects appear.


Select all items and then click ‘Upgrade’.


When all actions are done, this same page will appear but the upgraded items will not appear anymore. Just click ‘Cancel’ and you will return to the page where all upgrade actions are shown.
Oracle APEX works a little different when doing the actions for ‘Numeric, Required, and Date Picker item based upon conditional validations’. When you click the number at the right side of this step, you will see an overview of all affected pages concercing this upgrade action. You have to walk through this pages one by one by clicking the page numbers. To describe what happens, click on the first number under the header Page. In this example, click 11.


Select all items by clicking the checkbox at the left side of header ‘Validation Type’ and click ‘Next’.


In the next page, you repeat this: so check all checkboxes (in this example only one) and click ‘Next’.


In the next page again select all items and click ‘Next’.


In the final page of this step, click ‘Upgrade’.


Repeat this for the pages that remain.

Step 3: Add Universal Theme to your application and switch

In this step we will use the Universal Theme Migration bookmarklet as mentioned at the pre-migration step. In the Application Builder navigate to Shared Components. You can do this by clicking one of these icons. The large blue icon hardly can be missed in the Application Builder. The small icon can be found at the right side almost on top of the page.



In section ‘Navigation’, click Themes.


In the next window click ‘Create’.


A wizard appears. Do not change any of the defaults and click ‘Next’.


On the next page, leave ‘User Interface’ as defaulted by Oracle APEX and click ‘Next’.


On the ‘Create Theme’ page accept default values in ‘Theme Type’ and ‘Theme’ (Standard Theme and Universal Theme (Theme 42)) and click ‘Next’.


Click ‘Create’ in the final page of this wizard.


The Universal Theme is added tot your application as shown in the next image. Click ‘Switch Theme’.


Set values as shown below and click ‘Next’.


Now page 386 appears. The URL of the page you are in contains /f?p=4000:386:13938292352804::NO::: and shows you a warning.


Now click the bookmark we added to our bookmark bar at the beginning of this tutorial. This bookmarklet supports mappings from the 3 most popular legacy themes, Theme 24, 25, and 26. It also supports any themes that are based off these legacy themes, i.e.: if your theme is a modification off those themes, the bookmarklet will do most of the mappings correctly. When the bookmarklet detects an unsupported theme is used, you will be prompted to choose which theme is closest to your theme.
The ‘old’ theme in this example is theme 25. This results in this message.


If your theme is mapped properly, each of the select list values that were successfully altered will be highlighted in green as shown in the next screenshot. Click ‘Next’ at the bottom of this page.


Now click ‘Switch Theme’.


Check the Universal Theme is one of the current themes.



Step 4: Post migration actions

The following actions have to be performed before you can make changes to your pages. This ensures no unexpected bugs arise in your application.

List items
Make sure list items for your navigation menu is correct. The ‘current of’ properties also have to be set properly. In Shared Components (Application Builder) click ‘Lists’ under Navigation. Switch to the tab ‘List Details’ and their you can select a list. Choose ‘Report Tabs’ and click on the entries that appear on your screen.  On the tab ‘Current List Entry’ check whether the settings are correct. If the ‘Current of’ properties are not set correctly, the navigation links will not be selected when you navigate to the pages.

Decide if you want to stick with Side Navigation or the Top Menu as the app’s primary navigation. Go to Shared Components and then navigate to User Interface Attributes. (The edit button with the pencil icon on it). Under tab Navigation Menu set these values as following:
  • Position: Top
  • List Template:Top Navigation Menu


If you choose to stick with side navigation then you have to select navigation icons for your theme. A default page icon will appear if you do not select icons. Go to Shared Components, then to Navigation Menu and then pick the list Navigation Menu. Now pick a list entry and click on the name.
On the page that appears you find the select list ‘Image/Class’. When you click the arrow at the right side of the item an overview of all available icons is shown. Select an icon you want and this will be set into the calling page in ‘Image/Class’.



Now we migrated the Oracle APEX 4.2 application…






…. to an Oracle APEX 5.0 application using the Universal Theme.







In fact the migration is now finished but Oracle provides in his own migration guide some best practices for managing post-migration issues and frequently asked questions and for now we refer to that guide.


That’s it for this tutorial, great job!

5 Things I wish I knew about APEX when I just started (part 5)

Using Advisor to increase the quality of your application.

For the last post in this series, I want to end with something that will help you improve the overall quality of your apex applications. That something is called: “advisor”.

The easiest way to access advisor is through the edit page. If you press the “utilities” button, and then select the option “check”, you will end up in an advisor report for the current page.

The report will – for example – tell you when you are using page items that don’t exist, or when some of your processes have invalid code in them.


The other method to get to advisor is through the application page overview screen.

Click the utilities button and then advisor and you will end up on a page with all the checks that can be performed on your application.


It’s useful to run advisor every once in a while, especially when you are making or modifying applications to maintain the quality level of your application.

Pre-Apex 4.0

The built-in advisor is only available in Apex starting from version 4.0. However, for older versions of Apex there is an alternative, viz. a tool made by Patrick Wolf, called… “advisor” ;) .

You can download this tool and find some more information about it on: http://essentials.oracleapex.info/

Batch processing in JEE7 (JSR 352)

A new feature in JEE7 is Batch Processing, an implementation of JSR 352 based on the programming model of Spring Batch. With batch processing, we understand the execution of a series of jobs (steps) , sequential or in parallel, without any user interaction. In this blog, we want to give a small overview of the capabilities of this new feature.
The code of this blog can be downloaded from here.
The zip file contains an Eclipse maven project in the folder JobAPI, and a jobapi.war file that can be deployed and tested.

The JSR 352 specification defines 2 different approaches to set up a batch, which can be combined :

The ‘chunk’ approach

This approach implements a common ETL pattern of read/process/write. A chunk is a set of data, a number of items, that is written in one ‘transaction’. So a chunk of 3 items, gives you the following execution :

  1. read item 1
  2. process item 1
  3. read item item 2
  4. process record 2
  5. read item item 3
  6. process record 3
  7. write items 1, 2, 3
  8. start reading item 4, etc…

The ‘batchlet’ approach

This approach gives you the freedom to do as you like. It’s just a piece of code, executed as part of a batch job.

The Code

For this example, I have used GlassFish 4.0 from here (no Eclipse plugin yet for the 4.1). Download the full EE, and not the Web-profile, as that one only contains a subset of the JEE7 spec. More info about the differences between these two can be found here.

As the batch process runs in background, System.out.println is used for the output. If you’re using the GlassFish plugin in Eclipse, you’ll see the output in the console view. Otherwise, check the server.log in the dir : ../glassfish4.0/glassfish/domains/domain1/logs/server.log

In the example we generate a list of 10 Data objects in the reader. A Data object contains 2 operands and the result of a calculation of those 2 that was computed in the processor. The writer prints out the result.
Let’s go over our different classes :

The Data.java object (just a value object)

public class Data {
	private Integer num1;
	private Integer num2;
	private Integer result;

	public Data(Integer num1, Integer num2) {
		this.num1 = num1;
		this.num2 = num2;
	public String toString() {
		return num1+" en "+num2+(result != null ? " = "+result : "");
	public Integer getResult() {
		return result;
	public void setResult(Integer result) {
		this.result = result;
	public Integer getNum1() {
		return num1;
	public Integer getNum2() {
		return num2;

The flow of a batch job is defined in a job xml, that is placed under the /META-INF/batch-jobs directory. We called ours : myJob.xml
As all classes are CDI beans with default names (@Named annotated), we refer to them with their lowerCamelCase name. (don’t forget to add an empty beans.xml file in the WEB-INF dir.)
We define 2 steps: the first one uses the chunked approach, executing the batch in chunks of 3 items; the second step uses the batchlet approach. This batchlet will just print a message.
Note the “next” attribute in the step, declaring what the next step will be after “step1”.

<job id="myJob" xmlns="http://xmlns.jcp.org/xml/ns/javaee" version="1.0" >
	<step id="step1" next="step2">
		<chunk item-count="3">
 			<reader ref="myItemReader"/>
 			<processor ref="myItemProcessorPlus"/>
 			<writer ref="myItemWriter"/>
	<step id="step2">
		<batchlet ref="myBatchlet"/>

MyItemReader : open method is executed once and creates a list. ReadItem method reads 1 item.

public class MyItemReader extends AbstractItemReader{
	private List<Data> listData = new ArrayList<Data>();
	int count=0;

	public void open(Serializable c) throws Exception {
		System.out.println("FILL LIST");
		 Random randomGenerator = new Random();
		    for (int idx = 1; idx <= 10; ++idx){
		      int randomInt1 = randomGenerator.nextInt(100);
		      int randomInt2 = randomGenerator.nextInt(100);
		      listData.add(new Data(randomInt1,randomInt2));

	public Data readItem() throws Exception {
		try {
			Data data = listData.get(count++);
			System.out.println(Thread.currentThread().getId()+" | READ  "+data);
			return data;
		} catch (IndexOutOfBoundsException e) {
			// finished
			return null;

MyItemProcessorPlus : processes 1 item : calculates the sum and puts it in the result.

public class MyItemProcessorPlus implements ItemProcessor {

	public Data processItem(Object arg0) throws Exception {
		Data data = (Data)arg0;
		System.out.println(Thread.currentThread().getId()+" | PROCESSING PLUS data: "+data.toString());
		return data;

MyItemWriter : writes a chunk of items, in our case, just prints it.

public class MyItemWriter extends AbstractItemWriter{
	List<Data> resultList = new ArrayList<Data>();
	public void writeItems(List<Object> arg0) throws Exception {
		System.out.println(Thread.currentThread().getId()+" | start write "+arg0.size()+" elements");
		for (Object o : arg0) {
			Data data = (Data)o;
			System.out.println(Thread.currentThread().getId()+" | WRITE  "+data.toString()  );
		System.out.println(Thread.currentThread().getId()+" | end write ");

MyBatchlet : second step, runs after all the chunks have been written.

public class MyBatchlet extends AbstractBatchlet {
	public String process() throws Exception {
		System.out.println("BATCHLET : Processing something else in this batchlet....");
		return null;

Now that we have our classes, we create a servlet to start the batch.
“myJob” in the start method of the JobOperator refers to the name of the batch xml file in the META-INF directory.
When the method is executed, the batch will be started in the background, and the servlet continues.

public class RunBatch extends HttpServlet {
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws javax.servlet.ServletException ,java.io.IOException {
		JobOperator jo = BatchRuntime.getJobOperator();
		jo.start("myJob", null);
		resp.getWriter().println("Job started, check console for output.");

Now when we deploy our war file, and go to http://localhost:8080/jobapi/myRun, it will give the message “Job started, check console for output.” on the screen, but you can still see the output of the batch in the console/server.log :

Info: 175 | READ 42 en 80
Info: 175 | PROCESSING PLUS data: 42 en 80 = 122
Info: 175 | READ 47 en 41
Info: 175 | PROCESSING PLUS data: 47 en 41 = 88
Info: 175 | READ 74 en 6
Info: 175 | PROCESSING PLUS data: 74 en 6 = 80
Info: 175 | start write 3 elements
Info: 175 | WRITE 42 en 80 = 122
Info: 175 | WRITE 47 en 41 = 88
Info: 175 | WRITE 74 en 6 = 80
Info: 175 | end write
Info: 175 | READ 70 en 79
Info: 175 | PROCESSING PLUS data: 70 en 79 = 149
Info: 175 | READ 67 en 35
Info: 175 | PROCESSING PLUS data: 67 en 35 = 102
Info: 175 | READ 13 en 81
Info: 175 | PROCESSING PLUS data: 13 en 81 = 94
Info: 175 | start write 3 elements
Info: 175 | WRITE 70 en 79 = 149
Info: 175 | WRITE 67 en 35 = 102
Info: 175 | WRITE 13 en 81 = 94
Info: 175 | end write
Info: 175 | READ 89 en 89
Info: 175 | PROCESSING PLUS data: 89 en 89 = 178
Info: 175 | READ 24 en 29
Info: 175 | PROCESSING PLUS data: 24 en 29 = 53
Info: 175 | READ 20 en 18
Info: 175 | PROCESSING PLUS data: 20 en 18 = 38
Info: 175 | start write 3 elements
Info: 175 | WRITE 89 en 89 = 178
Info: 175 | WRITE 24 en 29 = 53
Info: 175 | WRITE 20 en 18 = 38
Info: 175 | end write
Info: 175 | READ 28 en 2
Info: 175 | PROCESSING PLUS data: 28 en 2 = 30
Info: 175 | start write 1 elements
Info: 175 | WRITE 28 en 2 = 30
Info: 175 | end write
Info: BATCHLET : Processing something else in this batchlet….

As you can see, there are 3x calls to read and process, and then 1 call to write, for every chunk of data.
When all 10 Data objects have been processed, the next step – the batchlet – is executed.

Parallel processing

The batch framework contains 2 techniques for processing data in parallel, in 2 or more separate threads. One of them is partitioning, where, for a set of data, you define which parts will be processed in the same step, but in multiple instances, in different threads. This partition definition is done in the reader. We won’t go into detail here.
A second technique can be used to define steps that will run in parallel, before continuing with the next step.

Let’s update our example for this last one. Suppose that we want a second list of data, where we compute the product iso the sum. We will create a new processor for that.

MyItemProcessorMultiply : process 1 item : calculates the product and put’s it in the result.

public class MyItemProcessorMultiply implements ItemProcessor {
	public Data processItem(Object arg0) throws Exception {
		Data data = (Data)arg0;
		System.out.println(Thread.currentThread().getId()+" | PROCESSING MULTIPLY data: "+data.toString());
		return data;

Now, in order to use this processor we need to add an extra step to our myJob.xml: use the tag “split” to indicate which steps need to run in parallel. To demonstrate the difference, the product batch wil run in chunks of 5, whereas the sum runs in chunks of 3.

<job id="myJob" xmlns="http://xmlns.jcp.org/xml/ns/javaee" version="1.0" >
	<split id="split1" next="step3">
		<flow id="flow1">
			<step id="step1">
				<chunk item-count="3">
		 			<reader ref="myItemReader"/>
		 			<processor ref="myItemProcessorPlus"/>
		 			<writer ref="myItemWriter"/>
 		<flow id="flow2">
			<step id="step2">
				<chunk item-count="5">
		 			<reader ref="myItemReader"/>
		 			<processor ref="myItemProcessorMultiply"/>
		 			<writer ref="myItemWriter"/>
	<step id="step3">
		<batchlet ref="myBatchlet"/>

Running our servlet will result in following output :

Info: 231 | READ 27 en 72
Info: 230 | READ 55 en 72
Info: 231 | PROCESSING MULTIPLY data: 27 en 72 = 1944
Info: 231 | READ 44 en 53
Info: 231 | PROCESSING MULTIPLY data: 44 en 53 = 2332
Info: 231 | READ 31 en 14
Info: 231 | PROCESSING MULTIPLY data: 31 en 14 = 434
Info: 230 | PROCESSING PLUS data: 55 en 72 = 127
Info: 231 | READ 87 en 84
Info: 230 | READ 3 en 8
Info: 231 | PROCESSING MULTIPLY data: 87 en 84 = 7308
Info: 230 | PROCESSING PLUS data: 3 en 8 = 11
Info: 231 | READ 31 en 1
Info: 230 | READ 63 en 28
Info: 231 | PROCESSING MULTIPLY data: 31 en 1 = 31
Info: 230 | PROCESSING PLUS data: 63 en 28 = 91
Info: 230 | start write 3 elements
Info: 231 | start write 5 elements
Info: 231 | WRITE 27 en 72 = 1944
Info: 231 | WRITE 44 en 53 = 2332
Info: 231 | WRITE 31 en 14 = 434
Info: 231 | WRITE 87 en 84 = 7308
Info: 231 | WRITE 31 en 1 = 31
Info: 230 | WRITE 55 en 72 = 127
Info: 230 | WRITE 3 en 8 = 11
Info: 230 | WRITE 63 en 28 = 91
Info: 230 | end write
Info: 231 | end write
Info: 230 | READ 30 en 90
Info: 230 | PROCESSING PLUS data: 30 en 90 = 120
Info: 230 | READ 39 en 71
Info: 230 | PROCESSING PLUS data: 39 en 71 = 110
Info: 230 | READ 48 en 23
Info: 231 | READ 51 en 36
Info: 230 | PROCESSING PLUS data: 48 en 23 = 71
Info: 231 | PROCESSING MULTIPLY data: 51 en 36 = 1836
Info: 231 | READ 49 en 30
Info: 230 | start write 3 elements
Info: 231 | PROCESSING MULTIPLY data: 49 en 30 = 1470
Info: 230 | WRITE 30 en 90 = 120
Info: 230 | WRITE 39 en 71 = 110
Info: 231 | READ 82 en 80
Info: 230 | WRITE 48 en 23 = 71
Info: 230 | end write
Info: 231 | PROCESSING MULTIPLY data: 82 en 80 = 6560
Info: 231 | READ 26 en 87
Info: 231 | PROCESSING MULTIPLY data: 26 en 87 = 2262
Info: 231 | READ 49 en 69
Info: 231 | PROCESSING MULTIPLY data: 49 en 69 = 3381
Info: 231 | start write 5 elements
Info: 231 | WRITE 51 en 36 = 1836
Info: 231 | WRITE 49 en 30 = 1470
Info: 231 | WRITE 82 en 80 = 6560
Info: 231 | WRITE 26 en 87 = 2262
Info: 231 | WRITE 49 en 69 = 3381
Info: 231 | end write
Info: 230 | READ 26 en 15
Info: 230 | PROCESSING PLUS data: 26 en 15 = 41
Info: 230 | READ 53 en 31
Info: 230 | PROCESSING PLUS data: 53 en 31 = 84
Info: 230 | READ 99 en 60
Info: 230 | PROCESSING PLUS data: 99 en 60 = 159
Info: 230 | start write 3 elements
Info: 230 | WRITE 26 en 15 = 41
Info: 230 | WRITE 53 en 31 = 84
Info: 230 | WRITE 99 en 60 = 159
Info: 230 | end write
Info: 230 | READ 54 en 91
Info: 230 | PROCESSING PLUS data: 54 en 91 = 145
Info: 230 | start write 1 elements
Info: 230 | WRITE 54 en 91 = 145
Info: 230 | end write
Info: BATCHLET : Processing something else in this batchlet….

You can see there are now 2 thread IDs : 230 and 231, one for each step.

This was just an introduction of how a batch can be set up in JEE7.
But there are many other usefull features not demonstrated in this blog.

Exception handling

  • You can continue reading/processing/writing after a configured exception is thrown during the run.
  • Configure how many times a certain exception can be thrown, before the batch is aborted.


You can write listeners that are executed

  • before/after an item is read/processed/written.
  • when a skippable exception is thrown in the reader/processor/writer
  • when a retry of a reader/processor/writer is executed


Add custom checkpoint from where to restart the batch when it was aborted.


The new Batch API gives us a lot of possibilities for setting up a batch in an EE environment. The biggest advantage is probably that it works seamlessly together with all the other EE components in our projects.

For instance, you can use your REST/WS clients to read data, your EJB’s and JPA entities to process the data, your JMS to write results to your queues, etc…

If you’re going to use this batch API, I hope you will share your experiences here, on this blog !



Migrate to the Oracle Database Cloud Service

On the 6th of October the Oracle Cloud Day 2015 takes place in The Netherlands. iAdvise is proud to be a bronze sponsor on what promises to be a day filled with interesting sessions.

One of the main topics will be the Oracle Database Cloud service. In this blog post, which was published in the OGh Visie earlier this year, you can read what the Oracle Database Cloud Service is, which configuration possibilities there are and what steps are needed to migrate.

Oracle Database Cloud Service

Suppose you want to start with the Oracle Database Cloud Service tomorrow, what options are there? What services are there, and what do they do? Below you will find the 3 services available. In the future, Managed Database as a Service will be added to these services.

Database Schema Service

Interesting to know is that, already since 2012, you can use the Oracle Cloud, namely through the Oracle Database Cloud Schema Service (ODCSS).  It was – and still is- possible to get a database schema in the Oracle Cloud, on an 11g database that is fully managed by Oracle. You can connect to this schema through: SQL Developer, RESTful Web Services, Oracle Java Cloud or
Oracle APEX.oracle_database_cloud_scheme_service

However, there are some limitations. You have no SQL*Net access, so you can only use SQL Developer (or APEX) to connect to your cloud database schema. You can do this through a so-called “Cloud Connection”, but need to know that you can only browse and load/unload data. So, not really convenient for development purposes. In addition, you cannot “connect” multiple schemas with each other, and packages such as: utl_smtp, utl_ftp and features as VPD (Virtual Private Database) are not available.

If you don’t mind these limitations, ODCSS is a very budget-friendly solution. Starting at $175 per month, you have an Oracle database in the cloud with 5GB storage available

Virtual image

Are you looking for a cloud environment where you can install and manage your databases yourself, then the virtual image option might be something for you. You get a dedicated VM on which you can install an 11g or 12c database, either manually or through the Database Configuration Assistant (DBCA). Unlike with the ODCSS, you have SQL*net access and full access to the VM.

You get the choice whether you want to pay per hour or per month. From $400/month or $0,672/hour you can get started with SE 1.

Database as a Service

If you choose the Database as a Service, you have the same possibilities as with the virtual image option, but in addition you have a number of nice tools at your disposal. So you can create a database instance in 4 easy steps thanks to Oracle’s automatic provisioning system. In addition, you have a number of cloud tools to maintain and monitor your database very easily.

You can start with the Database as a Service for a SE1 database from $600/month or $1,008/hour.

Setting Up the Oracle Cloud Database as a Service

Database instance

For the creation of an Oracle database you have a simple wizard. This initiates the automatic provisioning of your database.  The provisioning ensures that your database is installed with cloud tooling technology and it configures the backup facilities.


The 4 steps that you must follow are:

  1. Choose your service: Virtual image or Oracle Database Cloud Service
    How do you want to pay: hourly or monthly?
  2. Choose your database version: 11gR2 or 12c?
  3. Choose your edition: SE1, EE, EE High performance, EE Extreme performance
  4. Choose the name of your instance, your compute shape (number of CPUs) and connect an SSH key to access to your VM.  Set a number of database configurations: how much storage you need, SID, and – in the future also a possible – a fail over database. If you also choose for Backup and Recovery, you select the storage container here.
    Then simply confirm, and you’re done! Fifteen minutes later you have a fully functioning database instance in the Oracle Cloud and access to a dedicated VM.


Backup & Recovery

When you choose the Oracle Cloud Database as a Service, you also have the necessary cloud tooling technology to simply set up the desired backup options.

If you’re only interested in a local backup, then the block store only option will do. During the provisioning of the VM, some additional disk space will be configured for the backup, by default 1.7 times the required database data volume. Through a combination of the familiar Oracle RMAN and classic system management tools, nicely packaged in a command line API, not only your database but also important system files are added to the local backup.

If you want full protection for your VM, then your best option is to choose the block store and cloud storage option. This option presupposes that Oracle Cloud Storage as a Service is purchased and configured. With a few simple commands, you will then be able to restore your full VM instance, including system files and configured Oracle database, into a new VM.

Both systems follow a series of Oracle’s best-practice guidelines and can be expanded and adapted to suit your needs.

Do you want your on-premise Oracle database(s) or Oracle database(s) that were created in an Oracle Cloud Database – Virtual image instance, also to be backed up in the Oracle Cloud, then you can set this up via the Oracle Cloud Backup as a Service. This service only relates to the storage of Oracle database backups. Do you also want to back up other types of data in the Oracle Cloud – for example, important system files – then you can do the setup through the Oracle Cloud Storage as a Service. In that case you are responsible for setting up the necessary backup policy.

Access rules

Who has which access to the Oracle Cloud Database as a Service?

Each Compute Cloud Service VM, child to the Oracle Cloud Database as a Service, is shielded from the angry world through so-called access rules. These are firewall rules that check the access to the VM specific protocols, such as HTTP(S), SSH or SQL * Net access. In these rules you define what source groups of VMs (network groups) or lists of IP addresses on a specific protocol may connect within a certain target group of VM(s).

When creating an Oracle Cloud Database as a Service instance, by default, 7 Oracle Compute Cloud Service access rules are created. These access rules control access to SSH (port 22), Enterprise Manager Database Control (port 1158), Enterprise Manager Database Express 12c (port 5500), Database listener (port 1521), Glassfish Server admin console (port 4848), HTTP (port 80) and HTTPS (port 443). The last one is necessary for accessing the Oracle REST Data Services, Oracle APEX and Oracle Cloud on-instance database monitor.

Only the SSH access is standard enabled. The other access rules must be explicitly enabled after creation.


Migrate to the Oracle Cloud

Have you decided to use the Oracle Database Cloud Service? Nice, but now you probably want to know what steps are needed to get your current on-premise environment in the cloud. And that happens to be one of the strengths of the Oracle Cloud: because you have full access to your dedicated cloud VM, you can tackle the migration in different ways.

For the migration of OraEvents (an application we build for Oracle) we used the classical method: an Oracle Data Pump export (expdp) of the on-premise database and an import (impdp) into the cloud database. The steps we went through are:

  • Expdp of the database schema
  • Copy the expdp DATA_PUMP_DIR directory to the Oracle database
  • Preparing the database – create new tablespaces, user schemas and assign the necessary rights
  • Import the expdp into the new database schema
  • Export and import of APEX workspace and applications
  • Copy the APEX images in the default DocumentRoot of the Glassfish server running the ORDS
  • Ready for testing!


If you decide to migrate to the Oracle Cloud today, you have a range of options available to ensure that your cloud database is more than just a replacement for you on-premise database. The automatic provisioning, powerful cloud tooling and integrated systems offer a great extra value. Because migrating to the cloud is very similar to a classic migration, there are no major objections from a technical perspective. The Oracle Cloud is ready. Now, it’s up to us!

Creating a basic application in APEX 5.0

iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the first tutorial is presented, you will learn how to create a basic application.

This tutorial helps you step-by-step through the process of creating a basic application in Oracle Application Express 5.0.
To create a basic application with a few pages, you have to follow the next steps. We decided to split up this tutorial into six steps.

In this tutorial we talk about Oracle Application Express when Oracle APEX is mentioned. The intention of this tutorial is to become familiar with Oracle APEX 5.0 and its features.

We assume you have an environment where you can work with APEX 5.0.

Step 1: Upload and run SQL script.

First, go over here: https://goo.gl/cg2izy. Click ‘Tutorial 1 – Creating a basic application in APEX 5.0’ and you a page appears where 3 SQL-files are presented. Download the scripts to your local drive.

Login to the workspace provided to you. You will reach a window with these icons at the top of the page. Click ‘SQL Workshop’.


Now click ‘SQL Scripts’.


You reach a page where you can upload the SQL files by clicking ‘Upload’. Unfortunately the script cannot be loaded all at once. Click ‘Choose File’ and select the SQL-file you want to upload. Finally fill in the field ‘Script Name’ and click ‘Upload’ at the bottom of the page. In this tutorial keep the script name the same as the filename.


Repeat this steps for the other files. When all files are uploaded, they have to be executed. Select the file you want to execute by clicking the checkbox at the left side. Now click on the ‘Play’ button at the right side of the row.


The next page will appear. Just click ‘Run in Background’.


When you run these scripts in the right order ( 1_tutori… .sql, 2_tutori… .sql, 3_tutori… .sql) they should return no errors.

Step 2: Create a new application

After you have logged in to the right workspace, a window with this icons will appear. Click Application Builder and the next window will appear:


To create the application, click Create. Now it’s possible to choose a type of application. In this tutorial we want to create a desktop application. This option is already selected by Oracle APEX. To continue, click the icon or click Next.


The next page allows you to enter basic information about your new application.


Some values (Schema, Application, Theme, Theme Style) already have a default value. In this window only change Name. In this example, let’s name it ‘My APEX 5.0 Application’. Click the button Next. This same action also has to be performed in the next page.


In the following page, it’s possible to indicate that you want to copy Shared Components from other Applications. The default value is ‘No’. Don’t change this value and click Next.


The next page allows us to enter global settings for the application such as language and date/time settings. Don’t change the default values except for Date Format. Click the arrow at the right of the field and select the value ’12-JAN-2004’ in the popup window that appears. The popup will close automatically and will set the date format to ‘DD-MON-YYYY’.


Later you will see it is possible to use another format on a date field.


Click Next. A page with summarized information of the new application will appear. Now it’s time to create the application by clicking on ‘Create Application’.


You will see the Application Builder for the application we just created. In this example, the application number is 107. Two pages are already generated. (1 – Home, 101 – Login Page).

Step 3: Create an interactive report

We continue where step 2 ended: in the Application Builder. Click the button ‘Create page’.


A window with many page options appears. Click the icon ‘Report’.


A window with three options for report types appears. Click ‘Interactive Report’.


In the next window it is possible to enter the first basic information about the page we are creating. Only enter a clear page name. Here we enter ‘Department employees’. When you leave this field it automatically updates ‘Region Name’ with the value you just entered. In this situation this is ok.
Click Next.


Now it is possible to add information about navigation in menu entries. Accept the defaults by clicking on Next.


Next add information about which table or view we want to use in this interactive report.

  • Don’t change anything for the default values of ‘Source Type’ and ‘Table/View Owner’.
  • Choose ‘IADV_DPT_EMPLOYEES_VW (view)’ at ‘Table/View Name’.
  • As you can see, all available columns in the shuttle are at the right side so no action at this field is required.
  • Change value to ‘No’ at ‘Link to Single Row View’.

Continue the creation by clicking Next.


A window with summarized information about the interactive report appears. Now create the page by clicking Create.


Now you see the Page Designer from APEX 5.0.
You can watch the result by clicking on the blue ‘Play’ icon (Save and Run) at the right corner on top of the screen. Because this is (probably) the first time you navigate to the ‘runtime’ version of your application, you have to login. Use the same combination of user and password as you do in the Application Builder.

Keep in mind: in Oracle APEX 5.0 your application will by default not appear in the same window or tab as where you click ‘Save and Run’. When you use Google Chrome or Internet Explorer, it starts in a new tab. In Safari and Firefox a new window will open.



As long as you open both tabs, it is possible to use the Oracle APEX developer toolbar at the bottom of your ‘runtime’ screen. When you click ‘Edit Page 2’ you automatically switch to the tab window of your Page Designer. If you change something in the Page Designer and then click the blue icon (Save and Run), you automatically switch to the tab with your ‘runtime’ page.


A bit more about this toolbar: all items are clickable. What happens if you click on an entry of this toolbar?

  • Home:
    You will navigate to the workspace where your application belongs to.
  • Application xxx:
    You will navigate to the Application Builder of your application
  • Edit page x:
    Depending the settings you made before, you will navigate to your page in the Page Designer or in Component View
  • Session:
    Shows session state information about your page. Change the value in select list ‘View’ to ‘All’ to see more information.


  • View Debug:
    You will see the debug reports. Click on one of the numbers under ‘View identifier’ to see detailed debug information.



  • Debug:
    This toggles the page between Debug mode and No Debug mode.
  • Show Grid:
    This toggles between Show Grid and Hide Grid. This is only applies if a grid layout is used and that layout supports showing a grid and the page has more than one column.
  • Quick Edit:
    The cursor changes into a plus symbol (+) and then it’s possible to click the desired component in your page you want to (quick) edit. When you click you will navigate to the page in the Page Designer, even if you explicit showed this page in Component View before.
  • Theme Roller:
    It’s is possible to easily customize the appearance of the application by using all options that are shown in the window that appears. This theme roller only appears for themes supporting this feature. This theme roller offers some fancy features to change the layout of your page.



  • The ‘wheel’ icon:
    This offers you possibilities about where your toolbar is located. For example, the toolbar can be located at the right side of your page.



Step 4: Edit the interactive report

Let’s edit some little esthetical aspects in the interactive report we just created. As you can see, a few columns can be removed or hidden from the report and some headers have to be edited.


Columns Dee Id, Dpt Id and Epe Id are not interesting to show in the report. Besides this, Name and Employee Name have to be changed to more clear terms. Also despite our Application Setting concering date formats, we still want to change the date format for these columns into DD-MM-YYYY.


Click ‘Edit Page 2’ or navigate to page 2 in the Application Builder. Now you’re in the Page Designer. At the left side of this Page Designer you see all regions on this page. When you expand some nodes in this part of the page, all report items will be shown. In this example we have to edit region ‘Department employees’.


In APEX 5.0 it is possible to do a multiple selection by using CTRL and clicking on items you want to select. We decided to hide items Dee Id, Dpt Id and Epe Id. These items match with DEE_ID, DPT_ID and EPE_ID in the Page Designer. When you do a multiple selection, it will look like this in the Page Designer.


At the right side of the Page Designer it’s possible to change properties for all the selected items at once.


Again, we want to hide all selected items. Under ‘Identification’, change the item type to ‘Hidden Column’.

Now, we want to change the headers ‘Name’ and ‘Employee Name’ into ‘Department’ and ‘Employee’. Also align these headers at the left side. For ‘Name’, select report item ‘Name’ in the Page Designer.


At the right side, change the values under ‘Heading’.

Old properties


New properties


You can repeat this action for ‘Employee Name’. Change properties for item EMPLOYEE_NAME.

When you run the page, this result will be shown.


Now we have to change the alignment for ‘Start Date’ and ‘End Date’. You have already done this for other items, so the only hint we will give is that these items can be found in the Page Designer as START_DATE and END_DATE. The headers have to be aligned to the left. Also change the headings so only the first character is uppercase (Start date and End date).

When you have finished the actions for the headers, it’s time to change the format mask of these date items. At the right side of the Page Designer, navigate to the section ‘Appearance’. It’s important to notice properties can be shown in two ways. Here it is possible to add a format mask. You can use the list or you can write something yourself. Let’s enter DD-MM-YYYY.
It’s possible to show common properties or all properties. When common properties are shown, you cannot find ‘Format Mask’. Just click ‘Show all’ and this property will appear.

Show common properties


Show all properties



Again, push the Save and Run button at the right top of the Page Designer.

Now your interactive report is ready!



Step 5: Create a form

In this step we will create a report with a form based on an table with employees. (IADV_EMLOYEES). Make sure you’re in the Application Builder of the application you created before and click ‘Create Page’.


Choose the icon ‘Form’.


In the next window, choose ‘Form on a Table with Report’.


Only change ‘Page Name’ in the next page. As seen before, ‘Region Title’ will automatically be edited when leaving ‘Page Name’.
Click Next.


Now we can enter the table we want to display in the form and region. Select IADV_EMPLOYEES and click Next.


As before, in this tutorial we skip navigation, so you can immediately click Next.


Now we can select which columns will be shown in the report. Leave the defaults and click Next.


In the page that appears we can select the icon which will be used to navigate to the detail form. Select an applicable icon and click Next.


Next, we will arrange settings for the detail form. Edit ‘Page Name’ to ‘Employee’ and navigate out of this item (for example, user your tab key). You will see that ‘Region Title’ changes to ‘Employee’.
At ‘Page Mode’ choose ‘Modal Dialog’. Click Next.


At ‘Primary Key Type’ select ‘Select Primary Key Columns’. Two items will apear. In the first item (‘Primary Key Column 1’) select column ‘EPE_ID’ and then click Next.


On the next page leave all defaults and click Next.


Here, we can select the columns which have to be shown in the form. Select all columns by clicking on the double arrows which point to the right side of the shuttle. Then click Next.


Next you can indicate whether you want to use functionality concering insert, update and/or delete. Leave all defaults as generated by APEX and click Next.


The last page of this ‘wizard’ shows summarized information about the report and form we want to create. You can edit nothing over here. Just click Create.


Now you arrive in the Page Designer again. Click the Save and Run button (the blue icon with a with triangle inside) to take a look at your result.


The second column from the left (Epe Id) needs to be a hidden column. Besides this, we want all headers to align at the left side. We already did some actions in another report to change these settings. Can you do it again to let this page look like the following image?


Now let’s take a look at the form, the modal page. You can navigate to this form by clicking on the edit icon.


Edit properties to change prompts work in a simular way as shown before.

Aligning items is the next action for this page. Place First Name and Last Name on the same line. Also Email Address and Phone Number have to appear on the same line and change their order. In Oracle APEX 5.0 you can to this in the Page Designer by a kind of drag and drop action. Select the item you want to replace. Move your cursor over the item in the center part of the Page Designer (Grid Layout) until your mouse pointer like this:




For P4_EMAIL_ADDRESS and P4_PHONE_NUMBER you can do the same actions. Finally it looks like this in the Page Designer.


When you look at the ‘runtime’ version of this page, you can see ‘Last name’ is placed at the right side of ‘First name’ and ‘Phone Number’ appears before ‘Email Address’.


As you can see, the width for ‘Date of birth’ is not very nice so the alignment of the page can be better. One of the options to align the items is to give them all the same width. Because P4_DATE_OF_BIRTH is a Date Picker and all other items are Text Fields, it is not possible to edit the width for all items at once. So first select P4_DATE_OF_BIRTH and navigate to section ‘Appearance’ at the right side of the Page Designer and change width into 20. Select P4_FIRST_NAME, P4_LAST_NAME, P4_PHONE_NUMBER and P4_EMAIL_ADDRESS and change width into 20 for all items at once. You can do this multiple selection by clicking the items one by one while you use the CTRL-button from your keyboard.



When you run the application and navigate to the adjusted page, you see the alignment becomes much better.


The last action is to edit the label for ‘Phone Number’. Let’s change this to a label which indicates this field is required.  Use the developer toolbar by clicking ‘Quick Edit’. Click at ‘Phone Number’ in your screen and you will jump to the page in the Page Designer.


In the Page Designer go to section ‘Appearance’ at the right side of your page. There you can change the value in ‘Template’ to ‘Required’.


Now click ‘Save and Run’ and you will see the item is shown with a changed label.


Step 6: Adding navigation

In the final step of this tutorial we will add navigation to an interactive report to navigate to another page. At this point you can see this in your Application Builder.


In page 2 we will add a button to navigate to page 3. So now open page 2 by clicking on it. The Page Designer will appear. Right click the region ‘Department employees’ and a popup menu will appear. Click ‘Create Button’ as shown.


You will see a new folder name ‘Region Buttons’ under the existing region ‘Department employees’. Besides this, in the center part of the Page Designer there you can also see the new button.


At the right side of the Page Designer we can adjust some settings for this new button.
Copy the values as shown in the following images. In detail you have to edit a few properties in the sections Identification (Button Name, Label), Layout (Button Position), Appearance (Hot) and Behavior (Action, Target).






When you have finished this, click Save and Run. You wil see at the top of your interactive report a button ‘Employees’.


When you click this button you will navigate to the page containing more detailed information about Employees. To complete navigation we will add a menu to this application. Make sure you are in the Page Designer and select the page at the left side of your page.


Take a look at the properties under Navigation Menu at the the right side of your page.

These properties need the following values:

  • Override User Interface level: Yes
  • List: Desktop Navigation Menu
  • List Position: Side
  • List Template: Side Navigation Menu

Save this settings.


Make sure you’re in the Application Builder. There you will find the icon for Shared Components. Click it for the next action. On the page that appears you see several sections. Under ‘Navigation’ you can click ‘Navigation Menu’.



You will arrive on a page where you have to click ‘Desktop Navigation Menu’.


The screen that appears offers you the possibility to add menu entries. To do this click ‘Create List Entry’.


Add information on this page

  • Section Entry
    List Entry Label: Department employees
  • Section Target
    Target type: Page in this Application
    Page: Page 2 (Department employees)


Now use the button ‘Create and Create Another’ to repeat this for the other pages. When you add the last page use ‘Create List Entry’ instead. Navigate to the ‘runtime’ version from our application. Now you can see a menu at the left side of the application.


That’s it for this tutorial, great job!

5 Things I wish I knew about APEX when I just started (part 4)


Ever deleted or changed something by accident in your application and wished you could get it back?

You can! The oracle database has a feature called flashback, and APEX can take advantage of this feature. (Flashback needs to be enabled on the database, if you are not sure if it is enabled on your database, you can always ask your DBA).

Today I will show you some of the different places, where you can recover lost work.

Page items

Create a new application and a blank page. On that page add a region and some items.
Now delete one and modify some.
To undo the changes that you made, you can click the export page button. You will then see the following dialog:


In the field “As of” enter the time in minutes that you want to go back and export the page.
If you now import the page again you will notice your items are back.
This method can be used for any page component (regions, buttons, items, dynamic actions…)

Regions & Processes

For modified regions and processes there is another feature that you can use. Suppose that you have modified your process or region code, and then you notice that something is no longer working and you want to go back to what it was before… You can do this by opening your region or process.

On the right-hand side of the page in the tasks block, you will see a link “undo region/process source”, click it.


You will now get a popup with a select list showing all the times you modified something. Select the one you want, and press the set button. Next, click the past source text, to copy it to your region.



If you deleted the entire page by accident, there is still no reason to be worried. There is also a way to go back in time and get your page back. For this, you go to the screen where you export your application like you would usually do, and you will notice the “as of” field is also available there.



And what if I deleted the entire application? Well then it’s time to start panicking….. Then calm down again, and do the following. Download SQL Developer and open the schema where your application is in and execute the following code:

Exec dbms_flashback.enable_at_time(SYSTIMESTAMP – INTERVAL ‘10’ minute);

You should now notice your application has reappeared in the list of applications in SQL developer:


Now export your application by right clicking it. Select “quick ddl” and then save to file. You now have an export file of your application that you should be able to import again trough SQL Developer/APEX.

Please don’t do this for fun in a production environment to scare your boss, you never know if something might still get broken :)

But at least now you know the option is there, should you ever need it.

Also check out my previous post about build options.

Java 8 Lambda : the very basics

Functional interface

In many cases – when talking about Java 8 – lambda-functions are the first thing that comes up. I found it a concept hard to grasp, as it slightly changes the way you think about coding. In this blog, I would like to show you some very simple examples, and explain what is what in the lambda world.

In versions prior to Java 8, we had SAM interfaces : Single Abstract Method-interfaces. In Java 8, the concept of SAM-interfaces has been re-created and called “functional interfaces”. These interfaces can be used in lambda-expressions. Here’s a definition of such an interface that we will be using in the examples.

interface MyCalculator {
	public double calculate(double val1, double val2);

The “FunctionalInterface” annotation is used to generate compiler level errors, when the interface is not a functional interface. A functional interface is an interface with 1 abstract method. The annotation is not required, but improves the readability of your code.

Using the lambda

When the functional interface is created, you can declare a variable to be assigned with a lambda-expression.

MyCalculator multiply = (value1, value2) -> value1 * value2;
MyCalculator sum = (value1, value2) -> value1 + value2;
System.out.println(“multiply = “+multiply.calculate(5,6));
System.out.println(“sum = “+sum.calculate(5,6));

Generates following output:
multiply = 30.0
sum = 11.0

So if you are wondering what is what in a lambda-expression :
Before the arrow = variables of your function
After the arrow = code to be executed by the abstract method of the functional interface

If we would do this the ‘old-school’ way, our code would look like this :

MyCalculator multiply = new MyCalculator() {
	public double calculate(double val1, double val2) {
					return val1 * val2;
MyCalculator sum = new MyCalculator() {
	public double calculate(double val1, double val2) {
					return val1 + val2;
System.out.println("multiply = "+multiply.calculate(5,6));
System.out.println("sum = "+sum.calculate(5,6));

The advantage of lambda is pretty clear : less code, better readability.

If you want to define more than 1 line of code, write it like this :

MyCalculator calc = (value1, value2) -> {
			System.out.println("Before calculating");
			double result = value1* value2* 3;
			System.out.println("After calculating");
			return result;
System.out.println(calc.calculate(3, 4));

Generates the following output:
Before calculating
After calculating

Use of non-local variables in lambda expressions

In versions prior to Java 8, the following code :

Calendar cal = Calendar.getInstance();
MyCalculator sum = new MyCalculator() {
	public double calculate(double val1, double val2) {
		return val1 + val2;

would generate an error :
“Cannot refer to the non-final local variable cal defined in an enclosing Scope”
on statement

because the variable “cal” was not defined as final.

The Java 8 compiler has a new capability that will convert non-local variables, used in lambda-expressions and anonymous inner class methods, automatically to final.
So in Java 8, this code will actually compile. Even better: now, we can write the previous code as a lambda-expression :

Calendar cal = Calendar.getInstance();
MyCalculator sum2 = (value1,value2) -> {
			return value1+value2;

Streams API and lambdas

New in Java 8 is the Stream API (java.util.stream). According to Oracle’s documentation : “A stream is not a data structure that stores elements; instead, it transports elements from a source (data structure, array, generator function, or an I/O channel), through a pipeline of computational operations.”

This is where the lambdas make their entrance, as a pipeline is a sequence of lambda expressions that can process or interrogate every element in the stream. In short : by using streams and lambda’s, we can execute a whole bunch of operations on a Collection, all in 1 statement.

Suppose we want to print out every even number from a list of Integers.

List<Integer> list = Arrays.asList(1,2,3,4,5,6,7,8,9,10);
List<Integer> evenList = list.stream().filter(value -> value%2==0).collect(Collectors.toList());
evenList.stream().forEach(value -> System.out.println(value));

In the second line, the filter method will return the values matching the predicate, in this case, a lambda-expression. The collect method will return the result list.
On the third line, we use a stream to print out every value of our new list, containing the even values.

If you had to write the above code in Java 7, you would need more code, so more possible flaws, less readability.
But there is more :

Map<Person.Sex, List<Person>> byGender = roster.stream().collect(Collectors.groupingBy(Person::getGender));

In 1 line of code, from a list of person objects, we create a map with the gender as the key.

Read the Java Doc on java.util.streams, and you’ll discover a whole new way of writing code, including parallel processing of your stream. Also, check out the Collectors API where you can find methods to convert your collections into other sorts of collections, without any for loops, etc…

The better you understand lambdas and streams, the more you’ll understand why they are such a hot issue in the Java world, the more you’ll improve your code!