APEX / Javascript / CSS

Flash Charts - XML Loading Failed

2010-06-23T05:40:00.000-07:00

Has this ever happen to you....

You tested your application, that includes a simple 2D flash chart, throughly in the development environment, then you had your customer test it out as well fixing issues as they come up. When the testing is completed, you put it in the production environment. Everything should work the same. Right????

Well, not necesarily in the real world!! In this case, the system had a flash chart on the first page. I was getting a XML Loading Failed error when using IE8 but did not get the message while using Firefox 3.6. Since IE is about 60% of the market, the issue had to be resolved!!!

After creating an SR with Oracle Support, I found out it it was related to the reverse proxy server at my office. The reverse proxy server allows the public access to the specified server only. Well, the reverse proxy server has several different settings (I do not know what they are since it was configured by another technical group.). The group had to change the settings to allow Flash charts to be displayed.

This issue is described in My Oracle Support Note: 429364.1 Create Flash Chart Region Process Errors With Xml Loading Failed Error Message..

Tabular Form - Aligning at the Top!

2010-05-25T05:58:00.000-07:00

I recently created a manual tabular form that consisted of both text as well as textarea items. It seems that, by default, the items are vertically center aligned.

In order to align all items at the top border, you have to use CSS. You need to identify the class of the table and change the style to align at the top. For example, you would put the following in your page's HTML Header section:

<style>
.t15standard td{vertical-align:top}
</style>

Now the same form looks like this:

Manual Tabular Form

2010-05-15T11:09:00.000-07:00

The wizard creates a tabular form quickly and the form is optimized for performance. Also, page processing is handled automatically (no custom code). However, it is restricted in the following:

Validations – Errors have to be displayed on an error page.
Check boxes are not allowed to be displayed (except if it is only an image – not updateable)
Multiple tabular forms per page – only one tabular form can be displayed on a page

You can overcome these restrictions by creating one or more tabular forms manually. Creating manual forms uses the following features:

Collections
APEX_ITEM

Collections
Collections are temporary tables that are deleted when the session ends. So they are like temporary storage. They can be used as a holding place for data. You can store up to 50 items (all characters) and one CLOB field.

APEX_ITEM

APEX_ITEM is a package to create items on a page dynamically based on a SQL query instead of creating static items. The following functions can be used to create items:
CHECKBOX
DATE_POPUP
DISPLAY_AND_SAVE
HIDDEN
MD5_CHECKSUM
MD5_HIDDEN
POPUP_FROM_LOV
POPUP_FROM_QUERY
POPUPKEY_FROM_LOV
POPUPKEY_FROM_QUERY
RADIOGROUP
SELECT_LIST
SELECT_LIST_FROM_LOV
SELECT_LIST_FROM_LOV_XL
SELECT_LIST_FROM_QUERY
SELECT_LIST_FROM_QUERY_XL
TEXT
TEXTAREA
TEXT_FROM_LOV
TEXT_FROM_LOV_QUERY

Steps to Create a Manual Tabular Form

In order to create a manual tabular form, you following a six-step process:
1. Create Collection
2. Create a Report based on the Collection
3. Create Buttons
4. Update the Collection
5. Add new Row
6. Update the Database from the Collection

1. Create Collection
Create a Process that executes on loading the page (On Load – Before Header process). The process checks if the collection already exists. If it does, delete it. Then you create the collection from a query. Lastly, you set a condition on the process to only create the condition if the collection doesn’t exists or if the RESET button is pressed.

Source:

IF apex_collection.collection_exists (Collection name) = TRUE THEN
     apex_collection.delete_collection(p_collection_name => 'OEHR_EMPLOYEES_COLL' );
END IF;
apex_collection.create_collection_from_query(
     p_collection_name => Collection name,
     p_query => 'select field1 alias1
          , field2 alias2
     , field3 alias3
          .
          .
          .
     , fieldn aliasn
     , ''O'' original_flag
     , wwv_flow_item.md5(field1, field2, field3,…, fieldn)
          from Table Name',
     p_generate_md5 => 'YES');

CONDITIONS:

Example:

2. Create a Report based on the Collection
Instead of selecting the data directly from the table (or view), you will select the columns from the collection itself (or you can create a view of the collection and use the view instead). Remember the collection will be updated throughout your session so the database will remain intact. Collections can have up to 50 columns with character datatype(varchar2(4000)) named C001,0C02, C003,…., C050 and one large character attribute (CLOB) column named CLOB001. C001 is usually used for the key field (it will be hidden on the screen and populate via Before Insert Trigger using a Sequence). Also, you need to append the select statement with a null row so you can insert records as well. Lastly, in the Report Attributes, re-order the columns so that the checkbox item (i.e., chkbx) is the first column.
Source (Example):

SELECT
     apex_item.hidden(1,c001) id 
     ,decode(c010,'N',apex_item.text(2,c002)
     ,apex_item.display_and_save(2,c002)) last_name
     ,apex_item.text(3,c003,8) email
     ,apex_item.date_popup (4,null,c004,'DD-MON-YYYY',10,null,null
     ,lpad(to_char(rownum),4,'0')) hiredate
     ,apex_item.select_list_from_query(5,c005,
          'select distinct job_id, job_id 
           from oehr_employees order by 1 asc ',10) job
     , apex_item.text(6,c006,6) salary
     , apex_item.text(7,c007,6) commission
     , apex_item.popupkey_from_query(8,c008,
          'select last_name, employee_id 
           from oehr_employees order by 1 asc ',10) manager
     , apex_item.select_list_from_query(10,c009,
          'select department_name, department_id 
           from oehr_departments order by 1 asc',10) dname
     , apex_item.hidden(11, c010) status
     , apex_item.checkbox(12, seq_id) chkbx
     , apex_item.hidden(13, seq_id) seq_id
FROM apex_collections
WHERE collection_name= 'OEHR_EMPLOYEES_COLL' 
     AND c010 IN ('O','N','U') -- no deletes
union all -- add a blank row
SELECT
     apex_item.hidden(1,null) id
     , decode(null,'N',apex_item.text(2,null),apex_item.text(2,null)) last_name
     , apex_item.text(3,null,8) email
     , apex_item.date_popup(4,null,null,'DD-MON-YYYY',10,null,null,
lpad(to_char(rownum),4,'0')) hiredate
     , apex_item.select_list_from_query(5,null,
'select distinct job_id, job_id 
       from oehr_employees order by 1 asc ',10) job
     , apex_item.text(6,null,6) salary
     , apex_item.text(7,null,6) commission
     , apex_item.popupkey_from_query(8,null,
     'select last_name,employee_id 
          from oehr_employees order by 1 asc ',10) manager
     , apex_item.select_list_from_query(10,null,
     'select department_name,department_id 
          from oehr_departments order by 1 asc ',10) dname
     , apex_item.hidden(11, null) status
     , apex_item.checkbox(12, null) chkbx
     , apex_item.hidden(13, null) seq_id
from dual

3. Create Buttons

You need to create four buttons: Apply Changes (to post all changes to the database), Add button (to add new rows), delete button (to delete rows that are checked), and the reset button (to null all changes that were not posted and return to the data from the database. These buttons should be positioned at the top of the form (see above). For example, the follow buttons are for the Employee example.
When you create each button, supply the following information as described in the figure below:

Button Name: name of the button (this name can be referenced by the :REQUEST variable)
Text Label/Alt: the text that would appear within the button (e.g., Apply Changes)
Display in Region: select the manual tabular form region
Button Position: Top of Region
Button Alignment: Right

For Reset Button Only - Optional URL Direct >Target is a : Page in this Application, Page: current page, Clear cache: current page

4. Update the Collection
When either updates or deletions are done to the tabular form or pagination is performed, the collection needs to be updated. The database will not be updated until the APPLY CHANGES button is pressed. Until then, the collection will be updates (i.e., it is in a state of flux). Create a process that will fire before validation (Process Point: On Submit – Before Computation). You also have to set a condition so that the process will not fire when the ADD ROW button (e.g., ADD EMPLOYEE) is pressed.
Source: (An example)

DECLARE
     l_status VARCHAR2(1);
     l_seq_id NUMBER;
     l_original_md5 VARCHAR2(4000);
     l_array_md5 VARCHAR2(4000);
     l_exists boolean := FALSE;
BEGIN
--
FOR i IN 1..apex_application.g_f01.count LOOP
   if apex_application.g_f13(i) IS NOT NULL THEN
      SELECT c010
         , c011
      INTO l_status
         , l_original_md5
      FROM apex_collections
      WHERE collection_name = 'OEHR_EMPLOYEES_COLL'
         AND seq_id = apex_application.g_f13(i);
--
-- For existing rows only
-- 1. calculate the new md5
-- 2. if md5 changed, set status to 'U'
-- For new or updated rows, update collection
--
--
-- For existing rows: create an mad5 calculation and if it is different from original md5 calculation, set it status to ‘U’
--
IF l_status <> 'N' THEN
   SELECT wwv_flow_item.md5(apex_application.g_f01(i)
      , apex_application.g_f02(i)
      , apex_application.g_f03(i)
      , apex_application.g_f04(i)
      , apex_application.g_f05(i)
      , apex_application.g_f06(i)
      , apex_application.g_f07(i)
      , apex_application.g_f08(i)
      , apex_application.g_f10(i))
INTO l_array_md5
FROM DUAL;
   IF l_original_md5 != l_array_md5 THEN
      l_status := 'U';
   END IF;
END IF;
--
-- For new or updated rows: update the collection member
--
IF l_status IN ('N','U') THEN
   apex_collection.update_member(p_collection_name => 'OEHR_EMPLOYEES_COLL'
      , p_seq => apex_application.g_f13(i)
      , p_c001 => apex_application.g_f01(i)
      , p_c002 => apex_application.g_f02(i)
      , p_c003 => apex_application.g_f03(i)
      , p_c004 => apex_application.g_f04(i)
      , p_c005 => apex_application.g_f05(i)
      , p_c006 => apex_application.g_f06(i)
      , p_c007 => apex_application.g_f07(i)
      , p_c008 => apex_application.g_f08(i)
      , p_c009 => apex_application.g_f10(i)
      , p_c010 => l_status
      , p_c011 => l_original_md5
   );
   END IF;
END IF;
END LOOP;
--
-- DELETE EMPLOYEE - if employee was added, delete member from collection
-- if employee already existed, set status to 'D'
--
IF :REQUEST = 'DELETE_EMPLOYEE' THEN
   FOR i IN 1..apex_application.g_f12.count LOOP
      if apex_application.g_f12(i) IS NOT NULL THEN
         :p22_num_deleted := :p22_num_deleted + 1;
         SELECT seq_id
            , c010
         INTO l_seq_id
            , l_status
         FROM wwv_flow_collections
         WHERE collection_name = 'OEHR_EMPLOYEES_COLL'
            AND seq_id = apex_application.g_f12(i);
         IF l_status = 'N' THEN
            apex_collection.delete_member(p_collection_name => 'OEHR_EMPLOYEES_COLL'
               , p_seq => l_seq_id
            );
         ELSE
            apex_collection.update_member_attribute(            p_collection_name => 'OEHR_EMPLOYEES_COLL'
               , p_seq => l_seq_id
               , p_attr_number => '10'
               , p_attr_value => 'D'
            );
         END IF;
      END IF;
   END LOOP;
END IF;
--
-- Set the success message
--
if to_number (:p22_num_deleted )> 0 then
   apex_application.g_print_success_message :=          apex_application.g_print_success_message ||
:p22_num_deleted ;
   if to_number (:p22_num_deleted) = 1 then
      apex_application.g_print_success_message := apex_application.g_print_success_message ||
' Employee Deleted';
   else
      apex_application.g_print_success_message := apex_application.g_print_success_message ||
' Employees Deleted';
   end if;
end if;
END;

CONDITIONS (Example)

5. Add New Row
When either add new row is pressed, the collection needs to be updated to include the new row. The database will not be updated until the APPLY CHANGES button is pressed. Until then, the collection will be updates (it is in a state of influx). Create a process that will fire before validation (Process Point: On Submit – Before Computation). You also have to set a condition so that the process will only fire when the ADD ROW button (e.g., ADD EMPLOYEE) is pressed.

Source: (Example)

declare
   l_status varchar2(1);
begin
   l_status := 'N';
   for i IN 1..apex_application.g_f01.count LOOP
      if apex_application.g_f01(i) is null then
         apex_application.g_f13(i) := apex_collection.add_member(
            p_collection_name => 'OEHR_EMPLOYEES_COLL',
            p_c001 => apex_application.g_f01(i),
            p_c002 => apex_application.g_f02(i),
            p_c003 => apex_application.g_f03(i),
            p_c004 => apex_application.g_f04(i),
            p_c005 => apex_application.g_f05(i),
            p_c006 => apex_application.g_f06(i),
            p_c007 => apex_application.g_f07(i),
            p_c008 => apex_application.g_f08(i),
            p_c009 => apex_application.g_f10(i),
            p_c010 => l_status,
            p_generate_md5 => 'YES' );
      end if;
   END LOOP;
end;

CONDITIONS (Example)

6. Update the Database from the Collection
When either APPLY CHANGES is pressed, it is now time to update the database from the collection. Create a process that will fire before validation (Process Point: On Submit – After Computation). You also have to set a condition so that the process will only fire when the APPLY CHANGES button (e.g.,UPDEMP) is pressed. In this procedure, you calculate the number of rows deleted, added, and updated and display the result in the success message (i.e., apex_application.g_print_success_message).
Source: (An example)

DECLARE
   l_table_md5 VARCHAR2(4000);
   l_array_md5 VARCHAR2(4000);
   l_collection_md5 VARCHAR2(4000);
BEGIN
   :p22_num_updated := 0;
   :p22_num_added := 0;
   :p22_num_deleted := 0;
   FOR c1 IN (SELECT c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, seq_id
      FROM apex_collections
      WHERE collection_name = 'OEHR_EMPLOYEES_COLL'
   ) LOOP
--
-- New - insert it
--
      IF c1.c010 = 'N' THEN
         :P22_NUM_ADDED:= :P22_NUM_ADDED+ 1;
         INSERT INTO v_oehr_employees (employee_id, last_name, email, hire_date, job_id, salary,
            commission_pct, manager_id, department_id )
         VALUES (c1.c001, c1.c002, c1.c003, c1.c004, c1.c005, c1.c006, c1.c007, c1.c008, c1.c009 );
--
-- Delete row
--
      ELSIF c1.c010 = 'D' THEN
         DELETE FROM v_oehr_employees WHERE employee_id = c1.c001;
--
-- Update changed row
--
      ELSIF c1.c010 = 'U' THEN
         SELECT wwv_flow_item.md5(employee_id, last_name, email, hire_date, job_id, salary,
         commission_pct, manager_id, department_id)
         INTO l_table_md5
         FROM v_oehr_employees
         WHERE employee_id = c1.c001;
         IF l_table_md5 != c1.c011 THEN
            raise_application_error( -20001, 'Current version of data in database has changed since user initiated update process.');
            RETURN;
         END IF;
         SELECT wwv_flow_item.md5(to_number(c1.c001), c1.c002, c1.c003, to_date(c1.c004), c1.c005, to_number(c1.c006),
            to_number(c1.c007), to_number(c1.c008), to_number(c1.c009))
         INTO l_array_md5 
         FROM dual;
         IF c1.c011 <> l_array_md5 THEN 
            :p22_num_updated := :p22_num_updated + 1;
            UPDATE v_oehr_employees
            SET last_name = c1.c002,
               email = c1.c003,
               hire_date = c1.c004,
               job_id = c1.c005,
               salary = c1.c006,
               commission_pct = c1.c007,
               manager_id = c1.c008,
               department_id = c1.c009
          WHERE employee_id = c1.c001;
      END IF;
   END IF;
END LOOP;
--
-- Set Success Message
--
if to_number (:p22_num_updated )> 0 then
   apex_application.g_print_success_message :=    apex_application.g_print_success_message || :p22_num_updated ;
if to_number (:p22_num_updated) = 1 then
   apex_application.g_print_success_message := apex_application.g_print_success_message || ' Employee Updated';
else
   apex_application.g_print_success_message := apex_application.g_print_success_message || ' Employees Updated';
   end if;
end if;
if to_number (:p22_num_deleted )> 0 then
   apex_application.g_print_success_message := apex_application.g_print_success_message ||:p22_num_deleted ;
   if to_number (:p22_num_deleted) = 1 then
      apex_application.g_print_success_message := apex_application.g_print_success_message || ' Employee Deleted';
   else
      apex_application.g_print_success_message := apex_application.g_print_success_message || ' Employees Deleted';
   end if;
end if;
if to_number (:P22_NUM_ADDED )> 0 then
   apex_application.g_print_success_message := apex_application.g_print_success_message ||:P22_NUM_ADDED ;
   if to_number (:P22_NUM_ADDED) = 1 then
      apex_application.g_print_success_message :=       apex_application.g_print_success_message || ' Employee Added';
   else
      apex_application.g_print_success_message := apex_application.g_print_success_message || ' Employees Added';
   end if;
end if;
--
-- Clean up: delete collection
--
apex_collection.delete_collection( p_collection_name => 'OEHR_EMPLOYEES_COLL' );
END;

CONDITIONS (Example):

Conclusion
Creating a manual tabular form is only a five-step process that provides more flexibility and overcomes restrictions of declarative tabular form (i.e., created by a wizard). APEX 4.0 will have validations as well as additional item types (such as radio buttons) for tabular forms. Until APEX 4.0 is available (rumors have circulated that will be in June 2010 or July 2010), all tabular forms should be created manually using collections.

APEX Error Page

2010-04-22T07:32:00.000-07:00

I recently had to use the Error Page for Tabular Form's validation errors. It was not pretty. It includes the error message , OK hyperlink, an Error icon, and the error message created in your function. The page does not have any formatting. It is UGLY!!

I found out (form the Discussion Forum) that the template used is in your Theme. I found out mine was using the Login template. That is why it was so UGLY! So I created a new template for the Error Page...

I copied the Login template to a new template (let's call it Error Page).
I modified the template to place a box around it with a background color of light grey. In the "Body" of the template, I wrapped BOX_BODY in a table.

<table border="1" style="background-color:#E0E0E0;border-width: 1px;border-spacing: ;border-style: solid;border-color: black;border-collapse: collapse;"><tr><td>#BOX_BODY#</td></tr></table>

In the theme,I changed template for Error Page to ERROR PAGE

...now I have a better looking Error Page.

Now, I want to format my error so the error would stand out to the user:

Bold the general error message. In the error message in the validation, wrap the ... tag around the error message.
Highlight the detailed error message in red.In the detailed error message in the validation, wrap the ... tag around the detailed error message in your function.
Close the error message with "Click OK to correct your error" by appending it to your message is the function (e.g. l_error := l_error || 'Click OK to correct your error.';).
Separate the closing message from the error message with a line.e.g. l_error := l_error || <hr />;). (before the previous step)

... now I have a better looking Error message.

But....

I have to replicate the above in each of the error messages for each tabular form. That can be extensive. So I decided to create two functions in the package that I am using for the application:

error_line_wrap - this function wraps each error message with the needed format. A tabular form may have a message for each row.
error_close_wrap - this function adds the line and "Click OK to correct your error" to the end of the message.

While each validation function has to reference these two functions, it has several advantages:

If I want to change the format of the error line or closing error message, I only have to change it one place.
A consistent look throughout the application.
If the application is developed by several developers, each developer would just need to use the same functions.

Now I have a formatted error page and messages that are easy to create.

jQuery

2010-04-15T18:53:00.000-07:00

Introduction to JQuery

What does jQuery do for you? It makes your life easier on the Web. That is what!

It is cross-browser!
That means it works in all major browsers including older versions such as IE5 and IE6. You don't have to wonder if it will work in IE and FoxPro. You don't have to test in all browsers. If you use jQuery, it will work. It was already tested!
Small in size
The base javascript library is about 55K, less than most image fileswebpage. If you use the UI, you only need to include the one for your theme, not all themes.
Great widgets!! Some of the ones included are:

Date Picker
Dialog Box
Accordion
Tabs
Drop and Drag
Fixed-length Formatted Fields
Tooltips

Only one line is usually needed!
$(node).method
For example, $("#accordion").accordion will create an accordion widget for div with an id accordion.
You keep events, widgets, css changes, etc. away from your HTML.
It is kept within the javascript code in the header. Usually it run in a function called by $(initialize_function) where initialize_function will run all your events, widgets, etc.
It can be used in APEX (Also, APEX 4.0 will be using jQuery in the tool itself).

Install jQuery
You can install two ways:

Download jQuery and Install it on your server

Go to http://www.jquery.com and download the latest version.

Add the following code to your header.

<script type="text/javascript" src="jquery-1.3.2.min.js"><script>

Importing jQuery from Google

Add the following in your header (i.e., <HEAD>) (Please note: the number parameter is the version.):

<script type="text/javascript" src="http://www.google.com/jsapi">
</script>
<script type="text/javascript" >
  google.load("jquery", "1.4.2");
  google.load("jqueryui", "1.7.2");
</script>

Structure of a jQuery Statement
The jQuery statement has usually two parts: node and method:

jQuery node - $("identifier") where identifier is an id (#div_id_name) or a class (.class_name) or tag (for example, h1).

method - jQuery node.method such as $("#div_id_name").html ("html_code"). So $(h1).hide will hide all H1. And, $(#accordion_section).accordion will create an accordion for a DIV that has an id accordion_section. Lastly, $(.reg_hdr).tab will create tabs for a div whose class is reg_hdr.

Simple Methods

html to create html - html ("html_code");
css ("style_rule","value") e,g, $("h1).css ("backgroundColor","yellow"); or JSON$("#my_div_id").css({"backgroundColor":"black","color": "white"});

Cheat Sheet
Click here for jQuery 1.4 Cheat Sheet:
Events

Events can be issued with jQuery statements without affecting the HTML code. You usually call a function to perform the action.

Some examples are:

hover - $(node).hover(hover_function, no_hover_function);
click - $(node).click (click_function);
change - $(node).change (change_function);
dblclick - $(node).dblclick (double_click_function);
focus - $(node).focus (focus_function);
keydown - $(node).keydown (keydown_function);
mousedown - $(node).mousedown (mouse_down_function);
select - $(node).select (select_function);

Ajax

Create an empty div in the html
In script:

         $(document).ready (getAJAX_function); or   $(getAJAX_function)
         function getAJAX_function(){
              $("#div id").load ("text filename"};
         }

Animation

Show a hidden area - $("#div_id").show():
Hide a visible area - $("#div_id").hide():
Toggle area - $("#div_id").toggle():
Slide Down an area - $("#div_id").slidedown("speed"): speed = low, medium, fast or milliseconds
Slide Up an area- $("#div_id").slideup("speed"): speed = low, medium, fast or milliseconds
Fade In an area- $("#div_id").fadeIn("speed", function): speed = low, medium, fast or milliseconds
Fade Out an area - $("div_id").fadeout("speed"): speed = low, medium, fast or milliseconds

UI (User Interface)

Adding an icon

1. you add a theme
2. make sure the icons are accessible
3. $("div id").append('');

Drag and Drop

You make a clone of the draggle area

function clone_function () {
// make four clones
for (i-1, i<=4;i++) {
// indicate the overlapping of elements
zValue = (101+1) + "";
// each successive copy of the element to be a bit lower
yPos = 100 + (i*20) + "px";
// make a clone, insert , set the vertical position, set zIndex
$("div:first").clone()
   .insertAfter ("div:first")
   .css         ("top",yPos)
   .css         ("zIndex",zValue)
   .append      (" #" + i);
} // end of loop
} // end clone_function
}

Make the area draggable ($(".drag_class").draggable();
Bind a drop event to the target area ($("#target_div_id").bind("drop", change_target_function);)
Bind a dropout event to the target area ($("#target div id").bind("dropout", rest_function);)

jQuery UI (User Interface) Widgets:

Accordion - $("#div id").accordion;
Slider - $("div id').slider() .bind("slide", function); The function can read the slider's value via $("div id").slider("value");
Date Picker - $("#div id').datepicker;
Tabs - $("#div id").tabs();
Sortable list - $("#div id').sortable(); [Sortable elements are usually lists (ul/li) with an id]
Selectable elements - $("#div id").selectable(); [Selectable elements are usually lists (ul/li) with an id]
Dialog Box -$("#div id").dialog ("close"), $("div id").dialog ("open");

Pull_Multi_Values Javascript Issue

2010-04-09T08:48:00.000-07:00

Recently, I had a problem with a version of the pull_multi_values javascript function, which uses AJAX to populate four fields (county, city, state, and zip code) from a Zip Code lookup field. It populated county, city, and zip code fine but did not populate state. The only difference between these fields is that county, city, and zip code fields are text fields while state field is a select field. The original javascript function code was:

<script language="JavaScript" type="text/javascript">
<!--
function pull_company_city_state(pValue){
var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
'APPLICATION_PROCESS=SET_CITY_STATE',$v('pFlowStepId'));
if(pValue){
get.add('TEMPORARY_APPLICATION_ITEM',pValue)
}else{
get.add('TEMPORARY_APPLICATION_ITEM','null')
}    
gReturn = get.get('XML');
if(gReturn){
var l_Count = gReturn.getElementsByTagName("item").length;
for(var i = 0;i<l_Count;i++){
var l_Opt_Xml = gReturn.getElementsByTagName("item")[i];
var l_ID = l_Opt_Xml.getAttribute('id');
var l_El = html_GetElement(l_ID);    
if(l_Opt_Xml.firstChild){
var l_Value = l_Opt_Xml.firstChild.nodeValue;
}else{
var l_Value = '';
}
if(l_El){
if(l_El.tagName == 'INPUT'){
l_El.value = l_Value;
}
else if(l_El.tagName == 'SPAN' && 
l_El.className == 'grabber'){
l_El.parentNode.innerHTML = l_Value;
l_El.parentNode.id = l_ID;
}else{
l_El.innerHTML = l_Value;
}
}
}
}
get = null;
}
//-->
</script>

Since I am a relative newbit to javascript and AJAX, I could not resolve the problem on my own. So what did I do? You guessed it, I posted a thread on the APEX Discussion Forum hoping one of the Gurus would respond. One of the gurus, Jari, assisted me and informed me that I had no test for SELECT items. So asked me to include the following line in the nested if statement:

else if(l_El.tagName == 'SELECT'){
           $s( l_El, l_Value) // Case for select

Based on the documentation (http://download.oracle.com/docs/cd/E14373_01/apirefs.32/e13369/javascript_api.htm#CHDFBJGD), $s(pNd, pValue) sets the Application Express item value taking into account what type of item it is.
So the revised javascript is as follows:

<script language="JavaScript" type="text/javascript">
<!--
function pull_company_city_state(pValue){
var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
'APPLICATION_PROCESS=SET_CITY_STATE',$v('pFlowStepId'));
if(pValue){
get.add('TEMPORARY_APPLICATION_ITEM',pValue)
}else{
get.add('TEMPORARY_APPLICATION_ITEM','null')
}    
gReturn = get.get('XML');
if(gReturn){
var l_Count = gReturn.getElementsByTagName("item").length;
for(var i = 0;i<l_Count;i++){
var l_Opt_Xml = gReturn.getElementsByTagName("item")[i];
var l_ID = l_Opt_Xml.getAttribute('id');
var l_El = html_GetElement(l_ID);    
if(l_Opt_Xml.firstChild){
var l_Value = l_Opt_Xml.firstChild.nodeValue;
}else{
var l_Value = '';
}
if(l_El){
if(l_El.tagName == 'INPUT'){
l_El.value = l_Value;
}
else if(l_El.tagName == 'SPAN' && 
l_El.className == 'grabber'){
l_El.parentNode.innerHTML = l_Value;
l_El.parentNode.id = l_ID;
}
else if(l_El.tagName == 'SELECT'){
$s( l_El, l_Value) // Case for select}
else{
l_El.innerHTML = l_Value;
}
}
}
}
get = null;
}
//-->
</script>

After I changed it, it worked like a charm. Thanks to APEX Discussion Forum (and Jari, of course). I couldn't have done it without you guys. APEX Discusssion Forum is such a valuable resource. I learned so much from it. The people who responds to the questions are very helpful.

Robert

APEX and AJAX for Public Pages

2010-03-24T14:44:00.000-07:00

I have been using AJAX in my APEX applications so I can create cascading select list, filtered shuttle items, and lately, filling in fields from a database based on an input fields. I had very little problems with it until recently.

I had an application that fills in city and state based on a zip code entered. It worked fine until I set up the application for public access (i.e., Page is Public and does not required authentication). When I entered the 5-digit zip code and press tab, city and state did not get filled in. I looked at the javascript and the On-Demand Process and could not find anything wrong with it. Tried even to use Firebug to debug it but that did not help as well. So I put in an alert(i.e., alert('gReturn'+gReturn); where gReturn was set to get.get('XML');). I got gReturnNULL as a result. So something was messed with the XML. Since the get was a call to my ODP, I didn't know what was causing the problem.

var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
    'APPLICATION_PROCESS=SET_CITY_STATE',0);

So what does someone do in this circumstance. You create a new thread on APEX Discussion Forum. What else!! And hope that someone will respond to your threat quickly. Thank goodness one of the top gurus (varad) of the forum answered within an hour or so. After about an hour, he fixed my problem. It turned out that for public pages you have to use 'PFlowStepId', which contains the value corresponding to the APEX page-id from where the AJAX call was made, instead of '0', when it is a public page. So once I changed it to :

var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
    'APPLICATION_PROCESS=SET_CITY_STATE',$v('pFlowStepId'));

and removed a htp.p statement (for debugging purposes), it worked!! Thank goodness for the forum and for varad! I wouldn't have been able to figure it out without either one.

So remember, for public pages you must use $v('pFlowStepId') instead of 0 where accessing your ODP in your javascript.

I hope this helps someone else.

Robert

How to Create Mailing Labels in APEX

2010-03-12T08:30:00.000-08:00

Originally my customer wanted to be able to print company addresses on mailing labels using a specific Avery format. This involve three steps:

Create an additional report for the mailing label consisting of two fields: contact/address and company name. The first field (contact/address) will be displayed and the 2nd field (firm name) will not be displayed but will be used for sorting purposes. The first field will contact contact name, company name, and address.
Create a 'mailing label' button on the original report. This button will redirect to another page that contains the new report (see #1 above).
Create a new template for the mailing label.

Let's discuss each step in depth:

Create a new report
The report consist of a simple query with two fields: contact/address and company name. The contact/address field will consists of a concatenation of the contact, company name and address separated with breaks (i.e., ). The second field will be company name which will be the sort field. Here is an example of the query:



SELECT contact_name ||

'<BR />'||

company_name ||'<BR />'||

street_address||'<BR />'||

city||' '||state||' '|| postal_code, 

company_name 

from

COMPANY

Created a 'mailing label' button
Create a button on the region where your report is displayed:

Create a button
Button Region: Select the region of your report..Click Next.
Button Position:Create a button in a region position.Click Next.
Button Attributes: enter button name (label will be automatically filled in, change it if you like), click on template driven, click Submit Page and Redirect to URL.Click Next.
Image Attributes or Template : Click on the button template.Click Next.
Display Properties: Position : Top and Bottom of Region.Click Next.
Branching: Branch to Page(when button is pressed): enter page of the mailing label report. Click Next.
Click on Create Button

Create a new template for the mailing label
You now need to create a new template for the report you created in #1 since the mailing label needs to be printed on each physical label that can be a number of labels across and a number of label down a page. For example, Label #5360 has 21 labels per page, 7 rows by 3 columns. This is where the tricky part comes in. You need to create a row template with four different versions based on a PL/SQL Expression (one for the 1st label of a new page, the 1st label of the row, the 2nd label of the row, the 3rd label of the row)

The first row template is for a new page because you want to issue a "page break before" style so the printer advances to the next page after the cell is printed. So the code would look like this:

Row Template 1 
<td>#1#<p STYLE="page-break-before: always;"></p></td></tr><tr>
 
Row Template 1 Condition : Select Use Based on PL/SQL Expression

Row Template 1 Expression 
mod(#rownum#,21) = 0 (this will catch it on the 21st, 42nd, 63rd, etc... address)

The second row template is for the first label on the page. You need to start the row and put the define the 1st cell of the row. So the code would look like this:

Row Template 2

<tr><td>#1#</td>

Row Template 2 Condition : Select Use Based on PL/SQL Expression

Row Template 2 Expression 
mod(#rownum#,21) = 1  (this will catch it on the 22nd, 43rd, 64th, etc... address)

The third row template is for the third label on each row. You need to define the 3rd cell of the row and end the row. So the code would look like this:

Row Template 3

<td>#1#</td></tr>
 
Row Template 3 Condition : Select Use Based on PL/SQL Expression

Row Template 3 Expression 
mod(#rownum#,3) = 0

The fourth row template is for the all other labels. You need to define the cell of the row.So the code would look like this:

Row Template 4

<td>#1#<td>

Row Template 4 Condition : Select Use Based on PL/SQL Expression

Row Template 4 Expression 
#rownum#!=1

Lastly, you assign this new template for the label report.

Converting UTC to Local Time

2010-02-03T18:20:00.000-08:00

I am on a project that had several date fields in UTC
number format. The reason is that the data was loaded from another system's csv file and the system stores all dates in number format (in UTC). UTC is another name for Greenwich Time. So I had to convert the UTC number to a date field . I did some research (via Google) and found the following conversion technique:

to_date(TO_CHAR(to_date('01.01.70','dd.mm.rr')+ (number_UTC_field/(60*60*24)),'mm-dd-yyyy hh24:mi:ss'),'mm-dd-yyyy hh24:mi:ss')

It turns out that UTC dates are seconds from January 1, 1970. So you need to convert the UTC number to days (i.e., 60 seconds * 60 minutes * 24 hours). Great!! I was able to convert the UTC number to date format. But there was still one more issue, the date is in Greenwich (UTC) Time. So I had to convert it to my local time which is Eastern Daylight Savings Time. How could I do that? So I researched again (via Google!!) and found the new_time function which accepts three parameters ( a date, from_format, to_format). To convert from UTC to Eastern Daylight Savings Time (EDT), it would be NEW_TIME (date_field, 'GMT','EDT'), so I changed the above code to

new_time(to_date(TO_CHAR(to_date('01.01.70','dd.mm.rr')+ (number_UTC_field/(60*60*24)),'mm-dd-yyyy hh24:mi:ss'),'mm-dd-yyyy hh24:mi:ss'),'GMT','EDT')

Okay, now I'm fine with the date fields. Now I can focus on some other fields which were phone number fields. These fields could be internal or external number. Internal numbers consisted of the tie line (i.e., location) and the last four digits of the phone number. For example, 105555 is 10 for the tie-line and 5555 for the phone number. I needed to convert it to xx-xxxx (i.e., tie line - phone number). External numbers consists of area code and 7 digit phone number. So I needed to convert external numbers from xxxxxxxxxx to xxx-xxx-xxxx. Therefore, the conversion techique is as follows:

DECODE( LENGTH(PHONE1),
6,SUBSTR(PHONE1,1,2)||'-'||SUBSTR(PHONE1,3) ,
10, SUBSTR(PHONE1,1,3)||'-'||SUBSTR(PHONE1,4,3)||'-'|| SUBSTR(PHONE1,7) ,PHONE1)

Okay. the select statement for the source of the report is fine now. It now displays the correct format for dates and phone numbers. But now I have another dilemma...

For each report, I need to do these conversions for each date and/or phone numbers. Also, the select statement is so unreadable!! The solution came to me while on my morning train commute. I sometimes do my best thinking in "non-office" settings such as the shower, train, before I go to sleep. Anyway, the answer is a view!! I'll create a view based on the table that contains all the conversion formats. Therefore, in each report, all I need to do is reference the view's column(s) -- that is already formatted. Also, if the format needs to be changed, such as changing EDT to EST or adding external calls to foreign countries, all I need to do is change the view. That way, instead of changing all the reports, I just change it in one place, the view, and it will automaticallly be reflected in all the reports. That will make my life on this project so much easier!!

So, in summary, in order to reference UTC number fields and phone number fields, I did the following:

Convert UTC number to date (i.e., to_date('01.01.70','dd.mm.rr')+ number / (60*60*24),'MM/DD/YYYY HH12:MI')
Convert UTC dates to dates in local time (i.e., NEW_TIME)
Format phone numbers via DECODE and concatenation
Create a view that includes the above conversions.

I hope this helps others that have the similiar issues!

APEX 4.0 Early Adopter - Creating Plugin for Masked Input

2010-02-03T08:01:00.000-08:00

In APEX 4.0 Early Adopter, I attempted to create a Masked-input plug-in following Patrick Wolf's article Oracle APEX 4.0: How to create a Plug-in but when I attempted to upload jquery.maskedinput-1.2.2.min.js, I received the following error:

ORA-01407: cannot update ("APEX_040000"."WWV_FLOW_PLUGIN_FILES"."MIME_TYPE") to NULL

Unable to process row of table WWV_FLOW_PLUGIN_FILES.

It is very frustrating testing out creating a plug-in which was already tested. :(

I sent feedback to the Oracle developers. I hope they fix the problem in a timely manner so I can finish the test. I'll keep you posted.

Update....
Well, someone from the Oracle Appliation Express 4.0 team responded to my feedback. He/she asked if it works in Firefox. I tested it in Firefox and it works fine. No problems. But I have to develop in IE at my company (it is a company rule!) so they need to work out the bugs in IE 7.0 before it goes to production. :(

Reports via Apache FOP

2010-01-25T21:41:00.001-08:00

I would like to do my reports generation in PDF, Excel, or RTF format.Since my company doesn't have BI Publisher and we don't plan to purchase it (due to its cost), the only option is Apache.

Is Apache FOP already installed? I don't know but I don't think so.

How does it get installed? Who usually installs it? Where do I find
the documentation? These questions will need to be answered in the
next few weeks. I'll keep you posted. ;)

I found the link to PDF Printing in Application Express in the Discussion Forum:
http://www.oracle.com/technology/products/database/application_express/html/configure_printing.html
I will need to review the document first.

Setup with the following software combination should work:

win32_11gR1_database.zip
oc4j_extended_101350.zip
jdk-1_5_0_22-windows-i586-p.exe

APEX LDAP

2010-01-24T19:29:00.001-08:00

I was reading about LDAP with APEX from Pro Oracle Application Express (Expert's Voice in Oracle), by John Scott, Scott Spendolini, and it got me thinking that all intranet APEX applications should use LDAP for authentication purposes. I have to read more and I will keep you posted about my progress.
The next steps:

I will read the How-to article on otn.oracle.com: Authenticate Users Using an LDAP Server
I will go search on the APEX Discussion Forum for people's experiences and issues. Maybe there will be other links that I can follow and read up more information about LDAP.
Meet with staff who supports LDAP in my company.
Test out a simple application that is used within my department.

jQuery Progressive Bar

2010-01-24T14:27:00.001-08:00

I would like to incorporate a progressive bar but I don't know how long the proegress will take. JQuery site says it is coming out with a new widget for this. I just don't know when. I guess I'll have to wait. :(

jQuery - My new toy!

2010-01-21T12:12:00.000-08:00

Last month, I went to an Oracle User Group meeting that had Dan McGhan presenting a seminar for converting Forms to APEX. During this presentation, he showed a calendar that uses jQuery which he mentioned that APEX 4.0 will have jQuery. I never heard of jQuery before. And here he shows a datepicker is not more current than the datepicker that comes with APEX. It is larger and more colorful and has several additional features that the regular datepicker doesn't have. You even don't need a calendar icon to click. You can just click on the date field and it can automatically be displayed. Is that cool or what!!

So in the next few weeks, I searched the APEX Discussion Forum for jQuery datepicker and also went to Dan McGhan's blog. I tried to incorporate it an application that I am developing. I had some problems which I was able to resolve in a few days. And....lo and behold, I had the jQuery datepicker in my application. It works great!!

Now...I then searched several blogs to find other jQuery features that I can use. I found one for item help (Dan McGhan's AJAX Item Help) and another one for masked input for fixed-length fields (digitalbush.com's Masked Input Plugin). I was then able to incorporate them in the same application as the datepicker. For each jQuery feature, I had a couple of issues but I was able to resolve them in a few days. Now they all work!!!

jQuery is so cool!

How do you put a hyperlink in APEX?

2010-01-12T09:04:00.000-08:00

Have you ever wanted to create a item with type 'hyperlink' but it does not exists. So as an alternative, you create a text item and you put a <a href="..."> in the Pre Element Text and a </a> in the Post Element Text. That is so annoying to do it each time!!!!

So I created a button with a new button template instead!

Here are the 2 step process:
1. Create a new button template called MyHyperlink with the following definition:

<a href="#LINK#">#LABEL#</a>

2. Create a button in a region position (not as an item) using template MyHyperlink

That is it. All ready to go! Simple isn't it?!!

My sidebar region is too large...

2009-12-19T11:09:00.000-08:00

How does one change the width to make it narrow so it does not go beyond the width of the browser? I used the width= and it did not work.

What happened was that I was using a new template for my button and the table in the template did not have an closing tag ending </table> so the button continued and messed up the regions.

Always check your template syntax. The button template seemed to be working if it was placed in a region with no width.