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.

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.