ExcelDocumentType Code Update ...

I've made an update to the ExcelDocumentType code making it easier to create custom worksheet headers and footers.  The changes and some new examples can be found in the word document "Create Excel Spreadsheets with PL/SQL" that is bundled with code.  The link to the code bundle is:

As always, if you have questions or comments, shoot me an email.

10:27:53 AM    

The Oracle Report Bean

   The Oracle Report Bean is a cool little bit of code I developed this week that will let you to execute an Oracle Report from within your JEE or plain old Java application.  The bean allows you to configure all of the report execution parameters and contains methods to execute the report as a printed report or have the report streamed back to the client if the chosen format (PDF, RTF, HTML, XML, etc).  The code basically constructs the URL you need to access the Oracle Reports servlet (rwservlet). I'm actually using the Oracle Report Bean as the interface to Oracle Reports in my current ADF Faces project.

10:36:48 PM    

ADF Faces: How To Use a Single JSP for both INSERTING and UPDATING

   In this entry, I'll go over my technique for using a single JSP for both inserting and updating data.  This technique utilizes custom methods in the target ViewObjectImpl class, and Page Definition action methods in the Page Definition associated with our ADF/JSF JSP.  There are probably other ways to do this, but this one is fairly straight forward. (Note: I believe Steve Muench recommended a similar technique using methods in the ApplicationModuleImpl class)

    /***
     * This method will be used to pre-populate the view object using a passed value.
     */
    
     public void queryViewById(String p_id) {
    
      /*setp_emp_id is ADF generated method for setting the bind variable
        value defined in my View Object (p_emp_id). */

       setp_emp_id(p_id);
       executeQuery();
      
     }

The second method (used to clear the data from the view object) looks like this:

    /***

     * This method will clear any data out of the existing VO.

     */

    public void clearView() {

      if (getWhereClause() != null) {

        setWhereClause(null);

      }

      executeQuery();

    }

Add these two methods (modified to fit your View Object) to the ViewObjectImpl class associated with your View Object.  Some might say that you could combine the two methods (since both will be end up being called to put the page in update mode).  I chose to keep them separate in case I wanted (or needed) to call one without the other at some point.

Preparing the Page Definition File

ADF Faces JSPs use an XML based Page Definition file to bind data to the user interface and to perform some actions prior to rendering the page (overly simplified definition ...).  Each View Object referenced by an ADF Faces JSP component is represented in that page's Page Definition file.  ADF Faces comes with some canned page actions (such as Create and Delete) that can be "dropped" into the page definition file and then applied to event (or actions) that occur later in JSP page.  We can also create custom actions (known as method actions) that are mapped to methods we have created.  The two methods we created in the last section (queryViewById and clearView) will be included as custom method actions the Page Definition file for our ADF/JSF JSP page.  The method action entries go in the "bindings" section of the page, and look like this (I have my own View Object referenced in the example):

    <methodAction id="clearView"
                  MethodName="clearView"
                  RequiresUpdateModel="true" Action="999"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.MyemployeeView1"/>

    <methodAction id="queryViewById"
                  MethodName="queryViewById" RequiresUpdateModel="true"
                  Action="999" IsViewObjectMethod="true"
                  DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.MyemployeeView1">
      <NamedData NDName="p_id" NDValue="#{param.p_emp_id}"
                 NDType="java.lang.String" />
    </methodAction>

Notice the "#{param.p_emp_id}" in NDValue attribute above.  Any request parameter passed to the your page can be accessed using "#{param.<parameter name>}".  The only caveat is if you use the <f:param> tag in conjunction with a command link or command button and the navigation rule behind your navigation action specifies a "redirect" ... the parameters will not be passed.  Here is a quick break down of the attributes for the "methodAction" tag above:

• The "id" attribute represents the identifier of this methodAction tag as it relates to other components (tags) in the current Page Definition file.

• The "MethodName" attribute defines the binding name for this method action when called from the "invokeAction" tag ( tag that is responsible for executing the code).
• The "RequiresUpdateModel" attribute specifies whether or not the model needs to be updated prior to executing the method.
• The "Action" attribute identifies the internal class for which the data control is created.  Always seems to be 999 for custom class.
• The "IsViewObjectMethod" attribute indicates whether the method being invoked is defined within a View Object.
• The "InstanceName" attribute points to the View Object instance as defined by the application data control.
• The "NamedData" tag is used to map any parameters that the method might take. It contains attributes "NDName" (method parameter name), "NDValue" (value being passed), and "NDType" (data type of the parameter).

9:50:12 PM    

Recursive PL/SQL String Parser

In an earlier blog entry, I posted a PL/SQL package called StringTools.  This package contained a routine called getListElement that would let a user retrieve the nth element in a delimited string.  The routine uses iteration (iterates over the passed string until the desired element at position 'n' is located) to get the desired list element.  I was bored today, so I decided to write a variation of that function that uses recursion instead of iteration.  The recursive version has only eleven lines of code (not counting variable declarations and block declaration tokens).  It's fast and useful! 

Here is the code ... give it a try!

/*

   Recursive PL/SQL function that returns the nth element in a delimited String.
   The default delimiter is a ',' (comma) and the level indicator defaults to 0.

*/

CREATE OR  REPLACE FUNCTION getStringElement(p_string    VARCHAR2,
                                                                                      p_element   NUMBER,
                                                                                      p_delimiter VARCHAR2 := ',',
                                                                                      p_level     NUMBER   := 0)   RETURN VARCHAR2
IS

   v_string   VARCHAR2(2000) := NULL;
   v_element  VARCHAR2(2000) := NULL;

   v_level    NUMBER(4)      := 0;

BEGIN

   v_level := p_level + 1;

   v_element  := substr(p_string||p_delimiter,1,instr(p_string||p_delimiter,p_delimiter)-1);

   IF ((v_level >= p_element) OR (v_element IS NULL)) THEN

      RETURN v_element;

   ELSE

      v_string := substr(p_string||p_delimiter,instr(p_string||p_delimiter,p_delimiter)+1,length(p_string));

      RETURN getStringElement(v_string,p_element,p_delimiter,v_level);

   END IF;

END;
/

Example:

The statement:

SELECT getStringElement('This is an interesting test of recursion in PL/SQL',7,' ') from dual;

will return the value "recursion".

5:59:55 PM    

ADF Faces JavaScript Hack: How to Move a Generated Tag Event Handler

You may have noticed that ADF Faces generates some very specific JavaScript code for handling page level (tag level) events (onclick, onChange, onBlur, etc ...).  These event handlers "handle" all of those cool features such as "Partial Submit" and "Auto Submit".   Most of the time, we as developers (and users of ADF) have no direct control over where these framework generated event handlers are placed, or specific control over when they will fire.  The focus of this entry is on how to move a generated event handler from one event to another on the same tag.  For example:  Let's say that you want to implement a feature that will allow a user to type in an employee Id and then have the rest of the employee information (name, etc ..) auto populate. No problem, you just bind a method from a backing bean to a ValueChangeListener and turn on Auto Submit and you are done!  However, what if you also want to use a command link to implement an LOV dialog on the same data entry field and use the data (i.e. the employee id) to limit the results of the LOV?  Now you have a problem.  If you enter data in the employee id field, and then try to navigate to your LOV link, the value change event (onChange) fires before the LOV triggers and nullifies the LOV event.  The solution to the problem is obvious ... just change the triggering event from onChange to onKeyPress (for the auto population ... you would also need to add code to check for the triggering "Hot Key").  But wait ... that code was generated by ADF Faces.  We (the developers) have no direct control over where they place their code.  Now, the problem becomes slightly more challenging (or interesting).  The solution (one solution anyway ...) is to write a JavaScript function that will move it for you and leave the original code intact.  My version of the function looks like this:

function moveEventFunction(p_id,p_curr_event,p_new_event,conditionalFunc){

 

   /* Get instance of the tag we are modifying. */

   var formItem = document.getElementById(p_id);

 

   var eventFunc = null;

  

   /* Get text of target event handler code */

   var existingEventCode = formItem.attributes[p_curr_event].value;

 

   /* Only execute the move if the targeted event handler is populated */

   if (existingEventCode.length > 0){

  

      /* Convert text of existing event handler back into a working function */

      var existingFunction = new Function(existingEventCode);

  

      /* Create a function that will assign a new event handler to the desired item event.

       * This needs to be dynamic since we can only make a direct assignment under "normal"

       * circumstances.  Ex. node.onclick=  or node.onchange

       */

 

      var addNewEvent = new Function("node","eventfunc","node."+p_new_event+"=eventfunc;");

     

      /* We assign "nothing" to the existing event handler. This must be dynamic

       * for the same reason as the function above.

       */

 

      var removeExistingEvent = new Function("node","node."+p_curr_event+"='';");

  

      /* If a conditinal function (boolean function that can halt the event ...)

       * is provided, then wrap it around the existing code.  Otherwise, execute

       * the original handler under the new event.

       */

 

       if (typeof(conditionalFunc) != "undefined"){

          eventFunc = function(){ if (conditionalFunc()){ existingFunction()}};

       }else{

          eventFunc = existingFunction;

       }

     

      /* Add the new event handler */

      addNewEvent(formItem,eventFunc);

     

      /* Remove the old event handler */

      removeExistingEvent(formItem);

   }

}

 

The function, moveEventFunction, will move or swap the code assigned to one event handler and move it to a new event handler on the same node (or tag).  The function also gives the developer the option of wrapping a new conditional (Boolean) function around the original event handler code.

 

Now that we have a solution, how do we apply it?  You have a couple of options.  First, place the call to moveEventFunction at the bottom of the page (between script tags).  It will fire after all of the ADF/JSF  HTML has been rendered. 

 

<script>

     moveEventFunction(<id of target tag>,<existing event name>,<new event    name>,<optional conditional function>);

</script>

 

 

However, if you plan on making the target tag a partial target (dynamic refresh), you will lose the swap.  The second option handles this situation.  Place the call on the onFocus event of the target tag.  Doing so guarantees it the swap.

 

onFocus="moveEventFunction(this.id,<existing event name>,<new event name>,<optional conditional function>);"

 

The conditional function (the last parameter) can perform multiple tasks prior to executing the main function (original function).  In the case of the scenario above, the conditional function would check for the "Hot Key" that triggers our auto population. However, it must return true or false at the end of its execution.  The conditional function needs to be defined in the manner:

 

   var fnc_MyConditionalFunction = function MyConditionalFunction(){ ...}

 

This will allows allows you to pass the actual function as a parameter without it actually being executed (or evaluated) . Instead of passing in MyConditionalFunction, you pass in fnc_MyConditionalFunction.

 

(Another option that isn't JavaScript related might be to get an instance of the UIComponent prior to page rendering and make the swap ... haven't tried that yet.)

 

10:22:48 AM    

ADF Faces - How To Get a DBTransaction Object Anytime You Need One

    If you decide to execute a SQL statement or some PL/SQL code from with your ADF Faces application outside of the ADF BC framework (i.e. without using Entity Objects and View Object), you will need access to a JDBC connection.  Do you need to instantiate your own connections, or maintain a separate connection pool?  The answer is no!  ADF BC provides us with (abstracted) access to the JDBC connection object that is associated with our current application session.  Access is provided through the oracle.jbo.server.DBTransaction class.  You can get an instance of the DBTransaction object from several places within the ADF BC Framework: ViewObjectImpl, TransactionEventImpl, EntityImpl, and ApplicationModuleImpl.  All of these classes have a method called getDBTransaction() that return an instance of the DBTransaction object.  Armed with this knowledge, how do we go about getting an instance of DBTransaction anytime we want one?  By "anytime time we want one", I mean externally from a View Object or Entity Object instance.  The answer is pretty simple.  We just need to access to an instance of the current ApplicationModule.  Using the following code, you can get a DBTransaction object anytime you want:

    /***
     * This method returns the current instance of the session DBTransaction object.
     * The method below is implemented from a Singleton Object for utilitarian purposes.
     */
    public static synchronized DBTransaction getDBTransaction(){
   
       FacesContext ctx = FacesContext.getCurrentInstance();
      
       ValueBinding vb            = ctx.getCurrentInstance().getApplication().createValueBinding("#{data}");
       BindingContext bc          = (BindingContext)vb.getValue(ctx.getCurrentInstance());

       //Use your own data control name when creating the DataControl object
       //Look in the DataBindings.cpx file ... the id attribute of the BC4JDataControl tag
       DataControl dc             = bc.findDataControl("MyApplicationModuleControl");
       ApplicationModuleImpl am   = ((ApplicationModuleImpl)(ApplicationModule)dc.getDataProvider());
      
       return am.getDBTransaction(); 
      
   }

   Place the code listed above in a utility class.  I prefer to use a static class or Singleton. 

8:56:17 PM    

ADF Faces: Retrieve and/or Set values in a SelectOneChoice Component backed by a RowSetIterator

This entry covers how to retrieve values from a SelectOneChoice component that is backed (populated) by a ViewObject as defined in a ADF Faces page definition file.

   I've been working on a fairly large and complex ADF Faces project for my current employer.  I often find myself having to both retrieve and set values in SelectOneChoice components in my backing beans.  I was initially confounded by the fact that the HTML generated by the component did not include my own code values behind the select options. Rather, each option (option tag) uses a numeric value instead of the actual code you would normally see if you were creating the HTML yourself (such as "NC" as the code for description "North Carolina").  After some digging, I found that the numbers correspond to the Iterator index values of the Iterator that is bound to SelectOneChoice component.   By the way, setting the valuePassThru attribute on the component to true did not work for some reason.

   Armed with this new knowledge, I was able to create two utility methods that reside in my base (utility) managed bean class. 

  The first method, getSelectOneChoiceValues, retrieves both the code (the actual code from your database or view object) and the description value from the Iterator that is bound to the SelectOneChoice component given the index value.  Here is the code for first method:

    /***
     *  This method returns the code and desc values from a SelectOneChoice.
     *  Parameters
     *  (1) p_iteratorIndex - represents the index value of the iterator row we want. You get
     *                        this value from bound ADF component.
     *                        Ex. (Integer)boundSelectItem.getValue()
     *
     *  (2) p_iterator      - Name of the Iterator that populates the SelectOneChoice component
     *  (3) p_code_colname  - Column attribute name of the column containing the code value (get this
     *                        from the PageDefinition file for the screen, or the ViewObject that
     *                        is bound to the Iterator.)
     *  (4) p_codedesc_colname - Column attribute name of the column containing the code desc (get this
     *                           from the PageDefinition file for the screen, or the ViewObject that
     *                           is bound to the Iterator.)
     *  (5) p_noselection_val  - Set this value to "true" if you chose to add a "No Selection" row
     *                           or null value row to your selection object. This row will become the
     *                           zero index row.  This "null" value row is not represented in the
     *                           Iterator and throws the index values off by 1.
     *
     *  The return value is a HashMap, but you could create a simple class for this as well.      
     */
    protected HashMap getSelectOneChoiceValues(int p_iteratorIndex,
                                               String p_iterator,
                                               String p_code_colname,
                                               String p_codedesc_colname,
                                               boolean p_noselection_val)
    {
        HashMap hm_lovVals = new HashMap();
        String  lovTypeCode = null;
        String  lovTypeDesc = null;
        int     noSelectIncrementor = 0;
       
        if (p_noselection_val){
            noSelectIncrementor = 1;
        }
       
                                      
        try{

           // The code for "getRowSetIterator" can be found in the blog entry I published directly
           // before this entry or Google "Jason getRowSetIterator"

           RowSetIterator lovIter       = getRowSetIterator(p_iterator);
       
           Row iterRow = null;
       
           iterRow = lovIter.getRowAtRangeIndex(p_iteratorIndex-noSelectIncrementor);
       
           lovTypeCode = iterRow.getAttribute(p_code_colname).toString();
           lovTypeDesc = iterRow.getAttribute(p_codedesc_colname).toString();
          
        }catch(Exception e){
            System.err.println("Error looking up values for LOV iterator "+p_iterator);
        }
       
        //the keys "code" and "desc" should not be hard coded values in your production code ...
        hm_lovVals.put("code",lovTypeCode);
        hm_lovVals.put("desc",lovTypeDesc);
       
        return hm_lovVals;
    }

    The second method, getSelectOneChoiceIndex, performs the opposite function.  Given code value and code column, it returns the actual index value.  This allows you set the value of SelectOneChoice component on the screen ( Ex. selectionComponent.setValue(indexVal); ).  Here is the code for the second method:

    /***
     *  This method returns the index value for an item in a SelectOneChoice.
     *  Parameters
     *
     *  (1) p_iterator      - Name of the Iterator that populates the SelectOneChoice component
     *  (2) p_code_value    - Code value whose index we are looking for.
     *                        Ex. We pass in "NC" and get the index for the row in the Iterator
     *                        that contains the code "NC".
     *
     *  (3) p_code_column   - Column attribute name of the column containing the code value (get this
     *                        from the PageDefinition file for the screen, or the ViewObject that
     *                        is bound to the Iterator.)
     *
     *  (4) p_noselection_val  - Set this value to "true" if you chose to add a "No Selection" row
     *                           or null value row to your selection object. This row will become the
     *