Sunday, October 20, 2013

Pivot Table plugin for Apex

UPDATE 10.02.2018: A new version of the plugin has been released, see this blog post.

An interactive Pivot Table lets the user analyze a data set by selecting which fields to use as rows and columns in a grid. You've probably seen this in Excel, something like this:

I've created a region plugin for Apex which turns any SQL query into an interactive pivot table. The plugin is a simple wrapper for a pivot table written in Javascript by Nicolas Kruchten. There's also a nice tutorial page that explains the user interface.

This animated screenshot shows how the plugin is set up and used in Apex:

Note that you can have multiple Pivot tables on the same page if you want. You can also turn off the interactive features if you just want to display a preset summary.

You can download the plugin here. Enjoy!

Friday, July 26, 2013

Thoth Gateway version 1.3.7 available, including source code

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.7.

This version also includes the source code for the gateway. The project has been "open source" (under a BSD license) since day 1, but I never got around to actually publishing the source code, until now that is! :-)

This version fixes an issue with page rendering that was only impacting Apex 4.2 installations. Thanks to Patrick and Christian on the Apex developer team who responded quickly to my inquiries about the underlying changes in Apex that caused this problem to appear (the problem was caused by improper CGI setup in IIS/Thoth, not a problem with Apex itself).

Note also that this version removes support for OWA basic authentication (see the release notes in the downloaded archive for details). This should not have any impact on Apex applications at all (but may impact older, "pure OWA" applications).

Upgrades: For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Roadmap for the Thoth Gateway

The goal for the next release of the gateway (tentatively called version 1.4.0) is to change the Oracle Data Provider for .NET (ODP.NET) from the "Unmanaged" to the "Managed" driver. By using the Managed (pure .NET) driver, the installation of the Thoth Gateway becomes even simpler, because it removes the need to have the full Oracle client (or even the Instant Client) on the system. Note that this will change the minimum system requirements from .NET Framework 3.5 to 4.0 (the version that the managed driver requires).

For Thoth Gateway version 2.0, it might be fun to implement RESTful Web Services, to catch up feature-wise with the Apex Listener. (The Thoth Gateway already has automatic SOAP Web Services built-in, though.) By the way, now that the source code is available, anyone can pick up the challenge and contribute new features to the gateway!

Saturday, July 13, 2013

First mentions of Oracle 12c XE (Express Edition)

Oracle released an Express Edition (XE) of its 10g database back in 2005 or thereabouts, and this was later followed up by an Oracle 11g Express Edition database.

Oracle XE is great because it is lightweight and "free to develop, deploy and distribute". Dietmar Aust has posted a thorough clarification on XE licensing, where representatives from Oracle have confirmed that XE is indeed free. (The post also includes some good security advice in the comments.) Oracle XE has its limitations, but I've previously posted some performance benchmarks for Oracle XE, which shows that it is more than good enough for many use cases.

Back to the topic of this post... during the Q&A part of the "Oracle Database 12c Launch Webcast" there were several questions about a 12c version of XE:

Will there be an Oracle 12c Express Edition? It is planned, but there are no dates yet.

Is there an Express edition of 12c available? Planned, but no dates yet.

Will 12c be available for Express Edition? If so, schedule? Planned but no dates yet.

So basically, we now know that there will be an Oracle 12c Express Edition coming out, but we have no idea when. Hopefully it won't take 5-6 years, like the wait between 10g and 11g... (and when it arrives it will probably be for 64-bit Windows, which was promised for XE 11g, but which never materialized UPDATE: Oracle XE 11g for 64-bit Windows was released in June 2014).

But hey, it's free, and it's a great product, so it will be worth the wait! :-)

Saturday, May 18, 2013

JSON parser for PL/SQL in Apex 5 ?

I was looking at the Apex 4.2.2 installation script (coreins.sql), and noticed the mention of a new package, wwv_flow_json, which is apparently being worked on but was removed at the last minute "as no longer required for 4.2.2".

Even though the package is not installed into the database in Apex 4.2.2, the source code files are still present in the download. Looking at wwv_flow_json.sql we can see that this is a "JSON lexer and parser":

Now, obviously there is no guarantee of this package appearing in the next Apex release (5.0?), and the package spec even says it is not a public package and not for runtime deployment.

However, I would love to see an APEX_JSON package (that is, a synonym to the wwv_flow_json package) being bundled with Apex as an official, supported package. (It would obviously also need to be part of the runtime deployment to be of any use.)

Of course, there's already the excellent PL/JSON project which implements a JSON parser as an object type (and comes with a number of JSON-related utilities), but it would be great to have JSON parsing capabilities built-in to any Apex application (ie any database with Apex installed).

Also, it would make sense to move the JSON-generating procedures (json_from_items, json_from_sql, etc.) from APEX_UTIL to APEX_JSON. Those procedures, by the way, are still undocumented as of Apex 4.2.2.

So, dear Apex team, please consider adding the APEX_JSON package as a supported, documented, runtime-deployed package in the next Apex release!

And dear blog reader, if you think this is a good idea, go to the Apex Feature Request application and vote for my request with identifier "AFZU".

Friday, May 17, 2013

Using Apex with IIS Express

The Thoth Gateway is a gateway written in C# and ASP.NET that allows you to run PL/SQL web applications (including Oracle Application Express) on Microsoft's Internet Information Server (IIS). You can read more about the gateway here. It is an alternative to Apache/mod_plsql and the Java-based Apex Listener.

For production deployment on a server, you should use a regular IIS installation. However, if you are a developer working on a local Apex installation on your laptop, then the full IIS package is more than you need. Microsoft also offers a lightweight alternative in IIS Express:

IS Express (...) will run on Windows XP and higher systems, does not require an administrator account, and does not require any code changes to use.
You might want to take a look at this nice video with lots of information about IIS Express.

Using the Thoth Gateway with IIS Express is in many ways similar to running the Apex Listener in Standalone mode. Here's how to use the Thoth Gateway with IIS Express on your development machine.

Download and Install IIS Express

Download the IIS Express installer from Microsoft's website.

Run the installer.

This will put the IIS Express software under C:\Program Files\IIS Express and the configuration files under C:\Documents and Settings\Administrator\My Documents\IISExpress (assuming you are running Windows XP and your username is Administrator).

Configure IIS Express

Open \My Documents\IISExpress\config\applicationhost.config and add a new site under the "sites" node:

Copy and paste the following:

                <application path="/" applicationPool="Clr2IntegratedAppPool">
                    <virtualDirectory path="/i" physicalPath="C:\temp\apex_4.1.1_en\apex\images" />
                    <virtualDirectory path="/" physicalPath="C:\temp\apex_4.1.1_en\apex\images" />

                <application path="/web" applicationPool="Clr2IntegratedAppPool">
                    <virtualDirectory path="/" physicalPath="c:\temp\thoth_iisexpress" />

                    <binding protocol="http" bindingInformation=":8090:localhost" />

Some points to note:
  • This configuration sets up the virtual directories to serve the Apex images (in the "i" folder) and to run the actual gateway application (the "web" folder, which in mod_plsql is usually called "pls", but you can call it whatever you want). Both the images and the ASP.NET application are placed under c:\temp (but you can place them wherever you want).
  • The port number is set to 8090.
  • The traceFailedRequestsLogging has been disabled to improve speed.

For better performance with static files, you should also add a httpExpires value to the clientCache setting:

        <!-- see -->
        <clientCache httpExpires="Sun, 29 Mar 2030 00:00:00 GMT" cacheControlMode="UseExpires" />

Download and Install the Thoth Gateway

Download the Thoth Gateway and unzip the file.

Instead of following the normal installation instructions (which assume a full IIS installation), simply create a folder (I've used c:\temp\thoth_iisexpress in the example above) and copy the web.config and the bin folder from the unzipped file to this location.

Create a shortcut to start IIS Express

Now we just need a convenient way to start IIS Express whenever we want to do Apex work.

Create a batch file (I've called it start_iisexpress.bat) and put the following command in it:

"c:\program files\iis express\iisexpress.exe" /siteid:2 /systray:true

Create a shortcut to this batch file on your Windows desktop, and double-click the shortcut to start IIS Express.

As long as IIS Express is running, a console window will show each request:

That's it, you now have a lightweight, local web server for working with Oracle Application Express on a Windows machine.


If you need to troubleshoot the installation, you should enable the traceFailedRequestsLogging (see above). You'll find the trace files under \My Documents\IISExpress\TraceLogFiles. When you are done, disable this setting again for better performance.

Also note that all configuration changes to IIS Express require you to restart IIS Express before they take effect.

Sunday, February 17, 2013

Version 1.7 of Alexandria Utility Library for PL/SQL

The latest version of Alexandria, the utility library for PL/SQL, is now available for download. The previous version has been downloaded more than 2,000 times in the last 8 months.

The docs/readme.txt file contains a fairly detailed list of additions, enhancements and bug fixes. Below are some of the more noteworthy changes:

Improved installation script

You can now choose between installing the full library (close to 50 packages as of this version), by running the main /setup/install.sql script, or you can install just the core set of packages via setup/install_core.sql and then choose additional sets of packages on top of that (for example, /setup/install_microsoft.sql or /setup/install_inet.sql and so on).

Amazon S3 package enhancements

Jeffrey Kemp has submitted several functional enhancements to the S3 package which are incorporated into this version. Jeffrey has also done talks on the Alexandria Library in general and the S3 package in particular; the slides from these presentations are available here. Thanks for spreading the word, Jeff!

New packages: Icalendar, URI templates, and image utilities

A couple of new kids on the block:

  • ICALENDAR_UTIL_PKG: Lets you create Icalendar files from the database.
  • IMAGE_UTIL_PKG: Get information (such as image width and height) from JPG/GIF/PNG image files. This package is based on code from Anton Scheffer.
  • URI_TEMPLATE_UTIL_PKG: Use URI Templates to deal with "nice URLs" in PL/SQL and Apex web applications. 
I might get back to some of these topics later, in separate blog posts; here is a screenshot of the URI_TEMPLATE_UTIL_PKG package in action. (In real life, you would get the actual URI from a web request, for example via mod_plsql, and do something useful after parsing the request.)

Wednesday, January 16, 2013

PL/SQL and Microsoft technology

Many companies tend to have mixed environments, with Oracle databases (and PL/SQL) running business-critical back-office systems, as well as Microsoft products for front-office applications such as email, word processing and collaboration/file sharing (ie Outlook, Office and Sharepoint), and of course the desktop operating system itself (Windows, with Internet Explorer, Internet Information Server, all integrated via Active Directory).

Working in these mixed environments means that you, as an Oracle PL/SQL developer, frequently need to work/integrate with various Microsoft technologies from PL/SQL.

Over the last couple of years, I've written a number of blog posts on this topic. This post is just a convenient collection of links to these previous posts:


Oracle Data Provider for .NET (ODP.NET)

Oracle Application Express (Apex) and IIS

Microsoft Office and Office Open XML (OOXML)

Microsoft Exchange (Email, Calendar and Contacts)

Microsoft Sharepoint

  • I have not yet had the need (or the desire...) to work with Sharepoint from PL/SQL, but it would be quite easy to create a PL/SQL library package to interface with the Sharepoint web services, similar to the Exchange package listed above. Perhaps I will revisit this topic sometime in the future... ?