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!

Introduction to Oracle Database 12c

With the launch of DB 12c in 2013, Oracle introduced a new architectural concept, called “multitenant databases”, where you have one super database (=container; CDB) and one or more sub databases (= pluggable DBs; PDB).

Before running the installer on an Oracle Linux 6 environment, a library can be installed through yum to meet all the system per-requisites:

yum install oracle-rdbms-server-12cR1-preinstall

The software installer and DBCA are similar to 11g, except for this screen where you can pre-configure your CDB and PDBs:


This DBCA execution will not only create a CDB and 1 PDB, but a “seed pluggable database” as well.  You can use this seed database as a template to create other pluggable databases.


By default, after running the DBCA, all CDBs and PDBs are up and running.

Now, we will reboot the host machine and try to start all of our components.

Is there a difference when starting the listener and the CDB?

No!  You can start the listener and your CDB in exactly the same way as you did with your pre-12c database.

How can you connect to the CDB?

Very simple: just the same as in the past with pre-12c databases.

$ sqlplus system@apexdev

SQL*Plus: Release Production on Thu Mar 19 22:53:55 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Last Successful login time: Mon Mar 16 2015 22:20:50 +01:00

Connected to:

Oracle Database 12c Enterprise Edition Release – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


Are the PDBs opened by default, when starting the CDB?

No.  This can be verified by this query:

SQL> select open_mode from v$pdbs where name=’APEXDEV_PDB1′;


The PDB is mounted; to open it, just run this command:

SQL> alter pluggable database apexdev_pdb1 open read write;

Pluggable database altered.

SQL> select open_mode from v$pdbs where name=’APEXDEV_PDB1′;


Note: this must be done as “SYSDBA”.

How can we connect to the PDB?

There are 2 methods:

First method: connect to the CDB and then switch to the PDB by setting the container:

$ sqlplus system@apexdev

SQL> show con_name

SQL> alter session set container=apexdev_pdb1;

Session altered.

SQL> show con_name


Second method: Modify your tnsnames.ora file by adding an entry for the PDB, based on the CDB entry.

Now, you can connect as usual to the PDB:

[oracle@ol6db1 oracle]$ sqlplus system@apexdev_pdb1

SQL*Plus: Release Production on Tue Mar 24 20:37:38 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:
Last Successful login time: Thu Mar 19 2015 18:59:51 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name


As you can see, it is all quite easy.  One of the main benefits of this architecture is that you can handle every PDB as a separate database that can be upgraded or plugged/unplugged independently from other databases.

And of course your database will be ready for the cloud!

5 neat little features of the 12C database to remember

In this post, I’d like to introduce 5 of the many new features Oracle 12C brings to us, database developer’s.
Of course this blog would be to long to explain them all in detail, so I will stick
to a small introduction.

  1. Generating a primary key without triggers, using nextval or identity
    In 12C, you are now able to use sequence.nextval or the new keyword ‘identity’ as default values.
    The ‘identity’ keyword will generate the value max(id)+1 for your primary key. So now you don’t need to create triggers anymore, when generating PK’s with a sequence.
    And problems with sequences that are not in sync, when moving/copying tables to another schema/database, can be avoided by using the ‘identity’ keyword.
    Example PK row declaration :
    id_pers         number default person_seq.nextval primary key;
    id_pers         number generated as identity;
  2. Accessible key word : define which code can call your function/procedure.
    One of the major problems of PL/SQL is, when developing a lot of packages/procedures/functions, in the end there is no telling who is called by who. This problem can now be answered by ‘white listing’. This means that, on creation, you are telling the package/function/procedure/type by whom it is accessible, or may be used.
    The accessible by clause takes packages/functions/procedures/triggers as accessor clause.
    Example white listing :
    create procedure get_sales_data accessible by (my_sales_proc)…
    – create procedure get_sales_data accessible by (my_after_update_trigger)
    – create package my_package accessible by (my_other_package)…When the object is not accessible, following error will be thrown during compilation, or at runtime, in case of an anonymous PL/SQL block :PLS-00904: insufficient privilege to access object MY_PACKAGE.MY_PROCEDURE
  3. Temporal Validity of a row
    Sometimes rows in a table are valid or not, depending on a timeframe. For instance a subscription for a magazine, may only be valid for a year. Adding this validity to a row goes as follows :

     create table subscriptions
     ( person_id             number,
     subscription_id                 number,
     person_name                   varchar2(500),
     subscr_start_date   date,
     subscr_end_date              date,
     period for valid(subscr_start_date , subscr_end_date)

    Now with following query we can select the ‘valid’ subscriptions :

     select * from subscriptions
     as of period for valid sysdate;
  4. New PL/SQL Package UTL_CALL_STACK
    The UTL_CALL_STACK package provides subprograms to return the current call stack for a PL/SQL program. This could already be done by DBMS_UTILITY.FORMAT_CALL_STACK, but this new package returns this information in a more structured way, and includes the depth of the call (calling level) and the names of the subprograms.
    This will make this information more usable in code. Related to this subject, 2 new directives are added in 12c, next to $$PLSQL_LINE and $$PLSQL_UNIT that already existed).
    – $$PLSQL_TYPE

       dbms_output_put.line("Owner of this package is "+$$PLSQL_OWNER);

    Will print : Owner of this package is SCOTT

  5. An Invoker’s Rights Function Can Be Result Cached
    Caching results of a PL/SQL function already exists in 11g. Basically what happens is that, for a certain function, you define that the result, for given parameters should be cached in memory.
    So first time function getPerson(123) is executed, the data is fetched from the database, second time the function is called with parameter ‘123’, the result is fetched from the cache in memory, resulting in a better performance.
    Whenever a DML statement is executed on the table(s) used in that function, the cache is automatically cleared, causing the next call to return the new data. (Since 11G rel. 2, Oracle manages these dependencies himself.)
    So in our case, Oracle caches the result’s of function getPerson() for every key it is called with.
    Through Oracle Database 11g Release 2 (11.2), only definer’s rights PL/SQL functions could be result cached. Now in 12c, the identity of the invoker is implicitly added to that key.

As already mentioned, the possibilities of these new features go way beyond what I describe here. But hopefully it’s a start to a few experiments on your side !

More info can be found at http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm