TNS

Have been using the abbrevation TNS in Oracle context for last four years but never cared about what actually it meant. Well by chance I learned today that TNS stands for Transparent Network Substrate.

IS_NUMBER()

If we you are storing NUMBER and character in a VARCHAR2 column and you want to validate if the value is number or not you can use the following regex based SQL.

SELECT 
 nvl(regexp_substr(:test_number,
     '^(\+|-)?([0-9])*(.)?([eE])?(\+|-)?([0-9])+$'),              
    'Not a number') 
FROM   DUAL 

You can read more about it in asktom

OA Framework Custom Component: Pivot Table 1.1 beta version

I am in the process of creating a custom component for OA Framework called Pivot Table which has similar features of Microsoft Excel Pivot Table, but UI more inline with OA Framework. I am releasing the first (beta) version. It is meant to be used with table region, preferably a read only table. But it can be used independently. Currently it supports only single level of summary and needs to be added to your page from JDeveloper. Future vesions will have multiple levels of summary and ability to add it through personalization. Following are the steps to use it in your page.

1. Download the beta version.
2. Follow the installation steps in ReadMe. It is simple.
3. Add a stackLayoutRegion below the table region region of your page.
4. For the above stackLayoutRegion, set the extends property as
/ramble/oracle/apps/qa/component/pivot/ui/webui/PivotSharedRN
5. Set User Defined: Attribute1 in Property Inspector as the View Object Instance name of the table region.

Pivot Table has two regions
Pivot Parameter Region:
It will have four picklist.

• Row - Pivot row which will show all attributes of Source VO set in step 5. Can be null.
• Column - Pivot column which will show all attributes of Source VO set in step 5. Can be null
• Data - Pivot data which will show all attributes of Source VO set in step 5. Has to be Number attribute if you want some statistical function from pivot table.
• Function - Pivot data function. Currently supports COUNT, SUM, AVERAGE, MIN and MAX.

Result Region
Based on the parameter region and if the pivot source specified in step 5 is executed, user can press "REFRESH" to see the pivot summary.

I am working on final version of release 1.0 and will come up with better documentation soon.

OAF v ADF: Finally its official

I always had tough time convincing my management and sales folks (who in turn must convince customers) to prefer OAF over much hyped ADF for custom extension of Ebusiness Suite. Well, to be honest there was one part of me, a teeny tiny part, that was skeptical and sometime cynically pointing out the conservative nature of me sticking to the technology I know rather than learning or adopting the new. Oracle finally managed to release a white paper on it. (Got it from Steve Chan's blog) You can download it from metalink.

I am happy that Oracle finally felt it important to release something of this sort. Some of the key highlights
- OAF and ADF are not entirely different. Both uses some common components like BC4J, UIX etc
- If you are planning to integrate with Ebusiness suite, stick with OAF.
- If your application is entirely new and independent of ebusiness suite then use ADF.

Some of my observations

1. The disclaimer, quoted below, kinda scared me initially, but I guess its just some legal stuff.

"The following is intended to outline our general product direction. It is intended
for information purposes only, and may not be incorporated into any contract. It is
not a commitment to deliver any material, code, or functionality, and should not
be relied upon in making purchasing decisions. The development, release, and
timing of any features or functionality described for Oracle’s products remains at
the sole discretion of Oracle."


2. You have to believe things said in the article since it is from Oracle. But still I was curious about author of this white paper. Sarah Woodhull, not heard that name before but googled it found that she is Senior Product Analyst with ATG group. The co-author, Padmaprabodh Ambale, is famous in OAF circles and I have interacted with him many times during my stint in Oracle development and he took one of the OAF training class. (Ironically in that class like three years ago he kinda mentioned that OAF is gonna go away soon)

3. Oracle evades one key question. Can we easily migrate the custom extensions to fusion middle ware? White paper says that it is "possible" to migrate the UI (OK thats convincing) and Controller migration is non-trivial (How???) and easy migration for BC4J (phuh I guessed it).

4. Well guys it is final ..but not final yet. Wait until ADF11g is out. Oracle might change their mind.

"When ADF 11g becomes production, we will publish a revised paper that compares OAF with
ADF 11g, and discuss development against E-Business Suite."


5. OAF AND ADF – A DETAILED COMPARISON Table looks cool and totally favors OAF based on number of "X" marks against ADF. But I think the paper left out of lotsa cool things of ADF which would have cause lot many "X" against OAF. So it seems the table was made after making the decision or should I say recommendation.


6. It has "X" for PLSQL DML Operation Support for ADF. I dont know that much ADF, but thats scary ....


Overall I am sure that lotsa customers are gonna get huge benefits out of this white paper ....

Bound Values in OA framework - II

Lets take a closer look at the interface DataObject.

From javadoc

The DataObject interface provides an extremely simple API for retrieving arbitrarily structured data. All "queries" are based simply on a selection string. It is entirely up to an implementation of this interface to define the syntax for these strings.

It has only one method.

public java.lang.Object selectValue(RenderingContext context, java.lang.Object select)


OA Framework creates a named DataObject for each data source and then caches the DataObject on oracle.cabo.ui.RenderingContext. A data source is identified by the view usage name set on the web bean. OA Framework then gets the value from the DataObject using the view attribute on the bean as a lookup key.

OAWebBean.getValue() >> delegates to OADataBoundValue.getValue(RenderingContext context) and passes rendering context as parameter >> which lookups DataObject from
renderingcontext based on view usage name setup in the web bean and calls that DataObject.selectValue () with view attribute name as lookup key.

For a table bean OA fraemwork creates DataList which is a list of DataObject. ReneringContext.getCurrentDataObject() will return the DataObject for the current processing
row.

So code in OADataBoundValue.getValue(RenderingContext context) will look like

  DataObject dataobject = renderingcontext.getCurrentDataObject(); 
if(dataobject == null)   {
     // will return view usage name
     String s = ((OAWebBeanData)mOAWebBean).getDataObjectName();
     if(s != null)     {  
      // first parameter is name space URI
        dataobject = renderingcontext.getDataObject(
                             "oracle.apps.fnd.framework",
                              s);
     }
 }
   // gets view attribute name  
String s1 = ((OAWebBeanDataAttribute)mOAWebBean).getDataAttributeName();  
if(s1 != null && !s1.equals(""))    
   return dataobject.selectValue(renderingcontext, s);

Most of the above things are just good to know kind of information. So lets take a look at this in OAF developer perspective on how to use this.

Typical use cases are
(a) To programaticaly bind a web bean property like "Rendered" or "Prompt" etc to a view object
(b) To programaticaly set fire action event parameters.

STEP 1: Find out whether the web bean takes BoundValue as parameter for the property/attribute.
STEP 2: Find the right BoundValue implementation to pass it to the web bean. For example for the USE CASE (a) we will use OADataBoundValueViewObject and for USE CASE (b) we will use OADataBoundValueFireActionURL.
STEP 3: Determine the parameters required to be passed to the BooundValue constructor.
STEP 4: Create the corresponding BoundValue object and pass it to the setAttribute() method.

Example 1: (Based on USE CASE a: To set prompt of web bean to a view object)

OAMessageStyledTextBean mTextBean =
     (OAMessageStyledTextBean)webBean.findChildRecursive("TextID");
OADataBoundValueViewObject promptBV =
   new OADataBoundValueViewObject(mTextBean, "VoAttribute");
mTextBean.setAttributeValue(
       oracle.cabo.ui.UIConstants.PROMPT_ATTR, promptBV);

Example 2: (Based on USE CASE b: To set fire action event programaticaly )

        OAMessageStyledTextBean b =
        (OAMessageStyledTextBean)createWebBean(
                                    pageContext,
                                    MESSAGE_STYLED_TEXT_BEAN,
                                    "NUMBER",
                                    "TextID");       
    b.setViewUsageName("ViewusageName");
    b.setViewAttributeName("ViewAttributeName");
    Hashtable params = new Hashtable(1);
    OADataBoundValueFireActionURL fireEventBV = new
      OADataBoundValueFireActionURL(webBean,"{$ViewAttributeName}");
    params.put("FireEventParameter",fireEventBV);
    b.setFireActionForSubmit("fireEvent",null,
                             params,false,false);

TemplateCO in OA Framework

RAD way of assigning a controller to a region is to right click on the region node in Structure Pane and selecting "Set new Controller" option. OA framework automatically creates the CO with some default code. That default code comes from <JDEV_HOME>\jdevbin\jdev\lib\ext\jrad\config\TemplateCO.java

I modified this template CO to import OAApplicationModule and OAException. You can play around with other templates in this folder (at your own risk).