Tuesday, April 2, 2013

How to make each item in stragg list display on separate line



The stragg database function is useful for converting a subquery returning multiple rows into a single column containing a comma separated list of values.

I often use this function to display one of the columns in a row of data.  I recently had to format this so that each stragged item in the list displayed on it's own line.

This is the technique I used

The example subquery below will return a comma separated list of labels for a customer.  I'm only showing this one column selected for brevity but this would be one of many in the select clause.
The code in red was added to append a line feed character "chr(10)" to the beginning of each element in the list.  The substr(val,2) removes it from the 1st element so that it doesn’t begin with a line feed.


SELECT....
(SELECT  substr(stragg(chr(10) || label  ),2)
      FROM customer_label
      WHERE customer_label.account_id  = account.account_id
 ) labels,
 .... 

 FROM ....

 
Line feed characters are normally ignored in html but you can force them to be honored by applying a style of “white-space: pre-line”

add a style of pre to your output text within your table column


   <af:outputText value="#{row.label} styleClass="pre" />


The pre style is defined in SkinBasic.css (or whatever name is your custom skin)

/*** make line feed characters wrap.  For displaying line feed in table column ***/
.pre
{
  white-space: pre-line;
}

You could have also used inline style 
 <af:outputText value="#{row.label} inlineStyle="white-space: pre-line;" />



Thursday, March 7, 2013

How to Filter a table (QBE) using javascript

I recently had a requirement to double click some text on the page that would cause a table elsewhere on the page to populate one of the table column headers (Query by example) inputs and filter the table.   This can be done programmatically in a backing bean.  Here is a link to a forum thread for doing that and a sample can be downloaded from this page or directly with this link Programmatically Manipulating a Table's QBE Filter Fields.   A different solution I came up with performs this action on the client side and was pretty simple to implement.   I was able to add the code for doing this to the jspx file while the server was running and test it without even having to restart the server.  I performed the following steps

  • Create a filter facet for the column you need to populate.  Add an inputText inside this facet and set it to be a clientComponent so that you can retrieve it on the clientSide by Id. (example)

                 <f:facet name="filter">
           
             <af:inputText value="#{vs.filterCriteria.MyColumnName}"
                                  simple="true"
                                  binding="#{requestScope.myColumnName}"
                                  id="ncode" clientComponent="true"/>
      </f:facet>
  • Add javascript method to a .js file loaded by your page that looks like this

    function filterByValue(){
      var flter= AdfPage.PAGE.findComponent(
    myColumnName); // retrieve filter input text
      flter.setValue("SomeValue")'  // populate the filter with your value
      // parent of the filter component is the table component
      flter.getParent().getPeer()._handleFilterCellAction(); // this submits the value (like hitting enter)
    }

     
  • The above code assumes there is a variable on the page named myColumnName contain the full component id of the inputText you are populating.  There are various ways to get this ID.   I used the technique described in my other blog posting here and set it using this script at the top of the page.  Note that ColumnName is an arbitrary name given to the component binding and is bound to requestScope so that it can be set into this javascript variable when the page loads.

    <trh:script text="var
    myColumnName= '#{ClientIdMap['requestScope.myColumnName']}';" /> 
  • Add a client listener to the component that you want to double click on

    <
    af:clientListener method="filterCollectorComments"  type="dblClick" /> 
That's is everything needed.  Double clicking the component will populate my filter table header and submit it.

Monday, March 4, 2013

Debugging tip

I have noticed that sometimes when stepping through ADF code in the debugger, there is a long lag of 2-5 seconds each time I step.   Other times it is snappy and I can quickly step over each line of code with no delay.   I recently discovered that this lag is caused by having the ADF Structure Panel visible (See Image).   This panel in the lower left corner contains some useful information if you need to look at it but if visible it takes a long time to refresh each time you stop at a breakpoint,.   So to speed up your debugging simply switch to another tab so that the ADF structure Panel is not visible and you can step through code a whole lot smoother.   You may not notice this if you have a simple structure but our page had a number of nested regions that was causing delays when stepping through code.

Friday, March 1, 2013

Working with multiple branches of your code base

It's often necessary to run a different branch of your code base in Jdeveloper.   For very large projects it is problematic to use the same installation of Jdeveloper for this because after you close your project and load the project for the other branch you must perform a full clean and build because the project will have the same name and deployment location.   This can be a time consuming process.  Even after this is done I have seen cases where Jdeveloper uses cached classes or meta data from the other project which makes me feel uncomfortable that I am truly running all code from the new branch.   To get around this in the past I've installed a separate instance of Jdeveloper for each code base that I might need to run.   When you do this you must make sure the new installation does not share the System directory with the original installation or you will have the same problem.  The System directory.  (the one with a name like  system11.1.1.4.37.59.23)  contains the Weblogic instance and deployment files.   It gets generated the first time you start up the server.  Deleting the System directory is one way to get your jdev installation back to a good state if it's configuration somehow gets messed up and is quicker than reinstalling.  The System directory will be placed in the location defined in an environment variable or else by default in your windows user profile.  See Oracle's documentation here on how to do that.   Another way to assure the system directory is unique is to edit the startup shortcut and add the -su parameter to the command line startup (i.e Jdeveloper.exe -su).  This causes the system directory to be placed in the same directory where Jdeveloper is installed.  I've used the -su parameter forever on all of my installations because it makes it easy to find the system directory for any particular installation.   If you only need two instances of Jdeveloper you can get away with using one installation and two shortcuts, one with -su and one without and you will be using two different system directories.  Starting up Jdeveloper with either shortcut will load the project you last had open using that shortcut and the system directory will contain a separate build so you won't need to perform a clean and rebuild all each time you switch branches.  Just close Jdeveloper and run the other one.

Instead of installing extra jdeveloper installations I recently discovered (as mentioned in the linked documentation above) that I can specify the system directory on the command line.  So I created a separate shortcut for each branch and they all use the same jdeveloper installation but different system directories  


The link looks like this.
C:\JDev11g\jdeveloper\jdev\bin\jdev.exe -J-Dide.user.dir=c:/mywork/system/dev

Another link might look like this
C:\JDev11g\jdeveloper\jdev\bin\jdev.exe -J-Dide.user.dir=c:/mywork/system/qa

Tuesday, February 19, 2013

Spell checking input fields

Most browsers now (except for IE) have a built in spell checker that underlines unrecognized words as you type.   Firefox by default only underlines words in multi-line input fields but you can always right click and select spell check from the context menu to spell check a single line af:inputText element.   There is a configuration setting in firefox that can be turned on to enable spell checking on single line input fields but it's not easily accessed and since single line inputs are often used for common names, addresses, products etc that are not in the dictionary you may not want this turned on globally.   These browsers now support the spellcheck attribute on the html input element that you can use to force a field to be spell checked or else turn off spell checking.  We had a need to turn this on for a single line input field in firefox so I was able to make it work by adding a client listener to the af:inputText element that calls a javascript function when the element gets focus that sets the spellcheck attribute to true.  The client listener looks like this.

<af:clientListener 
method="(function(evt){evt.getSource().getPeer().getDomElement().setAttribute('spellcheck','true');})"
type="focus" />

Note that I used an inline function but you should put this in a .js file to reduce page size, especially if it is used for more than one input element on the page.

Monday, February 18, 2013

Using ANSI Joins with ADF business components


If you select the Model project and bring up project properties, there is a project setting (under business components/View Objects)  you can change to make entity based view objects use ANSI style joins instead of the Oracle style.  
i.e.
instead of having this in the where clause
AND (XrefSalesTransactionAccount.ACCOUNT_ID = Account.ACCOUNT_ID(+))
You would have this join clause predicate
LEFT OUTER JOIN ACCOUNT Account ON (XrefSalesTransactionAccount.ACCOUNT_ID = Account.ACCOUNT_ID)


I see three advantages to using ANSI style joins.  
  •  They are clearer and easier to read 
  • They give separation between the table joins and the where clause criteria so that when you add extra where clause criteria it doesn’t get mixed in with the table join criteria, so is easier to change in the future.  i.e.  everything in the query clauses input box will be custom sql added by the developer, not mixed in with the join criteria put there by jdeveloper. 
  •  If you add new entities to the view in the future, jdeveloper won’t have to update the where clause so it will be easier to keep the original where clause intact when you get the message “The query has a   custom where clause.  Would you like to overwrite the customized where clause by the default where clause?  If not you may need to manually add a join clause for the new entity in the where clause”.  There seems to be a bug in jdeveloper in that if you select “No”  it still deletes the custom where clause, except now you can save off the whole thing and paste it back in without having to separate the custom part from the generated part.
One problem you may discover after changing this setting is that if you open any pre-existing view object and edit anything (including hint text),  it will convert it to use ANSI joins, however it will leave the original "where" clause predicates in place containing the old join sequence.   The query may still work unless it contained outer joins but there will be redundant criteria in the "where" clause that should be deleted to make it clean.

Also if you have a view links that was customized to contain the Oracle outer join (+) syntax.  This type of view link won't work if the view being linked uses ANSI joins because the link is created using bind variables in the where clause and you can't combine Oracle and ANSI joins.  You could potentially use this type of link to a ANSI style view if the view uses expert mode and wraps the entire query in a extra select clause so that the where clause created by the link is added outside of the inner query but I don't recommend using Expert mode.

I  discovered one bug when using ANSI joins that occurs when you attempt to create a view object containing a Many-to-Many association between two entities.   A Many-to-Many association is when you have a intersecting xref table that joins the two tables together.   ADF will generate invalid SQL when you attempt to use this association in your view object and you will get  
(java.sql.SQLSyntaxErrorException) ORA-00933: SQL command not properly ended.
The work-around is to include the intersecting entity in your view object and use a "one to many" association instead of "many to many".  I have reported this bug to Oracle and verified it exists in 11.1.1.4 and 11.1.1.6
update: Oracle has confirmed and opened Bug 16781328 : ERROR ORA-00933 USING ANSI JOIN IN BUSINESS COMPONENTS.


I don't like the fact that the ANSI join style is a project setting and not a view object setting.   I think the determination of style should be on a case by case basis to mitigate the problems described above. It's easy enough to switch the setting on and off but I shouldn't have to.

I would be interested to hear from others who have used ANSI joins in ADF so please reply to this posting if you've had any experience or problems using them.