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.

"PRIOR is WEIRD"

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?
  function 'PRIOR'(colname VARCHAR2 CHARACTER SET ANY_CS)
          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.)

  function SOUNDEX(ch VARCHAR2 CHARACTER SET ANY_CS)

        return VARCHAR2 CHARACTER SET ch%CHARSET is

    c VARCHAR2(2000) CHARACTER SET ch%CHARSET;

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?

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

owa_util.get_cgi_env('LOGON_USER')

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 ?

Thoth Gateway version 1.3.6 available

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 the BeginRequest event, in order for information about the current user to be available when using Windows Integrated Authentication.

Version 1.3.5

Hide server banner: A new top-level (non-DAD-specific) configuration parameter, HideServerBanner, has been added. If this is set to true (the default is false), the server will not emit the response server headers that identify the server as IIS powered by ASP.NET. This can improve security in certain situations, and reduces the response size. Requires IIS 7 with "integrated pipeline" mode.

Set SOAP date format: A new DAD-specific configuration parameter, SoapDateFormat, has been added. The default is YYYY-MM-DD"T"HH24:MI:SS. This is used to set the database session NLS date format when the gateway receives a SOAP request. You can adjust this parameter if your SOAP clients are sending dates with an UTC time indicator or offset.

Bug fix: Don't upload empty files: The gateway incorrectly uploaded files even from an empty file browse field (when the user submitted a form without having selected a file).

Bug fix: Handle responses with HTTP headers but no HTTP body: An ArgumentOutOfRangeException was thrown if the request included (just) HTTP headers without any body.

Version 1.3.4

Bug fix: Handle missing accept-encoding header: Dynamic content compression did not correctly handle clients with missing accept-encoding header.

Version 1.3.3

Serve static content from gateway folder: A new top-level (non-DAD-specific) configuration parameter, ServeStaticContent, has been added. If this is set to true (the default is false), the gateway will check if the requested URL maps to a physical file, and if so, will serve up this file instead of connecting to the database.

Compress dynamic content: A new top-level (non-DAD-specific) configuration parameter, CompressDynamicContent, has been added. If this is set to true (the default is false), and the client (browser) signals that it can accept compressed content, the gateway will compress the generated output (using GZip or Deflate, depending on the client's stated preference). This reduces network traffic at the cost of more CPU usage on the server. Note that although the default value of this is false (for backwards compatibility), I recommend you set the parameter to true, as it can give your website a significant performance boost.

Version 1.3.2

SOAP Faults: A database exception during a SOAP request now creates a SOAP Fault response that contains the database error message (see the SoapErrorStyle configuration parameter), instead of the normal 404 error (or debug error page).

Version 1.3.1

Performance optimization: The main PL/SQL procedure call issued by the gateway now also includes the call to check if the response is a file download (which used to be a separate PL/SQL call). This reduces the total number of database calls, thereby improving performance.



For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

NTLM for PL/SQL

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 automatically without showing a login dialog box). Note that unlike Basic Authentication, which sends the password as plaintext to the web server, the NTLM protocol does not send the password but rather performs a cryptographic "handshake" with the server to establish your identity.

Use of Integrated Windows Authentication via NTLM on IIS is very common inside many companies (ie on intranets and internal web servers), where both the client and web server computers are part of the same, or trusting, domains.


Using NTLM from PL/SQL with UTL_HTTP

Unfortunately, from the PL/SQL developer's perspective, Oracle's UTL_HTTP package does not support NTLM authentication (it only supports Basic authentication via the SET_AUTHENTICATION procedure).

So, if you wanted to retrieve information from your intranet or call a web service (protected by Integrated Windows Authentication) from the database via PL/SQL and UTL_HTTP, you were out of luck.

Until now, that is... :-)


A pure PL/SQL implementation of the NTLM protocol

I came across a Python implementation of the NTLM protocol, and I decided that it should be possible to port this code to PL/SQL. Assisted by a couple of good friends and colleagues, and after a lot of bit-fiddling, reverse-engineering, study of protocol specifications, and liberal use of network packet sniffers, we got it working!

A pure PL/SQL implementation of the NTLM protocol is now available and included in the Alexandria Utility Library for PL/SQL.

The code is organized into two packages: NTLM_UTIL_PKG, which contains protocol-specific functions, and NTLM_HTTP_PKG, which is the package you actually use to make HTTP callouts, and which handles the initial NTLM "handshaking" with the web server.


Example 1: Simple request

This code simply grabs the page you direct it towards, and returns the contents as a CLOB. (What is really going on behind the scenes is a series of requests and responses to establish the authenticated connection, before the actual URL contents is served.)


declare
  l_clob clob;
begin
  debug_pkg.debug_on;
  l_clob := ntlm_http_pkg.get_response_clob('http://servername/page', 'domain\username', 'password');
  debug_pkg.print(substr(l_clob, 1, 32000));
end;




Example 2: Web service call

Here, a (persistent) connection is explicitly established before making one or more requests to the server. Note the returnvalue from the BEGIN_REQUEST function, which is the authorization string which must be passed along in the "Authorization" HTTP header on any subsequent requests. The connection is then is closed. Note that NTLM is a connection-based protocol, and will not work without the use of persistent connections.



declare
  l_url           varchar2(2000) := 'http://servername/page';
  l_ntlm_auth_str varchar2(2000);
  l_xml           xmltype;
  l_soap_env      clob := 'your_soap_envelope_here';
  
begin
  debug_pkg.debug_on;


  -- perform the initial request to set up a persistent, authenticated connection
  l_ntlm_auth_str := ntlm_http_pkg.begin_request (l_url, 'domain\username', 'password');


  -- pass authorization header to next call(s)
  apex_web_service.g_request_headers(1).name := 'Authorization';
  apex_web_service.g_request_headers(1).value := l_ntlm_auth_str;


  -- perform the actual call
  -- NOTE: for this to work, you must be using a version of apex_web_service that allows persistent connections (fixed in Apex 4.1 ???)
  --       see http://jastraub.blogspot.com/2008/06/flexible-web-service-api.html?showComment=1310198286769#c8685039598916415836
  l_xml := apex_web_service.make_request(l_url, 'soap_action_name_here', '1.1', l_soap_env);


  -- or use the latest version of flex_ws_api
  -- flex_ws_api.g_request_headers(1).name := 'Authorization';
  -- flex_ws_api.g_request_headers(1).value := l_ntlm_auth_str;
  -- l_xml := flex_ws_api.make_request(l_url, 'soap_action_name_here', '1.1', l_soap_env);


  -- this will close the persistent connection
  ntlm_http_pkg.end_request;


  debug_pkg.print('XML response from webservice', l_xml);
end;




Remarks

• Tested successfully on Oracle 10g XE (with AL32UTF8 character set) and Oracle 10g EE (with WE8MSWIN1252 character set).
• Tested successfully against IIS 6.0 with non-SSL "plain" website and SSL-enabled Sharepoint website (both set up with Integrated Windows Authentication, obviously).
• The current version ignores cookies when setting up the connection. If you depend on cookies being present, you may have to deal with this specifically.

Given the diverse nature of network configuration, there may be bugs or unhandled cases in the code. So please test the code in your environment and leave a comment below, letting me me know if it works for you or not.

New version of Alexandria Utility Library for PL/SQL

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 has his own website, but his package (which is open source under the GNU license) does not appear to have been updated for several years, and several indivuals have been making their own fixes and enhancements to this package. Rob Duke and Brian McGinity have both contributed bug fixes and enhancements (such as internal document links, Javascript support, using clobs to work around the 32k limit, etc.). These changes as well as some of my own have been merged and included in the Alexandria library as PDFGEN_PKG. You'll find this package in the "extras" folder (it is not installed by default if you run the install script).

Count lines in multiple files using Windows command prompt

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 whatnot. Leave a comment if you know of other methods (something that adds together all the file line counts into one grand total would be even better).

Mythbusters: Stored Procedures Edition

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 of layer and tier are often used interchangeably. However, one fairly common point of view is that there is indeed a difference, and that a layer is a logical structuring mechanism for the elements that make up the software solution, while a tier is a physical structuring mechanism for the system infrastructure. 

In fact, those that argue that stored procedures are bad tend to equate the three logical layers with three physical tiers:

1. Data layer = data tier (database)
2. Logic layer = middle tier (application server)
3. Presentation layer = presentation tier

But if we accept the above definition of "layers" and "tiers", it is obvious that the following is a valid mapping as well:

1. Data layer = data tier (database)
2. Logic layer = data tier (database)
3. Presentation layer = presentation tier

In other words, the database becomes our "logic layer" through the use of database stored procedures, which, as the name implies, are physically stored (and executed) in the database. (And although I use the term "stored procedure", I'm primarily talking about Oracle and PL/SQL, where the PL/SQL code should be put in packages rather than stand-alone procedures.)

But why is this a bad idea? In fact, as it turns out, it might not be a bad idea at all. The usual reasons given against the use of stored procedures for "business logic" (or for anything at all, really) tend to be myths (or outright lies), repeated so many times that they are taken as the truth.

So let's bust these myths, once and for all. And whenever someone argues against stored procedures using one of these myths, just give them a link to this blog post. (And leave comments to prove me wrong, if you will.)


Myth #1: Stored procedures can't be version controlled

Stored procedure code lives in text files, which can be version controlled like any other piece of code or document. Storing/compiling the code in the database is just like (re-)deploying any other code.

Claiming that stored procedures cannot be version controlled (because they are in the database) is like saying your application source code (Java, C# or whatever) cannot be version controlled because it is compiled and deployed to an application server.

Myth #2: Managing the impact of changes in the database is hard

Databases such as Oracle have built-in fine-grained dependency tracking.

A wealth of information about your code is exposed via data dictionary views.

Myth #3: Database tools lack modern IDE features

There are a number of free and commercial PL/SQL code editors and IDEs, and all have various levels of syntax highlighting, code insight and refactoring support.

Myth #4: Stored procedures always result in spaghetti code

To this, I can only say that bad programmers can make pasta in any language (the above is a visual representation of a Java or .NET enterprise framework "several dozen megabytes chock full of helper classes like IEnterpriseAuthenticationProviderFactoryManagementFactory").

And a good programmer can create "beautiful" code in COBOL, Visual Basic, PHP... and any stored procedure language, for that matter.

Myth #5: Code in the database can’t be properly encapsulated and reused, you need an object-oriented language for that

PL/SQL packages, views, pipelined functions and Ref Cursors offer encapsulation and reuse. And PL/SQL has object-oriented features, too.

Myth #6: Stored procedure languages are primitive, they lack basic features such as exception handling and dynamic execution

PL/SQL has had proper exception handling from the start, over 20 years ago (although exception handling was only introduced to SQL Server in 2005).

DBMS_SQL, EXECUTE IMMEDIATE and "weak" Ref Cursors enable dynamic execution of code. Parameter overloading and the ANYDATA and ANYTYPE types allow for generic code to be written.

Myth #7: Debugging stored procedures is hard/impossible

Both Oracle and SQL Server have built-in debugging capabilities, exposed via graphical user interfaces in the common IDEs, with full support for stepping though code, inspecting values, etc.

Myth #8: Stored procedures can't be unit tested

There are a number of free and commercial unit testing frameworks available for PL/SQL. Steven Feuerstein, one of the world's leading experts on the Oracle PL/SQL language, has been preaching the importance of unit testing in the database for years, and has developed several of the available unit testing frameworks.

Myth #9: Stored procedures are not portable, and tie you to one platform

This is the "vendor lock-in" argument. But the fact is that PL/SQL runs on multiple databases.

Such as DB2:

"IBM DB2 9.7 for Linux, UNIX, and Windows has out-of-the-box support for Oracle's SQL and PL/SQL dialects. This allows many applications written against Oracle to execute against DB2 virtually unchanged."

And Postgres (EnterpriseDB):

"Postgres Plus Advanced Server implements a comprehensive suite of Oracle-compatible functionality within and around the core PostgreSQL engine, including: (...) Oracle SQL syntax and semantics, Functions and Packages, PL/SQL (extensive support)"

Add to this the fact that the Oracle database runs on more operating systems than any other database, which means that your PL/SQL code will seamlessly transfer from Windows to Unix to Linux-based systems.

So PL/SQL-based code can actually be said to be more portable than, for example, .NET code (despite the existence of Mono). There are very few truly portable technologies; even Java is "write once, debug everywhere".

Myth #10: It's stupid/dangerous to put business logic in the database

This claim is usually made without any specific reason as to why it is stupid or dangerous. It usually "just is", because it is "against best practice" and "everybody else is putting the business logic in the middle tier". Sometimes it is claimed that putting logic in the database "mixes concerns", which must be a bad thing.

The problem with "business logic" is that nobody has a clear definition of what it is (but "you'll know it when you see it"). For example, where do you draw the line between "data logic" and "business logic"? Primary keys, foreign key constraints, unique key constraints, not null constraints, check constraints -- are these "data logic" or "business logic"? "Discount must be between 0% and 5%", is that a business rule or a data constraint, and/or is it a validation rule in the presentation layer?

The fact is, if you move ALL your logic into stored procedures, you entirely avoid the "mixing of concerns" between the data tier and the logic tier. (And if you think such an approach dooms your project to failure, consider the next myth, which features an example of a massive [and wildly successful] application written entirely in the database.)

Oh, and by the way, if your business logic is somewhere else than in the database, you always run the risk of someone or something bypassing your middle tier (for example by logging in with SQL*Plus), directly updating the database and possibly corrupting the data.

So let's turn this around and conclude instead that:

"If your business logic is not in the database, it is only a recommendation."

Myth #11: Stored procedures can't scale

A frequent argument against stored procedures is that by placing all the work in the database server, your solution won't be able to scale up, because you need "application servers" in the middle tier to do that. The scalability of the database is limited by the fact that you can only have a single database server (or you need to rewrite your code to work with partitioned/sharded databases like Facebook have done).

Of course, a lot of the people who throw around this kind of argument have never worked on an application or website which needed to scale up to millions of users (and to be clear, neither have I). That's because the vast majority of us work on much smaller enterprise business systems or "normal" websites (perhaps even the kind of website that can be well served with free database software on a server with less juice than your laptop).

But stored procedures CAN scale. It's only a matter of money. And if you have millions of users, you should be able to afford decent hardware.

Let's use Oracle Application Express (Apex) as an example of a big and complex PL/SQL application:

"[Application Express] lives completely within your Oracle database. It is comprised of nothing more than data in tables and large amounts of PL/SQL code. The essence of Oracle Application Express is approximately 425 tables and 230 PL/SQL packages containing 425,000+ lines of code."

This PL/SQL application can be deployed anywhere from your laptop to any kind of server:

The biggest and fastest server you can buy is currently Oracle Exadata.

"An 8 rack configuration has a raw disk capacity of 3,360 TB and 1,680 CPU cores for SQL processing. Larger configurations can be built with additional InfiniBand switches."

Oracle makes bold claims about this machine:

"Oracle claims that (..) two Exadata database systems would be able to handle Facebook’s entire computing load."

It's hard for me to verify that claim, not being associated with neither Oracle nor Facebook, but let's assume it has at least some truth to it.

So what about running our "stored procedure" application on Exadata?

"Does APEX Work on Exadata?

"Yep, Exadata is just Oracle. Let me say that again: It’s just Oracle. Exadata runs an 11.2 Database on Linux x64. It’s the exact same binary install if you download those binaries for “generic” Linux x64 11.2 from OTN. So, if your code / app runs on 11.2, it runs on Exadata. (..) The APEX Dev Team (my old friends and colleagues) did absolutely nothing to port their code to Exadata. I've run a number of customer benchmarks with customer's data and queries and have yet to make a single change to their queries or structures to make them work on Exadata."

So... without changing a single of those 425,000 lines of code, this "stored procedure" application can run on my old laptop (I've even tried it on an Asus EEE netbook), or it can run with 1,680 CPU cores. Without offloading any logic to an application server.

I'd say that's pretty scalable.