Monday, May 06, 2013

Oracle Database 12c and APEX

The below content is based on a David Peake's presentation at OOW 2012 and APEX World 2013.

As the Oracle Database 12c is not out yet at the time of writing, note that things might be different in the final product.

The Oracle Database 12c is a major release in Oracle history, as it contains a complete redesign of the underlying architecture. Two things will come back a lot when you read about Oracle DB 12c; the Container Database (CDB) and the Pluggable Database (PDB).

The below image shows how it works; you have a container database which the memory and processes are attached at, and then you can have multiple other databases that you can just plugin.


You find more information about this architecture here.

So what does Oracle DB 12c mean for APEX?



Oracle DB 12c comes with APEX 4.2.0. You can either install APEX in the CDB or in the PDB. APEX 4.2.0 is the minimum version you can install in Oracle DB 12c.
The advantage of running APEX in the CDB is that when you upgrade APEX, all database plugged into that CDB will have the latest version of APEX.
The advantage of installing APEX in the PDB is that you can run different versions of APEX in the different databases. If APEX is installed in the CDB, you can't remove APEX from CDB$Root anymore.
But one of the first things I would do is either upgrade APEX 4.2.0 to 4.2.2 or install APEX 4.2.2 from scratch depending which option you went with.

When you upgrade from DB 11g with APEX to 12c PDB here's a screenshot of the steps to take:


When you install APEX in the Oracle database 12c, some new options will become available in APEX, build on top of some new Oracle database 12c features.

The 12c database parameter - max_string_size will allow you to upgrade your VARCHAR2 from 4k (standard) to 32K (extended).
If you look in your APEX installation you will find /core/collection_member_resize.sql. Running that script will change the APEX collection VARCHAR2 columns from 4K to 32K.

In 12c there's also a new column type that is basically an "auto numbering field", something you would typically do with a sequence and trigger. In 12c the column type is called "Identity column" and it can have following values: ALWAYS - which means a Sequence number is always used, DEFAULT set value, DEFAULT ON NULL use seq nr if null.
When you run APEX in a 12c database, SQL Workshop will allow to specify an identity column in the create table wizards. Also the Create Form/Report wizard will create the correct item type (display only).

Another new column type in 12c database is the "Invisible Column": SELECT * from
will not display invisible columns. INSERT into
will not insert into invisible columns. To insert into the invisible column you must explicitly set it.

In APEX - SQL Workshop - Object browser; the column will not show up.

Data Redaction, to mask your application data dynamically, is also a new feature in Oracle 12c. Before you had to setup VPD policies and do FGAC (Fine Grained Access Control), now it becomes more native in 12c. APEX will just show you what you are allowed to see following your policy. For example depending your policy you will see a credit card number like 4541 **** **** **** or null. You can read more about data redaction and masking in a blog post of Lewis Cunningham.

8 comments:

Matthias Hoys said...

Great post - the CDB and PDB architecture is now more clear to me. Also learned something new about the invisible columns!

Amin said...

Are we still only allowed one version per database?

Dimitri Gielis said...

If you choose the PDB setup you can do a different version of APEX inside every PDB.

Frank said...

Is apex already install on 12c or we need to do a install of apex ? I try to connect to a pdb with apex listener and I don't see anything on the home page. It looks like the images are missing.

Frank said...

Is apex already install on 12c or we need to do a install of apex ? I try to connect to a pdb with apex listener and I don't see anything on the home page. It looks like the images are missing.

Dimitri Gielis said...

APEX is installed, but it's not the latest version.
You can upgrade to APEX 4.2.3, it supports the upgrade of the CDB or you can install APEX in every PDB.

Jallah Saywala said...

Nice post. I am new to Oracle/ APEX. I just install 12c on win 8 laptop, how do I access the apex that is installed by default?

Dimitri Gielis said...

Hi Jallah,

Did you configure the webserver part? (APEX Listener, Apache Mod_plsql or EPG)

To enable for example the EPG you can do in SQL Plus: exec dbms_xdb_config.sethttpport(8080);

It might be possible that you need to run the specific APEX config file: apex_epg_config.sql to include the images.

then in your browser you can access http://your_server:8080/apex

I typically setup the APEX Listener, but that involves a bit more steps.

Hope that helps,
Dimitri