Interactive Reports Not Working Properly on New Install

This is just a quick note to self, but may be useful to others experiencing similar problems. I would not recommend the Fix Two solution for production environments before conducting further research though...

Preface
Recently I installed Oracle Apex 3.2 in an existing database. Plain vanilla install on an Oracle 9.2.0.5 database with an Oracle iAS 9.2.0.3 in front. Installation went smooth, demo application works. Shift. Different site, same plain install, but this time on a 10.2.0.4 database, and 10.1.2 application server.

The Demo Effect
What is the first thing you show off when demonstrating Apex? The interactive reports, that is a no-brainer :-) But at both of these installations, something goes wrong. When clicking a report column, I just get the spinning wheel, no other response. What is amiss?

Dissecting the Problem
At the first location, I had no time and no Firebug. At the second location I had both, and two failed installations creating some sort of consistency.

In the Firebug console, I can see a javascript error pops up when a column is clicked. Sometimes it throws string not terminated error, sometimes some other cryptic message, but always the same javascript function. Examining the response in Firebug shows something odd; the response is cut short. Depending on the distinct values of the column I clicked, the response might be cut inside a string (string not terminated error), or in-between. When clicking numeric columns, it works. Hm... Special characters? NLS?

Fix One
Patching the Apex installation to 3.2.1 worked for the installation on the 10g system. IR's started working when the patch was applied.

One down, one to go...

Fix Two (The Dirty Fix)
Examining dads.conf for the 9i installation, I see previously configured dads has a different setting of PlsqlNLSLanguage. Both installs go against databases with NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 (don't ask me why, seems like a popular choice for older systems in Norway).

Changing from PlsqlNLSLanguage from AMERICAN_AMERICA.ALUTF8 to AMERICAN_AMERICA.WE8MSWIN1252 did the trick, IR's are now working as expected. I have not noticed anything else breaking (yet), but I am not at all comfortable with the workaround.

The documentation clearly states:
"The character set portion of the PlsqlNLSLanguage value must be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8."
or for older versions of iAS:
"The character set portion of the nls_lang value must always be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8."

Luckily, the 9i installation will not go to production in it's current state. Phew...

PL/SQL and Gmail (or UTL_SMTP with SSL)

In this post I will describe how to send mail from an Oracle database using UTL_SMTP over SSL using Stunnel. I conducted the test on Windows XP with Oracle database 11gR1, but it should work for nix-operating systems and database versions 9.2 and up. To be quite frank, this is nothing new, but it might be of use anyway.

Preface
I wanted to send emails from my database when some data changes. It was not a corporate solution with access to an internal smtp-host. A simple, accessible, ISP agnostic smtp-server would do. In my case, Gmail fitted the bill, only problem was that Gmail required SSL, which UTL_SMTP does not support. I am up for a challenge (meaning: I am good at complicating (ing, not ed :-)) things), so here goes...

Stunnel
Since UTL_SMTP does not support SSL, I will use a third party tool to "wrap" my connection. There are probably any number of tools which can do this, but Stunnel is quite often referred to, and very easy to install and configure. For nix systems, I suggest checking the Examples-page on stunnel.org, this is a Windows-specific explanation. This part of the post is based on a thread on ez.no.

Installing and configuring Stunnel

• Go to stunnel.org and download the latest Windows binaries
• Install Stunnel (take note of the installation path), in my example it is c:\stunnel
• Edit the file stunnel.conf located in installation folder to (just backup the original, and replace all the original text with the text below):

; Use it for client mode
client = yes

[ssmtp]
accept  = 1925
connect = smtp.gmail.com:465

Here I use port 1925 on my localhost (unused as far as I know) to connect to smtp.gmail.com.

Start Stunnel.exe, and test the configuration:

• Start cmd
• Write: telnet localhost 1925
• You should then see something like "220 mx.google.com ESMTP 5sm18031572eyh.34"
• Write: quit

Troubleshooting: If you cannot reach smtp.gmail.com, there can be any number of things gone wrong.

• Try a normal ping to smtp.gmail.com
• Check to see if stunnel.exe is excepted properly in all firewalls (Windows native and other software firewalls)

Once stunnel is working, and if you are familiar with UTL_SMTP, don't bother reading on. This is the same as UTL_SMTP with any other smtp-host requiring authentication.

Setting up ACL (11g only)
This is more or less monkeyed from Arup Nandas 11g series.

To create an access control list for your application user, and enabling it to connect to localhost on port 1925, do the following:

-- create acl
begin
        dbms_network_acl_admin.create_acl (
                acl             => 'gmail.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;
/
-- add priviliege to acl
begin
  dbms_network_acl_admin.add_privilege ( 
  acl       => 'gmail.xml',
  principal    => '<YOUR SCHEMA USER>',
  is_grant    => TRUE, 
  privilege    => 'connect', 
  start_date    => null, 
  end_date    => null); 
end;
/
-- assign host, port to acl
begin
  dbms_network_acl_admin.assign_acl (
  acl => 'gmail.xml',
  host => 'localhost',
  lower_port => 1925,
  upper_port => 1925);
end;
/

And you are ready to use UTL_SMTP against smtp.gmail.com.

Wrapping UTL_SMTP
I have created a small test-package based on the old UTL_MAIL example from Oracle. Your schema user must have execute privileges on UTL_SMTP and UTL_ENCODE for this to work:

create or replace package apex_mail_p
is
   g_smtp_host      varchar2 (256)     := 'localhost';
   g_smtp_port      pls_integer        := 1925;
   g_smtp_domain    varchar2 (256)     := 'gmail.com';
   g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
   -- send mail using UTL_SMTP
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   );
end;
/
create or replace package body apex_mail_p
is
   -- Write a MIME header
   procedure write_mime_header (
      p_conn in out nocopy utl_smtp.connection
    , p_name in varchar2
    , p_value in varchar2
   )
   is
   begin
      utl_smtp.write_data ( p_conn
                          , p_name || ': ' || p_value || utl_tcp.crlf
      );
   end;
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   )
   is
      l_conn           utl_smtp.connection;
      nls_charset    varchar2(255);
   begin
      -- get characterset
      select value
      into   nls_charset
      from   nls_database_parameters
      where  parameter = 'NLS_CHARACTERSET';
      -- establish connection and autheticate
      l_conn   := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
      utl_smtp.ehlo(l_conn, g_smtp_domain);  
      utl_smtp.command(l_conn, 'auth login');
      utl_smtp.command(l_conn,utl_encode.text_encode('<your gmail account including @gmail.com>', nls_charset, 1));
      utl_smtp.command(l_conn, utl_encode.text_encode('<your gmail account password>', nls_charset, 1));
      -- set from/recipient
      utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
      utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
      -- write mime headers
      utl_smtp.open_data (l_conn);
      write_mime_header (l_conn, 'From', p_sender);
      write_mime_header (l_conn, 'To', p_recipient);
      write_mime_header (l_conn, 'Subject', p_subject);
      write_mime_header (l_conn, 'Content-Type', 'text/plain');
      write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
      utl_smtp.write_data (l_conn, utl_tcp.crlf);
      -- write message body
      utl_smtp.write_data (l_conn, p_message);
      utl_smtp.close_data (l_conn);
      -- end connection
      utl_smtp.quit (l_conn);
   exception
      when others
      then
         begin
           utl_smtp.quit(l_conn);
         exception
           when others then
             null;
         end;
         raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);   
   end;
end;
/

This is NOT production-ready code: First of all, you do not want your credentials in the open, at least obfuscate the package body.

Some notes on the package:

• Parameters sender and recipient must contain e-mail addresses only, use the get_address function in the original Oracle example for more sophisticated use (you can also look at how to add attachments if you have the need).
• I had some trouble encoding my account name and password. My initial thought was to use utl_raw.cast_to_raw and utl_encode.base64_encode, but this did not work, so I ended up using utl_encode.encode_text
• Mime-type is set to "text/plain", set it to "text-html; charset=<something appropriate>" to enhance visual layout

Sending an E-mail
To test it all, try:

begin
   apex_mail_p.mail('<your gmail address>', '<recipient address>', '<Subject>', '<message body>');
end;
/

And you are done!

Well, if you don't get any error messages, that is. If you encounter any exceptions, first of all check your Gmail credentials. Next, check where (in the PL/SQL-code) it fails, and use your favorite search engine to do a combined search on smtp command sent and smtp-error received. Chances are others have worked through the same problems (even if they are not Oracle-related). Last resort is to use telnet and manually type the commands, a bit cumbersome but gives full control.

Happy coding :-)