thoth-gateway
Thoth Gateway: PL/SQL Gateway Module for Microsoft IIS, similar to mod_plsql and Apex Listener (ORDS)
Thoth Gateway: PL/SQL Gateway Module for Microsoft IIS
What is the Thoth Gateway?
In ancient Egyptian mythology, Thoth was an ibis-headed god who mediated between good and evil, making sure neither had a decisive victory over the other. He also served as scribe of the gods, credited with the invention of writing and alphabets. He is associated with the Eye of Horus because he restored it after a battle between Set and Horus in which the latter god's eye was torn out.
The Thoth Gateway is a bridge between an Oracle database and a Microsoft web server. It is an open-source alternative to mod_plsql and the Embedded PL/SQL Gateway, allowing you to develop PL/SQL web applications using the PL/SQL Web Toolkit (OWA) and Oracle Application Express (Apex), and serve the content using Microsoft's Internet Information Server (IIS).
Why use the Thoth Gateway? What's wrong with mod_plsql or ORDS?
Nothing is wrong with mod_plsql or ORDS. Those modules are professional-quality, well-tested, and officially supported. However, it requires the Apache webserver (in the case of mod_plsql) or a Java-based web server (in the case of ORDS). That can be a good thing or a bad thing, depending on who you ask. The Thoth Gateway was built as an alternative for those who prefer or require the use of Microsoft's IIS. And being open source, the Thoth Gateway can easily be modified or extended, for whatever reason.
Core features
Thoth implements the core features of mod_plsql and the Embedded PL/SQL Gateway. Most importantly, it allows you to run Oracle Application Express (Apex) applications.
- Web page content generation (HTML, XML, etc.) via the PL/SQL Web Toolkit (OWA)
- File uploads and downloads
- Forward CGI variables to OWA environment
- Process HTTP headers including cookies and redirects
- Basic authentication
- Request validation procedure
- Path alias procedure
- Flexible parameter passing
- Caching of procedure metadata to avoid the describe overhead on subsequent requests
- Debug-style error page and logs
- Database connection pooling
Features in Thoth that are not in mod_plsql
- Integrated Windows authentication (if the virtual directory that contains the Thoth Gateway is set up with integrated Windows authentication, you can get the username of the authenticated user via owa_util.get_cgi_env('LOGON_USER'))
- CLOB support (parameter values greater than 32k will automatically be converted to CLOB parameters)
- Inclusion list for procedures (which means you can whitelist procedures instead of blacklisting them, for increased security)
- No limit on the number of parameters (mod_plsql has a limit of 2000 parameters or name/value pairs per procedure)
- Optionally, querystring and form parameters can be passed along with the URL to the PlsqlPathAliasProcedure (which, among other things, means that you can implement complete RESTful web services with PL/SQL; the standard behaviour of mod_plsql is to discard the posted form parameters and just pass along the URL)
- Set maximum individual file size for uploads
- Publish PL/SQL as SOAP Web Services
- XDB integration
Features in mod_plsql that are not in Thoth (yet)
- Custom authentication methods, based on the OWA_SEC package and custom packages. Note that the Thoth Gateway does support basic authentication, but does not implement the OWA-specific authorization procedures. Most applications, including Apex, use cookie-based authentication anyway.
- File system caching of PL/SQL-generated web pages, based on the OWA_CACHE package. Note that the Embedded PL/SQL Gateway (DBMS_EPG) does not support caching, either. However, you can place static files such as images, stylesheets and Javascript in the file system [a separate virtual directory in IIS], and you can employ the built-in functionality in Apex to cache pages and page regions.
- HTML image maps, as represented by the OWA.IMAGE datatype. Who uses client-side image maps these days, anyway? I have not seen this technique used on a web page since the glory days of Netscape Navigator back in 1998...?!?
- Override CGI environment variables in DAD configuration file. Note that the Thoth Gateway passes all CGI variables from the web server to OWA; by default mod_plsql passes just a subset and you have to add additional parameters in the DAD config file.
Getting started
Download the package and refer to the Installation Guide for details on how to set up the Thoth Gateway.
Technical details
- The Thoth Gateway is implemented as an ASP.NET HttpModule written in C# using the .NET Framework
- The Oracle Data Provider for .NET (ODP.NET) is used to communicate with the database
- Logging and instrumentation is done using log4net
- Configuration of Database Access Descriptors (DADs) is stored in the web.config file
Contributions
If you have .NET or Oracle skills (especially in the areas of performance optimization and security) and would like to contribute to or improve the source code of the Thoth Gateway, please contact me.
Acknowledgements
Several other gateway implementations exist, including mod_owa (which is an Apache module written by Doug McMahon) and DBPrism (which is a Java servlet written by Marcelo Ochoa). The source code and documentation for these other (open-source) implementations have been of invaluable help during the development of the Thoth gateway. The documentation for Tom Kyte's older OWA Replacement Cartridge and the official documentation for mod_plsql itself have also been useful. I'd also like to give a shout-out to the community at StackOverflow.