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 1^st 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;" />

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.

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.

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

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.

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.

Should you use entity based views for read only queries?

Oracle documentation states under section

39.2.2 Consider Using Entity-Based View Objects for Read-Only Data

"Best Practice:

When you need to create a read-only view object for data lookup, you should use the entity-based view object and deselect the Updatable option in the Entity Objects page of the view object overview editor. The approach benefits from the design time editors which aid in generating the SQL query. The alternative of creating an expert-mode view object requires writing a SQL query. Expert mode queries are still useful for cases where Unions and Group By queries cannot be expressed using entity objects."

We've been using ADF 11g since it was in alpha releases and were given the same advice from Oracle at the time.   After several years of use I have come to the conclusion that I disagree with this advice.   We often have to refactor queries due to data model changes and in our agile process when it comes to sizing a user story we generally triple the size for refactoring a view object that is entity based over one that is a read only query.    Many times the result columns did not change, only the query or tables being joined.   If it's a read only query, you just go in and update the SQL and you're done.   When it's a Entity based view more often than not, the developer struggles because it breaks things when you replace one entity with another or change the association between entities.  ADF does not have a refactor option that lets you swap out a view attribute keeping the same name with one in a different entity.   The view might contains a dozen entities each with their own association to other entities and selecting the correct relationship and verifying the desired query is more time consuming than updating the sql yourself.   You always have to double check the sql it generated to make sure it was what you wanted so there's not much advantage to having ADF generate the query for you.  Also the query may have some extra parameters added to the where clause.  When you change any entity in your view, the custom where clause gets dropped, requiring you to save it off before hand and do some comparisons after the change to add the custom "where" clause back.  This requires you to eyeball the "where" clause to determine which part was generated and which part was custom.  The next problem is if there are generated Impl classes for the entity or view object, sometimes these have to be regenerated from scratch because when you change entity relationships the Impl's don't always update correctly and you have to delete them and let ADF recreate them.   Then you must add back any custom code that was added.  For these reasons I now always define the view as non-entity based when creating a read-only query.

Secondly,  for updatable entity based view objects I try to steer clear of using expert mode.  It may be a little harder to create the desired query using normal mode but most queries can be constructed without resorting to expert mode.  We've had view objects using expert mode that have gotten in a broken state when trying to refactor.   The sql works correctly when you run it externally but the attributes don't display their values when used in the UI.  Changing the view back to normal mode did not fix it and the view object had to be rebuilt from scratch to fix.  A time consuming task for large complicated queries containing dozens of columns.

My quote for the day:
The advantage of using a large framework like ADF is that it does a lot of stuff for you.
The disadvantage of using a large framework like ADF is that it does a lot of stuff for you.  (meaning... when it doesn't do it right its hard to fix)