On-screen tutorials in Apex

Can you just show me just one more time....?

How often as a developer or application administrator do you hear that phrase? I know I hear it most days. It doesn't seem to matter how much effort you put into static documentation or training, this request seems to be impossible to suppress. Maybe you have a large user base and constantly have to show new people the applications. Maybe you have a user base that is forgetful or doesn't pay attention the first time. In any case, I'm sure you have better things to do with your time than to walk users through web-screens.

Let me help.

I recently found myself launching a new application and didn't have time to show each of our users how to perform various tasks with it over and over. Knowing my user base wouldn't grasp all the complex tasks with a single training session, I built an on-screen tutorial system to deliver tutorials and walkthroughs to users via a self-service menu. Follow along with me and you can to!

This post will break down exactly what I did to create the tutorial system and will give you the tools to create your own on-screen tutorials in Oracle's Apex.

Before we get started, let's detail a few requirements:

1. Use Oracle Apex as basis
2. Use javascript and AJAX to prevent unnecessary page refreshes
1. Specific Javascript libraries required
1. jQuery (I use version 1.3.2)
2. json2.js - by Douglas Crockford
3. jApex.js - by Tyler Muth
3. Step-by-Step instructions should be displayed in an unobtrusive way
4. User focus should be directed to each step's objective
5. Admins should be able to add new tutorials without (much) re-coding

Now that we have our goals written down, let's get to work!

Creating the Structure 

The first thing we'll do is create a couple of database tables to store our tutorial definition and steps. This will allow us to add to tutorials at will later:

--Create a sequence to drive primary keys create sequence ui_tutorial_id_seq increment by 1 nocache; -- --Create definition table -- CREATE TABLE UI_TUTORIAL_DEF ( TUTORIAL_ID number not null , TITLE VARCHAR2(256 BYTE) , DESCRIPTION clob , STARTING_PAGE_NUM NUMBER , CREATE_TIME date , CREATED_BY VARCHAR2(512 BYTE) , UPDATE_TIME date , UPDATED_BY VARCHAR2(512 BYTE) , CONSTRAINT UI_TUTORIAL_DEF_PK PRIMARY KEY ( TUTORIAL_ID ) ENABLE ); comment on table UI_TUTORIAL_DEF is 'This table holds UI on-screen tutorial definitions'; comment on column UI_TUTORIAL_DEF.TUTORIAL_ID is 'Sequence driven key to identify tutorial'; comment on column UI_TUTORIAL_DEF.TITLE is 'Title of tutorial'; comment on column UI_TUTORIAL_DEF.STARTING_PAGE_NUM is 'Page number where tutorial begins'; comment on column UI_TUTORIAL_DEF.CREATE_TIME is 'Standard audit Column'; comment on column UI_TUTORIAL_DEF.CREATED_BY is 'Standard audit Column'; comment on column UI_TUTORIAL_DEF.UPDATE_TIME is 'Standard audit Column'; comment on column UI_TUTORIAL_DEF.UPDATED_BY is 'Standard audit Column'; -- -- Create table to hold tutorial steps -- CREATE TABLE UI_TUTORIAL_STEPS ( STEP_ID number not null , TUTORIAL_ID number not null , SEQ NUMBER , STEP_TITLE VARCHAR2(256 BYTE) , PAGE_NUM NUMBER , PAGE_ITEM_ID varchar2(256 BYTE) , PAGE_LOAD_ACTION varchar2(256 BYTE) , TEXT CLOB , CREATE_TIME date , CREATED_BY VARCHAR2(512 BYTE) , UPDATE_TIME date , UPDATED_BY VARCHAR2(512 BYTE) , CONSTRAINT UI_TUTORIAL_STEPS_PK PRIMARY KEY ( STEP_ID ) ENABLE ); comment on table UI_TUTORIAL_STEPS is 'This table holds UI on-screen tutorial steps'; comment on column UI_TUTORIAL_STEPS.STEP_ID is 'PK for step record'; comment on column UI_TUTORIAL_STEPS.TUTORIAL_ID is 'FK to tutorial definition'; comment on column UI_TUTORIAL_STEPS.SEQ is 'Sequence of step within tutorial'; comment on column UI_TUTORIAL_STEPS.STEP_TITLE is 'Title/Heading for tutorial step'; comment on column UI_TUTORIAL_STEPS.PAGE_NUM is 'Page number for step'; comment on column UI_TUTORIAL_STEPS.TEXT is 'Text of step, this is what will inform the user'; comment on column UI_TUTORIAL_STEPS.PAGE_ITEM_ID is 'The DOM ID (apex item name) of the page item to highlight for this step'; comment on column UI_TUTORIAL_STEPS.PAGE_LOAD_ACTION is 'What to do on page reload. Options are: (P)ersist - stay on same sequence number. (I)ncrement - Go to next sequence number.'; comment on column UI_TUTORIAL_STEPS.CREATE_TIME is 'Standard audit Column'; comment on column UI_TUTORIAL_STEPS.CREATED_BY is 'Standard audit Column'; comment on column UI_TUTORIAL_STEPS.UPDATE_TIME is 'Standard audit Column'; comment on column UI_TUTORIAL_STEPS.UPDATED_BY is 'Standard audit Column'; -- -- Add the Foreign Key constraint tying the tables together -- alter table "UI_TUTORIAL_STEPS" add constraint ui_tutorial_steps_fk1 foreign key("TUTORIAL_ID") references "UI_TUTORIAL_DEF"("TUTORIAL_ID");

Now that we have our tables, we want to add some audit triggers to ensure our primary key columns and audit fields are automatically populated:

-- -- Create the definition trigger -- create or replace TRIGGER UI_TUTORIAL_DEF_BIU before insert or update on UI_TUTORIAL_DEF for each row DECLARE v_location_i integer; PU_FAILURE exception; pragma exception_init (PU_FAILURE, -20000); BEGIN v_location_i := 1000; -- Update the audit columns accordingly. :new.tutorial_id := nvl(:new.tutorial_id, nvl(:old.tutorial_id, ui_tutorial_id_seq.nextval)); if (INSERTING) then :new.create_time := sysdate; :new.created_by := user; :new.update_time := NULL; :new.updated_by := NULL; else :new.create_time := :old.create_time; :new.created_by := :old.created_by; :new.update_time := sysdate; :new.updated_by := user; end if; EXCEPTION when OTHERS then dbms_output.put_line('Error at '||v_location_i||': '||sqlerrm); raise; END; / -- -- And the steps trigger -- create or replace TRIGGER UI_TUTORIAL_STEPS_BIU before insert or update on UI_TUTORIAL_STEPS for each row DECLARE v_location_i integer; PU_FAILURE exception; pragma exception_init (PU_FAILURE, -20000); BEGIN v_location_i := 1000; -- Update the audit columns accordingly. :new.step_id := nvl(:new.step_id, nvl(:old.step_id, ui_tutorial_id_seq.nextval)); if (INSERTING) then :new.create_time := sysdate; :new.created_by := user; :new.update_time := NULL; :new.updated_by := NULL; else :new.create_time := :old.create_time; :new.created_by := :old.created_by; :new.update_time := sysdate; :new.updated_by := user; end if; EXCEPTION when OTHERS then dbms_output.put_line('Error at '||v_location_i||': '||sqlerrm); raise; END;

/

A few things you should be aware of when entering data into your tables

• When entering a PAGE_ITEM_ID, you can specify the Apex item name, or you can use a jQuery selector to specify multiple elements.
• You can learn more about jquery at jquery.com
• This column will identify which Apex page items should draw focus for the given tutorial step. The JavaScript functions will dim all other page items and place a bright red box around the focused items. 
• You have two options on page_load_action
• Persist - This will leave the tutorial on the same step it was previously on
• Increment - This will increment the tutorial to the next step

Next we need to create a small utility package with some conversion routines so the Apex engine and JavaScript can pass data back and forth without special characters confusing one or the other. Many thanks to M. Nolan for providing instruction on asciiEscape functionality. Please see his original post here. Or you may simply use my adaptation below:

CREATE OR REPLACE PACKAGE html_utls_pkg AS FUNCTION asciiEscape( p_str VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; END; / --We need to grant execute to the apex_public_user for this to work grant execute on sd_utl_ui_pkg to apex_public_user; CREATE OR REPLACE PACKAGE BODY html_utls_pkg AS -- type for special char table (used in asciiescape) type specialCharReplace is record (pattern varchar(10), changeTo varchar2(10)); type specialChar is table of specialCharReplace index by binary_integer; tSpcChr specialChar; tSpcChr2 specialChar; -- Name: initSpecCharTable -- -- Type: Function -- -- Description: initializes special ascii characters for encoding -- Adapted from M. Nolan @ http://application-express-blog.e-dba.com/?p=1243 -- PROCEDURE initSpecCharTable AS BEGIN tSpcChr(1).pattern := '"'; tSpcChr(2).pattern := chr(8); -- backspace tSpcChr(3).pattern := chr(12); -- form feed tSpcChr(4).pattern := chr(10); -- new line tSpcChr(5).pattern := chr(13); -- carriage return tSpcChr(6).pattern := chr(9); -- tablulation tSpcChr(7).pattern := ''''; -- single quote tSpcChr(8).pattern := '\'; --tSpcChr(2).pattern := '/'; -- tSpcChr(1).changeTo := '"'; tSpcChr(2).changeTo := ''; -- backspace tSpcChr(3).changeTo := ''; -- form feed tSpcChr(4).changeTo := '
'; -- new line tSpcChr(5).changeTo := '
'; -- carriage return tSpcChr(6).changeTo := '	'; -- tablulation tSpcChr(7).changeTo := '''; -- single quote escape tSpcChr(8).changeTo := '\'; --tSpcChr(2).changeTo := '/'; END initSpecCharTable; -- Name: asciiEscape -- -- Type: Function -- -- Description: returns the encoded string according to ASCII encoding -- Adapted from M. Nolan @ http://application-express-blog.e-dba.com/?p=1243 -- -- FUNCTION asciiEscape( p_str VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS my_str varchar2(4000) := p_str; i pls_integer; BEGIN -- -- We need to initialize -- initSpecCharTable; -- -- XLST escape a string -- i := tSpcChr.first; WHILE (i IS NOT NULL) LOOP -- -- formating according to XSLT. -- my_str := replace(my_str, tSpcChr(i).pattern, tSpcChr(i).changeTo); i := tSpcChr.next(i); END LOOP; RETURN my_str; EXCEPTION WHEN OTHERS THEN Raise; END asciiEscape;

END html_utls_pkg; /

After creating your tables and package, you'll probably want to create a report and a master/detail form to populate them. I'll leave the implementation up to you as in my application I had to follow pre-determined design standards and create a more complex page. This step is pure APEX and should be straight forward. Instead of walking you through that, I'll get right to the good stuff.

You will want to create a custom region template that we will use for the tutorial region. This will allow you to position the region and style it as you see fit. I started by copying the "Form" region template. Then I altered it to match the following sections:

Template

<table summary="" id="#REGION_STATIC_ID#" width="100%" cellspacing="0" cellpadding="0" border="0" #REGION_ATTRIBUTES# > <tr> <td align="left" valign="top" width="100%" colspan="2" class="t15instructiontext">#BODY#<button type="button" id="closeTutorial_b" title="Click to exit the tutorial">X</button></td> </tr> </table>

HTML Table Attributes: Used when rendering items within regions to control formatting.

class="tableGrid" cellspacing="0" cellpadding="5" width="100%"

Leave the rest of the fields blank and save the template. Then we can create a region on page 0.

Apex Page 0 allows developers to create content that renders on all apex pages. We'll use this to ensure our region is available wherever we might need it.

Create a new html region called "Tutorial" with the following key attributes:

• Type - Html/text
• Template - Tutorial Region (the one you just created)
• Display Point - Before footer
• Static ID - P0_TUTORIAL_R
• Source -

• • <script type="text/javascript"> $(document).ready(function() { //Initialize Tutorial (if set) tTutorialInit(); }); //end jQuery document.ready function </script>
• This source will ensure the JavaScript we will create to load the tutorial fires on page load.

Now that you have the region created, you need to create the following page items within that region:

• P0_TUTORIAL - This will hold the tutorial ID and the "previous" button
• Display as - Display only
• Label HTML Table Cell Attributes - style="display:none;"
• Element HTML Form Element Attributes - style="display:none;"
• Post Element Text -
• • <div style="display:inline-table; min-width:30px;"><div id="P0_TUT_PREV_B" style="display:inline-table; margin-right:5px;padding: 3px;background: #EEE;" class="fc-button-next ui-state-default ui-corner-left ui-corner-right"><a href=javascript:void(0) onclick="tTutorialNav('PREV')" title="Click for previous step"><span class="ui-icon ui-icon-circle-triangle-w"></span></a></div></div>
  
  
• P0_TUTORIAL_LABEL - This will show the tutorial title and step identifying information
• Display as - Display only
• Begin on New Line/Field - No for both
• Label - <span id="P0_TUTORIAL_LABEL_S">Tutorial</span>
• HTML Form Element Attributes - style="display:none;"
• Post Element Text -
• • <div style="display:inline-table;"><div id="P0_TUT_NEXT_B" style="display:inline-table; margin-right:5px;padding: 3px;background: #EEE;" class="fc-button-next ui-state-default ui-corner-left ui-corner-right"><a href=javascript:void(0) onclick="tTutorialNav('NEXT')" title="Click for next step"><span class="ui-icon ui-icon-circle-triangle-e"></span></a></div></div>
  
  
• P0_TUTORIAL_TEXT - This will show the actual instructions
• Display as - Display only
• Begin on New Line/Field - No for both
• Label - blank
• Label HTML Table Cell Attributes - style="display:none;"
• Pre Element Text - <p class = "tutorialPara">
• Post Element Text -  </p>
• P0_TUTORIAL_SEQ - Stores the step sequence
• Display as - Hidden
• Begin on New Line/Field - No for both
• Value protected - No
• P0_TUTORIAL_ITEM - Stores the page item(s) to highlight
• Display as - Hidden
• Begin on New Line/Field - No for both
• Value protected - No
• P0_TUTORIAL_MIN - Stores first step sequence number
• Display as - Hidden
• Begin on New Line/Field - No for both
• Value protected - No
• P0_TUTORIAL_MAX - Stores the total number of sequence steps
• Display as - Hidden
• Begin on New Line/Field - No for both
• Value protected - No
• P0_TUTORIAL_NEXTPAGE - Stores the page number for the next step
• Display as - Hidden
• Begin on New Line/Field - No for both
• Value protected - No
• P0_APP_PAGE_ID - Stores the page number for the current page for use with JavaScript
• Display as - Hidden
• Value Protected - No
• Begin on New Line/Field - No for both
• Source Used - Always
• Source Type - Item
• Source Value - APP_PAGE_ID

Next you need to create one more region to hold the background element. This is the element that will dim the non-focused areas of the screen during the tutorial:

• Type - Html/text
• Template - No Template
• Display Point - Before footer
• Source -

• • <div id="backgroundPopup" class="backgroundPopup"style="display:none;"></div>
• This source will create the background dimming feature.

Now that you have the regions created, you need to style them. Specifically you want to ensure it isn't always hanging around on the screen and cluttering up your interface. I use the following CSS rule to style my tutorial screen. Feel free to play with colors and styles to suit your look and feel, but be sure to keep the display property set to "none" so it is hidden until we need it. I've also included the other tutorial region/item CSS here. To use this you can either host the CSS on a web server, or embed it in <style> tags in your page templates <head> section.

#P0_TUTORIAL_R { display:none; position: fixed; height:50px; /*max-height:150px;*/ vertical-align: top; bottom: 0px; left: 0; width: 100%; padding: 1px; /*border-top: 2px solid #336699;*/ border-bottom: 1px solid #AAA; background: #CDC; background-repeat: repeat-x; -moz-box-shadow: 2px 0 10px #888; -webkit-box-shadow: 2px 0 10px #888; box-shadow: 2px 0 10px #888; z-index:9900; /*----Transparency----*/ filter: alpha(opacity=95);/* This works in IE 5-9 */ -moz-opacity:0.95;/* Older than Firefox 0.9 */ -khtml-opacity: 0.95;/* Safari 1.x (pre WebKit!) */ opacity: 0.95;/* Firefox 0.9+, Safari 2?, Chrome, Opera 9+, IE9+ */ }

#P0_TUTORIAL_LABEL_S { display: inline-table; vertical-align: top; padding: 5px; font-weight: bold; font-size: 16px; color: darkGreen; } #P0_TUTORIAL_TEXT { margin-left: 40px; } .tutorialPara { min-height:50px; max-height:100px; overflow-y:auto; border-top: 1px solid darkGreen; margin: 0; padding-top: 10px; } .tutorialFocus , .tutorialFocus>td { border: 4px solid #FF0000; z-index:5500; position:relative; background-color:#FFFFFF; } #closeTutorial_b { position: absolute; top: 5px; right: 5px; color: red; }

Putting it in Motion

We will use a combination of Javascript, AJAX and Apex application processes to bring this tutorial system to life. First, let's create the application processes that will load the tutorial content into our region items. There will be a total of three processes, one to load the tutorial items on page load, and two to control tutorial navigation and display.

First, let's build the load tutorial process:

• Process Point - On Load: Before Header
• Name - L0_LOAD_TUTORIAL
• Type - PL/SQL Anonymous Block
• Process Text
  declare -- Name: L0_LOAD_TUTORIAL -- -- Description: Loads tutorial information based on tutorial and step v_location_i integer; -- This variable tracks our location within this procedure. v_cur_step number; cursor min_max_cur (cp_tutorial number) is select min(seq) mini, max(seq) maxi from ui_tutorial_steps where tutorial_id = cp_tutorial; v_min_max_rec min_max_cur%rowtype; cursor tutorial_step_cur (cp_tutorial number, cp_step number) is select title, seq, step_title, page_num, next_page_num, page_item_id, page_load_action, text from (select b.title, a.seq, a.step_title, a.page_num, a.page_item_id, LEAD(a.page_num,1) over (order by a.seq) next_page_num, nvl(a.page_load_action, 'P') page_load_action, a.text from ui_tutorial_steps a, ui_tutorial_def b where a.tutorial_id = b.tutorial_id and a.tutorial_id = cp_tutorial) where seq = (select min(seq) from ui_tutorial_steps where tutorial_id = cp_tutorial and seq >= cp_step); v_step_rec tutorial_step_cur%rowtype; begin -- Set Tracing Information. v_location_i := 500; --Starting a tutorial: Tutorial ID set, step is 0 if (:P0_TUTORIAL_SEQ = 0) then v_cur_step := 1; else v_cur_step := :P0_TUTORIAL_SEQ; end if; v_location_i := 2000; --Fetch data about current step for rec in tutorial_step_cur(:P0_TUTORIAL, v_cur_step) loop v_step_rec := rec; end loop; --Fetch min/max step data for rec in min_max_cur(:P0_TUTORIAL) loop v_min_max_rec := rec; end loop; v_location_i := 3000; if (:P0_TUTORIAL_SEQ = 0 and :APP_PAGE_ID != v_step_rec.page_num) then --Redirect to the starting page owa_util.redirect_url('f?p=' || :APP_ID || ':'||v_step_rec.page_num||':' || :APP_SESSION ); elsif (v_step_rec.seq = v_min_max_rec.maxi AND :APP_PAGE_ID != v_step_rec.page_num) then --Tutorial has ended, clear it out :P0_TUTORIAL := ''; :P0_TUTORIAL_TEXT := ''; :P0_TUTORIAL_SEQ := ''; :P0_TUTORIAL_ITEM := ''; :P0_TUTORIAL_MIN := ''; :P0_TUTORIAL_MAX := ''; :P0_TUTORIAL_NEXTPAGE := ''; else --Use the following to determine if we need to fetch the next step: -- 1. Is the current application page = to the next_page_num and != page_num for the record? -- 2. Is the page load action (I)ncrement? (and we aren't starting new or getting back on track) if ((:APP_PAGE_ID = v_step_rec.next_page_num and :APP_PAGE_ID != v_step_rec.page_num) or (v_step_rec.page_load_action = 'I' and :P0_TUTORIAL_SEQ != 0 and nvl(:REQUEST, 'X') != 'TUTORIAL_PERSIST') and :APP_PAGE_ID not in (107,108,109,110,111)) then --Need to fetch the next step v_cur_step := v_cur_step +1; for rec in tutorial_step_cur(:P0_TUTORIAL, v_cur_step) loop v_step_rec := rec; end loop; end if; v_location_i := 4000; --Populate the page items :P0_TUTORIAL_LABEL := v_step_rec.title||' - Step '||v_step_rec.seq||' of '||v_min_max_rec.maxi; --Check to ensure user is on the right page for the step if (:APP_PAGE_ID != v_step_rec.page_num) then :P0_TUTORIAL_TEXT := '<span class="entHeader urgentTxt">'|| 'Oops, it looks like you''ve lost your way. </span>'|| '<br><span style="padding-top: 10px; display: inline-block;">'|| '<b>To return to the tutorial please click '|| '<a href="f?p=&APP_ID.:'||v_step_rec.page_num||':&APP_SESSION.:TUTORIAL_PERSIST">here</a>.</b></span>'; else :P0_TUTORIAL_TEXT := v_step_rec.text; end if; :P0_TUTORIAL_SEQ := v_step_rec.seq; :P0_TUTORIAL_ITEM := v_step_rec.page_item_id; :P0_TUTORIAL_MIN := v_min_max_rec.mini; :P0_TUTORIAL_MAX := v_min_max_rec.maxi; :P0_TUTORIAL_NEXTPAGE := v_step_rec.next_page_num; end if; --redirect on tutorial start EXCEPTION when OTHERS then
  raise; end;
• Process Error Message - Error loading tutorial
• Conditions - PL/SQL Expression:
• :P0_TUTORIAL is not null

This process will fire on page load. It gathers the current tutorial step and populates the tutorial items on APEX page 0. This will happen for every page load when a tutorial is active. The PAGE_LOAD_ACTION column in UI_TUTORIAL_STEPS and the TUTORIAL_PERSIST request value will ensure we do not step forward unnecessarily. This process prepares the tutorial step and JavaScript will handle display and placement. We'll get to that in a moment, but first we need to build a couple more processes.


Next we'll build the process to navigate between tutorial steps:

Process Point - On Demand: Run this application process when requested by a page process

Name - A0_TUTORIAL_NAV

Type - PL/SQL Anonymous Block

Process Text

DECLARE -- Name: A0_TUTORIAL_NAV -- -- Description: Fetches a JSON object representing the next or previous step in a tutoral v_location_i integer; -- This variable tracks our location within this procedure. -- debugging and error recording operations. v_output varchar2(31500); --wwv_flow.g_x01 = Tutorial ID --wwv_flow.g_x02 = Current tutorial step --wwv_flow.g_x03 = Direction of navigation (NEXT/PREV) --wwv_flow.g_x04 = Random value to prevent browser caching, unused v_new_step number; cursor min_max_cur (cp_tutorial number) is select min(seq) mini, max(seq) maxi from ui_tutorial_steps where tutorial_id = cp_tutorial; v_min_max_rec min_max_cur%rowtype; cursor tutorial_step_cur (cp_tutorial number, cp_step number) is select title, seq, step_title, page_num, nvl(next_page_num, page_num) next_page_num, page_item_id, page_load_action, text from (select b.title, a.seq, a.step_title, a.page_num, a.page_item_id, LEAD(a.page_num,1) over (order by a.seq) next_page_num, nvl(a.page_load_action, 'P') page_load_action, a.text from ui_tutorial_steps a, ui_tutorial_def b where a.tutorial_id = b.tutorial_id and a.tutorial_id = cp_tutorial) where seq = (select min(seq) from ui_tutorial_steps where tutorial_id = cp_tutorial and seq >= cp_step); v_step_rec tutorial_step_cur%rowtype; begin --htp.p('AX01 = '||nvl(wwv_flow.g_x01, -1982)); v_location_i := 1000; if (wwv_flow.g_x03 = 'PREV') then v_new_step := wwv_flow.g_x02 - 1; elsif (wwv_flow.g_x03 = 'NEXT') then v_new_step := wwv_flow.g_x02 + 1; else v_new_step := wwv_flow.g_x02; end if; --Fetch data about new step for rec in tutorial_step_cur(wwv_flow.g_x01, v_new_step) loop v_step_rec := rec; end loop; v_location_i := 1800; --Fetch min/max steps for tutorial for rec in min_max_cur(wwv_flow.g_x01) loop v_min_max_rec := rec; end loop; v_location_i := 2000; --Output the JSON object htp.prn('{'); if (v_step_rec.page_num != :APP_PAGE_ID) then htp.prn('"url":"f?p='||:APP_ID||':'||v_step_rec.page_num||':'||:APP_SESSION|| ':TUTORIAL_PERSIST:::P0_TUTORIAL,P0_TUTORIAL_SEQ:'|| wwv_flow.g_x01||','||v_step_rec.seq||'"'); else htp.prn('"url":"",'); htp.prn('"step":'||v_step_rec.seq||','); htp.prn('"item":"'|| html_utls_pkg.asciiEscape(v_step_rec.page_item_id)||'",'); htp.prn('"text":"'|| html_utls_pkg.asciiEscape(v_step_rec.text)||'",'); htp.prn('"min":'||v_min_max_rec.mini||','); htp.prn('"max":'||v_min_max_rec.maxi||','); htp.prn('"nextpage":'||v_step_rec.next_page_num||','); htp.prn('"title":"'||v_step_rec.title||'",'); htp.prn('"step_title":"'||v_step_rec.step_title||'"'); end if; htp.prn('}'); v_location_i := 6000; EXCEPTION when OTHERS then raise; END;

This process will accept commands from the user interface (via JavaScript and AJAX) to fetch the next or previous step in the tutorial from the tables. It is an on-demand process, meaning it only fires when requested specifically. It behaves much like the page load process, but is called between page refreshes.



The final process we'll create is the one that will exit the tutorial and reset the page items:

Process Point - On Demand: Run this application process when requested by a page process

Name - A0_TUTORIAL_EXIT

Type - PL/SQL Anonymous Block

Process Text

DECLARE -- Name: A0_TUTORIAL_EXIT -- -- Description: Fetches a JSON object representing the next or previous step in a tutoral v_location_i integer; -- This variable tracks our location within this procedure. v_output varchar2(31500); --wwv_flow.g_x01 = Random value to prevent browser caching, unused /* --Testing wwv_flow.g_x01 number := 1; wwv_flow.g_x02 number := 1; wwv_flow.g_x03 varchar2(128) := 'NEXT'; */ begin --htp.p('AX01 = '||nvl(wwv_flow.g_x01, -1982)); v_location_i := 1000; APEX_UTIL.set_session_state(p_name => 'P0_TUTORIAL', p_value => null); htp.prn('SUCCESS'); v_location_i := 6000; EXCEPTION when OTHERS then raise; END;

This on-demand process simply sets the P0_TUTORIAL item to null so the tutorial region won't load on the next page refresh.



Now that we have the apex application processes built, we need to create some javascript routines to bring everything together. The javascript will process page load and/or user button presses and call the processes we just wrote to navigate through the tutorial. All this JavaScript has been tested in the top 3 browsers (IE 8+, Firefox, Chrome) and should work on any WC3 compliant browser (Safari, Opera, etc). Again you may pull all of the javascript into a file and host it on your web server. Then you simply need to link to it in your page template header. You may also embed the javascript directly in your header in <script> tags or in your tutorial region in <script> tags.


Loading the tutorial

My application displays a list of tutorials in a javascript popup window. As such I use the following code to redirect the parent window to the tutorial starting point and close the popup

/******************************************************************************** Function: tTutorialLoad Description: This function will load a tutorial selected from the help menu Parameters: pTutorialID - ID of the tutorial to load pPageNum - APEX page number to load (starting page for tutorial) */ function tTutorialLoad(pTutorialId, pPageNum) { var href = "f?p="+pAppId+":"+pPageNum+":"+pSession+"::::P0_TUTORIAL,P0_TUTORIAL_SEQ:"+pTutorialId+",0"; window.opener.location=href; window.opener.focus(); //window.close(); } //tTutorialLoad

If you didn't want to use the popup idea to display your list of tutorials, you could simply add a column to the report page you created earlier that redirects to the page and passes the request as follows (Don't forget to mark the report column type as "Standard Report Column":

select <other columns here>,
'<a href="f?p=&APP_ID.:'||STARTING_PAGE_NUM||':&APP_SESSION.::::P0_TUTORIAL,P0_TUTORIAL_SEQ:'||tutorial_id||',0">link</a>' link
from ui_tutorial_def;

Initializing the tutorial panel on-screen

The Apex on load process we created above will handle loading up the tutorial panel, but we'll use javascript to position it on screen and prepare the navigation and item highlighting:

/******************************************************************************** Function: tTutorialInit Description: This function will initialize the on-screen tutorial. Parameters: None */ function tTutorialInit() { var tutorialID = $("#P0_TUTORIAL").text(); var $tutorialR = $("#P0_TUTORIAL_R"); var $focusItem, $tutorialSeq, itemTop; var curPage = $("#P0_APP_PAGE_ID").val(); if (tutorialID && tutorialID != '' && $tutorialR) { var $tutLabel = $("#P0_TUTORIAL_LABEL").text(); //Manipulate the Tutorial Lable/title $("#P0_TUTORIAL_LABEL_S").html('Tutorial: '+ $tutLabel); //add the click event to the tutorial button $("#closeTutorial_b").click(function(event) { tTutorialExit('N'); }); $focusItem = tHTMLdecode($("#P0_TUTORIAL_ITEM").val());
$tutorialSeq = $("#P0_TUTORIAL_SEQ").val();

//add focus to item if ($focusItem && $focusItem != '' && $focusItem != '#') { if ($focusItem.indexOf("$") != 0) { $focusItem = $("#"+$focusItem); if ($focusItem.get(0).tagName != 'TABLE' && $focusItem.get(0).tagName != 'DIV') { $focusItem = $focusItem.closest('td'); } } else { $focusItem = eval($focusItem); } $focusItem.addClass("tutorialFocus"); itemTop = $focusItem.position(); if (itemTop && itemTop != '' && itemTop.top) { $("body").scrollTop(itemTop.top-100); } tToggleBackground("tTutorialExit('Y')", 100); } //Hide/Show prev button if ($("#P0_TUTORIAL_MIN").val() >= $tutorialSeq || $("#P0_TUTORIAL_TEXT").html().indexOf("urgentTxt") >= 0) { $("#P0_TUT_PREV_B").hide(); } else { $("#P0_TUT_PREV_B").show(); }

//Hide/show next button if ($("#P0_TUTORIAL_MAX").val() <= $tutorialSeq || $("#P0_TUTORIAL_NEXTPAGE").val() != $("#P0_APP_PAGE_ID").val()) { $("#P0_TUT_NEXT_B").hide(); } else { $("#P0_TUT_NEXT_B").show(); } //adjust location for htmldb toolbar var $builder = $("#htmldbDevToolbar"); if ($builder.html() && $builder.html() != '' && $builder.html().indexOf("table") >= 0) { $tutorialR.css("bottom", "30px"); } else { $tutorialR.css("bottom", "0"); } $tutorialR.show(); } $focusItem = $tutorialSeq = tutorialID = $tutorialR = null; } //tTutorialInit

Tutorial navigation

This function will call the on-demand process to provide navigation through the tutorial. It will make an ajax call to the Apex environment and then adjust the on-screen tutorial items with the JSON response:

/******************************************************************************** Function: tTutorialNav Description: This function will hide and reset the on-screen tutorial. Parameters: pDirection - (Next/Prev) indicates the direction of navigation for the tutorial */ function tTutorialNav(pDirection) { var tutorialID = $("#P0_TUTORIAL").text(); var step = $("#P0_TUTORIAL_SEQ").val(); var stepItem = tHTMLdecode($("#P0_TUTORIAL_ITEM").val()); //Remove the tutorialFocus class from the existing item if (stepItem && stepItem != '' && stepItem != '#') { if (stepItem.indexOf("$") != 0) { stepItem = $("#"+stepItem); if (stepItem.get(0).tagName != 'TABLE' && stepItem.get(0).tagName != 'DIV') { stepItem = stepItem.closest('td'); } } else { stepItem = eval(stepItem); } stepItem.removeClass('tutorialFocus'); }

if (pDirection == 'CHANGE') { step = 1; } //Fetch the next step information //On return set the page items and add the tutorialFocus class //Manipulate the Next button if necessary var nDate = new Date(); var jDate = "ts"+nDate.getTime()+"e"; var options = { appProcess: 'A0_TUTORIAL_NAV', x01: tutorialID, x02: step, x03: pDirection, x04: jDate, success: function(data){ if (data.indexOf("Error") >= 0) { //Display friendly error in global notification area alert('Error fetching '+pDirection+' tutorial step, please contact a system administrator for assist ance.'); } else { var jsonData = tHTMLdecode(data); jsonData = JSON.parse(data);

if (jsonData.url != '') { //Redirect to the new URL location.href=jsonData.url; } else { //Load the new tutorial step $("#P0_TUTORIAL_SEQ").val(jsonData.step); $("#P0_TUTORIAL_ITEM").val(jsonData.item); $("#P0_TUTORIAL_TEXT").html(jsonData.text); $("#P0_TUTORIAL_NEXTPAGE").val(jsonData.nextpage); $("#P0_TUTORIAL_MIN").val(jsonData.min); $("#P0_TUTORIAL_MAX").val(jsonData.max); //if (jsonData.step_title != '') //{ // $("#P0_TUTORIAL_LABEL").text(jsonData.title + ' - Step '+jsonData.step+' of '+jsonData.max+' - '+jsonData.step_title); //} //else //{ $("#P0_TUTORIAL_LABEL").text(jsonData.title + ' - Step '+jsonData.step+' of '+jsonData.max); //} tTutorialInit(); } jsonData = null; } } }; $.jApex.ajax(options); options = jDate = nDate = tutorialID = step = stepItem = null; } //tTutorialNav

Exiting the tutorial

This function will call the Apex process to reset the tutorial trigger and will take care of hiding the tutorial panel and artifacts:

/******************************************************************************** Function: tTutorialExit Description: This function will hide and reset the on-screen tutorial. Parameters: pAsk - Y/N flag indicating whether or not the system should ask about exiting */ function tTutorialExit(pAsk) { var tItem = tHTMLdecode($("#P0_TUTORIAL_ITEM").val()); //alert(tItem); var proceed = true; if (pAsk && pAsk == 'Y') { proceed = confirm("This action is not allowed during the tutoral.\n\nWould you like to exit the tutorial?"); } if (proceed) { var nDate = new Date(); var jDate = "ts"+nDate.getTime()+"e"; var options = { appProcess: 'A0_TUTORIAL_EXIT', x01: jDate, success: function(data){ if (data.indexOf("Error") >= 0) { //Display friendly error in global notification area alert('Error exiting tutorial, please contact a system administrator for assistance.'); }

else { if(tItem && tItem != '' && tItem != '#') { if (tItem.indexOf("$") != 0) { tItem = $("#"+tItem); if (tItem.get(0).tagName != 'TABLE' && tItem.get(0).tagName != 'DIV') { tItem = tItem.closest('td'); } } else { tItem = eval(tItem); } tItem.removeClass('tutorialFocus'); } $("#P0_TUTORIAL").text(''); $("#P0_TUTORIAL_ITEM").val(''); $("#P0_TUTORIAL_SEQ").val(''); $("#P0_TUTORIAL_MIN").val(''); $("#P0_TUTORIAL_MAX").val(''); tToggleBackground('HIDE', 0); $("#P0_TUTORIAL_R").hide(); } } }; $.jApex.ajax(options); }//end if proceed return false; }//tTutorialExit

The following block contains all the JavaScript helper functions I use in the main tutorial functions defined above. Interested parties can pick them apart, but this tutorial is already long enough, so I won't bore you with the details. Just put this in your .js file and you'll be good to go:

/********* Function: tToggleBackground Description: This function will toggle the background shading used to throw focus to modal windows and current tutorial steps Parameters: pClickAction - The function or action to take when the background is clicked pSpeed - The fade in speed */ function tToggleBackground(pClickAction, pSpeed) { var $bgPopup = $("#backgroundPopup"); var windowWidth = $(window).width(); var windowHeight = $(window).height(); if (pClickAction && pClickAction == "HIDE") { $bgPopup.fadeOut(pSpeed); } else { //unbind click from background to avoid multiple events in IE $bgPopup.unbind('click'); //Set height and opacity $bgPopup.css({ //"position": "fixed", "height": windowHeight, "width" : windowWidth, "opacity": "0.6" //must set this dynamically for IE });

//bind click to handle toggle out $bgPopup.click(function(){ if (pClickAction && pClickAction != '') { eval(pClickAction); } else { $bgPopup.fadeOut(pSpeed); } }); $bgPopup.fadeIn(pSpeed); }//End hide/show switch $bgPopup = windowWidth = windowHeight = null; } //tToggleBackground

/******************************************************************************** Function: tHTMLdecode Description: This function unencodes encoded special characters. This is specifically designed to get around a bug in flash links (p690) Parameters: pStr - The numeric/date string that has been incoded #A-I returns: the unencoded numeric string ***********************************************/ function tHTMLdecode(pStr) { var vReturn = pStr; vReturn = vReturn.replace(/&amp;/g, '&'); //alert(vReturn); vReturn= vReturn.replace(/&#34;/g, '"'); //alert(vReturn); vReturn= vReturn.replace(/&#quot;/g, '"'); //alert(vReturn); vReturn = vReturn.replace(/&#39;/g, "'"); //alert(vReturn); return vReturn; }

Congratulations

If you have followed me this far you deserve a commendation for stamina! You should also have a functioning on-screen tutorial like this one. For those of you that didn't follow along, you can save yourself some work by downloading the scripts and APEX application here. It goes without saying that everything in that zip worked for me, but your mileage may vary.

Thanks for sticking with me through this epic post!

Oracle Text and Friendly Search Expressions

Oracle Text is a feature of the Oracle database that allows developers to index large portions of data and provide users a search-engine like view into that data. It comes with a powerful set of functionality that allows the experienced or knowledgeable user to craft very specific queries. But what about everybody else? Most users want the search functionality to be basic and/or follow what they have become accustom to with the various online-search engines.

Today I will show you how to provide a sub-set of Oracle text features (specifically using the context index and contains clause) that mimic the major features of most online search tools. This will allow you to use Oracle Text in your application without forcing users into a new set of search expressions.

Before we get started with the search expressions, we need to build and populate a test table. This test table uses a clob, but you could also index a blob column or search against a multi-column index like the one I detail here.

create table simple_search_ex (  ident number,
content clob, );

insert into simple_search_ex (ident, content) values (1, 'This is the first row, it is red');
insert into simple_search_ex (ident, content) values (2, 'This is the second row, it is blue');
insert into simple_search_ex (ident, content) values (3, 'This is the third row, it is red');
insert into simple_search_ex (ident, content) values (4, 'This is the fourth row, it is green');

Now that we have the table built, we need to add the Oracle text index. I'm going to use the sync on commit parameter so that updating the column will update the index

create index simple_search_ex_idx on simple_search_ex(content)
indextype is ctxsys.context PARAMETERS ('SYNC ( ON COMMIT )');

Once we have the test data setup, we can get down to business. Oracle Text has a ton of complex search options and operators that you can exploit (see the documentation for the full list). To keep it simple for my users, I'm going to stick with the basics. My search will allow the user of  "AND", "OR", "NOT",  "()" and expressions in double quotes  or surrounded by braces "{}" will be evaluated as a single word. Finally spaces will translate to "AND" in my algorithm.

Limiting myself to the simple options means we can transform the user's input into the appropriate expression for the CONTAINS clause with a few regexp_replace calls. We simply want to transform the user's English search expression into one that Oracle Text will allow for. I've commented the code below, the only really tricky part is ensuring that we do not replace characters inside of quotes. That requires a few extra replacements to work properly.

Before we can create the function, we must create a pl/sql table to hold the tokenized input:

create or replace type vc_lst as table of varchar2(4000);

Now we can create the function:

create or replace function translate_search_expression(p_input varchar2) return varchar2 is v_procedure_c constant varchar2(30) := 'translate_search_expression'; v_location_i integer; -- This variable tracks our location within this procedure. v_input varchar2(31000); v_word_lst vc_lst := vc_lst(); v_return varchar2(31000); l_n number; begin -- Set Tracing Information. v_location_i := 500; dbms_application_info.set_client_info('Session="'||v('APP_SESSION')||'"'); dbms_output.put_line(v_procedure_c); v_location_i := 1000; v_return := p_input; v_location_i := 1500; --Replace "some text" with {some text} v_return := regexp_replace(v_return, '"(.*?)"', '{\1}',1,0,'i'); v_return := regexp_replace(v_return, '"', '', 1,0,'i'); v_location_i := 1600; --Replace ( and ) with ' ( ' and ' ) ' if not wrapped in spaces v_return := regexp_replace(v_return, '([^\s])\(([^\s])', '\1 ( \2',1,0); v_return := regexp_replace(v_return, '([^\s])\)([^\s])', '\1 ) \2',1,0); v_location_i := 2000; --Split the search expression on spaces loop l_n := instr( v_return, ' ' ); exit when (nvl(l_n,0) = 0); v_word_lst.extend; v_word_lst( v_word_lst.count ) := ltrim(rtrim(substr(v_return,1,l_n-1))); v_return := substr( v_return, l_n+1 ); end loop; --Replace non-supported oracle text whitespace or operators if (v_word_lst.count > 0) then for i in v_word_lst.first..v_word_lst.last loop --Underscore is seen as a wildcard usually, wrap it so it is literal if (instr(v_word_lst(i), '_') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --the dash (-) is normally minus (lowers score if second word exists) elsif (instr(v_word_lst(i), '-') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure the comma is literal, not accumulate elsif (instr(v_word_lst(i), ',') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure equal sign is literal not equivilance elsif (instr(v_word_lst(i), '=') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure semi-colon is literal, not near elsif (instr(v_word_lst(i), ';') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure explination point is literal not soundex elsif (instr(v_word_lst(i), '!') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; --ensure dolar sign is literal not stem elsif (instr(v_word_lst(i), '$') > 0) then v_word_lst(i) := '{'||v_word_lst(i)||'}'; end if; end loop; end if; v_location_i := 3000; --Put string back together with spaces v_return := ''; if (v_word_lst is not null and v_word_lst.count > 0) then for i in v_word_lst.first..v_word_lst.last loop v_return := v_return || ' ' || v_word_lst(i); end loop; --trim off excess delimiters at beginning and end v_return := ltrim(rtrim(v_return, ' '), ' '); end if; v_location_i := 3500; --Replace keyword " AND " with & unless it exists inside of {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+AND\s+|&', '\1&',1,0, 'i'); --Now replace "(&" with " (" and "&)" with ") " and remove any leading or trailing spaces from the string v_return := regexp_replace(v_return, '\s*\((&|\s)*', ' (',1,0); v_return := regexp_replace(v_return, '(&|\s)*\)\s*', ') ',1,0); v_return := ltrim(rtrim(v_return, ' ')); --dbms_output.put_line('after & replacement: '||v_return); --Replace keyword " OR " with | unless it is inside {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+OR\s+|\|', '\1|',1,0, 'i'); --dbms_output.put_line('after | replacement: '||v_return); --Replace keyword " NOT " with ~ unless it is inside {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+NOT\s+|~', '\1~',1,0, 'i'); --dbms_output.put_line('after ~ replacement: '||v_return); --Now replace any remaining spaces with &, unless they are inside {} v_return := regexp_replace(v_return, '(\{.*?\})|\s+', '\1&',1,0, 'i'); --dbms_output.put_line('after " " replacement: '||v_return); --Here we must cleanup the condition where we'll get }~|& due to above regex v_return := regexp_replace(v_return, '}[&~\|]+&', '}&',1,0,'i'); --dbms_output.put_line('after && replacement: '||v_return); --Here we must cleanup the condition where we'll get }~| due to above regex v_return := regexp_replace(v_return, '}[~\|]+\|', '}|',1,0,'i'); --In some cases we'll see &|& or &~&, so fix those v_return := regexp_replace(v_return, '&\|&', '|',1,0,'i'); v_return := regexp_replace(v_return, '&~&', '~',1,0,'i'); --In some cases we'll see &&( or )&&, so fix those v_return := regexp_replace(v_return, '&&\(', '&(',1,0,'i'); v_return := regexp_replace(v_return, '\)&&', ')&',1,0,'i'); --In some cases we'll see |&( or )&|, so fix those v_return := regexp_replace(v_return, '\|&\|\(', '|(',1,0,'i'); v_return := regexp_replace(v_return, '\)&\|', ')|',1,0,'i'); --In some cases we'll see ~&( or )&~, so fix those v_return := regexp_replace(v_return, '~&\(', '~(',1,0,'i'); v_return := regexp_replace(v_return, '\)&~', ')~',1,0,'i'); --Finally ensure we don't have any operatiors at the beginning or end and return the expression v_return := regexp_replace(v_return, '^[~&\|]+', '',1,1,'i'); v_return := regexp_replace(v_return, '[~&\|]+$', '',1,1,'i'); return v_return; EXCEPTION when OTHERS then return p_input; END translate_search_expression;
/

The following examples demonstrate how this code will translate your users' search expressions into the actual expression.

SQL> select translate_search_expression('hello world') results from dual; RESULTS -------------------------------------------------------------------------------- hello&world
SQL> select translate_search_expression('"hello world"') results from dual; RESULTS -------------------------------------------------------------------------------- {hello world}
SQL> select translate_search_expression('this~(is history) OR demo') results from dual; RESULTS -------------------------------------------------------------------------------- this~(is&history)|demo

To use this in your query, simply use the results of the function as the search expression portion of the CONTAINS clause like so:

SQL> select ident, content from simple_search_ex where contains(content, translate_search_expression('blue or green'), 1);
IDENT CONTENT --------------- ---------------------------------------------- 2 This is the second row, it is blue 4 This is the fourth row, it is green

Now you should be able to fully enable Oracle Text searches in your pl/sql or Apex application while allowing your users to search as they are used-to, while still providing the power of AND/OR/NOT expressions. I hope you have found this useful.

---

add_months() to timestamps in Oracle

Oracle has two classes of date/time types, "date" and "timestamp". The "date" type is very mature and has a variety of useful manipulation functions. Some of these functions are lacking in the more precise "timestamp" data types.

Today I'm going to provide one way of implementing the "date" type workhourse "add_months" for timestamps.

Why would you need this? Well, in my case, I'm writing a global application where users can request data for the next N months. All dates and times are implemented as timestamps with local time zone to present dates/times in a user's timezone. This is done so the user doesn't have to try and translate times from halfway around the world.

My problem is that Oracle's add_months() will work on a timestamp, but it doesn't reliably save and return the timezone data. I want to be sure timezone data is preserved, so I wrote my own add_months_tz().

My function performs the following basic steps:

• Extract seconds (and fractions of a second) and time zone data from incoming timestamp
• Alter the session time zone to match the incoming timestamp's time zone
• Use Oracle's built-in add_months (which returns a date)
• Use conversion functions to convert the date into a timestamp using the saved seconds
• Restore the original session time zone
• Return the newly built timestamp

In testing  I've noticed that sometimes (and with no visible consistency) I got the following error:

ORA-01862: the numeric value does not match the length of the format item

To prevent this from breaking my application, I wrapped the timestamp creation logic in a block and used the exception handler to create the timestamp without the fractions of a second.

Now that you have the theory and reasoning, here is the code:

create or replace function add_months_tz ( p_timestamp in timestamp with time zone, p_added_months in number) return timestamp with time zone is
--Created by Matthew Cox
--Please feel free to copy, use or alter as you see fit
--I'd appreciate it if you give me credit in your application comments
v_location_i number := 100; o_secs number; o_region varchar2(128); t_date date; r_timestamp timestamp with local time zone; v_session_timezone varchar2(128); begin --save session timezone so we can put it back v_session_timezone := sessiontimezone; v_location_i := 1000; --Extract the seconds and timezone region from the incoming timestamp o_secs := extract(second from p_timestamp); o_region := extract (timezone_region from p_timestamp); --If there is no region name specified, use the offsets if (o_region = 'UNKNOWN') then o_region := extract(timezone_hour from p_timestamp)||':'||extract(timezone_minute from p_timestamp); end if; --Alter the session timezone to ensure proper translation in our cast functions execute immediate 'alter session set time_zone = '''||o_region||''''; v_location_i := 3000; --We convert the timestamp to a date to do the month math t_date := trunc(p_timestamp, 'MI'); --Debug output --dbms_output.put_line('Extracted seconds: '||o_secs); --dbms_output.put_line('Extracted region: '||o_region); --dbms_output.put_line('converted date: '||t_date); v_location_i := 4000; t_date := add_months(t_date, p_added_months); --dbms_output.put_line('after add_months date: '||t_date); v_location_i := 5000; --Now we build a new timestamp with our manipulated date and extracted seconds begin r_timestamp := to_timestamp(to_char(t_date, 'DD-MON-YYYY HH24:MI')||o_secs, 'DD-MON-YYYY HH24:MI:SS.FF'); exception when others then v_location_i := 5100; --Use the simple date to the second r_timestamp := to_timestamp(to_char(t_date,'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'); end; --Debugging output --dbms_output.put_line('final timestamp: '||r_timestamp); --dbms_output.put_line('final timestamp region name: '||extract(timezone_region from r_timestamp)); --dbms_output.put_line('final timestamp region offset: '||extract(timezone_hour from r_timestamp)||':'||extract(timezone_minute from r_timestamp)); --Put the session time zone back execute immediate 'alter session set time_zone = '''||v_session_timezone||''''; return r_timestamp; exception when others then dbms_output.put_line('Error at '||v_location_i||': '||sqlerrm); return null; end add_months_tz; /

Feel free to use this code as you see fit, but please be courteous and give me credit (even if only in your pl/sql comments).

---

Apex Reports to Email - Part 1: Mechanics

Oracle Application Express has built in subscription services for its interactive report type. This allows authenticated users to receive daily, weekly or monthly updates delivered to a single email address. This killer feature allows for users to consume data from a site without having to log-in. Unfortunately it is only available for Interactive Report types.

There are many reasons why a site might not implement reports as interactive. I will not go into them here. My goal is to give you the technique needed to send any APEX page or region to users via email from the back-end database.

There are 3 basic steps:

1. Setup database prerequisites
2. Simulate user's APEX login and Request the page using Oracle's utl_http package
3. Reformat and send html to user as email using the apex_mail package

Step 1 - Setup Database Prerequisites

Before we develop the actual code to fetch and send the APEX page, we need to ensure the database is properly configured. You will likely need to get assistance from your DBA to setup some of these constructs as they require super-user access to the database.

Granting object permissions

First we need to be sure the application owner has execute permission on the packages we'll be using. The following grants and synonym creation are needed to ensure we can easily call the necessary Oracle code from our application schema. Note that this example is using APEX version 4.0.2.00.07.

grant select on apex_040000.wwv_flow_sessions$ to <application_schema>;
create or replace synonym <application_schema>.wwv_flow_sessions$ for apex_040000.wwv_flow_sessions$;

--The following should be granted as part of the APEX install/configuration process
--I've included this for completeness

grant execute on htp to <application_schema>;
grant execute on owa to <application_schema>;
grant execute on apex_040000.wwv_flow_api to <application_schema>;
grant execute on apex_040000.wwv_flow_custom_auth_std to <application_schema>;
grant execute on apex_040000.wwv_flow to <application_schema>;
grant execute on apex_040000.apex_custom_auth to <application_schema>;
grant execute on utl_http to <application_schema>;

11g - Setup Access Control List

Oracle 11g introduces fine-grained network access controls. In order to access network resources, such as an application server, you need to create and setup an Access Control List, or ACL. This requires super-user permissions, so you'll likely need DBA help here too. The following code sets up a fully open ACL where the application_schema has full rights to all network access. This step is unnecessary for versions prior to 11gR1.

BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl
          ( acl         => 'http_access_acl.xml',
            description => 'ACL allowing for targeted access for emailing pages',
            principal   => 'SERVICE_DESK',
            is_grant    => TRUE,
            privilege   => 'connect',
            start_date  => SYSTIMESTAMP,
            end_date    => NULL);
  --web server
  DBMS_NETWORK_ACL_ADMIN.assign_acl
          ( acl        => 'http_access_acl.xml',
            host       => '<webserver IP or hostname>', --Wildcard * allowed
            lower_port => 1,                            --All ports open for testing
            upper_port => 9999);                        --Restrict in production to
                                                        --a single port
END;
/

WARNING: The above ACL allows for full network access. For security purposes, you will most likely want to restrict access to only your apex application server and the standard http and/or https ports (defaults of 80 and 443).


Accessing SSL (https) sites

If your application is secured using SSL certificates, you will have to setup an Oracle Wallet on your database server to store the site's SSL certificate and allow access. If you do not, the code will error out when trying to fetch the page. This requires command-line/gui access to the Oracle user on the database server, so again, work with your DBA to set this up.

I will not go into detail in this post as there are numerous excellent write-ups of this process out there. One such article is Using UTL_HTTP and an Oracle Wallet to Establish a Secure URL Connection (SSL) by Jeff Hunter at idevelopment.info. Jeff goes into great detail and uses screen-shots to walk you through the entire process.

One thing to note is that OWM is a gui tool. This requires you to use X11 forwarding if accessing the database server through SSH. To do this in Putty, you can follow these steps:

1. Load your DB server profile and select SSH->X11 from the menu
2. Check the "Enable X11 forwarding box"
3. Add 127.0.0.1:0 in the "X display Location" field
4. If you need to "su" into the Oracle users, you will likely need to setup your authorization keys
1. From your initial login session enter "xauth list" at the command prompt. You'll get something like this:
   server.domain.com/unix:10  MIT-MAGIC-COOKIE-1  37206ef562651c1aede9cb4b8891cfe7
2. Next su into oracle and add the xauth key like this:
   > xauth add server.domain.com/unix:10 MIT-MAGIC-COOKIE-1  37206ef562651c1aede9cb4b8891cfe7
5. run "xclock". If it worked you should see the gui clock on your desktop.

Step 2 - Authenticate and Request Page

With the prerequisites out of the way, we can focus on the heart of the problem. Apex was designed to render pages through a browser. We want to eliminate the browser and render pages directly to email, from the database.

Oracle has kept tight wraps on the rendering engine in Apex, as it is the key to the product. We will not try to circumvent that closely guarded secret. Instead we will masquerade as a browser ourselves in order to render the page.

In this step we will be creating a procedure that will login to the Apex environment as a user, and then submit a request to the application server to render and return the page. This way we get the page html without having to alter or even care about the Apex rendering engine.

Authenticating as an Apex user

The first step toward our goal is to setup a user session in Apex. This section of code will setup the environment and log us into apex as the "EMAIL_TEST" user:

Create or replace 
  FUNCTION  get_apex_page(p_username in varchar2,
                          p_page_url in varchar2,
                          p_request  in varchar2 default null,
                          p_param_names in varchar2 default null,
                          p_param_values in varchar2 default null) return clob is

    v_procedure_c constant varchar2(30) := 'get_apex_page';
    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) := 'EmailRpt33';

    -- 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_cookie_name     varchar2(256);
    v_cookie_value    varchar2(1024);
    v_cookie_path     varchar2(1024);
    v_cookie_domain   varchar2(256);
    v_cookie_secure   boolean;
    
    v_cgi_name owa.vc_arr;
    v_cgi_val owa.vc_arr;
    v_workspace_id number;
    v_captured_html clob;
    v_output_buffer number := 250;
    v_output_length number;
    v_output_idx number := 1;
    v_page_url varchar2(2000);

    v_return          clob;
    
    
    
  begin



    --
    -- Step 1 - Need to simulate apex login
    --

    -- set up cgi environment - Needed to ensure we can call apex rendering functions
    htp.init;
    v_cgi_name(1) := 'REQUEST_PROTOCOL';
    v_cgi_val(1) := 'HTTP';
    owa.init_cgi_env(num_params => 1,
                     param_name => v_cgi_name,
                     param_val  => v_cgi_val);
 
 
    v_location_i := 2000;
 
    -- find the workspace ID of application
    for c1 in (select workspace_id
                 from apex_applications
                where application_id = v_app_id_c) loop
      v_workspace_id := c1.workspace_id;
    end loop;


    -- set security group ID, session ID, app ID globals
    wwv_flow_api.set_security_group_id(v_workspace_id);
    apex_application.g_instance := wwv_flow_custom_auth.get_next_session_id;
    apex_application.g_flow_id := v_app_id_c;

    v_location_i := 3000;

    --
    -- create authenticated session for v_username
    --
    wwv_flow_custom_auth_std.post_login(
      p_uname => upper(p_username),
      p_session_id => null,
      p_flow_page => apex_application.g_flow_id||':'||v_public_page_c);
 

    dbms_output.put_line('workspace:'||v('WORKSPACE_ID')||', session:'||apex_application.g_instance||', user:'||apex_application.g_user||' '||'APP_USER_ID = ' || apex_util.get_session_state('APP_USER_ID'));

    --
    -- We must call wwv_flow.show to trick Apex into fully initializing the session
    --
    wwv_flow.show (
      p_flow_id => apex_application.g_flow_id,
      p_instance => apex_application.g_instance,
      p_request => 'FSP_SHOW_POPUPLOV'
    );

    --
    -- We need to reset the security group ID because wwv_flow.show resets it
    --
    wwv_flow_api.set_security_group_id(v_workspace_id);


    v_location_i := 4000;

    --
    --Perform user post-authentication initialization steps here
    --


--<Continued in next code-block>

Note that the above code will get you an authenticated session. It will not perform any post-authentication code defined in the Apex authentication scheme. If you need session variables based on the user setup, you must populate them yourself using the apex_util.set_session_state procedure at location 4000 above.



Requesting the Apex page

Now that we have an authenticated session, we need to build and send the HTTP request to the app server. As you may or may not know, the web is built on HTTP requests and responses and APEX is no different.

One distinction APEX has over say the Yahoo home page is that it requires authentication. The APEX rendering engine expects a browser to report your authenticated session ID in two ways. First, and most obvious is in the URL itself as you can see in the below sample URL:

https://server.domain.com/pls/f?p=101:1:1815393136712746::NO::

The second place APEX expects your session id is in your session cookie. This cookie contains a hashed version of your APEX session ID, which must also match a record in the APEX sessions table. We are going to retrieve this hashed value from the wwv_flow_sessions$ table and build a cookie for our request.

The following block of code will show you how to create the utl_http request, create and pass the cookie and retrieve the response (our html source!). It is based upon code by Billy Verreynne shared in this OTN forum thread.

--<continued from above code-block>

    --
    -- Step 2 - Build and submit the request
    --
    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_cookie_support( TRUE );
    UTL_HTTP.set_transfer_timeout( 30 );
    UTL_HTTP.set_follow_redirect( 3 );
    UTL_HTTP.set_persistent_conn_support( TRUE );
    utl_http.clear_cookies;
 
    --Setup URL
    --f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:PrinterFriendly
    v_page_url := rtrim(p_page_url, ': ')||':'||apex_application.g_instance||':'||p_request||':::'||p_param_names||':'||p_param_values;
 
 
    dbms_output.put_line('cookie count: '||utl_http.get_cookie_count);


    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;
 
 
    --Setup user session cookie
    for rec in (select session_id_hashed
                  from WWV_FLOW_SESSIONS$
                 where id = apex_application.g_instance) loop
      v_cookie_value :=  rec.session_id_hashed;        
    end loop;
 
    v_location_i := 8000;  
 
 
    APEX_CUSTOM_AUTH.GET_COOKIE_PROPS(p_app_id        => v_app_id_c,
                                      p_cookie_name   => v_cookie_name,
                                      p_cookie_path   => v_cookie_path,
                                      p_cookie_domain => v_cookie_domain,
                                      p_secure        => v_cookie_secure);
   
    --
    -- Step 3 - Send Request and receive response
    --
    dbms_output.put_line('Fetching page: '||v_page_url);

    v_location_i := 10000;
 
    v_request := UTL_HTTP.begin_request( v_page_url, 'GET', UTL_HTTP.HTTP_VERSION_1_1 );

    -- set HTTP header for the GET
    UTL_HTTP.set_header( v_request, 'User-Agent', 'Mozilla/4.0' );
 
    --
    -- Send the user session cookie. This is key to ensuring you get the actual
    --   page requested, and not the login page
    --
    dbms_output.put_line('Cookie: '||v_cookie_name||'='||v_cookie_value);
    UTL_HTTP.set_header(v_request, 'Cookie', v_cookie_name||'='||v_cookie_value||';'); --LOGIN_USERNAME_COOKIE=coxm; TUSC_SERVICE_DESK=CA901BBB0C24F907

    v_location_i := 11000;
     
    -- get response to the GET from web server
    v_response := UTL_HTTP.get_response( v_request );
     
    v_location_i := 12000;
     
    --Capture incoming buffer to v_captured_html
    v_endLoop := false;
    loop
      exit when v_endLoop;

      begin
        UTL_HTTP.read_line( v_response, v_buffer, TRUE );
 
        if (v_buffer is not null and length(v_buffer)>0) then
           v_captured_html := v_captured_html || v_buffer;
        end if;
      exception
        when UTL_HTTP.END_OF_BODY then
          v_endLoop := true;
      end;

    end loop;
    UTL_HTTP.end_response( v_response );
 
    v_location_i := 13000;
 
 
    dbms_output.put_line('Captured html count: '||length(v_captured_html));

    /*
    --
    -- Output for testing - Note you will likely get buffer overflow errors
    --
    dbms_output.put_line('captured html:');

    v_output_length := length(v_captured_html);

    while (v_output_idx <= v_output_length) loop
      dbms_output.put_line(substr(v_captured_html, v_output_idx, v_output_buffer));
      v_output_idx := v_output_idx + v_output_buffer;
    end loop;
    */

    return v_captured_html;


EXCEPTION
    when OTHERS then
      dbms_output.put_line(v_procedure_c ||' Error found at '||v_location_i||'.'||sqlerrm);
      return null;
END get_apex_page;
/

Step 3 - Reformat and Send as Email to User

Now that we have a procedure to fetch an APEX page, we simply need to write code to call the page and send the email. Of course this is where the most variance will occur from user to user. Each of us has specific reasons for wanting to send an APEX page by email. Some may want an "Email this page" button or link to make collaboration easier. Some may want to setup a schedule of periodic emails for auditing or site use analysis. I will be using this functionality to allow my users to send performance and trending reports to themselves on a periodic basis. Some may not want to use this technique for email at all, but will send the page to some other post-processing routine.

Since I cannot possibly cover the wide variety of needs or uses of this technique, I simply give you a basic procedure to email a given page to a given email account. From there you can expand, schedule via dbms_scheduler, add code to watch a table of subscriptions, and so forth.

Before implementing the email procedure, you need to ensure your APEX environment is setup to send email. Your DBA and/or APEX administrator can assist you with this using the Oracle documentation

create or replace
Procedure  email_apex_page (p_username in varchar2,
                              p_page_url in varchar2,
                              p_target_email in varchar2,
                              p_request  in varchar2 default null,
                              p_param_names in varchar2 default null,
                              p_param_values in varchar2 default null) is

  -- Name: email_apex_page
  --
  -- Description: This procedure will package and email a report to the subscribing user
  --
  -- Parameters:  p_username     - Apex username of user to fetch page as
  --              p_page_url     - Url for accessing report page, up to and including page number
  --                                 https://site.com/testdad/f?p=106:620
  --              p_request      - Apex request value (can use this to turn off non-emailable regions/items
  --              p_param_names  - comma delimited list of page items to set
  --              p_param_values - comma delimited list of page item values (matches param names list)
 

    v_procedure_c constant varchar2(30) := 'email_apex_page';
    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.
 
    --Constants
    /*Change as required for your environment */
    v_sender_email_c constant varchar2(256)  := 'apex@company.com'; --Your desired "From" Address
    v_plain_body_c   constant clob           := 'APEX Page sent via email is in HTML format. PLease view HTML portion';
    v_subject_c      constant varchar2(256)  := 'Emailing Apex Page'; --Your desired "Subject"
 
 
    --Variables
    v_apex_html clob;

 
  begin
    v_location_i := 100;
 
    --
    -- Step 1 - Get the Page
    --
    v_apex_html := get_apex_page(p_username     => p_username,
                                 p_page_url     => p_page_url,
                                 p_request      => p_request,
                                 p_param_names  => p_param_names,
                                 p_param_values => p_param_values);

    --
    -- Step 2 - Send page via email
    --
    APEX_MAIL.SEND(
      p_to        => p_target_email ,
      p_from      => v_sender_email_c,
      p_body      => v_plain_body_c,
      p_body_html => v_apex_html,
      p_subj      => v_subject_c);

  EXCEPTION
    when OTHERS then
      dbms_output.put_line(v_procedure_c ||' Error found at '||v_location_i||'.'||sqlerrm);
      raise;
  end email_apex_page;
/

Tips and Traps

In writing this code, I've come across several tips and traps. Most are covered above, but some of the APEX specific ones are listed here in brief:

• By default CSS and Javascript files use relative paths. 
• You must convert to absolute paths if you want the emailed HTML to have access to them
• Remember that flash doesn't work in email for security reasons, so trim out any flash based charts
• Many people do not allow javascript in email for security reasons, so be sure to accommodate them especially if using javascript based widgets
• It is probably easier to use APEX conditional display to trim your HTML than to try and do it from the database. You can set this up with one of these three methods:
• Use the printer friendly templates to trim out your header, navigation, etc
• Use the "request" parameter of the URL and the corresponding "request not equal to" condition to hide unnecessary page elements from the email
• Use a page or application variable in lieu of "request" if request is already in use

---

Keyword Search via Oracle Text

Oracle text is a feature available in the Oracle Database and is used to provide keyword search indexing to large blocks of text and even binary formatted files like Word and PDF files.

As part of a project I am working on, I need to create a keyword search index that spans multiple columns. This will allow my users to search for keywords in the title, abstract and content of a note entered into the system. The note could be in the form of an uploaded file, or it could be manually entered through the interface.

This post will take you step-by-step through the process of building the back-end Oracle Text index and then using the Oracle SQL Contains clause to leverage that index for full text search across multiple columns.

Before we can create the search indexes, we need a table:

create table note_content ( TITLE VARCHAR2(256 BYTE) --Note Title , ABSTRACT VARCHAR2(4000 BYTE) --Short Description , TEXT_CONTENT CLOB --Content for text-based notes , FILE_CONTENT BLOB --Storage for binary file , FILE_NAME VARCHAR2(400 BYTE) --File name for binary file , FILE_MIME_TYPE VARCHAR2(256 BYTE) --Mime type for binary file , INDEX_FLAG VARCHAR2(1 BYTE) --Column used to control index --sync (more later) , NOTE_TYPE VARCHAR2(1 BYTE) --Flag indicating text/binary );


insert into note_content(title, abstract, text_content)
values ('foo', 'bar', 'this is a test');


insert into note_content(title, abstract, text_content)
values ('abc', 'def', 'foo is the source of bar');

insert into note_content(title, abstract, text_content)
values ('123', '456', '7890');

commit;

Now that we have our table and data, we need to setup the multi-column Oracle Text index. This is where the magic happens as we'll allow the users to search for keywords in the notes title, abstract or content (text or binary).

First we need to setup a multi-column data store that includes each of the columns we want to be searchable:

begin ctx_ddl.create_preference('note_multi_store','MULTI_COLUMN_DATASTORE'); ctx_ddl.set_attribute('note_multi_store', 'columns', 'title,abstract,text_content,file_content' ); end; /

After creating our data store, we need to create a CONTEXT type index. Since we'll be specifying our custom data store, we can create the index on any column we want. In this example we'll choose the INDEX_FLAG column:

CREATE INDEX note_content_search_idx ON note_content(index_flag) INDEXTYPE IS ctxsys.context PARAMETERS('DATASTORE note_multi_store sync (on commit)');

Note: Context indexes require special handling to ensure the content is indexed and available for search. While the create statement above will sync on commit (10g+), the INDEX_FLAG column must be updated as part of the transaction to flag the record for synchronization. Without updating INDEX_FLAG the sync won't happen and new content will not be searchable. We can automate this update by using a trigger:

create or replace TRIGGER note_content_audit BEFORE INSERT or update ON note_content FOR EACH ROW BEGIN :new.index_flag := 'Y'; END;
/

Now that we have the trigger, let's update our records to ensure index_flag is set and the context index is updated

update note_content
set title = title;

select title, index_flag
from note_content;


TITLE INDEX_FLAG
----------------------
foo Y abc Y 123 Y

It is also possible to manually synchronize the index or create a dbms_scheduler_job to do so on an interval. This is advisable if the sync on commit is causing performance problems, or you do not need the note content indexed immediately. It is also advisable to optimize the index periodically to  eliminate data fragmentation. The relevant commands are as follows:

--Synchronize Oracle Text indexes for full-text search using 2MB of memory CTX_DDL.SYNC_INDEX('note_content_search_idx', '2M'); --Optimize the oracle text indexes for full-text search --Full indexing (compact + remove) maximum 60 minutes each ctx_ddl.optimize_index('note_content_search_idx', 'FULL', 60);

To leverage the oracle text index we need to use the contains clause in our sql statements. We can use any column as the first argument. In this example I am going to use the same INDEX_FLAG column we are using to control synchronization. Because the Index uses our custom data store, the indicated column doesn't matter:

Select title, abstract, text_content from note_content where contains (index_flag, 'foo and bar', 1) > 0;

TITLE ABSTRACT TEXT_CONTENT
----------------------------------------- foo bar this is a test abc def foo is the source of bar

The above query will return any record where foo and bar occur in the title, abstract, text_content of file_content columns. Note that 'foo' may occur in one column while 'bar' can be in a separate column. This query also demonstrates the use of boolean AND/OR operators to target keyword searches.

This article should provide a solid foundation for using Oracle Text to implement full-text search for a table. Oracle text is a robust product that allows for a high degree of customization. It also has tools to rank results and/or highlight and display resulting documents. For a more general introduction, please see Oracle's Introduction to Oracle Text article.

---