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".

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>

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

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

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 http://madskristensen.net/post/Add-expires-header-for-images.aspx -->
        <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.

Troubleshooting

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.