Joel Kallman

Subscribe to Joel Kallman feed
Turning customers into rockstars, with the Oracle Database, PL/SQL and Oracle Application Express.Joel R. Kallmanhttp://www.blogger.com/profile/01915290758512999160noreply@blogger.comBlogger203125
Updated: 6 hours 9 min ago

A Great APEX Developer is.....a Full Stack Developer!

Thu, 2017-10-05 07:32


I've heard these phrases from customers:

  • Where can I find Oracle APEX developers?  
  • I'm having a tough time filling a vacancy for an APEX developer.  
  • I'm getting pushback from my own management because they say Java developers are much easier to find than APEX developers.

The last comment was from Peter, a potential customer, who recently posted this on Twitter.

Make no mistake about it.  If you go to indeed.com or dice.com or monster.com, you're going to find far more openings for .NET or Java developers than Oracle APEX developers.  But I would like to take a step back and ask - what makes a really great Oracle APEX developer?  Well, a great APEX developer is competent in:

  1. The Oracle Database (installation, setup, tuning, monitoring)
  2. Data modeling
  3. SQL (and SQL tuning)
  4. PL/SQL
  5. User Interface Design
  6. Responsive Design
  7. HTML
  8. CSS
  9. JavaScript
  10. Web applications
  11. Web servers
  12. Web security
  13. Network security
  14. Server administration
  15. Continuous integration
  16. Unit testing
  17. Application Express (APEX) development

...and probably a few more not even listed here.  It's an amazing list of very specific and relatively complicated technologies.  Don't be alarmed if you consider yourself a rockstar developer with APEX and you are competent in less than half of these areas.  I can't really think of anyone on our own Oracle APEX Development team who is intimately familiar with all of these technologies.  A few experts in the APEX community can truly claim to be proficient in all of these areas, but they're finite in number.  If you look at this list of technology areas, you will quickly conclude:

If you're looking for a great APEX developer, you're really looking for a full-stack developer

...and those are pretty rare in the industry.  Let's look at a couple examples.

It's easier to find Java developers than it is APEX developers.  Sure, I agree.  But it's a common misconception that because someone knows .NET or Java (or PHP or Python or Ruby or JavaScript or whatever) that they are competent in elegant and attractive user interface design.  Just because you know a programming language that is commonly used in Web applications has no correlation to you having the ability to create rich and elegant UI.  This is a very rare skill, and one I see even some experienced APEX developers wrestle with.

As another example, I heard a manager say that he was going to pursue building an application with Oracle JET because it's easier to find JavaScript developers.  We on the APEX team love Oracle JET and want Oracle JET to succeed (so much so, that we've integrated Oracle JET to produce the data visualizations in APEX 5.1!).  But this manager's statement was a bit misguided, because 1) your knowledge of JavaScript doesn't mean you'll immediately know how to use Oracle JET, you'll need to learn it, and 2) what about the rest of the stack?  Is a JavaScript developer good at data modeling?  Are they competent in authoring and tuning SQL?  Do they know when it's preferable to use PL/SQL in their SQL and when not to?  Or is that someone else's job?

My advice when you're looking for APEX developers - determine what is most important to you, and what skills and technologies you need the most assistance with.  APEX development is ultimately Oracle Database development (Smart DB development, as some would say), and experience with SQL and PL/SQL and data modeling is always relevant.  If you have a subpar data model and poor SQL, your application will suffer regardless of front-end technology.  If you have no knowledge of Web applications and you are rich in Oracle Database development talent, then focus instead on those who bring Web application development experience to the table.  I've found that the customers who "get it" the most are those who have done large-scale Web app development in another language or technology.  They are the ones who can appreciate the framework of APEX and how it enables them to focus on the business problem at hand, instead of focusing on all of the necessary minutiae typically associated with modern Web app development.

When reviewing potential candidates, don't focus exclusively on APEX experience.  APEX can be on the wish list of skills, but someone adept at database development or Web development can usually pick up APEX relatively quickly.

Oracle Database application development with APEX and Oracle REST Data Services (ORDS) continues to grow, for both on-premises applications and the cloud.  Industry analysts are responding to more and more inquiries about APEX than ever before.  Countless colleges and universities are taking advantage of the free Oracle APEX curriculum and the free offering by Oracle Academy, helping to familiarize thousands of students on appdev with the Oracle Database & APEX.  The Oracle APEX community is vibrant and growing.  There's a reason why APEX skills are in high demand.

P.S.  If you've stumbled upon this blog post and have no idea what Oracle APEX even is, I suggest you review the Simple Guide to Oracle Application Express.

The Insurance of Oracle APEX - Can Your Low Code Framework Do This?

Thu, 2017-09-14 08:27
Oracle Application Express (APEX) runs wherever the Oracle Database runs.  I've said these words to thousands of customers and partners for the last 15 years.  So why should you care?  Simple.  Peace of mind.

Last evening, Amazon Web Services tweeted the following announcement:

"Use Amazon RDS for Oracle with Oracle APEX versions 5.0.4 & 5.1.2 to build applications within your web browser."

Now, one might think I'm daft for even mentioning AWS on my blog, but I think this presents an opportunity to emphasize two very important points about Oracle APEX:

  1. While I believe that Oracle Cloud is superior for running your Oracle APEX applications, if for some reason you need to not run it on the Oracle Cloud at some later date, you have that choice.
  2. Oracle APEX is not exclusively a cloud-only solution.  You can run it in the Oracle Cloud (my preference), you can run it on AWS, you can use a number of other hosting providers, you can run it with other infrastructure providers, but you can also choose to run it on-premises.  And you can change your mind among these options whenever you wish.  This is very powerful.

Oracle Application Express is a part of every Oracle Database Cloud Service from Oracle, including:

There are other cloud services coming from Oracle later this year which will also include and feature Oracle APEX.

If you think that the Oracle Database is only good for storing data (i.e., a "bit bucket"), you'd be wrong.  If you're unfamiliar with Oracle APEX, please check out this Simple Guide to Oracle APEX.  From bespoke applications in the Oracle Cloud & on-premises to Oracle ERP Cloud PaaS Extensions using Oracle APEX, our customers and partners are really killing it with the Oracle Database and Oracle APEX.


P.S.  Kudos to Amazon for referencing it as Oracle APEX and not Oracle Apex!

Emails That I Do Not Like to Receive

Fri, 2017-09-01 09:07
There have been numerous articles and blog posts about how to properly write an email, or how to properly ask a technical question. These seem to be rarely read or they are simply misunderstood.  So let me tell you about the types of emails I receive with seemingly greater frequency, and which I completely abhor.


Message Body: +Joel

What they're saying:  Rather than provide a clear and succinct explanation why I'm including you in this email thread, it's my expectation that you will read the 15 messages and replies below and discern the issue being discussed.  You are to also guess why I'm including you in this thread, and if there is an action item for you in the message below, it's expected that you will find it and take action immediately.

Sentiment implied:  My time is more important than yours.


Message Body: +[someone I don't know]

What they're saying:  Rather than provide a clear and succinct explanation why I'm including this new person in this email thread, it's my expectation that they will read the 15 messages and replies below and discern the issue being discussed.  They are to also guess why I'm including them in this thread, and if there is an action item for them in the message below, it's expected that they will find it and take action immediately.  Additionally, I am choosing to subject everyone else to a barrage of followup emails in the near future, as this new recipient will surely reply all, and ask numerous questions about this topic and why they were included on the email in the first place.

Sentiment implied:  My time is more important than the person's whom I've added to this email thread.  And everyone else's, for that matter.


Message Body: Joel, since [one member of your team] is on vacation, can you please help with the issue below?

What they're saying:  It's possible that you don't have any context in the long-running issue which is discussed in the 15 messages and replies below.  I was too busy to provide a succinct explanation or even ask if you're familiar with the issues below, so can you please read all of the messages below and guess at what further information may be required?

Sentiment implied:  My time is more important than yours.


Any time I receive emails like this in the future, I'll respond back to the authors with a link to this succinct and lucid blog post.  Maybe I'll even include an explanation why I'm sending the link!

An Important Change Is Coming for Application Express 5.2 (if you use the Mobile User Interface)

Tue, 2017-08-22 07:24
An important change is coming in Oracle Application Express 5.2.  The mobile user interface (based upon jQuery Mobile) will be deprecated.  The deprecation announcement will be included with the forthcoming Application Express 5.1.3 patch set release.

What this means for you
  • If you have applications developed with the mobile user interface based upon jQuery Mobile, they will continue to function in a supported fashion in Application Express 5.1.x.  Premier Support of Oracle Application Express 5.1.x continues until December 2021.
  • New mobile applications should be created using the Desktop User Interface and Universal Theme (Theme 42) and not the Mobile User Interface & jQuery Mobile-based theme (Theme 51).
  • Existing mobile applications should be migrated to the Universal Theme if you want to run them on Oracle Application Express 5.2 and later.

Why is this happening?The last stable version of the jQuery Mobile library was released on October 31, 2014, and it only provides support for jQuery versions 1.8 through 1.11, and jQuery 2.1.  Patches are no longer provided for these versions of jQuery.  Application Express 5.2 will ship with jQuery 3.2 (necessary for us to adopt the latest Oracle JET libraries).  jQuery Mobile will not function properly with jQuery 3.2.

While we will make every effort to devise a way to include the necessary libraries for jQuery-mobile based applications in Application Express 5.2, that may not solve all problems entirely, especially when we try to include the data visualizations via Oracle JET, which have different dependencies.  We do truly strive to ensure backward compatibility of APEX applications across upgrades.  It's of paramount importance to us, and we spend a lot of time and energy ensuring that stability, operation, look and feel remain constant across APEX version upgrades.  But in this specific case, there doesn't appear to be a practical solution.  We're in a box.

There has been some recent activity in a new version of jQuery Mobile, namely 1.5alpha.  But given the fact that there was a very long hiatus on jQuery Mobile for an extended period of time, and it's unlikely that jQuery Mobile 1.5 will be stable enough by the time Application Express 5.2 is released, we collectively decided to announce depreciation of the Mobile User Interface based upon jQuery Mobile.  We literally spent months researching and contemplating this dilemma.  It's unfortunate.  The positive angle, though, is that we're in complete control of the Universal Theme and should not encounter similar issues with Universal Theme-based applications in the future.

How do I share a URL to my APEX application?

Thu, 2017-08-17 08:17
What is the URL for my APEX application?  If I want to provide a URL to my APEX application to others, what URL do I give them?

Great questions!  I see this done incorrectly all the time inside of Oracle.  A person new to Application Express (APEX) will create an application or install a Productivity Application, run it, copy the full URL, and include the full URL in an email.  I encountered this today in an email, for me to complete a Global Tax Survey via an APEX application (let the good times roll!).  The URL provided was:

https://server/ords/f?p=23828:2:113570213685366:::::

Wow, that sure looks complex!  However, it's unnecessarily complex.

The syntax for the entry points into the APEX engine is well documented.  Interpreting the above URL and the colon-separated arguments, it means:

  • 23828 - Application ID
  • 2 - Page ID
  • 113570213685366 - APEX Session ID
  • ::::: - null values for the rest of the arguments in the URL

However, let's evaluate each one of these values in reverse order and assess their necessity:

  • ::::: - Utterly not necessary.  The argument values will default to null anyway.
  • 113570213685366 - Also utterly not necessary.  Assuming the application requires authentication, the APEX Session ID is specific to you, and only you, and only at a specific moment in time.  You'll notice that if you give this URL to someone else, they'll be prompted to authenticate, and then get an entirely new APEX Session ID.  Why include it if it's going to change anyway?
  • 2 - There is a Home URL to every application, so if you're including the starting Page ID in the URL, then it's redundant to include the Page ID again.  It's not necessary.  Additionally, if this is not the same Page ID which is specified in the Home URL, and you have Deep Linking disabled (the default), your end users are going to end up accessing your application via the Home URL anyway.  This is another reason not to include the Page ID in the URL which you share with others.
  • 23828 - This is the only value that is absolutely necessary.  This is the ID of the application you wish to share.

Thus, the URL that I received in email could have been provided simply as:

https://server/ords/f?p=23828


I don't fault new users to APEX for doing this.  It's not obvious.  But now, hopefully it is!

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

Wed, 2017-07-26 14:37
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 SolutionFor 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.

Oracle Application Express Curriculum Announcement - an Addendum

Wed, 2017-07-19 15:09
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.

Built with ❤️ using Oracle Application Express (APEX)

Thu, 2017-07-13 10:59
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!


The APEX Community & ODTUG Kscope

Sun, 2017-07-02 10:28
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

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

Thu, 2017-06-08 14:44
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).

Is there a mailing list for APEX release announcements?

Wed, 2017-06-07 09:49
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.

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

Tue, 2017-06-06 11:43
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.

APEX and ORDS up and running in....2 steps!

Thu, 2017-05-04 13:36
In January 2017, I had a meeting with external customer Sriram from Hyderabad.  He got my attention when he said he would love to start a 1000-person APEX Meetup group in Hyderabad (gotta love aggressive goals!).  However, he spent much of December and January just trying to figure out how to get APEX installed, configured and running.  He won't profess to be an expert, but he's exactly the type of person we want to enable.  He was correct in saying that if a potential customer struggles to get APEX installed, we've already lost.

Recently, Gerald asked for some assistance in creating a Docker image for APEX.  His goal was to create an APEX Docker image on top of the base Oracle Database Docker image.  He knows a lot about Docker, but he won't profess to be an expert in APEX.  He wanted something that is scriptable and can result in APEX being installed, configured and up and running, along with ORDS, in as few steps as possible.  A "silent install", if you please. This was the final bit of motivation I needed for this blog post and video.

While the installation documentation is complete and detailed, it's also lengthy and sometimes confusing - especially for the new person.  Thus, I wanted to provide the simplest set of instructions with as few steps as possible to get APEX installed, configured and up and running, along with ORDS configured and up and running.  It can be done in two steps.  That's right, two.  While I explain the individual steps executed from SQL*Plus in detail below, you can combine all of these SQL commands into a single SQL script.  I prefer the name "hookmeup.sql".



  1. Download and unzip APEX http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
  2. cd to apex directory
  3. Start SQL*Plus and ensure you are connecting to your PDB and not to the "root" of the container database (APEX should not be installed at all):
    sqlplus sys/your_password@localhost/your_pdb as sysdba @apexins sysaux sysaux temp /i/
  4. Unlock the APEX_PUBLIC_USER account and set the password:
    alter user apex_public_user identified by oracle account unlock;
  5. Create the APEX Instance Administration user and set the password:
    begin
    apex_util.set_security_group_id( 10 );
    apex_util.create_user(
    p_user_name => 'ADMIN',
    p_email_address => 'your@emailaddress.com',
    p_web_password => 'oracle',
    p_developer_privs => 'ADMIN' );
    apex_util.set_security_group_id( null );
    commit;
    end;
    /
  6. Run APEX REST configuration, and set the passwords of APEX_REST_PUBLIC_USER and APEX_LISTENER:
    @apex_rest_config_core.sql oracle oracle
  7. Create a network ACE for APEX (this is used when consuming Web services or sending outbound mail):
    declare
    l_acl_path varchar2(4000);
    l_apex_schema varchar2(100);
    begin
    for c1 in (select schema
    from sys.dba_registry
    where comp_id = 'APEX') loop
    l_apex_schema := c1.schema;
    end loop;
    sys.dbms_network_acl_admin.append_host_ace(
    host => '*',
    ace => xs$ace_type(privilege_list => xs$name_list('connect'),
    principal_name => l_apex_schema,
    principal_type => xs_acl.ptype_db));
    commit;
    end;
    /
  8. Exit SQL*Plus.  Download and unzip ORDS http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html
  9. cd to the directory where you unzipped ORDS (ensure that ords.war is in your current directory)
  10. Copy the following into the file params/ords_params.properties and replace the contents with the text below (Note:  this is the file ords_params.properties in the "params" subdirectory - a subdirectory of your current working directory):
    db.hostname=localhost
    db.port=1521
    # CUSTOMIZE db.servicename
    db.servicename=your_pdb
    db.username=APEX_PUBLIC_USER
    db.password=oracle
    migrate.apex.rest=false
    plsql.gateway.add=true
    rest.services.apex.add=true
    rest.services.ords.add=true
    schema.tablespace.default=SYSAUX
    schema.tablespace.temp=TEMP
    standalone.mode=TRUE
    standalone.http.port=8080
    standalone.use.https=false
    # CUSTOMIZE standalone.static.images to point to the directory
    # containing the images directory of your APEX distribution
    standalone.static.images=/home/oracle/apex/images
    user.apex.listener.password=oracle
    user.apex.restpublic.password=oracle
    user.public.password=oracle
    user.tablespace.default=SYSAUX
    user.tablespace.temp=TEMP
  11. Configure and start ORDS in stand-alone mode.  You'll be prompted for the SYS username and SYS password:
    java -Dconfig.dir=/your_ords_configuration_directory -jar ords.war install simple --preserveParamFile

That's it!!  You should now be able to go to http://localhost:8080/ords/, and login with:

Workspace: internal
Username:  admin
Password:  oracle





IMPORTANT, PLEASE READ:

By no means is this a recommended or secure installation.  These are minimal instructions to get someone from zero to up and running easily and quickly.  In a production instance, I would create different tablespaces for APEX and ORDS, I would use far more complex and distinct passwords, I would use HTTPS and not HTTP, I would deploy ORDS on a physically distinct server, and more.

The above steps were tested with Oracle Application Express 5.1.1.00.08, Oracle REST Data Services 3.0.9, and Oracle Database 12.2.0.1 running on Oracle Linux.


End users still on IE8? Upgrade them before upgrading to Application Express 5.1 or later

Wed, 2017-05-03 07:30
If you have end users of your Application Express (APEX) applications still using Internet Explorer 8, you should upgrade their browsers before upgrading to Oracle Application Express 5.1 or later.

The documented general browser compatibility for Oracle Application Express is N-1, where N is the latest production version of a browser, and N-1 is the major production version prior to that.  At the time of release of Application Express 5.1, Microsoft Edge was the current Microsoft browser and Internet Explorer 11 was the prior major version.

The Oracle Cloud Database Schema Service was recently upgraded to APEX 5.1.1, and a customer contacted me directly, saying that some of his end users who were still on IE8 couldn't even login to the application.  "We" (Anthony and John) reviewed these issues, but concluded that there really wasn't any practical way for us to resolve this for the customer.  We'll move heaven and earth to not impact a customer and avoid unplanned cost and work for them, but in this case, there simply wasn't anything we could do.

If you need a compelling reason to have your end users upgrade to a modern version of Internet Explorer, remember that Microsoft no longer supports or provides security patches for the older IE versions.

Developing a training course or university curriculum for Oracle Application Express (APEX)? Start here!

Thu, 2017-04-20 13:37
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.  To lessen the burden on educators, we have developed and made available for public download a full course curriculum for Oracle Application Express.

This courseware, developed by our product manager Chaitanya Koratamaddi over the past year, includes 16 distinct lessons, complete with PowerPoint presentations, hands-on-labs, and all necessary SQL scripts and application export files.  You can use all or a portion of these materials in your own curriculum.

This same courseware was provided to the Oracle Academy team, who now also offer an Oracle Application Express course.  The Oracle Academy course is offered in a hosted interactive form, complete with quizzes, and it also includes both educator and student curriculum.  There are many other benefits to joining Oracle Academy.  For more information about Oracle Academy, go here.

To access the publicly available Oracle Application Express curriculum, go to:

https://apex.oracle.com/education

Oracle Application Express 5.1.1 now available

Tue, 2017-03-28 16:18
Just in time for APEX World 2017, Oracle Application Express 5.1.1 is now released and available for download.  If you wish to download the full release of Oracle Application Express 5.1.1, you can get it from the Downloads page on OTN.  If you have Oracle Application Express 5.1 already installed, then you need to download the APEX 5.1.1 patch set from My Oracle Support.  Look up patch number 25341386.

As is stated in the Patch Set Note that accompanies the Oracle Application Express 5.1.1 patch set:
  • If you have Oracle Application Express release 5.1 installed, download the Oracle Application Express 5.1.1 patch set from My Oracle Support and apply it.  Remember - patch number 25341386.
  • If you have Oracle Application Express release 5.0.4 or earlier installed, download and install the entire Oracle Application Express 5.1.1 release from the Oracle Technology Network (OTN).
  • If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 5.1.1 release from the Oracle Technology Network (OTN).

As usual, there are a large number of issues corrected in the Application Express 5.1.1 patch set (over 106!).  You can see the full list in the 5.1.1 Patch Set Notes.

    Oracle technologies used to analyze the Inaugural Addresses of the Presidents of the United States

    Sun, 2017-01-22 22:42


    The Oracle Database ships with a wonderful technology called Oracle Text.  Most people associate Oracle Text with the mere ability to index documents or data, perform text or phrase queries and get the results back.  But Oracle Text can do so much more.  One of the more interesting features of Oracle Text is the ability to analyze a document and generate a list of themes for the document along with a weight associated with that theme.

    Back in 2004, in the era of Oracle HTML DB, Jason Straub on the Application Express development team wrote a demonstration application named Presidential Inaugural Addresses, which used both Oracle HTML DB and Oracle Text.  This application actually shipped with Oracle HTML DB for a time.  This was a fascinating application, which enabled you to view all of the Inaugural Addresses of the Presidents of the United States, identify the top themes associated with those addresses, and even identify which past presidents delivered addresses that had similar dominant themes.

    This demonstration application stopped shipping with Oracle Application Express in 2006.  But I thought it might be interesting to dust off this application and improve the UI using Oracle Application Express 5.1.  So that's how I spent this past weekend (let the good times roll).  Using the existing data structures, I was able to create a new Universal-theme based application which turned out pretty elegant.

    Please understand - this application and everything associated with it is not a political statement.  Given the relevance of this data at the time of this writing, I simply thought it would be a fascinating way to demonstrate insights into this information, using the Oracle Database, Oracle Application Express, Oracle Text and Oracle JET!

    You can run this application HERE.

    Details about this incident are available via debug id....

    Thu, 2017-01-19 08:07


    In Oracle Application Express, unexpected exceptions can occasionally occur.  Instead of communicating the technical details to an end user who is running the application, an error message will be issued of the form:

    Contact your administrator. Details about this incident are available via debug id "NN".

    But what actually happened?  Who is "your administrator"?  And what exactly are you supposed to do with this information?

    What happened?An internal exception occurred - it either wasn't properly handled by the application's error handler (or there was no handler in place), or an unforeseen exception occurred in the Application Express engine itself, which wasn't properly caught by the error handler of APEX.  If you're logged into the development environment as a developer and running the application, you should be able to see the full error stack and more details of what actually occurred.  But if you're a mere end-user of the application, you are shielded from those details - not only would the details be mystifying to an end user, but it could also be viewed as a security risk, to show the details.


    Who is "your administrator"?
    • A developer of this application, who has access to SQL Commands in the APEX workspace?
    • A developer who can connect directly to the database where this error occurred, connecting as the database user associated with the workspace (commonly referred as the "workspace schema").
    • A database user who has been granted the APEX_ADMINISTRATOR_ROLE (or, beginning in APEX 5.1, also the APEX_ADMINISTRATOR_READ_ROLE).
    • Someone who can connect as SYS or SYSTEM to your database.

    What can you do with this information?The full details of the incident and the error stack at the time of the exception are written to the Debug Messages tables in the Application Express schema.  To view these details, you simply need to query the view APEX_DEBUG_MESSAGES:

    select *
    from apex_debug_messages
    where page_view_id = NN
    order by message_timestamp asc;

    This information is also accessible from the development environment of Application Express.
    • A developer can select their application in App Builder, and then navigate to Utilities -> Debug Messages.
    • A workspace administrator can navigate to Workspace Administration -> Monitor Activity -> Page Views By View.  You can then filter the output by the Debug ID column.

    OTN Appreciation Day: Oracle Text

    Tue, 2016-10-11 08:13
    For OTN Appreciation Day, I was told that it wouldn't be appropriate to write about my favorite Oracle feature (APEX, obviously).  So I'll gladly promote my second-favorite Oracle Database feature...Oracle Text!

    I've used Oracle Text for many years - from when it was SQL*TextRetrieval to Oracle ConText Option to Oracle interMedia Text to finally Oracle Text.  This was one of those products that used to be a for-cost option and was merged into the Oracle Database as native, no-cost functionality (how cool is that?).  You can use Oracle Text to index BLOB columns containing Microsoft Word or PDF documents, you can score the query results for relevance, you can perform a proximity search within the contents (find "Oracle" and "APEX" within 10 words of each other), you can search within sections of a document, you can do a fuzzy search, you can create a thesaurus to assist in searching for similar terms, you can create a text result with the matching words highlighted, and on and on.

    The beauty of Oracle Text is that it's all completely accessible in SQL.  Any tool that can "talk" SQL can easily take advantage of this rich functionality in the Oracle Database - Java, .NET, PHP, Node, and of course, APEX!  I authored the PL/SQL functions and text indexes (and text queries) for AskTom back in 2001 - and they're still running as fast as ever today.  One of the most popular applications inside of Oracle, an employee directory (1.5M page views every day from 55,000 distinct users), is an APEX application that we're responsible for - and we are in the process of expanding this to use the fuzzy search capabilities of Oracle Text - what is more commonly misspelled than someone's name?  And it's easy, because this is all running inside the Oracle Database.  Whether your content is a string or BLOB or XML or JSON, once this content is inside the Oracle Database, it's accessible to Oracle Text and SQL, and the application development opportunities on top of this are easy.  I'm a big  fan of Oracle Text, and you should take a look at it too!

    Correlating APEX Sessions to Database Sessions

    Fri, 2016-09-30 08:01
    I received the following question via email today:

    "Had a question from a client yesterday concerning the subject:  I want to know which database session (APEX_PUBLIC_USER)  is servicing which APEX session. Poking around in the V$ tables, I can see that in v$SQL, the module column will reveal the APEX Application and Page, but not the Session ID.  Even if the session ID was in there, I don’t see an obvious way to join back to V$SESSION." 

    It's a bit of a puzzling problem for DBA's and developers - being able to correlate a database session with a specific APEX application and session.  As I wrote about earlier, all database sessions in the database session pool of ORDS are connected as APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL Gateway).  If a user is experiencing slowness in their APEX application, how can a DBA look under the hood, identify the database session associated with the request from that user, and also look at the active SQL statement and possibly any wait events with that session?

    This question comes up a lot, and should really be covered in the Oracle Application Express documentation.  But in the meantime, here's the definitive answer:

    APEX populates the following information in GV$SESSION for ACTIVE sessions:

    client_info: Workspace ID:Authenticated username
    module: DB Schema/APEX:APP application id:page id
    client_identifier: Authenticated username:APEX Session ID

    For example, for a recent request I did on apex.oracle.com, I had the following values in the DB session that executed my request:

    client_info: 3574091691765823934:JOEL.KALLMAN@FOOBAR.COM
    module: JOEL_DB/APEX:APP 17251:4
    client_identifier: JOEL.KALLMAN@FOOBAR.COM:12161645673208

    There is no permanent, fixed correlation between an APEX session and a database session.  The assignment of a session in the session pool to service an APEX request is essentially unpredictable and not constant.  That's why this correlation can only be done for active database sessions, which are actively servicing APEX requests.

    There's one caveat.  A developer could overwrite these values using the database-provided PL/SQL API, which I've seen customers do occasionally.  Otherwise, for active database sessions, you'll see these three elements populated in GV$SESSION, and module & client_identifer will also be present in the Active Session History.  Carlos Sierra has an excellent blog post about how to query the Active Session History and identify poor performing APEX applications and their associated SQL.


    Pages