Tuesday, March 28, 2017

Oracle Application Express 5.1.1 now available

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.

Sunday, January 22, 2017

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



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.

Thursday, January 19, 2017

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



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.

Tuesday, October 11, 2016

OTN Appreciation Day: Oracle Text

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!

Friday, September 30, 2016

Correlating APEX Sessions to Database Sessions

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.


Wednesday, September 14, 2016

Lessons Learned in 20 Years at Oracle

I've known Mark, a technical pre-sales consultant at Oracle, for a number of years.  I was bcc'd on the farewell email message that he sent out today, his last working day at Oracle.  As he said, he's learned a few things over his past 20 years at Oracle and thought he would share them.  They were quite simple and powerful reminders - and important enough that I shared them with our entire development and QA teams.

OWN IT —  Things sometimes go off track.   Whether it was something on your team that went sideways or it was another team's responsibility, step up, own the problem and deliver a resolution.  
LEARN IT —  There are always new technologies, solutions, processes, and procedures.   Set aside time to learn and master what is new so that you are prepared when the time comes.   
TEACH IT — When you master something new, find someone else with whom you can share it.  
GROW IT — Your team is incredibly valuable.   Take the time to invest in your teammates and equip them with new capabilities. 
OVERLOOK IT — People can make poor decisions.   Fight the urge to gossip about them.   Look for the best and ignore the rest.  

Sunday, July 24, 2016

Securing Oracle Application Express when using Oracle REST Data Services (ORDS)

If you are using Oracle REST Data Services as the "PL/SQL Gateway" for Oracle Application Express, ensure that your ORDS configuration includes the following line:

wwv_flow_epg_include_modules.authorize

It is important that you do this, and let me explain why.

Fundamentally, the APEX "engine" is really nothing more than a big PL/SQL program running inside the Oracle Database.  When a browser makes a request for a page in an APEX application, that request is mapped to a PL/SQL procedure which is running inside the database.  If you examine an APEX URL in your browser, you may see something like 'f?p=...', and this is invoking a PL/SQL procedure in the database named 'F' with a parameter named 'P'.

There are a number of procedures in the APEX engine which are intended to be invoked from a URL.  But there may be other procedures in your database, possibly owned by users other than the Application Express user, which are not intended to be called from a URL.  In some cases, these other procedures could leak information or introduce some other class of security issue.  There should be a simple list of procedures which are permitted to be invoked from a URL, and all others should be blocked.  This is known as a "whitelist", and fortunately, there is a native facility in APEX which defines this whitelist.  You just need to tell ORDS about this whitelist.

When you configure ORDS with the following entry in the configuration file:

wwv_flow_epg_include_modules.authorize

You are instructing ORDS to validate the PL/SQL procedure requested in the URL using the PL/SQL function wwv_flow_epg_include_modules.authorize.  This whitelist will contain all of the necessary entry points into the APEX engine, nothing more, nothing less.

If you rely upon functionality in your application which makes use of PL/SQL procedures not defined in this whitelist, this functionality will break when you specify the security.requestValidationFunction.  I often encounter customers who invoke PL/SQL procedures in their application schema to download files, but there are better (and more secure) ways to do this, which would not break when implementing this whitelist.

Like any change to infrastructure or configuration, you should thoroughly test your applications with this setting prior to introducing it into a production environment.  But if one or two things break because of this change, don't use that as an excuse to not implement this configuration change.  Identify the issues and correct them.  While there is a method in place to extend the whitelist, in practice, this should be seldom used.

If you're using ORDS as a mod_plsql replacement for your PL/SQL Web Toolkit application and not using APEX, then please avoid this configuration setting.  APEX typically won't be installed in your database, and the whitelist will be irrelevant for your application.

The function wwv_flow_epg_include_modules.authorize has been around for more than 10 years (our teammate Scott added it in 2005), and it has been a part of the embedded PL/SQL Gateway and mod_plsql default configuration for a long time.  And while it has been documented for use with ORDS, a reasonable person might ask why this isn't simply part of the default configuration of APEX & ORDS.  I did confirm with the ORDS team that this will be included in the default configuration when using the PL/SQL Gateway of ORDS, beginning in ORDS 3.0.7.