Validate Email Addresses with this PL/SQL Function

June 1, 2011 Leave a comment

After becoming frustrated by users entering invalid email addresses into the system, I found this  brilliant piece of code using the regexp_like function. I implemented it into our Oracle EBS customer master right away using Forms Personalization. I’m not going to pretend to completely understand the regexp_like SQL function, but here is how I used it.
Create this function stand-alone or part of your favorite stored package. (I put it inside a package that retrieves email addresses in flexible ways.)

-- function will return 1 upon valid email, 0 on invalid
 function valid(p_email in varchar2)
 return number
 is
 cemailregexp constant varchar2(1000) := '^[a-z0-9!#$%&''*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|arpa|biz|com|info|intww|name|net|org|pro|aero|asia|cat|coop|edu|gov|jobs|mil|mobi|museum|pro|tel|travel|post)$';

begin
 if regexp_like(p_email,cemailregexp,'i') then
 return 1;
 else
 return 0;
 end if;

exception
 when others then return 0;
 end;

Then, in the Customer Standard form, I created this personlization at both the address and the customer account level where email address can be entered. If you need, you could put this in the contacts as well as any other EBS form that supports personalizations.

Works like a charm! Now onto using regex to clean up my Item Master descriptions 😉

Attach BI Publisher Reports to Email from Concurrent Manager

May 12, 2011 Leave a comment

Most Oracle users know you can have Oracle EBS notify users via email when their Concurrent Request completes. It will even send you a link to the output. But what if you’re not on your corporate VPN? Or if you don’t see the email for 2 days. Oracle only allows access to the output for one day. There are commercial solutions to this, but the cost may or may not be prohibitive if your needs are fairly simple as mine were.

I’m still using Oracle Applications 11i, so most of my custom reports as well as Oracle standard reports are built using Reports Developer. However, with new reports and major revisions, I always use BI Publisher (formally known as XML Publisher). This has many advantages (that I won’t go into here), one of them being that output can be Excel spreadsheets, PDFs, HTML, among others. What if we wanted these files to be emailed to us when we run the request? Imagine the big boss getting his fancy spreadsheet in his email inbox every day without having to log into the big Oracle beast!

I scoured the Web for a solution to use on a Linux-based install. I found several options, but settled on one that relies on a shell script, the Mutt email client (which may already be installed on your system), and an EBS print “driver” (really, it’s a command-line call to the shell script). Here’s how I did it:

Create a new print “driver” definition in EBS using Application or System Administrator.

You will then need to attach this driver to a new printer type, which is then attached to your “virtual” printer as shown here.


And that’s all the EBS setups that need to be done. All that’s left is creating the shell script to store on the file system under $XX_TOP/bin directory which is referenced in the driver definition.

And this is how I wrote my file. There are several ways to go about this part, so feel free to tweak it to your own needs.

# xx_xml_emailer shell script for Linux</pre>
# https://developingoracle.wordpress.com

# Set up your DB connection here
# Optionally, have OS environment variable(s) to store these values
CONN_STRING="scott/tiger"

# Call to SQL*Plus to gather all information needed
# Request ID is $1 variable passed in from EBS driver
# Additional variables can go here if need be
# Table fnd_conc_req_outputs only contains reports
# which have XML data sources
fields=`sqlplus -s $CONN_STRING <<EOF1
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET HEADING OFF
SET FEEDBACK OFF
select nvl(o.file_name,r.outfile_name)||'~'||
u.email_address||'~'||
p.user_concurrent_program_name||'~'||
o.file_type||'~'||
r.argument_text
from apps.FND_CONC_REQ_OUTPUTS o
,fnd_concurrent_requests r
,fnd_concurrent_programs_tl p
,fnd_user u
where r.request_id = $1
and o.concurrent_request_id = r.request_id
and p.concurrent_program_id = r.concurrent_program_id
and u.email_address is not null
and u.user_id = r.requested_by
and rownum=1;
exit success;
EOF1`

fields="`echo $fields`"

if test "$fields" = "" # No record found in query
then
 echo "no email address or output file found"
else
 # Parse fields from query line and store
 file=`echo $fields | awk -F~ '{printf "%s", $1}'`
 email=`echo $fields | awk -F~ '{printf "%s", $2}'`
 title=`echo $fields | awk -F~ '{printf "%s", $3}'`
 filetype=`echo $fields | awk -F~ '{printf "%s", $4}'`
 params=`echo $fields | awk -F~ '{printf "%s", $5}'`

 # For some reason, I was getting random spaces put into the email address
 # This removes them.
 email=`echo $email | sed -e "s/ //g"`

 # This replaces spaces with underscores for a better filenama
 filename=`echo $title | sed -e "s/ /_/g"`

# This section is to force the filename extensions so
 # that the user's client will open them correctly since
 # they don't always default to these from Oracle.

 # I'm using the /tmp directory for my file staging
 # This is the same directory set in the XML Publisher Admin
 if test "$filetype" = "EXCEL"
 then
 newfile=`echo "/tmp/$filename-$1.xls"`
 elif test "$filetype" = "PDF"
 then
 newfile=`echo "/tmp/$filename-$1.pdf"`
 elif test "$filetype" = "HTML"
 then
 newfile=`echo "/tmp/$filename-$1.html"`
 elif test "$filetype" = "RTF"
 then
 newfile=`echo "/tmp/$filename-$1.rtf"`
 elif test "$filetype" = "FO"
 then
 newfile=`echo "/tmp/$filename-$1.fo"`
 else
 newfile=`echo "/tmp/$filename-$1"`
 fi

cp $file $newfile

# Set your "from" address here (or bring it in from EBS)
 export EMAIL='Your Friendly Oracle Developer <email@foo.com>'

 # Use mutt email client, which was installed on my Linux system already
 # This command line will send a message with the raw parameters in the body for
 # referenece as well as the request ID and filetype in the subject
 # This can all be tailored to your needs.
 echo -e "Attached is the $filetype output of request $1: $title \n\nParameters: $params" | mutt -a $newfile -s "$title (Request $1)" $email

# delete temp file
 rm $newfile
fi

When you run a Concurrent Report and choose your XML output, just choose the new “printer” and set the copies to 1. The way I have it written, the email will go to the user email address set in fnd_user.

Hopefully this has saved you some time and created a low-cost solution!