Wednesday, July 26, 2017

Easy Dashboard using nothing but APEX, Font APEX and SQL!

A customer from Tennessee recently asked for help in creating a simple dashboard in their Oracle APEX application.  In the PHP system they were coming from, they had a dashboard that looked like the following:



Most people think of dashboards as a nice cockpit panel containing charts and graphics.  While this example doesn't perfectly fit that description, it can be classified as a report that is summarized, and any elements which need attention are presented in a different color.

I've implemented similar solutions in the past, selecting an image reference in the SELECT clause of my report query, and then referencing this image reference as the column value.  But this time, I first solicited the opinion of Shakeeb Rahman, the Design Lead for Oracle APEX, and he provided me a better solution.  Using a simple combination of SQL and Font APEX, this can be easily and elegantly solved!

For this example, I created a new table CITY_STATUSES

create table city_statuses (
    city_name varchar2(100) primary key, 
    status1   number, 
    status2   number, 
    status3   number);

I populated it with data, and then I created a new application with an Interactive Report on the table.  The query of the Interactive Report was simply:

select city_name,
       status1,
       status2,
       status3
  from city_statuses

and my initial report looked like:



In my example, 1 is a good condition, 0 is a warning, and -1 indicates that an action must be taken.

Universal Theme


Universal Theme is a responsive, versatile, and customizable user interface for your Application Express apps.  The Universal Theme in Oracle APEX 5.1 includes Font APEX, a drop-in replacement for Font Awesome, but with better graphics and more of them (courtesy of master graphic artist Bob Daly).  You can learn more about the Universal Theme at https://apex.oracle.com/ut, and you can learn more about Font APEX at https://apex.oracle.com/fontapex.

Shakeeb recommended I use Font APEX and the Universal Theme helper classes to solve this problem.  The helper classes can be used to set the colors on any custom component.  You can find these Universal Theme helper classes at https://apex.oracle.com/ut -> Reference -> Color and Status Modifiers.  What's nice about these colors is that they are coordinated with the Theme Roller in APEX.  If you change the global success color in Theme Roller, the icon color will also be updated.

To solve this specific problem for the dashboard, I selected two additional columns in the SELECT clause for each STATUS column:
  1. status_icon - String representing the icon class and modifier class
  2. status_description - Description of the status icon, for accessibility purposes.  This is very important, because we are changing from a discrete value in the report to an icon and a color.  Without the description column, this information will be inaccessible.
For the icons and modifiers, I used:
  • Success: fa-check-circle-o u-success-text
  • Warning: fa-exclamation-triangle u-warning-text
  • Error: fa-exception u-danger-text

The Solution

For each status column in my SELECT clause, I added a corresponding icon and description column:


 select city_name,
        status1,
        status2,
        status3,
        case status1 
            when 1  then 'fa-check-circle-o u-success-text'
            when 0  then 'fa-exclamation-triangle u-warning-text'
            when -1 then 'fa-exception u-danger-text'
        end status1_icon,
        case status1 
            when 1  then 'OK'
            when 0  then 'Warning'
            when -1 then 'Danger'
        end status1_description,        
        case status2 
            when 1  then 'fa-check-circle-o u-success-text'
            when 0  then 'fa-exclamation-triangle u-warning-text'
            when -1 then 'fa-exception u-danger-text'
        end status2_icon,
        case status2 
            when 1  then 'OK'
            when 0  then 'Warning'
            when -1 then 'Danger'
        end status2_description,                
        case status3 
            when 1  then 'fa-check-circle-o u-success-text'
            when 0  then 'fa-exclamation-triangle u-warning-text'
            when -1 then 'fa-exception u-danger-text'
        end status3_icon,
        case status3 
            when 1  then 'OK'
            when 0  then 'Warning'
            when -1 then 'Danger'
        end status3_description
  from city_statuses

After saving the updated query for the Interactive Report, I edited these columns in Page Designer and changed the property Type from Plain Text to Hidden Column.

Then, for the columns STATUS1, STATUS2 and STATUS3, in Page Designer I changed the property HTML Expression to:

<span class="fa #STATUS1_ICON#" title="#STATUS1_DESC#"></span>

Obviously, replace STATUS1 with the correct corresponding column name. I adjusted the heading and column alignment of each column to center, and voila!  It couldn't be easier.



If for some reason you want to make the icons even larger, no problem!  Simply add the fa-2x modifier in the HTML expression (after #STATUS1_ICON#).

Experiment with the modifiers of Font APEX at https://apex.oracle.com/fontapex.  Choose your icon, vary the size, animation, modifier, and status.  Just don't go crazy - we don't want to see the world's APEX apps introduce the equivalent of the <marquee> tag again.

Shakeeb presented the Universal Theme, these modifiers, and much more in a recorded Webinar from ODTUG.

P.S.  While you might be tempted to simplify the query and use an inline PL/SQL function in the WITH clause of the query, you most likely will encounter error "ORA-32034: unsupported use of WITH clause".  This is because the Interactive Report will enclose your original query in a subquery, and in general, inline PL/SQL functions in subqueries are intentionally prohibited by the Oracle Database.  However, the solution above also works with a Classic Report, and in that case, you could use an inline PL/SQL function in the WITH clause of the query.

Wednesday, July 19, 2017

Oracle Application Express Curriculum Announcement - an Addendum

Earlier today, on the "official" Oracle APEX blog, I published the announcement of the release of the Oracle Application Express curriculum with a permissive-use license.

Firstly, many thanks go to Chaitanya Koratamaddi, a product manager on the Oracle APEX team, based out of Hyderabad, India.  Chaitanya worked tirelessly on the development of this curriculum over the past year.  It's been a grand goal of ours to quickly grow the global APEX community, and one of the best ways to do this is by working with educators.  As I said in an earlier blog post, while education in Oracle Application Express (APEX) is offered as a part of many university and secondary school courses around the globe, in most cases, the educators took it upon themselves to develop their own custom curriculum.  We wanted to make it easy for new educators to adopt Oracle Application Express as part of a secondary school or university curriculum, and this freely offered curriculum will go a long way towards that.

Once the curriculum was developed and published, then the real fun began, namely, working within Oracle to have the curriculum released with a permissive use license.  One of the primary reasons for a permissive-use license was to be able to engage the ever-vibrant Oracle APEX community in the crowd-sourcing of translated content, as evidenced by translate-apex.com.  If this curriculum content was provided with a standard Oracle copyright, then translation of the content by a third-party couldn't even be considered.  We were told from the outset that it was very unlikely this request for a permissive-use license would ever be approved by one or more of the approving lines of business within Oracle.  We were asked if this was this ever done before at Oracle, and to our knowledge, it was not.  Setting precedent is never a good position to be in when you're humbly seeking approvals. ;)

However, after numerous months and rewriting the business justification numerous times, all approvals were obtained within Oracle.  And from this, I have two simple observations:

  1. Oracle is changing, and for the better.  Five years ago, there is zero chance this would have ever been approved within Oracle.  Zero.
  2. Oracle Application Express leads this great company...again.


If you're interested in the freely available curriculum for Oracle APEX, please visit apex.oracle.com/education.

Thursday, July 13, 2017

Built with ❤️ using Oracle Application Express (APEX)

I couldn't get to my keyboard fast enough, to write this blog post.  Shakeeb Rahman showed me something last night that I wanted to share with the awesome APEX community as soon as possible.

APEX is used in literally thousands of applications within Oracle.  And in some of the latest apps that we're writing ourselves, in the footer we're including a short phrase which lets people know it was "built with Oracle APEX."  We're proud of the app, proud of the UI, and we want people in the company to know that it's an APEX app.  But Shakeeb showed me something last night that took this to a whole new level!



The instructions to add this to your APEX 5.1 application are extraordinarily simple:

  1. Create a new region on the Global Page (page 0) and set the Region Position to Footer. Set the Region Template to Blank with Attributes (no grid).

  2. Use this HTML as the Region Source:
    <span class="footer-apex">Built with 
      <span class="fa fa-heart">
        <span class="u-VisuallyHidden">love</span>
      </span> 
      using <a href="https://apex.oracle.com/" target="_blank" title="Oracle Application Express">Oracle APEX</a>
    </span>
    
  3. Add this CSS to your Theme Style by opening Theme Roller and pasting into the Custom CSS section:
    .footer-apex {font-size: 11px; line-height: 16px; display: inline-block; vertical-align: top;}
    .footer-apex .fa.fa-heart { vertical-align: top; font-size: 10px; 
        line-height: 16px; width: 16px; text-align: center; 
        transition: color 1s ease; }
    .footer-apex:hover .fa.fa-heart { color: #FF0000; animation: pulse 1s infinite; }
     
    @keyframes pulse {
        0% { transform: scale(0.9); }
        70% { transform: scale(1.25); }
        100% { transform: scale(0.9); }
    }
    

That's it!  Simple.  It might work in APEX 5.0 and later, but it will definitely work in APEX 5.1 and later.

I encourage everyone in the APEX community to add this to the footer of their applications.  Many end users don't even know they're using an APEX application, or even what APEX is.  This is an easy way to show it, and and show it with style!

BOOM!


Sunday, July 02, 2017

The APEX Community & ODTUG Kscope

Another successful Oracle Development Tools User Group (ODTUG) Kscope conference is in the books.  And like every year, the global APEX community convenes at Kscope. It is really an amazing collection of experts and first-time attendees from around the globe - from the USA, Germany, England, Australia, Croatia, Brazil, Estonia, India, Austria, Belgium, Netherlands, Canada, Poland, Finland, New Zealand and more!

Make no mistake - I appreciate and fully support many other conferences around the globe where APEX has a presence, including:

  • OUG Ireland
  • OUG Scotland
  • APEX World
  • APEX Connect
  • Great Lakes Oracle Conference
  • Oracle OpenWorld
  • Slovenian Oracle User Group
  • Croatian Oracle User Group
  • Oracle Developer Tour Latin America
  • DOAG Konferenz + Ausstellung
  • UKOUG Technology Conference and Exhibition
  • High Five Polish Oracle User Group

But if you're in the APEX Community, or you want to join the passionate and growing global APEX community, please plan on attending an ODTUG Kscope conference.  Just once.  You'll thank me later.

Registration for Kscope18 is already open!


Green means GO! APEX sessions at Kscope.


APEX Sunday Symposium


APEX Open Mic Night


#LetsWreckThisTogether APEX Talks


Thursday, June 08, 2017

How long will Oracle APEX remain an included feature of the Oracle Database?

Another great question today from an internal employee.  Tom, a Managed Cloud Services Delivery Director, asked:
"I am the OMCS Client Manager for <redacted>.  They are currently a large Discoverer customer who would like to convert to Oracle APEX.  To that goal, they have asked me to find out how long Oracle APEX will continue to be a no-cost feature of the Oracle Database (could be an unanswerable question, I realize)."
Great question!  And my response to Tom:

  1. Oracle Application Express has been an included feature of the Oracle Database since 2004.  It remains an included feature of the Oracle Database in 12cR2 and in the foreseeable future.
  2. There are no plans to charge separately for Oracle Application Express.  It has *never* been discussed.
  3. Application Express is a feature of every Oracle Database Cloud Service (Database Schema Cloud Service, Database Cloud Service, Exadata Express, Exadata Service)
  4. In my 21 year career at Oracle, I have never seen a situation where Oracle has included something as part of the database license and then decided later to charge separately for it.  It might have happened, but I'm not aware of it.  I have seen the reverse (where we used to charge a separate license fee, and then eventually included it with the Oracle Database - Oracle Text is a good example).

People often classify APEX as "free".  I call it "an included feature of the Oracle Database." If you're licensed and supported for the Oracle Database, you're licensed and supported for Oracle Application Express.  APEX runs wherever the Oracle Database runs - on premises, your cloud, Oracle Cloud.  We have customers running APEX on a laptop on the free version of the Oracle Database Express Edition (XE), we have customers running APEX on 28-node Oracle RAC clusters, and everything in-between.

If you're interested in learning more about APEX, see A Simple Guide to Oracle Application Express (APEX).

Wednesday, June 07, 2017

Is there a mailing list for APEX release announcements?

There was an interesting question today from Oracle Support, on behalf of a customer.  They asked:
"The customer would like to know if there is any mailing list or other type of subscription to be notified whenever there is a new or upgrade release of APEX."
And I answered with:

As far as mailing lists, they could always sign up for the Oracle Database Insider Newsletter.  Announcements about Oracle Application Express, SQL Developer, Oracle REST Data Services and many other database-related technologies are typically included in this newsletter.

I can also suggest that your customer:
  1. Follow Oracle Application Express on Facebook:  https://www.facebook.com/orclapex/
  2. Join the LinkedIn group:  https://www.linkedin.com/groups/8263065
  3. Follow @oracleapexnews on Twitter:  https://twitter.com/oracleapexnews
  4. Visit the Web site:  https://otn.oracle.com/apex
The APEX releases and patch sets are always announced through these social media channels.

And let's not forget https://apex.world, which is the clearinghouse of information for everything APEX.

Tuesday, June 06, 2017

excel2collection functionality of ORDS will not work with APEX 5.1 or later

If you're using the functionality of Oracle REST Data Services (ORDS) with Oracle Application Express to parse a Microsoft Excel file and store the parsed results in an APEX collection, I have good news and bad news.

Bad News:  This functionality will no longer work in Application Express 5.1 or later.  Due to architectural changes in the way that pages are submitted in APEX 5.1, this functionality was unfortunately (but necessarily) broken with no practical way to restore it.  And due to other necessary architectural changes, the bundled library which does the Excel parsing in ORDS is being removed in a future version of ORDS.

Good News:  Some customers have reported that they have successfully replaced this functionality using Anton Scheffer's EXCEL2COLLECTIONS plug-in.

We take very seriously our desire to always preserve existing functionality for customers, and to try to always avoid any change in user interface or functionality upon upgrade.  But in this case, it was simply not practical.

Thanks to Denes Kubicek for prominently raising this issue on the OTN discussion forum.