Swiss-Army Development: Apex Reports to Email

A few months a go I did this post on how to send any APEX page via email. Now that my application is approaching deployment, I'm deep into testing and refining processes. I discovered that my email client of choice, Thunderbird, is a lot more forgiving than many email clients when it comes to HTML and CSS.

Most mail clients will not render HTML with CSS properly. There are a variety of reasons behind this. Webmail clients like Gmail and Yahoo don't want conflicts with their site CSS, and Outlook, especially 2007+ uses Word instead of IE to render HTML. This has actually sparked a bit of a debate, which if you are interested in web standards is something to check up on. Regardless of where you stand on the issue, the fact remains that emails generated via my prior post don't render properly in many email clients. Today we are going to fix that.

There are several utilities out on the web designed to take external CSS and "inline" it, that is use the html "style" tag to convey the same message. Most of these utilities (like this one, or this one) are targeted at crafting a template or interactively producing a marketing piece.

Obviously we want to avoid manual intervention if we are sending reports directly from our application. I will show you how to write a light-weight CGI program in Perl to effectively "inline" your CSS. This allows you to have your design cake and eat it too. Web reports and Email reports from the same source, now with the same look and feel!

Let's get started.

Step 1 - Writing the code

I chose Perl as a language for this project because I am familiar with it and it already has a module designed to do this very thing. Also Oracle's HTTP Server has the ability to run Perl programs out of the box using mod_perl. (In retrospect, Oracle has partial support....more on that later).

Preparing the Perl environment

The first thing we need to do is to install a Perl module called CSS::Inliner. It does just what it says. It takes HTML and "inlines" the CSS composed in <style></style> tags from the header. WAIT a minute. I hear you....no <style></style> tags aren't best practice, externally linked .css files are.

Ok, so we'll also need to use the HTML::LinkExtor package to fetch the external CSS files and convert them to <style> tags that CSS::Inliner can work with. So it's a multi-step process.

To install the above modules I used Perl's CPAN module. After many fights with CPAN and the Perl installed under the OHS home, I decided to use the site Perl that came with my OS. If you are on Windows, I recommend installing Strawberry Perl, on a *NIX system the Perl that came with your OS is probably fine.

We invoke a CPAN shell like this:

> perl -MCPAN -e shell

If you haven't used CPAN before, it will ask you several configuration questions, you can mostly go with the defaults, but do try to pick mirrors geographically close to you. If you have issues http://www.cpan.org/ is a good place to start your troubleshooting process.

After configuration install the two modules as follows (remember to say yes to all dependencies)

CPAN> install CSS::Inliner
CPAN> install HTML::LinkExtor

Writing the worker script

Now that we have our Perl environment configured, it's time to write our worker script. This script will accept an html file and output the "inlined CSS" results. I've put comments throughout so you can follow what I'm doing:

#!/usr/bin/perl
use CSS::Inliner;
use HTML::LinkExtor;
use Encode;

# These variables will allow us to directly load the css files
# We'll set their values in httpd.conf
my $urlSearchPatt = $ENV{'CSS_URL_BASE'};
my $urlReplace = $ENV{'CSS_URL_REPLACE'};

my $styleCSS = "";
my $html ="";

#
# This routine will open each css file and scrape its
# contents into the global $styleCSS variable
#
sub process_link {
my ( $tag, %attr ) = @_;

return unless $tag eq 'link';
return unless defined $attr{ 'href' };

my $file = $attr{'href'};
$file =~ s/$urlSearchPatt/$urlReplace/gis;

$styleCSS = sprintf("%s\n%s\n", $styleCSS,$attr{'href'}, $file, $sfc);

if(open(sfh, "<$file"))
{
$styleCSS = sprintf("%s\n/*CSS from %s*/\n", $styleCSS,$attr{'href'});
foreach my $sfc ( <sfh> )
{
chomp;
next if $sfc =~ /^\s*$/;
$styleCSS = sprintf("%s%s\n", $styleCSS, $sfc);
}
close sfh;
}
return;
}

#
# Main Routine
#

#Read in parameters
my $sourceFile = $ARGV[0];

#
# Read in the source file
#
open (srcFh, "<$sourceFile") or die "Could not open file $sourceFile\n";
foreach my $ln ( <srcFh> )
{
$html = sprintf("%s%s",$html, $ln);
}
close srcFh;

#
#Setup and parse the HTML to grab the css styles
#
my $p = HTML::LinkExtor->new( \&process_link );
$p->parse( $html );

#
# Now we need to inject the <style> tags into the source file, we'll do this just before </head>
#
$html =~ s/<\/head>/<style type="text\/css">$styleCSS<\/style><\/head>/i;

#
#Clean up the html by removing link tags and scripts
#
$html =~ s/<link.*?>//sgi;
$html =~ s/<script.*?>.*?<\/script>//sgi;

#
# Now handle any a:link selectors and ensure our <a> tags are prpoerly classed
# we do this because :link isn't supported by CSS::Inliner
#
$html =~ s/a:link/a.link/sgi;
$html =~ s/<a(.*?)href/<a class="link"\1 href/sgi;
$html =~ s/<a class="link"([^>]*?)class="([^"]*?)"([^>]*?)>/<a \1 class="\2 link"\3>/sgi;

#
# Encode the html as utf8 and run it through the CSS::Inliner
#
$html = encode("utf8", $html);
my $inliner = new CSS::Inliner();
$inliner->read({html => $html});
my $inhtml = $inliner->inlinify();

#
# Debugging information - this will print out any warnings from CSS::Inliner
#
# my $warnings = $inliner->content_warnings();
# my @warnarr = @$warnings;
#
# foreach my $warn ( @warnarr )
# {
# print "$warn\n";
# }
#
print "$inhtml\n";

1;

Be sure to test this code, create an apprporiate "images" directory on your local box (you are testing locally and not in production right?) and setup the environment variables to match your situation. Then run the code, passing in a .html source file.

The Wrapper Script

Now that we have the main worker script done, it's time to write a wrapper script that will be invoked from mod_perl.

Why are we doing it this way you ask?

Because the Perl that ships with OHS is a compiled binary and cannot support our CSS::Inliner package directly. After I spent the better part of a week trying to force OHS Perl to work with CPAN and get my modules installed, I decided to take the back-door approach. It won't scale as well, but for my purposes it will suffice.

The wrapper script is what mod_perl will invoke. It will accept the posted HTML. write it to a temp file and then invoke our worker script (using site Perl) and return the results.

#!/usr/bin/perl -w
use CGI;
use Digest::MD5 qw(md5);

my $html = <STDIN>;

$html =~ s/.*?source=//sgi;
$html =~ s/\+/ /sgi;
$html =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/seg;

# Now we have the html, we need to write it to a file so we can call the file-based inliner
my $fid = sprintf("%s.html", int(rand(100000))+1000);

open(tmpfh, ">/tmp/$fid") or die "Could not open tmp file\n";
print tmpfh "$html";
close tmpfh;

#Pass environment variables along (these are set in httpd.conf)
my $csb = $ENV{'CSS_URL_BASE'}; #The portion of your Apex URL up to /i/
my $csr = $ENV{'CSS_URL_REPLACE'}; #The actual file system path /i/ maps to
my $sitePerl = $ENV{'SITE_PERL_LOC'}; # Fully qualified path to site Perl
my $inlinerLoc = $ENV{'INLINER_LOC'}; # Fully qulaified path to inlineCSS.pl (worker script)

# Set this to the path set displayed under @INC when you run perl -V for your site perl
my $perl5lib = '/usr/local/lib64/perl5:/usr/local/share/perl5:/usr/lib64/perl5/vendor_perl:/usr/share/perl5/vendor_perl:/usr/lib64/perl5:/usr/share/perl5'

my $results = `export PERL5LIB=$perl5lib; export CSS_URL_BASE=$csb; export CSS_URL_REPLACE=$csr; $sitePerl $inlinerLoc /tmp/$fid`;

unlink("/tmp/$fid");

print "Content-type:text/html\r\n\r\n";
print "$results";

Step 2 - Configuring Oracle HTTP Server

Now that we have our code in place, it's time to deploy it to the OHS server and ensure we can call it.

You'll want to pick a safe place to store these Perl scripts outside of the apex installation path. This will ensure the scripts aren't overwritten if APEX or OHS is upgraded. If you already have a cgi-bin directory, a sub-directory or sibling-directory of that would be appropriate.

Mine is /u00/app/oracle/conf/mod_perl_lib

Copy both the worker script (inlinerCSS.pl) and the wrapper script (htmltoemail.pl) into this directory.

Next we need to configure OHS to use mod_perl. Mine was setup that way out of the box. If yours isn't, consult the documentation for instructions on setting up mod_perl.

We need to make modifications to our httpd.conf file. This is the primary Apache configuration file, so do yourself a favor and back it up before proceeding. Once you have a backup, you can add the following section (adjusting for your environment) to the file:

#
# Setup mod_perl to handle perl based css inliner
#

# These are environment variables passed to the htmltoemail.pl script
PerlSetEnv CSS_URL_BASE http.*?.com/+i/
PerlSetEnv CSS_URL_REPLACE /u00/app/oracle/www/apex/images/
PerlSetEnv SITE_PERL_LOC /usr/bin/perl
PerlSetEnv INLINER_LOC /u00/app/oracle/conf/mod_perl_lib/inlineCSS.pl

#setup the /pl/ alias used in the URL
alias /pl/ /u00/app/oracle/conf/mod_perl_lib/

#Setup the /pl/ alias for CGI
<Location /pl/>
SetHandler perl-script
PerlResponseHandler ModPerl::Registry
PerlOptions +ParseHeaders
Options +ExecCGI
Order allow,deny
Allow from all

</Location>

## Only uncoment this for short testings
## Be sure to adjust the AllowFrom to your IP
##
#<Location /perl-status>
# SetHandler perl-script
# PerlHandler Apache::Status
# Order deny,allow
# Deny from all
# Allow from 127.0.0.1
#</Location>

After saving the above changes, you will have to restart the web server. Use the following command:

> $ORACLE_HOME/opmn/bin/opmnctl stopall
> $ORACLE_HOME/opmn/bin/opmnctl startall

You should then test this code by posting a small sample HTML with CSS links using your favorite HTTP tool. I use the Postman Chrome extension. Be sure to use the x-www-form-urlencoded feature.

Hopefully you will get back the HTML without the CSS <link> tags and with the styles embedded into the html tags.

Step 3 - Invoking from PL/SQL

Now that we have the CGI and Perl scripts working, we'll need to craft our PL/SQL to use this new tool. I'm going to assume you have read the previous post on this and have the certificate and ACL setup already. If not, go back to Part 1. I'll wait.

Back? Good.

Now then, we need to create a function that will call our CGI script and post the HTML to it. This is detailed below. Again, I've used comments to explain what I am doing.

Create or replace FUNCTION inline_css(p_service_url in varchar2,
p_source_html in clob) return clob is

v_procedure_c constant varchar2(30) := 'inline_css';
v_location_i integer; -- This variable tracks our location within this procedure.
v_parms_c varchar2(4000); -- This variable records the calling parameters for
-- debugging and error recording operations.

v_app_id_c constant number := 106; /* change this to your application ID */
v_public_page_c constant number := 101; /*Change this to a public apex page in your site*/

-- fixed constants
V_NO_PROXY_FOR_C constant varchar2(4000) := 'localhost';
V_WALLET_C constant varchar2(4000) := 'file:/u00/app/oracle/product/11.2.0/owm/wallets/oracle/';
V_WALLET_PASS_C constant varchar2(4000) := '<your wallet password>';

-- Proxy settings that can be made arguments in the WebBrowser() call
v_proxyServer varchar2(20);
-- not all proxy servers use authentication, but many corporate proxies do, in
-- which case you need to specify your auth details here
-- (make it nulls if not applicable)
v_proxyUser varchar2(50) := '<proxy username goes here>';
v_proxyPass varchar2(50) := '<proxy password goes here>';

-- our local variables
v_proxyURL varchar2(4000);
v_request UTL_HTTP.req;
v_response UTL_HTTP.resp;
v_buffer varchar2(30000);
v_endLoop boolean;
v_tmp_param clob;
v_param clob;
v_param_length NUMBER;

v_cgi_name owa.vc_arr;
v_cgi_val owa.vc_arr;
v_workspace_id number;
v_captured_html clob;
v_out_buf_length number := 4000;
v_output_length number;
v_output_idx number := 1;
v_page_url varchar2(2000);

v_return clob;

begin

-- Set Tracing Information.

v_location_i := 500;

dbms_application_info.set_module(pg_package_c, v_procedure_c);
dbms_application_info.set_client_info('Session="'||v('APP_SESSION')||'"');
dbms_output.put_line(v_procedure_c);

v_location_i := 1000;

--
-- Step 1 - Initialize the request and SSL
--
v_location_i := 5000;
--Now setup the "browser" including session cookie, etc
dbms_output.put_line( 'Setting browser configuration' );
UTL_HTTP.set_response_error_check( TRUE );
UTL_HTTP.set_detailed_excp_support( TRUE );
UTL_HTTP.set_transfer_timeout( 300 );
UTL_HTTP.set_follow_redirect( 3 );
UTL_HTTP.set_persistent_conn_support( TRUE );

v_location_i := 6000;
-- set wallet for HTTPS access
dbms_output.put_line( 'Wallet set to '||V_WALLET_C );
UTL_HTTP.set_wallet( V_WALLET_C, V_WALLET_PASS_C );

-- Configure for proxy access
if v_proxyServer is not NULL then
dbms_output.put_line( 'Proxy Server is '||v_proxyServer );
v_proxyURL := 'http://'||v_proxyServer;

if (v_proxyUser is not NULL and v_proxyPass is not NULL) then
v_proxyURL := REPLACE( v_proxyURL, 'http://', 'http://'||v_proxyUser||':'||v_proxyPass||'@' );
dbms_output.put_line( 'Proxy URL modified to include proxy user name and password' );
end if;

dbms_output.put_line( 'Proxy URL is '|| REPLACE(v_proxyURL,v_proxyPass,'*****') );
UTL_HTTP.set_proxy( v_proxyURL, V_NO_PROXY_FOR_C );
end if;

v_location_i := 7000;

--
-- Step 2 - Send Request and receive response
--
v_tmp_param := p_source_html; --regexp_replace(p_source_html, ' ', '+');
while length(v_tmp_param) > 3000 loop
v_param := v_param||utl_url.escape(substr(v_tmp_param,1,3000), true);
v_tmp_param := substr(v_tmp_param,3001);
end loop;
v_param := 'source='||v_param||utl_url.escape(v_tmp_param, true);
v_param_length := length(v_param);

dbms_output.put_line('Parameter length: '||v_param_length);

v_location_i := 10000;

v_request := UTL_HTTP.begin_request( p_service_url, 'POST', UTL_HTTP.HTTP_VERSION_1_1 );

-- set HTTP header for the GET
UTL_HTTP.set_header( v_request, 'User-Agent', 'Mozilla/4.0' );

UTL_HTTP.SET_HEADER (r => v_request,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');

dbms_output.put_line('starting to write content: '||systimestamp);

if (v_param_length <= 20000) then

dbms_output.put_line('Writing at once');
UTL_HTTP.SET_HEADER ( v_request, 'Content-Length', v_param_length );
UTL_HTTP.WRITE_TEXT (r => v_request,
data => v_param);
else
dbms_output.put_line('Writing in chunks');
UTL_HTTP.SET_HEADER ( v_request, 'Content-Length', v_param_length );

while (v_output_idx <= v_param_length) loop
dbms_output.put_line('Writing location '|| v_output_idx||' - '||to_char(v_output_idx + v_out_buf_length));
utl_http.write_text (r => v_request,
data => substr(v_param, v_output_idx, v_out_buf_length));
v_output_idx := v_output_idx + v_out_buf_length;

end loop;

end if;
v_location_i := 11000;

dbms_output.put_line('Transfer complete, requesting response: '||systimestamp);

-- get response from web server
v_response := UTL_HTTP.get_response( v_request );

dbms_output.put_line ('status code: ' || v_response.status_code);
dbms_output.put_line ('reason phrase: ' || v_response.reason_phrase);
v_location_i := 12000;

--Capture incoming buffer to v_captured_html
v_endLoop := false;
loop
exit when v_endLoop;

begin
v_location_i:=13100;
UTL_HTTP.read_line( v_response, v_buffer, TRUE );
v_location_i:=13200;
--dbms_output.put_line('Buffer line length: '||length(v_buffer));
--dbms_output.put_line(v_buffer);

if (v_buffer is not null and length(v_buffer)>0) then
v_location_i:=13300;
v_captured_html := v_captured_html || v_buffer;
end if;
v_location_i:=13400;
exception
when UTL_HTTP.END_OF_BODY then
dbms_output.put_line('End of body found at location '||v_location_i);
v_endLoop := true;
end;

end loop;
UTL_HTTP.end_response( v_response );

v_location_i := 13500;

v_captured_html := unescape_html(v_captured_html);

dbms_output.put_line('Captured html count: '||length(v_captured_html));
return v_captured_html;

EXCEPTION
when OTHERS then
dbms_output.put_line(v_procedure_c ||' Error found at '||v_location_i||'.'||sqlerrm||' '||systimestamp);
return null;
end inline_css;

The final step to bringing this all together is to call our newly minted inline_css function from our get_apex_page function just before returning the html. You can invoke it like this:

v_captured_html := inline_css(p_service_url => 'https://yoursite.domain.com/pl/htmltoemail.pl',
p_source_html => v_captured_html);

Tips and traps

Even with In-lined style tags, some mail clients (I'm looking at you Outlook) don't support some CSS. Be sure to preview your email in as many clients as you can.

There are guides on what is and isn't supported available on the Interwebs. My favorite is put out by Campaign Monitor.
Outlook 2007+ seems to have issues with table widths, especially those Apex uses for side bar regions. You may want to consider eliminating side bars from your emailed pages, or having a special email only region that moves them inline

The CSS::Inliner is pretty good, but not perfect. I've noticed that the all selector * doesn't inline appropriately, causing my font to be a slightly different size. Your mileage may vary.
Very large pages may present a problem, despite all efforts to avoid this issue. I have personally tested this code with html up to about 700K in size with no issues, but buyer beware.
Be careful to ensure you are setting PERL5LIB correctly for your site-perl. Otherwise you may run into Perl lib version mis-match errors when site-perl is updated

I hope this tutorial has helped you enhance your Apex applications and improved your business. It's important to remember that "cool is not a concept" and we as technicians are there to support the business, be that at a corporation or via our own entrepreneurial pursuits. Cool widgets mean nothing until they are used to support an idea or plan. Happy coding!

Swiss-Army Development

Friday, November 2, 2012

Apex Reports to Email - Part 2: Styling