Wednesday, March 7, 2012

ROWID: "this dreadful identifier"

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 would get a bit tedious after a while...).

But enough lecturing! This post is really about the fun and/or weird stuff found in the comments of the STANDARD package.

You can have a peek at the package specification yourself by running the following query:

select text
from all_source
where owner = 'SYS'
and name = 'STANDARD'
and type = 'PACKAGE'
order by line

I'm using Oracle 10g XE to run this, the code and comments may obviously be different in other versions.


Line 700:

  -- PRIOR is WEIRD - For now, it will be treated as a function call.
  -- Does the function only take a column name?  how about its use in
  -- a predicate?
          return VARCHAR2 CHARACTER SET colname%CHARSET;
      pragma FIPSFLAG('PRIOR', 1452);

As programmers, I guess we all leave some questions in our code, but I am a bit concerned to find questions like this in production code in the core package of the database...

"ROWID: this dreadful identifier"

Line 863:

  -- ROWID: this dreadful identifier is supposed to represent a datatype
  -- outside of SQL and and a pseudo-column (function, to us) when inside
  -- a sql statement.  ADA data model doesn't allow for any
  -- function X return X;
  -- so we must special case this.  Yuk.  There's special-case code in ph2nre
  -- which maps "rowid" to "rowid " if we're inside a SQL stmt.
  function "ROWID " return ROWID;
    pragma builtin('ROWID ', 1, 209, 240);  -- this had better never be called.

Yeah, yuk, nobody likes to special case stuff.

And remember: This had better never be called...! :-)

Who is JEM?

Line 978:

  -- Next two added for NLS 6/3/92 JEM.
  function TO_CHAR(left date, format varchar2, parms varchar2) return varchar2;
  function TO_CHAR(left number, format varchar2, parms varchar2)
    return varchar2;

This just reminds us that PL/SQL has been around for a long time; it's 20 years since this comment was added to the code (1992). That's kind of cool. Does anybody know who JEM is, and if he/she still works at Oracle?

"Under development"

Line 1483:

  --## Support for ANSI datetime data types is under development.
  --## The following operations, as well as the related types and
  --## operations defined above in the 8.1.5 section, are not yet
  --## available for use and are still subject to change.

I'm looking at version 10.2 (XE) and I see comments related to work under development in version 8.1.6. Was this ever finished?

"Should there be a pragma?"

Line 3088:

  -- Should there be a 8.2 new names pragma ?

I don't know. Since there was never a version 8.2 I guess the question is now moot.

Oracle 10i ?

Line 3132:

  -- 10i Binary Floating-point Built-in Functions

Looks like Oracle version 10 was intended to be called 10i (for "internet"), like its predecessors 8i and 9i. As we all know, it was called 10g (for "grid") instead, just like 11g.

Rumor has it the next version will be called 12c (for "cloud"). Ironic, since Larry himself called cloud "just water vapor" a few years back. The marketing guys are to blame, I guess.

"Ref Cursor has problems"

Line 3242:

--  Ref Cursor has problems. The MOVCR instruction needs more information than
--  the other MOV* instructions, including the PVM register of the destination
--  This cannot be easily supplied through the generic NVL instruction, so
--  for now, this flavor will continue to have a real body
--  PRAGMA interface(c,nvl,"pes_dummy",9);       -- Ref Cursor

I won't pretend to understand any of that, except the fact that the Ref Cursor has some kind of problem. Despite this, it's doing a pretty good job in my PL/SQL programs.

We are at the end of the package specification, but for some reason the package body is not wrapped (obfuscated) like most other package bodies in the SYS schema. So we can continue reading...

"The old 'select from dual' thing"

Line 62:

  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'select soundex(...) from dual;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)

Sounds like a good idea.

"Perhaps more intelligently in the future"

Line 109:

-- Just call the other to_char with a null format string.
-- Perhaps this can be done more intelligently in the future. JEM 3/14/90.
--  function TO_CHAR(LEFT NUMBER)        return varchar2 is
--  begin
--    return TO_CHAR(LEFT, '');
--  end TO_CHAR;

There's JEM again. This time the comment is even older (22 years ago!). As the whole block of code is now commented out, I guess they already found "a more intelligent way" to do it.

"Why do we need these?"

Line 371:

-- why do we need these dummy bodies for LEVEL and ROWNUM?

  function LEVEL return NUMBER is
        begin return 0.0; end;

  function ROWNUM return NUMBER is
        begin return 0.0; end;

Don't ask me...!

That's it, we're at the end of the STANDARD package. The DBMS_STANDARD package is much shorter and does not really contain any interesting comments. But I'm sure there are other packages in the SYS schema that contain some other gold nuggets -- can you find them?

Thursday, March 1, 2012

Windows NTLM SSO with Apex

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 mod_ntlm, mod_auth_sspi, proxies, etc., but you must look elsewhere for more details about those.
  • If you are using (or can use) Microsoft Internet Information Server (IIS) to serve your Apex applications, then read on.

How to set up Single Signon for Oracle Apex applications on Microsoft IIS

Here are the high-level steps you need to take:

  1. Install (enable) Microsoft IIS on your Windows Server
  2. Download and install the Thoth Gateway, a mod_plsql replacement for IIS
  3. Configure the virtual directory of the gateway application (typically the "pls" folder) to use Integrated Windows Authentication
  4. Create an authentication scheme in your Apex application that checks the HTTP header called "LOGON_USER" and make that the current scheme of your application
Let's look at the two last steps in some more detail.

Configure virtual directory to use Integrated Windows Authentication

After you have installed the Thoth Gateway (see the installation instructions provided with the download), you can use the IIS management console to change the directory security settings of the virtual folder (application) where the gateway is installed.

In the following screenshot the folder is called "web-integrated-security", which just shows that you can really call it anything, but in a default installation of the Thoth Gateway the folder will be called "pls" (which mimics the naming convention used by Apache and mod_plsql).

Simply check "Integrated Windows Authentication" to enable SSO. Also remember to remove anonymous access to the website!

Create Apex authentication scheme that checks LOGON_USER

IIS will now capture the username of the current user and pass it along to the Thoth Gateway as the CGI variable called "LOGON_USER".

You can access this value from PL/SQL using


To use this value in an authentication scheme in Apex, you can use the code provided by Joel Kallmann in this post where he provides a "custom authentication scheme, which can be used with (...) really any environment which will securely set a header variable to an authenticated username." Joel describes five high-level steps, of which you can ignore steps 3 and 4, as this is now handled by IIS and the Thoth Gateway. Also remember to reference "LOGON_USER" rather than "REMOTE_USER".

If you are using Apex 4.1 or later, then there is no need to create a custom authentication scheme, as there is a new built-in "HTTP Header Variable" authentication scheme:

Be sure to make this authentication scheme the current scheme of your application.

Verify that it works

If you are logged into the network with a Windows (Active Directory) account and use Internet Explorer to access your website (which should be in the browser's list of Trusted Sites -- it will typically already be trusted if the website is in the Intranet zone), you should not be asked for a username and password, and your Apex username (APP_USER) should be automatically set to "DOMAIN\USERNAME".

That was easy, wasn't it ?