Thursday, January 13, 2011

What happens when a PL/SQL process throws an exception in APEX

If your apex page has a PL/SQL process that throws an exception after page submit, the end result will depend the way you handle the exception.

Case 1: PL/SQL process doesn't raise an exception at all or you handle/catch the exception and doesn't raise it again.

Result: You will be forwarded to the page as per the branch created and the notification area will show the value assigned to the apex_application.g_print_success_message and "Process Success Message"

Case 2: PL/SQL process raised some exception.

Result: You will be forwarded to the apex error page. Here you can see the message in notification area if you had assigned some value to apex_application.g_print_success_message before the exception in process.

In addition to that the error page body will show the exception in below format.

ORA-20010: "Error text from Exception"
  Error : "Process Error Message"  
 OK 
Here the "Process Error Message" is the message you specified while creating the process.
e.g. : Process failed : #SQLERRM_TEXT#

In coming post, I will explain the way you can avoid redirecting to error page. This way you can show the exception on same page in a dialog box. This is good, isn't it...

Tuesday, January 11, 2011

Dynamically getting and setting Apex session values using AJAX

Problem Statement: After a user enters the account number, the application should display account holder first and last name without a page submit.
Solution: You can create an “On Demand process” and call it using Javascript but how will you pass the Acc. no. value to this process to use and then how will you print the value back on the page returned by the process. It means somehow you have to store the page item value in session for process to use and then take the result back from session/process to display. I am exposing this code (setting and getting values from session) for you to reuse it anywhere in apex as a library.

Here I am creating a dynamic action on lost focus of Acc. No. page item with 3 true actions. 
The first true action will store the value for Acc. No. in session.
            javascript:setSession([‘P1_ACC_NO’]);

The second true action will execute your PL/SQL code to fetch the value for first and last name using account number stored in the session.
            BEGIN
                        //Put your logic here to fetch first and last name
                        :P1_FIRST_NAME := ‘Nitesh’;
                        :P1_LAST_NAME := ‘Kumar’;
            END;
The third true action will get the value for first and last name from session and assign it to the page items for display.
javascript: getFromSession([‘P1_FIRST_NAME’,’P1_LAST_NAME’]);

Now, all you need is to copy the below javascript in your page.

/* This method stores the value of source items into the session. If for some reason you need different items to get the value from source then specify the targetItemList.
*/
function setSession(sourceItemList, targetItemList) {
  var source = sourceItemList.toString();
  var target;
  if(targetItemList) target = targetItemList.toString();
  if(source) {
    source = source.split(",");
    if (target) {
      target = target.split(",");
    }
    var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=V_DUMMY',&APP_PAGE_ID.);
    for(i=0; i < source.length; i++) {
      if(target) {
       get.add(target[i], $v(source[i]));
      }else {
      get.add(source[i], $v(source[i]));
      }
    }
    gReturn = get.get();
    get = null;
  }
}

/* this method get the value for page items from session and set to the corresponding DOM object for display.
*/
function getFromSession(itemList) {
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=getFromSession',&APP_PAGE_ID.);
  get.add('P1_SESSION_ITEMS', itemList.toString());
  gReturn = get.get();
  if(gReturn) {
   var itemValues = gReturn.split(",");
   for(i=0; i < itemValues.length; i++) {
            $s(itemList[i], itemValues[i]);
   }
  }else {
   for(i=0; i < itemList.length; i++) {
            $s(itemList[i], '');
   } 
  }
  get = null;
}

Note that the method getFromSession() is calling an “On Demand PL/SQL Process” getFromSession and passing the list of value to be fetched through a hidden item “P1_SESSION_ITEMS”.
Therefore your will have to define one hidden page item named P1_SESSION_ITEMS and the “On Demand Process” named getFromSession. The code for this process is as below.

BEGIN
DECLARE
TYPE t_array IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
i       number :=0;
pos     number :=0;
v_str  varchar2(500) := :P1_SESSION_ITEMS;
p_delim char := ',';
strings t_array;
return_str  varchar2(500);
BEGIN
       pos := instr(v_str,p_delim,1,1);
       if (pos = 0) then
        strings(1) := v_str;
       end if;
       WHILE ( pos != 0) LOOP
          i := i + 1;
          strings(i) := substr(v_str,1,pos-1);
          v_str := substr(v_str,pos+1,length(v_str));
          pos := instr(v_str,p_delim,1,1);
          IF pos = 0 THEN
             strings(i+1) := v_str;
          END IF;
       END LOOP;
      
      for i in 1..strings.count loop
            if return_str is null then
               return_str := APEX_UTIL.GET_SESSION_STATE(strings(i));
            else
               return_str := return_str || ',' || APEX_UTIL.GET_SESSION_STATE(strings(i));
            end if;
      end loop;

htp.prn(return_str);

END;
END;

I am sure this information will save your time in creating an interactive web page using apex.