Tuesday, September 29, 2015

Column Group Headings in an Interactive Report (APEX 5.0)

In 2008 I blogged about how to add Group Headings into an Interactive Report in Oracle Application Express (APEX 3.x!).

Here's an example what I mean with Group Headings, you want to logically group certain columns (e.g. Empno, Ename) and have a group name above them (e.g. Group 1):

The approach I used at that time worked, but it required a lot of manual work. In 2009 Martin D'Souza improved it and wrote a blog post about that. In 2010, when plugins were introduced in APEX 4, Martin wrapped it all in a nice APEX plugin which made adding Column Group Headings almost declarative. The plugin was further improved and put on GitHub.

Today, having APEX 5, and the Interactive Reports being changed a lot behind the scenes, the plugin was broken. As we're in the middle of restructuring how to share code, plugins etc., I forked Martin's git repo and made the plugin compatible with APEX 5 and Universal Theme (see version 1.0.4), so when you need it, you can grap it from the forked repo. At a later time the plugin will be moved to OraOpenSource, so depending when you read this post, check out OraOpenSource as Martin, I and many other developers will start putting our open source code on OraOpenSource.

Here's a quick how to use the plugin.

Step 1: Download the file dynamic_action_plugin_com_clarifit_apexplugin_ir_column_grouping.sql
Step 2: Import the plugin in your APEX application
Step 3: Define the Groups in the Interactive Report (you can right click on Column Groups in the  Attributes of your Interactive Report and add the Groups you need):

Step 4: Assign the group to the columns of your report (you can multi select the columns and in the right pane you can select the group):

Important: define for every column that has a group defined, a static id equal to the name of the column (e.g. ENAME).

Step 5: Add the dynamic action plugin to the page
- Event: After Refresh
- Selection Type: Region
- Region:
- IR Column Grouping [Plug-In]

That's it... note that when you scroll down and you have sticky headers, the group will stay with it.

Sunday, September 27, 2015

Custom image on your APEX app login page

In a comment on my post APEX 5.0: pimping the Login page I got a question how to put your own logo or a custom image on the login page, instead of an icon.

You only need a bit of CSS to do the trick:

You can add the above CSS to your login page - Inline CSS in page attributes or you can add it to your page template or custom CSS in Universal Theme.

The result is this:

To differentiate the login page you can do a lot more, here're some things we show during our APEX UI training:

  • add transparency to the login box
  • add a background image to your entire page (blurred or not)
Again, you can do that with just some CSS.

Friday, September 25, 2015

ORDS... Yes we can!

On September 13th I got a nice surprise, an email of Steven Feuerstein with the message: "You have been selected as a finalist in the ORDS category for the 2015 Oracle Database Developer Choice Awards!"

It's always nice to get recognition for the efforts you do, so thanks so much already for the nomination. I didn't make publicity yet in order to get some up-votes, but I hope by reaching out to the people who read my blog, I gain some more up-votes :)


To be honest, I wondered why I deserved this nomination, especially in a category that I'm probably less "known" for.
After giving this more thought, I remembered ORDS actually started being very much linked to APEX. In the early days ORDS was even being called the "APEX Listener", and I was one of the early adopters and promoters of using the APEX Listener (now ORDS) in your APEX architecture.

In 2012 I literally travelled around the world (Belgium, the Netherlands, UK, San Francisco, San Antonio, Uruguay, Brazil, Chile) to talk about why you should move to the APEX Listener.

When looking back at that presentation, today it still stand; you still have different options for your APEX architecture, but we don't have to convince anymore about the benefits of choosing for ORDS. ORDS is now main-stream, widely adopted and proven technology. Unless it's a legacy system, I don't really see any reason anymore why you should not use ORDS in your architecture.

ORDS evolved a lot over time, and the new name reflects more what the core feature is "Oracle REST Data Services". REST web services became so important in the last years and that is exactly what I've been blogging and talking about lately (see further).

In my presentation of Microsoft Sharepoint and Oracle APEX integration (given in San Francisco, BeNeLux and Germany) I talk about the architecture and how you get your APEX data in Microsoft Sharepoint by using ORDS. But also the other way round, by using Sharepoint APIs, REST web services come into play. When you want to integrate with other systems, ORDS can really help.

I didn't blog much yet about a really interesting R&D project we've been working on in the last months - using wearables to capture sensor and patient data. At the Oracle Mobile day in the Netherlands I did a presentation which explains it in more detail. We developed native smarth phone applications that call REST web services all build in ORDS. ORDS is getting or pushing the data into our Oracle Database using JSON. Next to that we've dashboards in APEX to "see" and work with the data. We learned and still learn a lot of this project; about the volume of data, the security etc. but without ORDS it would not have been possible.

And finally a product where I'm really proud of APEX Office Print (AOP). I found it always a challenge to get documents out of APEX. I'll do some more blog posts about it in the future, but where APEX is so declarative to build your web applications, it's far from declarative to get documents out in Word, Excel, Powerpoint or PDF (at least without BI Publisher). With APEX Office Print we hope to address that. Just one APEX plugin where you define the template and which data you want to use and presto you get your document. Behind the scenes we use JSON and having ORDS makes it really easy to generate it. If you want to know more about the components behind the printing solution (and do it yourself), you'll find that in my presentation about Printing through Node.js which I presented in different countries and will present at Oracle Open World.

Furthermore, if you're interested in reading more about JSON, I've done a series of blog posts and was interviewed by Oracle about it, you find the links here.

Hopefully by doing this post you see the power of ORDS and you get some ideas yourself how to best leverage the power of this wonderful piece of software. If you liked my "ORDS-efforts" and want to give me an up-vote in the Database Developer Choice Awards, I really appreciate that.

Thanks so much,

Monday, September 14, 2015

Report Carousel in APEX 5 UT

The Universal Theme in APEX 5.0 is full of nice things.

Did you already see the Carousel template for Regions
When you add a region to your page with a couple of sub-regions and you give the parent region the "Carousel Container" template it turns the regions into a carousel, so you can flip between regions.

I was asked to have the same functionality but than on dynamic content.
So I decided to build a report template that would be shown as carousel. Here's the result:

I really like carousels :)

Here's how you can have this report template in your app:
1) Create a new Report Template:

Make sure to select Named Column for the Template Type:

Add following HTML into the template at the given points:

That's it for the template.

Now you can create a new report on your page and give it the template you just created.
Here's the SQL Statement I used:

select PRODUCT_ID          as id,
       PRODUCT_NAME        as title,
       PRODUCT_DESCRIPTION as description,
       dbms_lob.getlength(PRODUCT_IMAGE) as image,
       'no-icon'           as icon,
       null                as link_url 

Note 1: that you have to use the same column aliases as you defined in the template.
Note 2: make sure you keep the real id of your image in the query too, as otherwise you'll get an error (no data found)

To make the carousel a bit nicer I added following CSS to the page, but you could add it to your own CSS file or in the custom css section of Theme Roller.

Note: the carousel can work with an icon or an image. If you want to see an icon you can use for example "fa-edit fa-4x". When using an image, define the icon as no-icon.

Eager for more Universal Theme tips and tricks? check-out our APEX 5.0 UI training in Birmingham on December 10th. :)

For easier copy/paste into your template, you find the source below:

 *** Before Rows ***  
 <div class="t-Region t-Region--carousel t-Region--showCarouselControls t-Region--hiddenOverflow" id="R1" role="group" aria-labelledby="R1_heading">  
  <div class="t-Region-bodyWrap">  
   <div class="t-Region-body">  
   <div class="t-Region-carouselRegions">  
 *** Column Template ***  
 <div data-label="#TITLE#" id="SR_R#ID#">  
  <a href="#LINK_URL#">  
   <div class="t-HeroRegion " id="R#ID#">  
        <div class="t-HeroRegion-wrap">  
         <div class="t-HeroRegion-col t-HeroRegion-col--left">  
          <span class="t-HeroRegion-icon t-Icon #ICON#"></span>  
         <div class="t-HeroRegion-col t-HeroRegion-col--content">  
          <h2 class="t-HeroRegion-title">#TITLE#</h2>  
         <div class="t-HeroRegion-col t-HeroRegion-col--right"><div class="t-HeroRegion-form"></div><div class="t-HeroRegion-buttons"></div></div>  
 *** After Rows ***  
 *** Inline CSS ***  
 .t-HeroRegion-col.t-HeroRegion-col--left {  
 .t-HeroRegion {  
  border-bottom:0px solid #CCC;  
 .t-Region--carousel {  
  border: 1px solid #d6dfe6 !important;    
 .t-HeroRegion-col--left img {  
  max-height: 90px;  
  max-width: 130px;  
 .no-icon {  

Thursday, August 20, 2015

Expert Oracle Application Express (second edition) available

During my holiday Apress released the second edition of Expert Oracle Application Express.
In the second edition we not only updated the content to APEX 5.0, but there're some new chapters in too from new authors. In total you get 14 chapters from 14 different authors.
I believe it's a very nice book with great content and it's for a good cause. Roel describes it very nicely in his blog post "the goal is to raise as much money as we can for the funds that support the relatives of two of the greatest Oracle APEX Development Team members who passed away a few years ago: Carl Backstrom and Scott Spadafore."

You can get it from the Apress website or from Amazon. Happy reading :)

Thursday, July 16, 2015

First patch set (5.0.1) released for APEX 5.0

I know some people waiting till the first patch set that comes available after a major Oracle APEX release... today you no longer have to wait, APEX 5.0.1 is now available.

In the patch set notes you can read what changed.

If you're still on APEX 4.x you can go immediately to APEX 5.0.1, you only need to download the latest version on OTN.

If you're already on APEX 5.0, you can download the patch set from support.oracle.com, search for patch number 21364820. Applying the patch took less than 5 minutes in my environment.

This patch set updates the Universal Theme too, so don't forget to update your images folder. When you login in APEX after the patch, it will check if you have the correct images folder, if not it will give you an alert. Although I updated the images directory I still got that alert due to browser caching. Refresh your page and it should be ok.

Note that it's important to be on APEX 5.0.1 when you use APEX Office Print - currently available in beta for a select audience, public release end of this month (July 2015). Behind the scenes we use the APEX_JSON and APEX_WEB_SERVICE packages which got an update in APEX 5.0.1.

And finally, there's a nice new D3 chart available in APEX 5.0.1 called "D3 Collapsible Treemap Chart"

Happy upgrading...

Update 18-JUL-2015: there're still a couple of known issues, please read those too and install some patchset exception when necessary.

Tuesday, June 30, 2015

Make even more of UKOUG Tech15: APEX 5.0 UI Training - Dec 10th in Birmingham

APEX 5.0 has been released this spring. People who have already spent some time on this new version know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.
Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way. 
The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller. To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!

The training will be at the Jury’s Inn in Birmingham (UK) on Thursday Dec 10 - so conveniently immediately after the UKOUG Tech15 conference.
More information and registration see www.apextraining.eu

If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!