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.comBlogger212125
Updated: 9 hours 23 min ago

APEX Alpe Adria - A New APEX Conference That You Should Attend

Sat, 2018-02-10 13:47


Have you heard of APEX Alpe Adria?  It's the latest "All APEX" conference, joining APEX World and APEX Connect as high-quality conferences dedicated to Oracle APEX developers and related technologies.

APEX Alpe Adria is a very modestly-priced one-day conference occurring on April 20, 2018 in Graz, Austria.  The list of speakers that they have organized is unbelievably impressive - they are all very knowledgeable & experienced, as well as very polished presenters.  All presentations will be in English.  Also, the evening before the conference, there will be an Ask an ACE session, where you can ask any question to a panel of highly experienced Oracle ACEs.

APEX Alpe Adria is the creation of three different Oracle partners, Dario Bilić from BiLog d.o.o., Aljaž Mali from Abakus Plus d.o.o., and Peter Raganitsch from FOEX Gmbh.  As Peter told me, they are not organizing this conference for commercial purposes.  Instead, their primary goal is to bring a high-quality dedicated APEX conference to a region where there is significant demand.  And they've developed a 100% APEX Conference - everyone and everything is driven by Oracle APEX:  the conference Web site, abstract submission, abstract voting, the back-end administration apps, everything.  The conference is already a great testament of what is possible with the Oracle Database & Oracle APEX.

While the location primarily caters to Austria, Slovenia and Croatia, the appeal stretches far beyond the immediate region.  As you can see in the map below, Graz is a short 500 miles (1.5 hour flight) from many places in central Europe.  And even though it may be a bit longer to travel for those outside the circle, I'm hoping that this conference will attract many of our other customers and partners from Eastern Europe and the Middle East.  Tickets have already been sold to conference attendees from Austria, Czech Republic, Germany, United Kingdom, Croatia, Macedonia, Russia, Slovenia, and the USA.  If you want an easy way to get connected and immersed into the global APEX community, please attend APEX Alpe Adria.  It's that simple.

Patrick Wolf & Christian Neumueller from the Oracle APEX Development team will be joining me to kick off this inaugural conference and show our support for this growing APEX community.  We hope to see you there!


How do I Refresh a Region or Report after a Dialog is Closed?

Wed, 2018-02-07 20:57
I was going to write this blog post back in December 2017, as this question comes up frequently.  And even though there are numerous examples available in the Sample Database Application and other sample applications, and it's even in the documentation, the solution still isn't always readily apparent.

I received this question from an internal employee today:
"When I edit the page and update the row and save and close the modal page, existing report doesn't refresh the page with revised value.   It was working on another page, but not working on this new page (I built last week).  Couldn't find a way to refresh it as soon as I close the modal window with saved data."He has a report and a form in his application, and the form opens up in a modal dialog.  He simply wants the report on the parent page to refresh after the modal dialog is closed.  Simple, right?



For anyone well-versed in APEX, you'll find this blog post superfluous.  But for everyone else who is looking for the quick explanation, as I've received this question 3 times in the past couple months, here it is.

  1. On the report page, create a new Dynamic Action and enter the following attributes:
    • Name: <provide a meaningful name>
    • Event: Dialog Closed
    • Selection Type: Region
    • Region:<choose your report region>

  2. Select the True action of the Dynamic Action and enter the following attributes:
    • Action: Refresh
    • Selection Type: Region
    • Region: <choose your report region>

That's really all there is to it.  If you have multiple regions on the parent page, you could add multiple True actions for the dynamic action.  If the triggering event of the modal dialog is a button instead of a region, you could specify that as your Selection Type for the triggering event.  If there are multiple regions on the page which could open the modal dialog, you could even use a jQuery Selector and reference multiple elements as your Selection Type for the triggering event. 

For example, if you're converting a non-modal form and report to use a modal form, and you wish to refresh the report after the modal dialog is closed (after creating a new record), you'll need to associate the triggering event with the button.  The steps would be:
  1. On the report page, create a new Dynamic Action and enter the following attributes:
    • Name: <provide a meaningful name>
    • Event: Dialog Closed
    • Selection Type: Button
    • Region:<choose your button, for example, your Create button>

  2. Select the True action of the Dynamic Action and enter the following attributes:
    • Action: Refresh
    • Selection Type: Region
    • Region: <choose your report region>

You should get this behavior out of the box when creating an application with a report and form, or adding a report and form to your application.  But if you're manually adding components, then it's really quite easy and straightforward to add this functionality to your APEX app.

If you install the Sample Database Application, you'll find numerous examples of this dynamic action refresh operation after the close of a modal dialog.  And it is briefly documented in this tip.

APEX World 2018: Het gaat los!

Thu, 2018-02-01 05:53
While we're delighted that there are more APEX-focused conferences springing up around the globe, there is one conference that started this trend, and that is...APEX World.  Thanks to the foresight of the Dutch Oracle User GroupRobin Buitenhuis and Oracle ACE Learco Brizzi, APEX World was started 9 years ago, with the primary purpose of bringing together the growing APEX community in the Netherlands.  Since that time, it has grown into a premier conference for the APEX community in Europe and beyond.

When I first had the opportunity to present at APEX World in 2014, it was a one-day event in the quaint city of Zeist.  Since that time, APEX World has blossomed into a large, international, multi-track 2-day conference with more than 400 attendees expected in 2018.  Even better, the conference itself is on board the cruise ship SS Rotterdam, which is very historic and beautiful.  The theme of this years conference is "A Deep Dive into Low Code", and it will be on March 22nd and 23rd in Rotterdam, Netherlands.

Four members of the Oracle APEX product development team will be at APEX World 2018 - John Snyders, Anthony Rayner, Shakeeb Rahman and David Peake.  We are all very open, we would love to understand what you're doing, and we also want to learn how we can help you be successful.  I encourage you to come to this great event and join the large and growing international community at APEX World 2018.


Infographic from APEX World 2017.  2018 will be even bigger and better!

Who used apex.oracle.com in 2017?

Sat, 2018-01-20 09:55
A number of years ago, I provided statistics on the geographic distribution of people who used the free, evaluation service for Oracle Application Express at https://apex.oracle.com.  I did this in 2008, 2009 and 2011.  It's time for an update.

I've included a graphic of the top 30 and the full report below, and here are my observations:

  • Since 2008, the number of visitor sessions to apex.oracle.com increased 280 times! That's not percentage, but times.  Percentage-wise, that's 27,985 % growth from 2008 to 2017.
  • In 2008, there were 1 or more visitor sessions from 122 countries/territories.  In 2017, that number increased to 212 countries/territories
  • The USA, India and United Kingdom remain in the top 3.  But the UK is barely hanging onto the #3 position.
  • Colombia vaulted from #11 in 2011 to #6 in 2017.  Bangladesh jumped from #26 to #14.  Japan jumped from #19 to #9.  Wow!

Usage of apex.oracle.com doesn't necessarily translate into usage of APEX.  These statistics are only a measurement of who is logging into apex.oracle.com - the actual usage of APEX is far greater.  Also, I fully anticipate the comment "...but as a percentage of population size, our country has the top usage in the world!" (here's looking at you, Netherlands).  But just because someone's country has declined in the rankings doesn't mean that APEX isn't growing there....it's just growing in other countries too!

Lastly, the statistics presented below are sessions, not to be confused with activity.  At the time of this writing, there are 36,133 workspaces on apex.oracle.com.  In the past 7 days, there were 4,643,958 page views, and 1,808 new workspaces requested and approved.

Not only is usage of APEX growing on a global basis, but that growth is accelerating.





The top 5 reasons why you should submit an abstract for APEX at the Great Lakes Oracle Conference (GLOC)

Mon, 2018-01-15 18:50
APEX Developer Day at Great Lakes Oracle Conference 2017
The Northeast Ohio Oracle User's Group (NEOOUG) is easily one of my favorite user groups on the planet.  They've been graciously hosting me at their user group events since 2004 (when I first gave a demonstration on Oracle HTML DB 1.5!).  They are a large, active and passionate user group.  In the past 14 years, I've seen them grow from simple user group events to "Training Days" at the Cleveland State University campus to a nicely sized regional conference named Great Lakes Oracle Conference.

If you're into Oracle APEX, either on-premises, or in the Oracle Cloud, I encourage you to submit an abstract to speak at the Great Lakes Oracle Conference.  Here are my top 5 reasons why you should strongly consider this:
  1. There is a real hunger for Oracle APEX content at this conference.  There are countless customers in the immediate region who use Oracle APEX.  Last year, they had the first ever Oracle APEX Developer Day in advance of the conference, and it was sold out (100 attendees)!
  2. It's the largest Oracle user's conference in the Midwest US.  It draws people from all over Ohio, Michigan, Indiana, Kentucky and Pennsylvania.  There will be over 500 attendees at the conference in 2018.
  3. The Great Lakes Oracle Conference routinely gets world-class speakers from all over the world, both Oracle employees and Oracle ACEs.  As a speaker, you would be able to attend any session in any track.
  4. There are numerous tracks at the Great Lakes Oracle Conference, including APEX, Oracle Applications, Business Intelligence, DBA, Database Developer and Data Warehousing.
  5. Cleveland, Ohio is on the North Coast of the US.  There, you can visit Great Lakes Brewing Company, Market Garden Brewery, Platform Beer Company,  and the Rock & Roll Hall of Fame.

I come across so many people who say "why would anyone want to hear me talk about that?"  From case studies to lessons learned to best practices in your environment, it's all interesting and valuable.  Not everyone who attends the APEX sessions at GLOC are experts, so entry-level sessions are also welcome!

I encourage you to submit an abstract today.  The deadline for abstract submission is February 2, 2018.

Can you use Oracle Database Unified Auditing with Oracle APEX?

Tue, 2018-01-09 16:24
A customer asked me this morning:

"Can we monitor the table access from an APEX application?"

This is a security-minded customer, who was also interested in the use of Oracle Database Vault to help protect unauthorized access to personally identifiable information which would be maintained in their database tables.  The only access to these tables would be through their APEX applications (for now), and they wanted to be able to monitor access to these tables.  While Oracle offers a very robust, enterprise solution in this problem domain named Oracle Audit Vault and Database Firewall, auditing via the native database functionality would be sufficient for now.

This blog post is not intended to be a complete treatise on the subject of database auditing in the Oracle Database, there is already a lot of documentation on this topic.  This blog post is a simple proof that auditing can be enabled to track accesses from an APEX application and how to see the audit log entries.

  1.   I created a simple APEX application based upon an updated version of the TASKS CSV file, used in this 2015 Oracle Magazine article.  This was done in the JOEL_DB schema.  The Interactive Report on page 1 is based upon the query:

    select id, project, task_name, start_date, end_date, status, assigned_to, cost, budget
    from tasks

    The application looked like:




  2. I created an audit policy named JOEL_TASKS_POLICY and enabled it using the following SQL:

    create audit policy joel_tasks_policy actions 
    select on joel_db.tasks,
    update on joel_db.tasks,
    delete on joel_db.tasks,
    insert on joel_db.tasks;

    Note:  This is Oracle Database 12cR2.  Before beginning this step, I ensured that the database was configured for Unified Auditing.

  3. I ran the following SQL statement to validate existence of the audit policy:
    SQL> 

    select audit_option, object_schema, object_name
    from audit_unified_policies
    where policy_name = 'JOEL_TASKS_POLICY';

    AUDIT_OPTION OBJECT_SCHEMA OBJECT_NAME
    DELETE JOEL_DB TASKS
    INSERT JOEL_DB TASKS
    SELECT JOEL_DB TASKS
    UPDATE JOEL_DB TASKS

  4. I stopped and restarted Oracle REST Data Services (ORDS).  This is important, because the policy will only take effect for database sessions established after the audit policy was enabled.  Since ORDS maintains a pool of database sessions, I needed to nuke the existing sessions and establish new ones.

  5. I ran the page a few times in my APEX application using the Interactive Report.

  6. As a DBA, I queried the database view UNIFIED_AUDIT_TRAIL using the following query:
    select dbusername, action_name, event_timestamp, scn, 
    object_schema, object_name, sql_text, sql_binds, client_identifier
    from unified_audit_trail
    where unified_audit_policies = 'JOEL_TASKS_POLICY'
    and object_schema = 'JOEL_DB'
    order by event_timestamp desc



    and voila!  You can easily see that the SELECT statements against the TASKS table are properly being audited, complete with any bind values used in the query.  The username of the database session is APEX_PUBLIC_USER as expected, because this is what the database sessions in the ORDS pool are connected as.  In the CLIENT_IDENTIFIER column, you can see that the name of the authenticated user to the APEX application along with the APEX session identifier are also recorded.

Does this give you everything you wish?  Probably not.  I'd like to know the APEX application ID, the page number, the actual parsing schema, and more.  But using the APEX session ID and correlating it with entries in the APEX_ACTIVITY_LOG database view, I can find so much more about this activity.  There are also many other columns in the UNIFIED_AUDIT_TRAIL database view which are used in conjunction with Oracle Database Vault and Real Application Security, and which would capture still more information about access of this underlying database object.

A great benefit of using database auditing instead of simply implementing "poor-man's" logging in the APEX application is that any access will be recorded from any application, not just the APEX app.



How do I create a responsive Rich Text Editor in Oracle APEX?

Fri, 2017-12-08 13:40
I was in a video call this morning with a great customer from England (and by the way, this customer is in the process of transforming the healthcare industry across the UK).  They asked me a very simple question:

How do I create a responsive Rich Text Editor item on a page?

Simple question and answer, right?  Well, you'd be wrong.  While we pride ourselves on the responsive user interfaces that you can easily create with Oracle Application Express (APEX), unfortunately, the item type of Rich Text Editor is not responsive, out of the box.

So - I did what all smart people do, and I reached out to the Oracle APEX Development team, in this case, the ever-intelligent Carsten Czarski.  And in a few minutes, he showed me exactly what I needed to do.

  1. Open up Application Builder, and in Page Designer, edit the page with the Rich Text Editor item.  In my example, my Rich Text Editor page item name is P3_RESUME.
  2. Navigate to the attributes of the Rich Text Editor item, and in the Advanced section, enter the following code in the "JavaScript Initialization Code" attribute:
    function (o) {
    o.width = $("#P3_RESUME").closest(".t-Form-inputContainer").width() - 5;
    o.height = 300; // Specify your desired item height, in pixels
    return o;
    }
    This code determines the width of the region container of the item, subtracts 5, and returns the object initialized to this size.  This will take care of the Rich Text Editor when the page is initially displayed. But it won't handle the case when the browser is resized. To handle that case, we'll need to add a dynamic action.
  3. Click the Dynamic Actions sub-tab in Page designer (the lightning bolt)
  4. Select Events in the upper-left, right-click your mouse and choose "Create Dynamic Action".
  5. In the attributes, enter "Resize" for Name, and select "Resize" for the Event.
  6. Select the True action of the dynamic action (it should be "Show").  Change the Action to "Execute JavaScript Code".
  7. In the Code attribute, enter the code:
    CKEDITOR.instances.P3_RESUME.resize( $("#P3_RESUME").closest(".t-Form-inputContainer").width() - 5, 300);
    This is an absolute reference to the Rich Text Editor item on the page, named P3_RESUME. And like the code before, this will determine what the width is of the container of the item, subtract 5 from it, and invoke the resize() method of the Rich Text Editor (CK Editor) element.
That's all there is to it!



Obviously, this item type (like all others) should be responsive, out of the box.  And Carsten is looking at this for the next version of APEX.  In the meantime, if you're using Universal Theme with Oracle APEX 5.1, all it takes is a tiny amount of JavaScript to get a responsive Rich Text Editor.

Errors when downloading a file on page submit in Oracle Application Express 5.1 or later...

Thu, 2017-12-07 15:59
Recently, Sharon Kennedy from our team approached me for some help with file download in Oracle Application Express (APEX).  Sharon is the primary developer of Oracle Live SQL (among many of her other responsibilities), and she wanted to initiate a file download in a page process, after page submission.  Since I've done this 100 times in APEX applications, should be easy, right?

Back in 2014, I wrote a short blog post showing how to generate a link to download a file from a BLOB stored in a table.  But this problem was slightly different.  The application flow was:

  1. In Oracle Live SQL Administration, an administrator would click the button "Download Oracle Content"
  2. The page would then be submitted, and a PL/SQL page process would fire, which would query all of the static scripts and tutorials from Live SQL, zip them up using APEX_ZIP, and initiate a file download.

However, when the button was clicked, the page would be submitted, no file download would be initiated, and the following error was displayed on the page:


Error: SyntaxError: Unexpected token r in JSON at position 0



After spending more than an hour debugging the Live SQL application, I resorted to a simpler test case.  I created a trivial application with a button on the first page, which would submit and invoke the PL/SQL page process:

declare  
l_file_blob blob;
l_file_name apex_application_files.filename%type;
l_file_mimetype apex_application_files.mime_type%type;
begin
select blob_content, mime_type, filename into l_file_blob , l_file_mimetype , l_file_name from apex_application_files where id = 2928972027711464812;
sys.owa_util.mime_header( l_file_mimetype , false );
sys.htp.p('Content-Disposition: attachment; filename="' || l_file_name ||'"');
sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_file_blob ));
sys.owa_util.http_header_close;
sys.wpg_docload.download_file( l_file_blob );
-- Stop page processing
apex_application.stop_apex_engine ;
end;


With my test case, it was exactly the same error encountered, the meaningless error message of "Error: SyntaxError: Unexpected token r in JSON at position 0".

I finally gave up and contacted Patrick Wolf on the APEX product development team, who helped me solve this problem in one minute.  Granted...Patrick was both the creator of the problem and the creator of the solution!

To resolve this problem:

  1. Open the page in Page Designer in Application Builder
  2. Edit the page attributes
  3. In the Advanced section of the page properties on the right hand side of Page Designer, change "Reload on Submit" to "Always" (changing it from "Only for Success" to "Always")
That's it!



Setting "Reload on Submit" to "Always" will POST the page and render the result using the behavior as it was in APEX 5.0 and earlier.  In APEX 5.1, if Reload on Submit is set "Only for Success" (the default), it will use the new optimized page submission process, and expect a specifically formatted JSON result returned from the APEX engine.  Obviously, when I employ a page process which overrides the HTP buffer and emit binary content (instead of a well-formed JSON result), the libraries on the page don't know how to deal with that, and thus, results in this obtuse "Unexpected token r..." message.

If you're in Panama, Colombia, Ecuador, Paraguay, Brazil or Argentina, Oracle APEX is coming to you!

Tue, 2017-10-17 21:31

In the first part of November, my colleague David Peake and I are taking the Oracle APEX & Oracle Database Cloud message to a number of Oracle user groups and communities who are graciously hosting us as part of the Oracle Developer Tour Latinoamérica.  These are countries for which there is growing interest in Oracle Application Express, and we wish to help support these groups and aid in fostering their growing APEX communities.

The dates and locations are:
  1. Panamá, November 1, 2017
  2. Colombia, November 2-3, 2017
  3. Ecuador, November 7, 2017
  4. Paraguay, November 8, 2017
  5. Brasil, November 9-10, 2017
  6. Argentina, November 13-14, 2017
You should consider attending one of these conferences if:

  • You're a CIO or manager, and you wish to understand what Oracle Application Express is and if it can help you and your business.
  • You're a PL/SQL developer, and you want to learn how easy or difficult it is to exploit your skills on the Web and in the Cloud.
  • You come from a client/server background and you want to understand what you can do with your skills but in Web development and Cloud development.
  • You're an Oracle DBA, and you want to understand if you can use Oracle Application Express in your daily responsibilities.
  • You know nothing about Oracle Application Express and you want to learn a bit more.
  • You're experienced with Oracle Application Express and you want to learn what's in the future for Oracle APEX.

If you have any interest or questions or concerns (or complaints!) about Oracle Application Express, and you are nearby, we would be very honored to meet you in person and assist in any way we can.  We hope you can make it!

P.S. I couldn't make it to Panamá, but David will be there, so please join him.

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.

Pages