Tuesday, November 6, 2012

Apex Plugin: Execute PL/SQL code and return content to page

Here is a Dynamic Action plugin for Oracle Application Express (Apex) that lets you execute PL/SQL code in the database, and then send output generated on the server (via calls to the "HTP.P" procedure) back to any element on the web page.

A more specific use case would be to respond to the clicking of a button by generating a fragment of HTML using PL/SQL, and then refresh a DIV on the page with that dynamically generated content. See the screenshot below for an example; notice that part of the first region has been updated with content generated on the server (using input from the user, in this case the name from a regular text input item). Every time the user clicks on the button, the line above the button is updated using an Ajax call to the server.

The second region illustrates another use case: Automatically refreshing part of the page at a fixed interval.

This is how the page has been set up:

The first region has a standard text input item, and a button. The button has a Dynamic Action that is triggered when the button is clicked. The click executes the PL/SQL code which generates the greeting.

The dynamic action is set up as follows:

We can see that the content generated by the PL/SQL code is returned into a page element called "my_div". This div is simply included as the static content of the HTML region:

The second dynamic action has been set up to trigger on "Page Load". The plugin is set up to call the PL/SQL code every 5 seconds (nice way to hammer your database with requests...) and return the content into another div, called "my_other_div". The mode has been set to "Append after", which means the content will be added to the end of the existing content, instead of replacing it.

Here is the setup for the second region:

Conclusion: This plugin combines the flexibility of Dynamic Actions with the power of PL/SQL for dynamic content generation. You can download the plugin here. Update 22.05.2016: This plugin is now in my plugin repository at GitHub.

Monday, August 13, 2012

Database (schema) backup to the cloud with PL/SQL

In my last blog post I described using the DBMS_DATAPUMP and DBMS_JOB packages to automate database schema backups from the database itself, rather than using batch files to do so. I also mentioned that "since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. [One] option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3."

Since I have previously written a PL/SQL API for Amazon S3, uploading your backup files to S3 is not very difficult. The high-level steps are:

  1. Create an export (dump file) of your database schema using DBMS_DATAPUMP (or the DATAPUMP_UTIL_PKG wrapper described in my last blog post).
  2. Save the export file to disk on the server.
  3. Upload the export file to S3.
  4. (Optionally) Delete the export file from the server.
  5. (Optionally) Send an email notification indicating if the upload was successful or not.
In the "demos" folder of the Alexandria Utility Library for PL/SQL you can find a package called DATAPUMP_CLOUD_PKG which does the work described above in a procedure called BACKUP_SCHEMA_TO_S3.

Here is how you can set up a call to DBMS_JOB to schedule regular backups of your database schema to Amazon S3 (note the double quoting of the string values):

  l_job number;
  l_what varchar2(4000);

  l_what := 'datapump_cloud_pkg.backup_schema_to_s3(''your_amazon_id'', ''your_amazon_password'', ''your_amazon_bucket'', ''your_amazon_folder'', ''YOUR_ORACLE_BACKUP_DIR'', ''your_backup_file_name_'' || to_char(sysdate, ''yyyymmddhh24miss''), p_email_failure => ''your_email_address@domain'', p_email_success => ''your_email_address@domain'', p_version => ''10.2'', p_gmt_offset => 0);';

  -- run job at the end of each day

  dbms_job.submit (job => l_job, what => l_what, next_date => sysdate, interval => 'trunc(sysdate + 1) + 23/24');



Security Tip: You may not like to expose your Amazon password in the job definition. What you can do to reduce the risk is to use the AWS Identity and Access Management (IAM) tools to define a separate backup user which only has upload ("put") permissions on your S3 folder, and no privileges to list file contents, delete or download files. This will limit the damage that someone with a stolen password can do.

Monday, July 23, 2012

Simple database (schema) backup using PL/SQL

Usually, database backups are the responsibility of DBAs, and not something that developers really care or think too much about. However, if you are a lone developer, or part of a small team that doesn't have the luxury of having a dedicated DBA, then you might have to deal with backups yourself.

There are several ways to make Oracle backups; what I will be concentrating on here is the "Data Pump Export" method. You may already be familiar with the command-line "expdp" command which allows you to create a dump (.dmp) file containing your database objects (schemas, tables, procedures, etc.).

To perform a regular backup, one could create a simple batch file to run "expdp" and schedule it using the Windows Task Scheduler or a Unix Cron job. While this certainly works, it means that you have one more "moving part" to think about (the OS scheduler), and you may have to hardcode database passwords in the batch file unless you use OS authentication.

However, the "expdp" command-line utility is actually just a "front-end" to a PL/SQL package called DBMS_DATAPUMP, as can be seen in this diagram:

This means that we can call the DBMS_DATAPUMP package directly from our own PL/SQL code to create dump files, and use DBMS_JOB to schedule this backup at regular intervals. We avoid the dependence on batch files, and we don't have to expose any passwords.

The API for DBMS_DATAPUMP is flexible and, as mentioned, supports everything that can be done from the command line (both exports and imports), but it can sometimes be a bit tricky to get the (filtering) syntax correct.

I've written a wrapper package called DATAPUMP_UTIL_PKG, which you can download as part of the Alexandria PL/SQL Utility Library. This package makes it really easy to perform the most common use case: Exporting a single schema to a file on disk. This can be accomplished with the following code:

-- export current schema to file, use default file name, and make the export compatible with XE 10g
-- include a custom message

  datapump_util_pkg.export_schema_to_file ('DEVTEST_TEMP_DIR', p_version => '10.2', p_log_message => 'it is possible to include custom messages in the log');

To schedule this as a regular job, use the following code:

  l_job number;
  l_what varchar2(4000);

  l_what := 'datapump_util_pkg.export_schema_to_file(''DEVTEST_TEMP_DIR'', p_version => ''10.2'');';

  -- run job at the end of each day

  dbms_job.submit (job => l_job, what => l_what, next_date => sysdate, interval => 'trunc(sysdate + 1) + 23/24');



For this to work, you also need to set up a directory on the database server:

-- run as SYS
create directory devtest_temp_dir as 'c:\some_folder\backup\';
grant read, write on directory devtest_temp_dir to your_database_schema;

Obviously, since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. Unlike the "hot" database files, these dump files can be safely copied by normal backup software.

Another option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3. I will talk about that in my next blog post...

PS. Don't forget to regularly test a restore (import) of your backup files. The only thing worse than not having a backup is having a backup that can't be restored... !

Saturday, May 26, 2012

MS Exchange API for PL/SQL

As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!


Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.


  • You need access to an Exchange server, obviously! I've done my testing against an Exchange 2010 server, but should also work against Exchange 2007.
  • If the Exchange server uses HTTPS, then you need to add the server's SSL certificate to an Oracle Wallet. See this page for step-by-step instructions.


The API should be fairly self-explanatory. The only thing you need to do is to call the INIT procedure at least once per session (but remember that in Apex, each page view is a new session, so place the initialization code in a before-header process or a page 0 process).

  ms_ews_util_pkg.init('https://thycompany.com/ews/Exchange.asmx', 'domain\user.name', 'your_password', 'file:c:\path\to\Oracle\wallet\folder\on\db\server', 'wallet_password');

Note also that there are two varieties of most functions: One pipelined function intended for use from plain SQL, and a twin function suffixed with "AS_LIST" that returns a list intended for use with PL/SQL code.

Code Examples

-- resolve names

  l_names ms_ews_util_pkg.t_resolution_list;
  l_names := ms_ews_util_pkg.resolve_names_as_list('john');
  for i in 1 .. l_names.count loop
    debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).mailbox.name, l_names(i).mailbox.email_address);
  end loop;

-- resolve names (via SQL)

select *
from table(ms_ews_util_pkg.resolve_names ('john'))

-- expand distribution list

  l_names ms_ews_util_pkg.t_dl_expansion_list;
  l_names := ms_ews_util_pkg.expand_public_dl_as_list('some_mailing_list@your.company');
  for i in 1 .. l_names.count loop
    debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).name, l_names(i).email_address);
  end loop;

-- get folder

  l_folder ms_ews_util_pkg.t_folder;
  l_folder := ms_ews_util_pkg.get_folder (ms_ews_util_pkg.g_folder_id_inbox);
  debug_pkg.printf('folder id = %1, display name = %2', l_folder.folder_id, l_folder.display_name);
  debug_pkg.printf('total count = %1', l_folder.total_count);
  debug_pkg.printf('child folder count = %1', l_folder.child_folder_count);
  debug_pkg.printf('unread count = %1', l_folder.unread_count);

-- find up to 3 items in specified folder

  l_items ms_ews_util_pkg.t_item_list;
  l_items := ms_ews_util_pkg.find_items_as_list('inbox', p_max_rows => 3);
  for i in 1 .. l_items.count loop
    debug_pkg.printf('item %1, subject = %2', i, l_items(i).subject);
  end loop;

-- get items in predefined folder

select *
from table(ms_ews_util_pkg.find_folders('inbox'))

-- get items in predefined folder, and search subject

select *
from table(ms_ews_util_pkg.find_items('inbox', 'the search term'))

-- get items in user-defined folder

select *
from table(ms_ews_util_pkg.find_items('the_folder_id'))

-- get items in user-defined folder, by name

select *
from table(ms_ews_util_pkg.find_items(
             ms_ews_util_pkg.get_folder_id_by_name('Some Folder Name', 'inbox')

-- get item (email message)

  l_item ms_ews_util_pkg.t_item;
  l_item := ms_ews_util_pkg.get_item ('the_item_id', p_include_mime_content => true);
  debug_pkg.printf('item %1, subject = %2', l_item.item_id, l_item.subject);
  debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
  debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));

-- get item (calendar item)

  l_item ms_ews_util_pkg.t_item;
  l_item := ms_ews_util_pkg.get_item ('the_item_id', p_body_type => 'Text', p_include_mime_content => true);
  debug_pkg.printf('item %1, class = %2, subject = %3', l_item.item_id, l_item.item_class, l_item.subject);
  debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
  debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));
  debug_pkg.printf('start date = %1, location = %2, organizer = %3', l_item.start_date, l_item.location, l_item.organizer_mailbox_name);

-- create calendar item

  l_item ms_ews_util_pkg.t_item;
  l_item.subject := 'Appointment added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.start_date := sysdate + 1;
  l_item.end_date := sysdate + 2;
  l_item.item_id := ms_ews_util_pkg.create_calendar_item (l_item);
  debug_pkg.printf('created item with id = %1', l_item.item_id);

-- create task item

  l_item ms_ews_util_pkg.t_item;
  l_item.subject := 'Task added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.due_date := sysdate + 1;
  l_item.status := ms_ews_util_pkg.g_task_status_in_progress;
  l_item.item_id := ms_ews_util_pkg.create_task_item (l_item);
  debug_pkg.printf('created item with id = %1', l_item.item_id);

-- create message item

  l_item ms_ews_util_pkg.t_item;
  l_item.subject := 'Message added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.item_id := ms_ews_util_pkg.create_message_item (l_item, p_to_recipients => t_str_array('recipient1@some.company', 'recipient2@another.company'));
  debug_pkg.printf('created item with id = %1', l_item.item_id);

-- update item
-- item id and change key can be retrieved with following query:
-- select item_id, change_key, subject, is_read from table(ms_ews_util_pkg.find_items('inbox'))

  l_item_id varchar2(2000) := 'the_item_id';
  l_change_key varchar2(2000) := 'the_change_key';
  ms_ews_util_pkg.update_item_is_read (l_item_id, l_change_key, p_is_read => true);

-- get list of attachments

select *
from table(ms_ews_util_pkg.get_file_attachments('the_item_id'))

-- download and save 1 attachment

  l_attachment ms_ews_util_pkg.t_file_attachment;
  l_attachment := ms_ews_util_pkg.get_file_attachment ('the_attachment_id');
  file_util_pkg.save_blob_to_file('DEVTEST_TEMP_DIR', l_attachment.name, l_attachment.content);

-- create attachment (attach file to existing item/email)

  l_attachment ms_ews_util_pkg.t_file_attachment;
  l_attachment.item_id := 'the_item_id';
  l_attachment.name := 'Attachment added via PL/SQL';
  l_attachment.content := file_util_pkg.get_blob_from_file('DEVTEST_TEMP_DIR', 'some_file_such_as_a_nice_picture.jpg');
  l_attachment.attachment_id := ms_ews_util_pkg.create_file_attachment (l_attachment);
  debug_pkg.printf('created attachment with id = %1', l_attachment.attachment_id);


The MS_EWS_UTIL_PKG package is included in the Alexandria Utility Library for PL/SQL.

Known Issues

The CREATE_ITEM functions don't seem to return the ID of the created item (which do get created in Exchange). I think the issue is with the XML parsing of the returned results; I will look into this later.

Please report any bugs found via the issue list.

Friday, May 25, 2012

A friend in need: Flashback Query

"It is sayd, that at the nede the frende is knowen."
- William Caxton: "Sonnes of Aymon" (1489)
"I say, that when you screw up, Flashback Query is a friend indeed!"
- Morten Braten: "SQLs of Oracle" (2012)

The Flashback Query feature of Oracle (10g) can be really helpful when you run that UPDATE or DELETE statement and then, after the COMMIT, you discover that you made a mistake!

Wouldn't it be nice to be able to look back and see what the data looked like before you messed things up? With a Flashback Query you can do just that:

-- see the data as it was 10 minutes ago
select *
from emp
as of timestamp systimestamp - interval '10' minute
where empno = 7369

Or you can see a history of changes:

-- history of all changes last 20 minutes
select empno, ename, sal, job,
from emp
versions between timestamp systimestamp - interval '20' minute and systimestamp
where empno = 7369
order by versions_endtime nulls last

I've found this useful several times lately, so I'm adding it to the blog as a quick reminder, as I always struggle to remember that funky interval syntax thingy.

Monday, May 21, 2012

jQGrid Integration Kit for PL/SQL and Apex version 1.2 available

It's been over two years since I released the initial version of the jQGrid Integration Kit for PL/SQL and Apex.

Since then, I've added some additional features and fixed a few bugs:

Version 1.1

  • Procedure include_static_files_once will make sure required JS and CSS files are correctly included
  • Support for "popup_lov" column edit type
  • Procedure render_grid supports p_read_only parameter that can be used to set read-only dynamically
  • Support for some jQGrid built-in formatters based on Oracle column data type
  • Lists of values (select list variety, not Popup LOV) now support bind variables and Static LOVs
  • Date parser handles dates and times
  • Bug Fix: JSON generator now handles NULL parameter values (Thanks to Matt Nolan for spotting this one)

Version 1.2

  • Popup LOV now handles Apex bind variables and arbitrary LOV column names
  • Support for jQGrid grouping (experimental)

The latest version can be downloaded here.

New installation: Run the install.sql script. Then run all scripts in the upg folder. Copy the files in the js folder to your web server.

Upgrade from previous version: Run all scripts in the upg folder. Copy the files in the js folder to your web server (overwriting any previous version of the same files; or copy to a different folder and change references accordingly).

Note on jQuery and jQGrid versions: The Integration Kit (via the include_static_files_once procedure) references a specific jQuery version (1.3.2). If your web page includes a more recent version of jQuery, you should modify/remove the reference in the Integration Kit to avoid conflicts.

The version of jQGrid bundled with the Integration Kit is version 3.6. At the time of writing, the latest jQGrid version available is 4.3.2; this has not been tested with the Integration Kit.

Have fun, and please put any bug reports into the project issue list.

Tuesday, May 1, 2012

Select * from Inbox

... 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, a lot of companies use Microsoft Exchange as their mail server. Exchange has (since at least Exchange 2007) a robust set of web services that expose its features:

Exchange Web Services (EWS) provides the functionality to enable client applications to communicate with the Exchange server. EWS provides access to much of the same data that is made available through Microsoft Office Outlook. EWS clients can integrate Outlook data into Line-of-Business (LOB) applications. SOAP provides the messaging framework for messages sent between the client application and the Exchange server. The SOAP messages are sent by HTTP.

This figure from Microsoft shows the steps involved in calling the web service:

So it's basically SOAP (XML) over HTTP. We can easily do that in PL/SQL!

Use Cases

Of course, you can use Exchange Web Services for lots of things besides just reading your email, for example:

  • Integration with other systems via email messages (your PL/SQL program checks email periodically and processes the contents of the email and/or attachments)
  • Searching email archives, combining the search results with search results from the database
  • Checking availability of people for scheduling
  • Adding or updating appointments and tasks
  • Sending emails
  • And lots more

You are welcome to add a comment below if you have more good examples of how the Oracle Database and Microsoft Exchange can work together.


The building blocks we need to work with Exchange Web Services in PL/SQL are:

  • For building and parsing the SOAP messages we can use the built-in XMLTYPE data type.
  • For calling the web service we can use the APEX_WEB_SERVICE package, or alternatively the FLEX_WS_API package (which is more or less the same package as that included in Apex, but the code is not wrapped, so you can study and modify it).
  • Your Microsoft Exchange server is very likely set up with Integrated Windows Authentication, which means we need to authenticate to the web service using NTLM. I wrote a package last year called NTLM_UTIL_PKG which allows us to do just that.
  • Your Microsoft Exchange server is probably also set up with SSL security, in which case you need to set up an Oracle Wallet with the SSL certificate of the Exchange server. There is a good explanation of that here and here. (By the way, there has been some confusion as to whether you can use Oracle Wallets with Oracle XE, as the Wallet Manager is a feature of the Advanced Security Option not included in XE, but this old thread states that "only the latter [call ins (i.e client to database network encryption)] requires ASO, SSL call outs don't". Since we are doing callouts from the database to Exchange that should be okay then.)

The MS_EWS_UTIL_PKG package

Given the above building blocks, I have started implementing a PL/SQL package that offers an easy-to-use API for working with Exchange Web Services (EWS). The name, unsurprisingly, is MS_EWS_UTIL_PKG.

Currently, the quite-not-yet-ready-for-public-consumption package handles the following operations:

  • Get Folder Id By Name
  • Find Folders
  • Find Items
  • Get Item
  • Get File Attachment
  • Move Item
  • Delete Item

Note that an "Item" in Exchange can be an email item, a calendar entry, a task, and so on. Currently, the package handles email items and calendar entries.

So here's me, checking my inbox from SQL*Plus....

Of course, if you wanted an actual inbox in your database, you could just run "create or replace view my_inbox_v as select * from table (ms_ews_util_pkg.find_items(...) )".

Or create an Interactive Report on top of it in Apex. Or create a Region Plugin for Apex that displays a mail folder. Or create an Apex calendar on top of the calendar entries.

The possibilities are many!


Next Steps

When I get this polished enough for an initial public release, I'm going to include it in the Alexandria Utility Library for PL/SQL.

Leave a comment below if you are interested in this package, and if there are any specific parts of the EWS API that you would like to see implemented (and why).

Saturday, April 21, 2012

Interesting PL/SQL and Apex links, April 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 personally would like to see in future PL/SQL include a UTL_FILE.LIST_FILES function that would list the files in a specified directory (without having to resort to unsupported features or workarounds in Java), a DBMS_JSON package for parsing and generating JSON (similar to PL/JSON, but built-in), and the ability to use FUNCTIONs as variables and parameters, like you can in JavaScript.
  • PL/SQL rises strongly in the TIOBE ranking: I don't really care about or trust the TIOBE rankings, as the results tend to vary wildly from month to month and year to year, but it's nice to see PL/SQL climbing from 24th place to 12th place over the last year. (Then again, if you look at the chart, it appears that PL/SQL was popular in 2007/2008, then not popular in 2009, fairly popular in 2010, not popular in 2011, and rising sharply in popularity again in 2012. In other words it's seemingly popular every other year... so take these rankings and the methodology used with a grain of salt.)

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 ?

Tuesday, February 28, 2012

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.