Friday, February 14, 2014

Oracle on Windows Azure pricing revealed

I just noticed that prices for Oracle Database running on Windows Azure have now been published.



The prices for the virtual machines with the Oracle license included run from about USD 820 per month for Oracle Standard Edition (SE) running on 2 cores, to a whopping USD 9300 per month for Oracle Enterprise Edition (EE) running on 8 cores. These prices are for Oracle software only and do not include the cost of the VM, which is billed separately.

Alternatively, with "License Mobility", you obtain a license as normal from Oracle and run that software on Windows Azure by deploying a Windows Server or Linux VM and installing a copy of the licensed Oracle software. Azure is listed as an "Authorized Cloud Environment" in the Oracle Cloud Licensing Policy. As I understand it, this policy allows you to use Oracle Standard Edition One (SE1) on Azure (up to 4 cores = 1 processor license, up to 8 cores = 2 processor licenses), which would be a quite cheap entry-level option for small businesses, since you would pay once for a perpetual database license, and then just pay monthly for the Azure VM (ie just the Windows/Linux Server software, not the database).

There's also an article at ZDNet with more details.


Sunday, October 20, 2013

Pivot Table plugin for Apex

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

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>

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

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