Upgrade Oracle DB 11gR2 with APEX to 12c

Now that Oracle Database 12c is available I upgraded my VM to try it out. The installation went very smooth and I was up-and-running in no time!

My virtual machine had Oracle 11gR2 installed with the latest patch of APEX 4.2.

Here are the steps I followed:
- downloaded the Oracle Database 12c zip for Linux 64-bit
- unzipped the files
- made sure I had all prerequisites and updated my Oracle Enterprise Linux from the command line with: yum update
- next I wanted to upgrade my current database; from the command line in Linux, went to the unzipped directly and ran: ./runInstaller
- I installed the software in a new directory /u01/app/oracle/product/12.1.0
- Next I followed the wizard to upgrade my current database

With Oracle 12c APEX will be installed automatically, but the upgrade was smart enough to detect I had a more recent version of APEX and it looks like it didn't touch it. My existing workspaces and applications were functioning just fine after the upgrade.

You find more information in the Oracle Database 12c documentation and the Upgrade Guide.

I wrote another blog post about the new features that will be enabled in APEX when running on Oracle DB 12c. Another recommend read is the full New Features document of the Oracle database.

OTN Forums updated

Today the OTN Forums where updated. The site is still in read-only mode as they are fixing some issues. 

This is how the new APEX Forum looks like:

I need to get used to this new look and feel, but it looks more up-to-date design wise.

You can expand a thread straight from this page by clicking on the arrow-down icon which is nice.

At first sight it might actually organise the threads nicer; for example when people start to use the likes, the content page shows a nice overview and it should give an idea what is playing in the community.

For me the search and performance are one of the most important features of a forum, after a couple of days and when the upgrade is finalised, we know more...

My thought on APEX 5.0 plans

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

The below are plans for APEX 5.0, not marked in stone objectives... So there are no promises it will all make it in APEX 5.0 and things might be different in the final release. David didn't show any pre-release, but if this release follows previous version, most likely at ODTUG KScope '13 (end of June) the APEX team will show something live.

The focus in APEX 5.0 is on improved developer productivity, which is great to hear for us developers.

Below you find the main areas the APEX development team is looking into and what my thought are on them:

Modal Dialog

At the moment you can use some plugins to create modal dialogs, but in APEX 5.0 you'll be able to declaratively declare modal pages (so real pages no regions) with a nice UI where you can add buttons on the modal and have modals on top of modals. Yes!

Drag and Drop Layout Editor (different view)

With HTMLDB 1.5, the first public release of APEX, we had the Component View where you saw the definition of your page. Since APEX 4.0 we got next to the Component View the Tree View, which allowed you to see the definition of the page the way it gets rendered. Using that view also increased productivity as you could drill-down faster to certain areas or right click on components to create other things and, based on where you were, it would skip steps in the wizard.

APEX 5.0 will introduce a new view the "Design View". This is a more visual UI to build your pages. You find the Page Elements on the left, the Layout Editor - Source in the middle, and the Component Library below that and finally the Property Editor on the right. The following screenshot is showing the concept:



So you can do drag-and-drop to create your page elements and edit them straight in the property editor. You keep doing that till you are happy and after hitting Apply Changes button it gets saved. So when you edit your page, remember the changes won't be applied automatically only when you hit the button (which I find good).

I do wonder how the Layout Editor will work with for example responsive design; will it do ratios (percentage) or will it depends on the theme that it will be "pixel perfect"? When the early adopter comes out, it will probably be one of the first thing I want to see how the html gets generated behind the scenes.
I also wonder if this editor will work on a tablet (iPad). Would be cool to be in a meeting, grap your iPad and discuss requirements, you quickly drag items on the screen hit Apply Changes and presto they see immediate results.

It will also be interesting to see how they implement the Layout Editor behind the scenes as there is so much information on the screen. When do they get more data through an Ajax process or do they load most of it on the initial load?

The other thing that David asked was who would like the APEX team to keep maintaining all 3 views. Most people like to stick to what they know and don't like to give up something, but if the APEX team needs to maintain all 3 views, it will be time consuming. So if it comes down to get more features or get to keep the 3 views, I probably would give up a view for more/better features :-)

HTML5 capabilities

APEX 4.2 already introduced many HTML5 features and I'm a big fan of having more of those declarative available; things like new input types, improved HTML5 charts, incorporate more CSS3 instead of images, use CSS3 animations for transitions, latest jQuery Mobile features (panels, responsive table, dual range slider) etc.
Another thing I would find interesting is, if they looked into local storage and offline capabilities declaratively. I never heard that would be on the list for APEX 5.0, it's just something I threw in, in this section for the future to think of.

PDF Printing

Designing your pages for PDF Printing has been something that isn't as declarative and easy to do as creating for example pages. The APEX Listener has now build-in FOP support, so they can use that and in APEX 5.0 they will add additional declarative formatting options and will make it easier to work with different templates, add control break and master/detail reports.

Web Service Support

They look into improving the Restfull web services publising capabilities, so it's easier and more performant to integrate with other databases and be in a more SOA architecture.
In a next version of SQL Developer you'll be able to define DML stored procedures using future SQL Developer

Improved framework and enhancements to the packaged applications

This is something that is true for every version. Many small changes are done that makes APEX just a little bit better, more user-friendly and more productive.
The new packaged applications were already included in the latest APEX 4.2 patch set.
Another area they look into is allowing third-party apps to be included as packaged applications. This is especially useful in the Oracle Cloud.

Multi-Row Edit Region Type

The current tabular forms are a bit old school - they got introduced in HTMLDB 1.5 and got native  validations in APEX 4.0, but apart from that it's still a bit behind what you can do with normal page items. For example multiple checkboxes are not declarative in APEX 4.2 tabular forms.
APEX 5.0 will introduce the Multi-Row Edit Region Type. It will be a new Region Type, so it would be a manual change of your existing tabular forms to move to that.
The biggest advantage for the APEX team is that they don't need to maintain the old code. I'm more than happy to switch my tabular forms manually to get a more feature rich experience.
This new region type would also allow to create a master-detail-detail page.
Currently they are investigating different plugins; JQGrid is one of them which might be used behind the scenes.

Multiple Interactive reports

This has been on the list for some time, but it looks like APEX 5.0 will allow any number of IR to be defined on a single page.

Application Builder Security

The security in APEX is a big area and has been increased in every release. In APEX 5.0 they will allow different authentication schemes to be used to control developer access and there will be more  pre-built schemes to pick from. Although David didn't mention it, I hope SSO with AD is one of them and a remember me functionality will be declarative available.

Websheets

APEX 5.0 will continue to modernize and enhance websheet capabilities, improve usability, evolve the user interface and simplify the creation and maintenance of data grids.
Websheets are as good as every other Wiki, but with Data Grids, which are so much more powerful.


Again the above are things the APEX development team looks into, so it's not guaranteed it will make it in APEX 5.0. So when I said "APEX 5.0 will, read it as APEX 5.0 might".


You want a specific feature in APEX? Log it in the Feature Requests app: http://apex.oracle.com/vote
More info on Oracle Application Express (APEX): http://apex.oracle.com

Goodies - APEX 4.2.2 included Libraries

External libraries are typically used to extend APEX so you (and the APEX development team) don't need to build certain features from scratch. 

One of the advantages of applying the latest patches is that the external libraries that come with APEX carry an update too. Most known examples of external libraries are AnyChart (chart engine in APEX) and the CKEditor (Rich Text Editor), but there are some other nice libraries that come with APEX too.

Here's an overview of the libraries that you will find in the images folder:

Some are declaratively integrated in APEX for example respond-js is part of the Blue Responsive theme, some others are not, for example twitter-bootstrap and another part is used in some sample or packaged apps, for example jquery-flot.

Having those libraries already there, means you can more easily integrate those in your own apps yourself too. Remember, if you want to go the extra mile it's worthwhile to create an APEX plugin so it's easier to reuse.

If you want to know more about those libraries, you find the links below:

Library	(latest) version in APEX	Latest version of library
960.gs	120203	130207
bgiframe	2.1.1	2.1.2
ckeditor	3.6.4	4.1.1
codemirror	0.66	3.12
ios-inspired-theme	121106	121106
jquery	1.7.1	1.9.1 / 2.0.0 (no support IE6/7/8)
jquery-autocomplete	1.1	deprecated - part of jQuery UI
jquery-colorpicker	1.4	1.4
jquery-flot	0.7	0.8
jquery-jstree	0.9.9a2	pre 1.0 fix 2
jquery-mobile	1.2.1	1.3.1
jquery-qtip2	2.0-6.26.2011	2.0.1
jquery-quicksand	1.2.2	1.3
jquery-ui	1.8.22	1.10.3
jquery-validate	1.7	1.11.1
modernizr	2.5.3	2.6.2
respond-js	1.1.0	1.1.0
selectivizr	1.0.2	1.0.2
twitter-bootstrap	2.2.1	2.3.1

Many of the libraries come out with newer versions more frequently than there are APEX patches.
So note that APEX is not always including the latest version of the library. 

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.

APEX Ice Cream - Oracle ACE Cookbook

The warm days are coming... time for some APEX Ice Cream! 

People in my close environment, they know I can't resist ice cream and chocolate! 

So what I did was I bought a real ice cream machine, so I could make my ice cream myself :-)

The different ways and flavours you can make your ice cream is unlimited ... anyway, this is how it looks like when you put your cream into the ice machine and wait 30 minutes.

So, is this blog post about making ice cream? Not really... I just wanted to bring under your attention that under the lead of Lewis Cunningham some Oracle ACE and ACE Directors created a real cookbook (for food)!

Lewis came up with the idea and did the hard work of putting everything together. There's a nice video of Lewis explaining why he wanted to do it. In 2008, it was actually Lewis who put forward my name to become an Oracle ACE Director which I will be forever thankful. When he raised the idea about the cookbook, I didn't have to think long to put the recipe in to make one of my favourite desserts; home made ice cream with chocolate sauce, I call it "APEX Ice Cream". You can read in the recipe why :-)

I embedded the cookbook from Scribd below:

ACE Cookbook by Lewis Cunningham

If you want to make your ice cream responsive...

Here's how a version of a desktop version of APEX Ice Cream looks like:

And here's the smarth phone version, I didn't remove any content as you can see, it's just a bit closer together... APEX Ice Cream is why to good to show less on a smaller plate ;-)

Enjoy!

Goodies - APEX 4.2.2 Sample and Packaged Apps

After reading Michael Hichwa's blog post about APEX 4.2.2 where he mentions the patch set includes a major upgrade of all 18 productivity and all 16 database sample applications, I decided to install all of them again and check them out.

Those apps are a great way to see how to do certain things and see working applications behind the scenes. The packaged applications you first have to unlock before you can see the pages and source.

As APEX is meta-data driven you can also query for specific information.

I was for example interested in knowing which apps where using the Responsive Design Theme (Theme 25). Following query shows you per application which theme is used.
As you can see the sample apps are using Blue Responsive (theme 25) and most of the packaged apps are using the Cloud Apps theme. As those also have a mobile version, you can see the JQuery Mobile Smartphone theme is used there too.

select a.application_name, a.alias, a.compatibility_mode, a.pages, a.installation_scripts,
       listagg(t.theme_number, ',') within group (order by t.theme_number) as theme_numbers,
       listagg(t.theme_name, ',') within group (order by t.theme_name) as theme_names
  from apex_applications a, apex_application_themes t
 where a.application_id = t.application_id
group by a.application_name, a.alias, a.compatibility_mode, a.pages, a.installation_scripts   
order by 1

Here's the result:

The sample and packaged apps are not only nice examples (and useful apps), but they also contain some plugins that are not on the official Oracle Plugins page.

There are 21 plugins used in the sample and packaged apps. But two I believe are the same (CSS Bar Chart vs CSS Bar Charts and Slider vs APEX Slider), so that brings the total to 19 plugins to checkout!
And there are some really nice ones, like the Gantt Chart (completely in css, so works on the iPad) :

and Flot Pie Chart (build in JavaScript and CSS) :

Running following query shows you all the plugins in your workspace and in which application it was used:

select p.plugin_type, p.display_name, p.name,
       count(a.application_name) as nbr_app_using_plugin,
       listagg(a.application_name, ',') within group (order by a.application_name) as applications
from apex_appl_plugins p, apex_applications a
where p.application_id = a.application_id
group by p.plugin_type, p.display_name, p.name
order by 1,2

Here's the result:

I would definitely recommend having a look at the apps, the APEX team did a nice job on those.

Importing APEX Team Development Feedback from TEST to DEV (on same instance)

One of the great features in APEX is Team Development. It was introduced in APEX 4.0 and I already blogged about it before, so if you don't know what it is, have a look at my previous post first.

This post is about exporting the feedback from your other environments (TEST, ACC, PROD, ...) back into Development. It's a lot easier to have everything in one Team Development environment (typically DEV) for an application / workspace, so you need a way to get the feedback from the other environments feed into your development environment.

When you connect to your TEST environment, you can export the feedback through the normal export wizard in APEX (the wizard is the same to import/export apps, but then with the feedback tab):

Once you have your feedback exported to a sql file, you can connect to your development environment and import the feedback:

This goes well as long as you're importing from a different APEX instance than what you exported from.
In case you are running your TEST environment on the same database (and APEX instance) as your development instance, when you try to import the feedback you might get following error:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_FEEDBACK_PK) violated

It's a known limitation of APEX. The way to get around it is to export the feedback from TEST to a file (see first step), purge the data in that environment (TEST) and after that you can import it in your DEV environment.

You find the Purge Data under Team Development - Utilities (Team Actions on the right side):

Here you can purge the feedback (or other entries):

The import will work fine in your DEV environment. But be careful you are in the right environment, because once you purged the feedback in one environment you can't import it anymore in that environment. When you try doing that, you get following error:

Review PhoneGap 2.x Mobile Application Development (e-book)

Packt asked me if I was interested in reading one of their books, PhoneGap 2.x Mobile Application Development Hotshot, and write my thoughts on it.

As the topic is in my interest sphere and I was having a long flight ahead, I decided to use that time to read it. So I read the book on my iPad in about 9 hours, but didn't try the code the comes with the book yet.

I strongly believe in developing mobile applications for both smartphones as tablets. You can either go the native route (iOS, Android, Windows phone, ...) or go the HTML5, CSS3, JavaScript route. I see a market for both, but I personally lean more towards the HTML5 way, as I can use the skills that I currently have. PhoneGap (built on Cordova) bridges the gap between what you can do standard in HTML5 and what you can do if you would code natively for the device. PhoneGap offers a JavaScript API to access the hardware of the device, for example the camera, microphone, accelerometer, filesystem etc.  PhoneGap wraps your HTML and JavaScript into a native shell, specific to the device, so you could even submit it to the platform's app store.

The book is mostly build of examples, which is a great way to see the potential of the tool. The author starts with giving a brief overview what PhoneGap is and where it fits in. It also walks you through how to prepare your environment to run the examples. Next it's all examples... The first chapter talks about how to create multilingual applications and sets the foundation for the other chapters. I found it interesting to see the author is using the YASMF framework (instead of for example JQuery Mobile).

In the second chapter you basically build a basic twitter application which also allows you to share the information. To do the sharing she used ShareKit, a very nice open source project.
In the following chapters she builds applications around the PhoneGap's File APIs, geolocation features together with Google Maps, recording and playing of audio, using the camera to store pictures and video, all very nice examples.

Chapter 8 is interesting as it builds a game! Behind the scenes it's using the HTML5 canvas, multi-touch gestures and the accelerometer. It's also reusing some of the things you learned in previous chapters like the file api, but it expands now to use localStorage.

The final two chapters are about expanding PhoneGap with native functions from the device and supporting bigger mobile devices like the iPad.

As you can see, a lot of ground is covered, with all excellent and useful examples!

Before, I only played a bit with PhoneGap, but I didn't find it hard to follow the content. I found the chapters well structured. I like to know why I'm doing something, so it was great to see that was covered first and then it builds up by designing the app (UI/interactions), the data model, putting it together and wrapping it up. You need a good understanding of HTML and JavaScript to follow the examples, but the author highlights that too in the Preface.

APEX 4.2.2 patch set released

Today APEX 4.2.2 was released. As with every patch set a number of bugs got fixed, but this patch also contains a new packaged application to build surveys.

Here's a screenshot of the app:

If you are on 4.2 or 4.2.1 (first patch set), you should get the update from Oracle Support, the patch number is 16277995.

If you are on a previous version you should download the entire APEX version from OTN.

I upgraded my virtual machine in about 6 minutes.

If you want to read more, Joel Kallman and Marc Sewtz did some blog posts about APEX 4.2.2.
Marc recorded some nice videos about the updated PDF Printing in APEX 4.2.2 in combination with the APEX Listener.

Happy patching...

Online Oracle APEX documentation updated

Today, Anthony Rayner tweeted that the online APEX (JavaScript APIs) documentation was updated.

The JavaScript APIs of APEX got a complete rewrite over time and contains many hidden gems of APEX. Now you can read more about those in the documentation too and see some examples straight in there.

Next to the documentation it's also useful to look at the non-minified .js files that come with APEX.
You find them in ~your APEX directory~/images/libraries/apex/

In there you find many interesting .js files, based on the filename you have an idea which area they are in. For example if you want to work with cookies you can find that in storage.js. The navigation.js for example contains an interesting function to create popups and so much more...

Not everything is in the online documentation yet, but the documentation is updated frequently now, so definitely good to check it out again.

Oracle APEX books under Christmas Tree

If you are searching for some presents under the Christmas tree, you might want to consider following Oracle APEX books:

-) Oracle APEX Best Practices by Alex Nuijten, Iloon Ellen-Wolff, Learco Brizzi

This book was published a month ago. I was one of the reviewers and believe it's a nice collection of best practices that are now used in the APEX community. One of my favourite chapters in the book is chapter 2 "Leveraging the Database". It's not 100% APEX as such, but if you know how to write your queries (by understanding analytical functions, rollup, pipelined functions, etc.) you can get so much more out of your (interactive) reports in APEX. 

-) Expert Oracle Application Express Plugins by Martin Giffy D'Souza

This book is almost a year old, but it's still a great resource if you want to build Oracle APEX plugins.
Martin explains very well the different plugin types and shows how to create them. I like the way he wrote the book as he also explains the business need for the different plugins.

-) Expert Oracle Application Express by Dietmar Aust, Martin Giffy D'Souza, Doug Gault, Dimitri Gielis, Roel Hartman, Michael Hichwa, Sharon Kennedy, Denes Kubicek, Raj Mattamal, Dan McGhan, Francis Mignault, Anton Nielsen, John Scott

More than a year old, but still a great source as so many different authors wrote about their expertise, so you get a big variety of topics. The royalties of the book also go to a good cause.

There are many other Oracle APEX books out there; from beginner to advanced. 

Update: Packt is pleased to announce a special eBook offer – stock your reader this Christmas!  During this celebration, you can buy two or more Packt eBook's of your choice for just $5.

Merry Christmas & Happy holidays.

APEXBlogs part of odtug.com/apex

Over the last years things changed fast: HTMLDB became APEX, many developers started to use APEX day-in-day-out, the development team released APEX 4.2 a few days ago and the Oracle Cloud is using APEX as a key component...

Oracle Application Express (APEX) matured over the last 8 years and people blogging and using APEX grow exponentially.

In my personal life things changed as well; I co-founded APEX Evangelists, a company 100% focussed on Oracle Application Express (development, consulting, training).  I became a father of a great boy and a wonderful daughter and, despite my long working hours and many travelling, my wife and I are still married and enjoying our time together.

It has always be my dream to bring the whole APEX community together so we share our passion and experience. And I think the APEX community is doing great; from the first APEX Meet-up at Oracle Open World in 2006 where we started with about 10 people till the recent one of a few weeks ago where we were with over 100 people, over the many conferences, especially ODTUG where APEX is huge. I build APEXBlogs to promote APEX and as a platform to share our knowledge (or even to become "the" APEX Portal).

I gave it a lot of thought, and I believe I brought APEXBlogs the furthest I could in the time I had and to take it to another level, things need to change...

Below is a short history in the life of APEXBlogs:

February 05, 2008 - Launch of APEXBlogs.info

Goal was to have "the" portal for the APEX Developer with links to great resources
and a place to see all blogs that write about APEX or APEX related content.
It also acted as a public showcase application of an APEX application.



November 23, 2009 - APEXBlogs also includes twitter messages



March 05, 2011 - APEXBlogs v2 goes live with many new features:

• global search
• knowledge base
• statistics
• APEX Usage
• new links
• Plugins
• use of interactive reports
• use of google reader in the backend
• iPad compatible
• new theme

April 06, 2011 - Webinar - How APEXBlogs was built

 I explained how I built APEXBlogs and how things looked like behind the scenes

October 25, 2012 - APEXBlogs part of odtug.com/apex

After more than 4,5 years APEXBlogs will move under the umbrella of a the Oracle user group ODTUG.

That's right, APEXBlogs will from now on redirect to odtug.com/apex ...

I believe there should be one Global APEX User Group in the world which has "the" conference and "the" platform where you find everything you need as an APEX developer. Don't get me wrong, I still think having local user groups is very useful too and the official Oracle sites (OTN, apex.oracle.com) are a must visit for every person! But as things start to expand so much, it's good that an organisation follows-up what is going on everywhere, and who's better than ODTUG to do this?

I had many talks with the ODTUG board and I'm convinced the APEX community is even better served when everything is under that flag...

Moving your APEX app to the Oracle Cloud

Now that the Oracle Cloud is publicly available, I want to show you what it takes to put your APEX application on there. 

As an example I will use the application "DGTournament" that I created in 2006 to promote APEX and upgraded every two years to the latest version of APEX. I thought this would be a good app to use as there's some legacy code in, but it also contains many new features that came with every release of APEX. Next to that you might actually have played with it, the app/site is still live and we can also do a side-by-side comparison of Amazon EC2 vs Oracle Cloud.

In this first post I want to take my current application with all it's data and move that onto the Oracle Cloud.

Step 1: Get an Oracle Database Cloud Service 

You can apply for an account on this link and you will receive some emails when you are ready to go as described in a previous post. 

Step 2: Connect to the Oracle Cloud from Oracle SQL Developer

Kris Rice did an excellent blog post about it. Next to that post I would definitely recommend reading the Oracle Cloud documentation as it contains important information how to connect with SFTP. You have to reset your password first, to do that you need to connect to IDM, all that is in the doc very well explained.

Step 3: Move your database objects, data and APEX app to the Oracle Cloud

Normally I would take a datapump export of my existing schema and take an export of my APEX application. So that was the route I wanted to follow, however I couldn't find the option to do a datapump import in the Oracle Cloud! (not through the tree, not through the Datapump wizard in SQL Developer)

After looking into the Oracle Cloud documentation again, it said you have to use the Cart option (which you find under the View menu) in SQL Developer. I used that before, I even asked for an enhancement request. 

So I connected to the database DGTournament is currently in and dragged everything from the tree into the Cart. (note that you need to specifically drag the package bodies, otherwise you just have the specifications) I also thought to be smart and dragged my APEX application into the cart (see next screenshot at the bottom)... 

So once everything is in the cart (and you can say you want all data for all tables as well) you can deploy to the cloud by clicking on the cloud icon (top, most left).

Fill out the details of the Oracle Cloud connection and hit Apply. A few minutes later all my database objects and data were in the Oracle Cloud! I was impressed how smooth it was.

The following screenshot shows the deployment overview and I expanded the tables tree of my Oracle Cloud connection so you can see everything is effectively in, even the BLOB columns and data got transferred without any issue.

As I dragged the APEX application in the Cart as well, I thought I would find it in my APEX Builder, but it wasn't there, so you have to manually import your APEX application.

Step 4: Import your APEX application (through the APEX Application Builder)

Importing an APEX app is not a big deal, export your app from one APEX instance and import through the wizards in the other APEX instance and done.

Step 5: Run your APEX application

Next I just ran the application but got an error ... it would have been wonderful if everything just worked, right? The backend was there and the app was there ... unfortunately I got an Oracle error ORA-... (see Step 6).

Step 6: Review (and try to recompile) all invalid database objects

And here is the biggest catch with the Oracle Cloud: some database packages are not available in the Oracle Public Cloud. So for example I recompiled one of my packages and got this error:

So after reviewing my code, following packages I used are not working in the Oracle Cloud:

• DBMS_OBFUSCATION_TOOLKIT
• I used this to hash the password
• DBMS_UTILITY
• I used it to calculate timings
• UTL_TCP
• I used it for crlf (character linefeed)
• UTL_SMTP
• I used it to send out mails with attachments

I also had in the APEX application direct calls to my PL/SQL package, for example to show an image I used in the src attribute of the img tab: "#OWNER#.tdg_pkg_content.SHOW_IMAGE?p_image_id=#IMAGE_ID#". But as you can't give the correct grants (to APEX_PUBLIC_USER) this doesn't work either.

Step 7: Make your code "Oracle Public Cloud"-compatible

So this basically means that all the package calls I do, that don't work, I will need to either remove or rewrite to something that does work.

For example the DBMS_UTILITY package I don't really need anymore as you can get timings native in APEX today, so not necessary to calculate yourselves.

The call to UTL_TCP for just the character linefeed I can remove too.

UTL_SMTP is interesting... in 2006 (and later in 2008), when I wrote some part of the application, there wasn't the apex_mail package yet that supported attachments, so that is why I had to use utl_smtp and write everything myself... but even using the apex_mail package wouldn't work as sending mails from the database in general is not supported in the Oracle Cloud. So the only way to solve that issue, is to integrate with a service (for example MailChimp) that sends emails for you.

And showing the images I wouldn't really code anymore using my own procedure either, as you can now use the native BLOB (image) support in APEX.

So that leaves me with DBMS_OBFUSCATION_TOOLKIT which I need to replace by something else that works in the Oracle Cloud.

Step 8: Review and Test the entire application (in all browsers)

There might be some code flying around in your application which should have been in packages (but isn't) or other components of the APEX app that are not compatible, for example some third party plugins which need some specific filesystem files and folders which you can't create in the Oracle Public Cloud.

To identify the code that should be in packages and which might not be compatible I use the APEX Advisor to find those and I did! When you upload your profile picture in DGTournament I create a thumbnail on the fly with ORDSYS.ORDIMAGE... and it looks like that's something you can't use either in the Oracle Public Cloud. It actually remembers me of one of my conversations with the Oracle team during my beta testing; I really would love to see Intermedia and Locator support in the Oracle Cloud as many of my applications are using that... the answer was it might come in the future, but it's not available yet.

For the rest you just need to go through your application and test it...

Step 9: Enjoy the Oracle Public Cloud

Once you have everything running and everything is compatible, you can enjoy the Oracle Public Cloud... Click this link to see DGTournament in action in the Oracle Public Cloud. (note that I didn't correct all incompatibilities yet and didn't upload all external files it needs)

In my next blog posts I will talk about other things like the performance, doing the maintenance etc. ... and my last post will be a conclusion.

Integration of Oracle Social Network and APEX

At Oracle Open World (OOW) Martin and I had a lot of fun integrating the Oracle Social Network (OSN) with Oracle Application Express (APEX).

In short there was a developer challenge at OOW where teams had to find a good use case to integrate OSN with the technology of your choice... Martin and I obviously used APEX... and we won! We hacked it together in one night! Jake (one of the organisers) wrote a nice blog post about it or you can read about it on the Oracle Blog. In that post there's a video of Martin and I smiling because the jury just told us we won :-)

Here's a video where you can see what we build:

I didn't really want to talk about the developer challenge in this post, but more on how you can integrate the Oracle Social Network with APEX. So lets concentrate on that now...

The Oracle Social Network is still in beta and we got only access to it during OOW (and a couple of days after to make the video). During the challenge we used the PL/SQL API which was part of the documentation (edit: I'm unsure what we got will be part of the production release, as it was Noel Portugal who build that example for the challenge. Thanks Noel!). As we have some great web service API's in APEX I immediately thought to try to use those. Unfortunately during the challenge we didn't have enough time to get things working, but straight after I discussed with Jason Straub and after he told me I didn't need to specifically set the complete headers, it was working great (Thanks Jason!).

Below I try to explain the most important parts of the code.

To integrate with OSN you can use RESTfull webservices. The OSN documentation is a great resource and the calls are well documented.
Line 119 shows the url you need to do to post a new message. You post a message to a conversation. In the full package you can see how to create a conversation too.
Line 120 shows what you have to post, basically a json string with the message.
Line 121 shows the REST request; you call a url, and do a post with in the body the json string. As everything is going over https you have to have your Oracle wallet configured with the certificate.

If you look at the complete osn_pkg package, the submit_osn procedure is the call we do in the video. As OSN is secured you first have to connect with a username and password. You get back a cookie and random id and you have to set that in the header of all the next calls.
By using the apex_web_service package you only need to set the headers once. Next in the procedure we create a new conversation, we add more members to it so others can participate in the conversation. In the conversation we add a message and a document (screenshot) if there's one.

I made the package open source on GitHub, so you can see the complete package there and you can quickly integrate with OSN from APEX. The only thing I want to ask is, if you improve it or integrate other parts of the Oracle Social Network once it launches, give your code back as well so we can use this project as a great source for the OSN and APEX community.

Hope you like it...

Reading Oracle (APEX) docs on iPad

With the new release of APEX (or Oracle in general) it's always useful to re-read the documentation again, even if it's just the release notes and the installation guide...

I use my iPad a lot to read articles and books, especially when I'm offline it's very useful.

You can go to the site and read the documentation in HTML or download the PDF, but you can also download the documentation in ePub format (for examples the release notes of APEX 4.2).
When you click on the HTML link, the ePub link will appear for that document.

I find the ePub format useful as I can read offline and set some bookmarks for certain pages.

Hope you like this quick tip!

Changing to Responsive Theme in APEX 4.2

If you read my previous post about APEX 4.2 being in production you know I really like Responsive web design.

So what does it take to change your current theme to a responsive theme in APEX 4.2?

The first thing you have to do is create a new theme based on "Theme 25: Blue Responsive". Go to Shared Components > Themes and follow the wizard to create a new (Desktop) theme from the repository.

Once you created the new theme you need to follow the wizard to switch your themes.

Note that if you customised your current theme to include for example some custom JQuery libraries or your own css files, those won't be included in the new theme. You can always add that later. 

But once you switched your theme, that is about it... you have now a responsive theme and your app is using it... but here's the catch... it might not use it yet to the full extend and it might not respond as you would like it to do. The reason is your regions and page items are now making use of the new Grid Layout, but the wizard did only as good as it could, so you need to verify every page yourself and see if it looks like you want.

Let's take the example of the Feedback Page in an old Sample Database Application I had in my workspace. After I switched the theme the page looked like this:

Not really nice heh?  The display only text is shifting down and the icons are not next to the items, instead they are underneath. Also the buttons seem a bit small in this design.

Here's the backend of the page:

You see P102_A, P102_X and P102_Y? Those are Start Stop Table item types, to align items and buttons nicer...

Note that in APEX 4.2 that item got renamed to Stop and Start Grid Layout and it is even deprecated now... and that makes perfectly sense, table based layout is passé, welcome to div and css or even better HTML5 and CSS3! 

So first thing I would do is just get rid of those items.

The next big thing is how the items are set on the grid. The wizard moved the items on Column Span 1 (so it starts on Row 1 and it takes only the first column of the grid, in total there are 12 columns in the grid). It looks like one column span is a bit small for our content, so either you make that bigger, or you put Column Span to Automatic so APEX itself figures out what to do, which I would recommend (to start with at least).

If you have many items and you don't want to go into every item separately you can right click in the tree view and "Edit All" page items at once. You need to remove the one in Column Span for every item, that will set it to Automatic.

To make our Feedback page a bit nicer, change the region template to "Alert Region".
Edit the buttons and chose the Large Button (Hot) as the template.
Run your feedback page again and this is the result:

I know which one I prefer :-)