Friday, June 20, 2008

iCalendar event via email

I recently attended a session by Scott Spendolini at the ODTUG conference. In the session he spoke about ApEx "integration" with Outlook among other things. He touched on both the calendar and contacts. It reminded me of a forum post I did a while ago and inspired me to formalize it with this blog post. In Scott's examples, he demonstrated how to make contacts and events in ApEx load directly into Outlook. He even came up with a good way of letting you know if the contacts information has changed since you last loaded it. These were all great new techniques I'd not thought about before.
In the forum post, I wrote about how to send a calendar request via email. This was a technique that I had used in a work flow scenario as the final step. It was necessary because we use Outlook to schedule meetings and didn't want to have to manually add data to Outlook from something that had been scheduled in an ApEx application so that the time would not appear as "free".
At the time, I thought of this as an ApEx solution but the truth is that it is a PL/SQL solution that just works great in ApEx. The solution requires two ingredients: a function (ical_event) to generate an iCalendar event and a process (send_ical_email) to send the request. The APEX_MAIL.SEND procedure will not work in this case as we need more control over the email contents. In the following examples, just the basics are included to get you started but the code can be modified to suite any additional needs. Additional information on the iCalendar (based on vCalendar) standard can be found here. Also, Google has added some of their own additional fields and information on those can be found here. Lastly, an example of the technique can be found here.
Here's what to do...
  1. Compile the following function (replace "Your company name" and "" as needed):
    create or replace
    FUNCTION ical_event (
       p_summary         IN VARCHAR2
     , p_organizer_name  IN VARCHAR2
     , p_organizer_email IN VARCHAR2
     , p_start_date      IN DATE
     , p_end_date        IN DATE
     , p_version         IN VARCHAR2 := NULL
     , p_prodid          IN VARCHAR2 := NULL
     , p_calscale        IN VARCHAR2 := NULL
     , p_method          IN VARCHAR2 := NULL
       l_retval VARCHAR2(32767);
       l_lf     CHAR(1) := CHR(10);
       l_retval := ''
          || 'BEGIN:VCALENDAR' || l_lf
          || 'VERSION:' || NVL(p_version,'2.0') || l_lf
          || 'PRODID:' || NVL(p_prodid,'-//Your company name//NONSGML ICAL_EVENT//EN') || l_lf
          || 'CALSCALE:' || NVL(p_calscale,'GREGORIAN') || l_lf
          || 'METHOD:' || NVL(p_method,'REQUEST') || l_lf
          || 'BEGIN:VEVENT' || l_lf
          || 'SUMMARY:' || p_summary || l_lf
          || 'ORGANIZER;CN="' || p_organizer_name || '":MAILTO:' || p_organizer_email || l_lf
          || 'DTSTART:' || TO_CHAR(p_start_date,'YYYYMMDD') || 'T' || TO_CHAR(p_start_date,'HH24MISS') || l_lf
          || 'DTEND:' || TO_CHAR(p_end_date,'YYYYMMDD') || 'T' || TO_CHAR(p_end_date,'HH24MISS') || l_lf
          || 'DTSTAMP:' || TO_CHAR(SYSDATE,'YYYYMMDD') || 'T' || TO_CHAR(SYSDATE,'HH24MISS') || l_lf
          || 'UID:' || RAWTOHEX(SYS_GUID()) || '' || l_lf
          || 'STATUS:NEEDS-ACTION' ||  l_lf
          || 'END:VEVENT' || l_lf
          || 'END:VCALENDAR';
       RETURN l_retval;
    END ical_event;
  2. Create the following procedure (replace "yoururl" ass needed):
    create or replace
    PROCEDURE send_ical_email (
       p_from      IN VARCHAR2
     , p_to        IN VARCHAR2
     , p_subj      IN VARCHAR2
     , p_body_html IN VARCHAR2
     , p_body_ical IN VARCHAR2
       l_connection UTL_SMTP.CONNECTION;
       l_mail_serv  VARCHAR2(50) := '';
       l_mail_port  PLS_INTEGER := '25';
       l_lf         CHAR(1) := CHR(10);
       l_msg_body   VARCHAR2(32767);
       l_msg_body :=
             'Content-class: urn:content-classes:calendarmessage' || l_lf
          || 'MIME-Version: 1.0' || l_lf
          || 'Content-Type: multipart/alternative;' || l_lf
          || ' boundary="----_=_NextPart"' || l_lf
          || 'Subject: ' || p_subj || l_lf 
          || 'Date: ' || TO_CHAR(SYSDATE,'DAY, DD-MON-RR HH24:MI') || l_lf
          || 'From: <' || p_from || '> ' || l_lf 
          || 'To: ' || p_to || l_lf 
          || '------_=_NextPart' || l_lf
          || 'Content-Type: text/plain;' || l_lf
          || ' charset="iso-8859-1"' || l_lf
          || 'Content-Transfer-Encoding: quoted-printable' || l_lf
          || l_lf
          || 'You must have an HTML enabled client to view this message.' || l_lf
          || l_lf
          || '------_=_NextPart' || l_lf
          || 'Content-Type: text/html;' || l_lf
          || ' charset="iso-8859-1"' || l_lf
          || 'Content-Transfer-Encoding: quoted-printable' || l_lf
          || l_lf
          || p_body_html || l_lf
          || l_lf
          || '------_=_NextPart' || l_lf
          || 'Content-class: urn:content-classes:calendarmessage' || l_lf
          || 'Content-Type: text/calendar;' || l_lf
          || '  method=REQUEST;' || l_lf
          || '  name="meeting.ics"' || l_lf
          || 'Content-Transfer-Encoding: 8bit' || l_lf
          || l_lf
          || p_body_ical || l_lf
          || l_lf
          || '------_=_NextPart--';
       l_connection := utl_smtp.open_connection(l_mail_serv, l_mail_port);
       utl_smtp.helo(l_connection, l_mail_serv);
       utl_smtp.mail(l_connection, p_from);
       utl_smtp.rcpt(l_connection, p_to);, l_msg_body);
    END send_ical_email;
  3. Use ApEx to create a submit button along with the necessary items to collect the following:
    1. Start Date
    2. End Date
    3. Organizer Name
    4. Organizer Email
    5. Attendee Email
    6. Email Subject
    7. Email Body
  4. Create a page process similar to the following that fires when the submit button is pressed (this will vary depending on step 3):
       l_ical_event VARCHAR2(32767);
       l_ical_event := ical_event(
          p_start_date      => TO_DATE(:PXX_START_DATE || :PXX_START_TIME,'DD-MON-YYYYHH:MIPM')
        , p_end_date        => TO_DATE(:PXX_END_DATE || :PXX_END_TIME,'DD-MON-YYYYHH:MIPM')
        , p_summary         => :PXX_SUBJ
        , p_organizer_name  => :PXX_USER_NAME
        , p_organizer_email => :PXX_USER_EMAIL
          p_to        => :PXX_TO_ADDRESS
        , p_from      => :PXX_USER_EMAIL
        , p_subj      => :PXX_SUBJ
        , p_body_html => :PXX_BODY_HTML 
        , p_body_ical => l_ical_event

That should do it. Submit the page to send the request.

Saturday, June 7, 2008

tapiGen is born

I've just released the first version of tapiGen. tapiGen is a PL/SQL table API generator - it creates packages for your tables. Each package contains functions, procedures, and types that can help enforce best practices, reduce code and maintenance, and improve performance.

The project is on SourceForge at:

Feel free to let me know what you think!

Replacing Date Pickers

The default date pickers in ApEx are great. But there were two things I didn't like about them:

  1. They require a server side hit to generate.
  2. You have to click on the number inside the day. The entire day "square" does not work.

I spent some time looking and found a great jQuery plugin that solved both problems. It's called jQuery UI Datepicker and it was created by Mark Grabanski and Keith Wood. If you decide to use their product, although it's not required, you may want to consider making a donation. Information about their product can be found at the following URLs.

To view an example of the traditional ApEx date picker and jQuery UI Datepicker side by side, see the following example.

Here are some simple instructions on how to get it working in ApEx...

  1. Install jQuery in ApEx.
  2. Download ui.datepicker.css and ui.datepicker.js from here (under where it says "Use jQuery UI Datepicker"). Create a new folder called uidatepicker in your i/jquery directory (created in step 1) and copy both files into that location.
  3. Add a text item that you would like to serve as the date picker. If you are converting an old date picker, open the item and change its type to "TextField".
  4. Go to edit the page and enter the following in the HTML Header area:

    <link type="text/css" rel="stylesheet" href="i/jquery/uidatepicker/ui.datepicker.css" />
    <script type="text/javascript" src="i/jquery/uidatepicker/ui.datepicker.js"></script>
    <script type="text/javascript">
             dateFormat: 'dd-M-yy', 
             showOn: 'button',
             buttonImageOnly: true, 
             buttonText: '',
             buttonImage: '/i/asfdcldr.gif', 
             monthNamesShort: ['JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC']

    Note: make sure to replace "PXX_ITEM_NAME" with the correct item name.

That's it! Run the page and check it out.

AJAX Item Help

As most people know, items in ApEx have a "help" built in. When the end user of your application clicks on an item's label, a new window opens displaying that item's help text. I wanted to see if I could achieve this without a new window. After a little digging I found a great jQuery plugin called clueTip which can be set to use another plugin called hoverIntent. Information about the products can be found at the following URLs.

To view an example of an item that has the standard popup help and one that has "Hover Help" side by side, see the following example.

Here are some simple instructions on how to get it working in ApEx...

  1. Install jQuery in ApEx.
  2. Download cluetip from the links above. Create a new folder called cluetip in your i/jquery directory (created in step 1) and unzip the contents of the zip file into that location.
  3. Donload hoverIntent from the links above. Save the contents in a file called jquery.hoverIntent.js and save that file in a new folder named hoverintent in the i/jquery directory.
  4. Enter the following in the HTML Header of the page where you would like to use hover help. If you would like to use hover help on multiple pages, the link and script elements can be placed in the page template instead of the individual page.
    <link href="i/jquery/cluetip/jquery.cluetip.css" type="text/css" rel="stylesheet" />
    <script src="i/jquery/dimensions/jquery.dimensions.js" type="text/javascript"></script>
    <script src="i/jquery/hoverintent/jquery.hoverIntent.js" type="text/javascript"></script>
    <script src="i/jquery/cluetip/jquery.cluetip.js" type="text/javascript"></script>
    <script type="text/javascript">
          var pageID = $('#pFlowId').val();
          $('.hoverHelp').each( function(i) {
                var $item = $(this);
                var get = new htmldb_Get(null,pageID,'APPLICATION_PROCESS=ITEM_HELP',0);
                get.add('TEMP_ITEM', $item.attr('for'));
                $item.attr('rel', get.url());
                $item.attr('relTitle', $item.html());
                return true;
             arrows: true,
             titleAttribute: 'relTitle',
             hoverIntent: {    
                sensitivity: 2,
                interval: 200,
                timeout: 0
  5. Create an application item called TEMP_ITEM
  6. Create an application process named ITEM_HELP that fires ondemand with the following code:
       SELECT *
       INTO l_item_rec
       FROM apex_application_page_items
       WHERE application_id = :APP_ID
       AND item_name = :TEMP_ITEM;
       htp.p ('<div style="padding-right: 4px; padding-left: 4px; padding-bottom: 4px; margin: 4px; padding-top: 4px"><span class="instructiontext">' 
          || l_item_rec.item_help_text 
          || '</span></div>');
  7. Create two new label templates called "Optional Label with Hover Help" and "Required Label with Hover Help". For the Optional Label with Hover Help, set the "Before Label" value to:
    <label class="hoverHelp" style="cursor: help" tabindex="999" for="#CURRENT_ITEM_NAME#" rel="" reltitle="">
    Then set the "After Label" value to:
    For the Required Label with Hover Help, set the "Before Label" value to:
    <label class="hoverHelp" style="cursor: help" tabindex="999" for="#CURRENT_ITEM_NAME#" rel="" reltitle="">
    <img alt="" src="#IMAGE_PREFIX#requiredicon_status2.gif" />
    Then set the "After Label" value to:

    The important attributes in both label elements are rel and relTitle. Both are empty when the page is loaded but are filled with the URL needed for the AJAX call and the label name respectively as soon as jQuery can do so.

  8. Finally create some items and set the templates for the labels to the newly created templates and populate some help data for the items. Then run the page and see the new help!

"Installing" jQuery in Application Express

So you've decided to use jQuery - smart move! Here's how you get it working in Application Express.

  1. Go to and download the latest copy of the library. You'll find that each version is released with a few different compression levels. You can read more about them in their site but I recommend the "Packed" version to get you up in running quickly.
  2. Make the file you downloaded available on your web server. The most common way to do this in ApEx is to drop it in your "/i" directory. I would create a folder under that directory called "jquery" and place it in there. Later this folder can be used to hold the various plugins that you'll want to use.
  3. Now that the file is available for use you have to "include" it in your ApEx application. To make jQuery available on every page go to Shared Components > Templates. Open the page template that your application is using. Look in the Definition/Header textarea and between the <head> and </head> tags, add a line like the following but with the directory and filename specific to your setup: * Make sure to update use the version of jquery you downloaded.
    <script type="text/javascript" src="/i/jquery/jquery-1.2.3.pack.js"></script>
  4. If you application is using multiple page templates and you want to use jQuery in all of them, you'll have to add the line from step 3 to each one. Also, these steps must be repeated for each application. If you or your company has not already done so, you might want to create a custom theme that includes it so that each future application that created will have it by default.
That's it, you're done. Now it's time to have some fun!