Select * from Inbox

| May 1, 2012
... or how to read your (Microsoft Exchange) email using SQL and PL/SQL.


You are probably familiar with sending email using PL/SQL; there's the UTL_SMTP package, the APEX_MAIL package, and various other utilities that do this.

But what about reading email using PL/SQL? You need to investigate the POP and IMAP protocols. A quick search on Google turns up some sample code and even a feature-rich package, although the latter requires Java in the database (which I try to avoid whenever possible, and which is not available in Oracle XE in any case).

Approaching this from a different angle, (more...)

Interesting PL/SQL and Apex links, April 2012

| Apr 21, 2012
Here's a bunch of interesting stuff related to PL/SQL and Apex that I've come across lately, I'm posting it both as a reminder to myself, and to help spread the word:

  • First mention of PL/SQL enhancements for Oracle Database 12c: In his PL/SQL newsletter for March 2012, Steven Feuerstein revealed that "I've also started beta testing Oracle Database 12c PL/SQL. I can't yet share with you any of the new features, but I can tell you that PL/SQL will certainly be a richer, more usable language when 12c goes production!".
  • On that topic, a couple of things I (more...)

ROWID: "this dreadful identifier"

| Mar 7, 2012
Today I'm taking a look at the core packages of the PL/SQL language, namely the STANDARD and DBMS_STANDARD packages in the SYS schema. These packages contain a lot of the language features we use every day in PL/SQL programming.

These two packages are special in that you do not need to qualify the name of functions, procedures and constants in these packages with the package name. For example, SYSDATE and USER and DECODE and so on are declared in the STANDARD package, but you can use it without having to specify STANDARD.SYSDATE or STANDARD.USER or STANDARD.DECODE (which (more...)

Windows NTLM SSO with Apex

| Mar 1, 2012
I see this question being asked now and then:

Is it possible to set up my Oracle Application Express (Apex) application with Single Signon (SSO) based on Windows Integrated Security (NTLM), so that the end-users are automatically logged into the Apex application with their Windows (Active Directory) account without entering their username and password?

The answer is yes, but the implementation depends on your infrastructure, and specifically your web server:

  • If you are using Apache (Oracle HTTP Server), the Embedded PL/SQL Gateway (DBMS_EPG) or  the Apex Listener, then this post is not for you. There are various options such as (more...)

Thoth Gateway version 1.3.6 available

| Feb 28, 2012

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

CC by Flickr user gzayatz

The previous version available for download was 1.3.0, so there are a number of enhancements and bug fixes rolled up into this release, including:

Version 1.3.6

Bug fix: Value of the CGI environment variable LOGON_USER was empty on IIS 7 running in Integrated pipeline mode: This was due to a breaking change on IIS 7. The Thoth Gateway now hooks into the PostAuthenticate event, rather than (more...)


| Aug 3, 2011
NTLM, or more properly NTLMSSP is a protocol used on Microsoft Windows system as part of the so-called Integrated Windows Authentication.

Integrated Windows Authentication is also known as HTTP Negotiate authentication, NT Authentication, NTLM Authentication, Domain authentication, Windows Integrated Authentication, Windows NT Challenge/Response authentication, or simply Windows Authentication.

In Microsoft Internet Information Server (IIS), the system administrator can protect a website or folder with "Integrated Windows Authentication". When you browse to this website or folder, you must enter your Windows (domain) username and password to get access (although Internet Explorer will, depending on your security settings, send your credentials (more...)

New version of Alexandria Utility Library for PL/SQL

| Aug 3, 2011
I've just uploaded a new version of the Alexandria Utility Library for PL/SQL.

Updates include both small bug fixes and some major new features (which I'll return to in another post).

Among the improvements are:

  • Additional functions in OOXML_UTIL_PKG for working with Excel 2007 and Powerpoint 2007 files.
  • Kris Scorup has contributed improved CSV parsing to the CSV_UTIL_PKG. It now handles double quotes and separator characters inside strings.
  • Anton Scheffer's packages for building PDF and XLSX files have been included in the library.
  • The PL_FPDF library by Pierre-Gilles Levallois is a port of the FPDF library for PHP. Pierre-Gilles Levallois (more...)

Count lines in multiple files using Windows command prompt

| Jul 29, 2011
Not really Oracle-related, but I'm posting this as a reminder to myself and possibly useful to others.

To count the number of lines in a given set of files using the Windows command prompt, do the following:

for %G in (*.sql) do find /c /v "_+_" %G

This invokes the "find" command once for each file, counting the lines that do NOT contain the string "_+_" (the string has no special significance, any weird string that would not occur "naturally" in the files can be used).

There are probably more sophisticated ways of doing this, perhaps using PowerShell and (more...)

Mythbusters: Stored Procedures Edition

| Jul 29, 2011

These days, the use of database stored procedures is regarded by many as a bad practice.

Those that dislike stored procedures tend to regard them as incompatible with the three-tier architecture:

By breaking up an application into tiers, developers only have to modify or add a specific layer, rather than have to rewrite the entire application over. There should be a presentation tier, a business or data access tier, and a data tier.

This is illustrated as follows:

Note that the "tiers" in the figure should actually be labelled "layers", for as the accompanying Wikipedia article says:
The concepts (more...)

Mobile device support in Apex 4.1

| May 24, 2011
The current Apex Statement of Direction for Apex 4.1 states that it will "include themes and HTML templates suitable for smart phones and mobile devices".

If you are wondering what that means, then check out this thread on the Apex OTN Forum where Marc Sewtz, one of the developers on the Apex team, provides more details about this new feature.

Interestingly, some of these "mobile-enabling" features are also relevant for regular applications, such as the ability to render a form without a table grid, enhanced label templates, and dynamic (SQL-based) lists.