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!