Display CLOB data in Oracle Forms deployed on Oracle E-Biz

1.   Introduction

More and more data is being stored in large object storing fields such as CLOB and BLOB. Some tools such as Oracle Forms do not natively support display of data which is more than 32K in size.

This document discusses how Oracle Forms running in Oracle E-Business environment can display CLOB data more than 32 K in size.

2.   Approach

As discussed earlier, the Oracle forms’ does not natively support CLOB data types. The ‘Text Editor’ object available in Oracle forms can display data of maximum 32K size. Web pages on the other hand have no such limitation and can display any amount of data that is received from the web server.

We can therefore develop a webpage and integrate it with Oracle forms to display the CLOB data. Webpage can be developed as a JSP page which can be called from Oracle forms. But JSP pages need to build and manage their own database connection. Oracle has developed OAF technology for building web pages in Oracle E-Biz, the database connection in OAF is seamlessly passed between various components when the user has logged into Oracle E-Biz.

So our approach would be to develop an OAF page which can display CLOB data and integrate the same with Oracle Forms.

3.   Use Case

We will consider a case where we have to display data from a particular column (which contains the CLOB data) of a particular row in the table. So we can develop a generic OAF page which can be used to display such data where the following data is passed dynamically from the Oracle form to the OAF page

1)    Name of the table

2)    Name of the data column (column which contains the data)

3)    Name of the WHERE column (column which helps in uniquely identifying a row in the table)

4)    Unique ID (value which when matched with the WHERE column, returns a unique row)

5)    A free text string which can be used as a heading on the web page.

4.   Solution

Note: The solution assumes that you are well conversant with OAF concepts, development and deployment.

The solution will be developed using the following OAF, Oracle Forms and E-Biz components.

OAF Page:

Create an OAF page, DisplayClobPG as shown in the diagram below. The main points to note are

1)    The page should have an Item of style ‘MessageStyledText’, which will be used to display the CLOB data

2)    The Data Type of the above item should be ‘CLOB’

OAF Application Module:

Define an AM, ‘DisplayClobAM’ and attach it to the PageLayout region of the page DisplayClobPG. This will just be a placeholder AM with no Entity or View Objects in it.

OAF Controller:

Define a Controller ‘DisplayClobCO’ and attach it to the PageLayout region of the page. The controller will contain the logic to display the CLOB data on the OAF web page. It will interact with the Oracle Form to get the values of ‘Table’, ‘Data Column’, ‘Where Column’, ‘ID’ and ‘Page Title’. Since our aim is just to display the data, the program logic will be added in the ProcessRequest method. The code of the controller is provided below.

import oracle.apps.fnd.common.VersionInfo;

import oracle.apps.fnd.framework.OAApplicationModule;

import oracle.apps.fnd.framework.OAException;

import oracle.apps.fnd.framework.webui.OAControllerImpl;

import oracle.apps.fnd.framework.webui.OAPageContext;

import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean;

import oracle.apps.fnd.framework.webui.beans.message.OAMessageStyledTextBean;

import oracle.jbo.Row;

import oracle.jbo.ViewObject;

/**

 * Controller for Display Clob Page

 */

public class DisplayClobCO extends OAControllerImpl

{

  public static final String RCS_ID="$Header$";

  public static final boolean RCS_ID_RECORDED =

        VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

  /**

   * Layout and page setup logic for a region.

   * @param pageContext the current OA page context

   * @param webBean the web bean corresponding to the region

   */

  public void processRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processRequest(pageContext, webBean);

   

    /* Define variables */

    String clobStr = new String();

    OAApplicationModule am =  (OAApplicationModule)pageContext.getRootApplicationModule(); 

    ViewObject clobVO = (ViewObject)am.findViewObject("clobVO");   

   

    /* Get variables passed to the page */

    String selectColumn = pageContext.getParameter("SELECT_COL");

    String table = pageContext.getParameter("TABLE");

    String whereColumn = pageContext.getParameter("WHERE_COL");

    String id = pageContext.getParameter("ID");

    String title = pageContext.getParameter("TITLE");

   

    /* Construct the SQL Query */

    String selectQuery = "SELECT " + selectColumn +

                         " FROM " +  table +

                         " WHERE " + whereColumn + " = " + id ;

   

    /* Create and get the handled to the VO */

    if (clobVO == null)

       clobVO = am.createViewObjectFromQueryStmt("clobVO", selectQuery);

    clobVO = am.findViewObject("clobVO");

   

    if (clobVO != null)

    {

       /* Get the value of the clob */

       clobVO.setWhereClause(null);         

       clobVO.executeQuery();

       Row row = clobVO.next();

       try

       {

         clobStr = row.getAttribute(0).toString();

       }

       catch(Exception exception)

       {

           throw OAException.wrapperException(exception);  

       }

   

     

       /* Populate the page with the clob string */

       OAMessageStyledTextBean messageStyleText = (OAMessageStyledTextBean)webBean.findIndexedChildRecursive("clobText");

       messageStyleText.setMessage(clobStr);

     

       /* Set the page and the window title */      

       OAPageLayoutBean page = pageContext.getPageLayoutBean();   

       page.setTitle(title);

       page.setWindowTitle(title);

     }

  }

  /**

   * Procedure to handle form submissions for form elements in

   * a region.

   * @param pageContext the current OA page context

   * @param webBean the web bean corresponding to the region

   */

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processFormRequest(pageContext, webBean);

  }

}

E-Biz Form Function:

Define a Form Function to access the OAF page as shown in the screenshots below

E-Biz Menu:

Assign the Form function that you have created above to the Menu of the Responsibility from which you will be accessing the Oracle Form.

 Oracle Form:

This is the Oracle Form from which you want to view the CLOB data. On the event on which you want to display the CLOB data (for e.g. WHEN-BUTTON-PRESSED), call the Form function defined above using the following code. In the parameter ‘OTHER_PARAMS’ a concatenated string is passed. This string passes the values for ‘Table’, ‘Data Column’, ‘Where Column’, ‘ID’ and ‘Page Title’.

FND_FUNCTION.EXECUTE(FUNCTION_NAME=>'XXHK_DISPLAY_CLOB',

              OPEN_FLAG=>'Y', SESSION_FLAG=>'Y',                                                              OTHER_PARAMS=>'SELECT_COL='||:tt7||'&TABLE='||:tt8||'&WHERE_COL=test_id&ID='||:tt9||'&TITLE='||:tt10);               

Note: The parameter names that are passed in this string should be the same values that are expected in the OAF controller.

When the form is accessed through front end and the requisite event is fired a new web page opens up which will display the CLOB data as expected.

5.   Solution

Oracle Forms can be integrated with OAF pages to support features which it does not support. We have demonstrated how CLOB data can be displayed when an event is triggered in Oracle Forms.

Places to visit in India

Use the website http://www.incredibleindia.org/#/travel/destination to research the destinations.

For luxury hotels visit :-)
http://www.tajhotels.com

For self booking of package tours and flights visit
http://www.ezeego1.co.in/india-holiday-packages/

=================================================================
State Rajasthan (Central India, North of Mumbai)
=================================================================
Cities to Visit in Rajasthan.
Jaipur - Pink City, Capital of rajasthan, connected by air (http://www.incredibleindia.org/#/travel/destination/jaipur)
Jodhpur - Famous for Forts, Palaces
Jaisalmer - Famous for Forts, Palaces
Udaipur - Famous for Forts, Palaces
Ranakpur - Famous for Jain Temples
Package tours - http://www.ezeego1.co.in/holidays/india-packages-rajasthan-3399#!1

Cities in Uttar Pradesh (state neighbouring rajasthan)
Agra - Taj Mahal - No introduction needed !!

=================================================================
State Kerala (South India, South of Mumbai)
=================================================================
Thiruvananthapuram - Capital of kerala, connected by air
Kochi - Another major city, connected by air
Munnar - Hill Station, famous for Tea and Cardamom estates (http://www.incredibleindia.org/#/travel/destination/munnar)
Kovalam - Very good beaches
Allepey, Kumarakom - Famous for backwater house boats - In your package choose the option to stay one night in the house boat
Thekady - Wild life sanctuary 
Package tours - http://www.ezeego1.co.in/holidays/india-packages-kerala-3398#!1

Investing Blogs

Links to blogs on value investing principles and investing ideas. Happy Investing & Reading :-))

http://fundooprofessor.wordpress.com
http://www.safalniveshak.com
http://manufacturedluck.blogspot.in
http://neerajmarathe.blogspot.in
http://valueinvestorindia.blogspot.in
http://dalal-street.in

Handle Exceptions in Oracle Workflow

While working with Oracle workflow, one frequent requirement is to handle errors in Functions that are used by workflow Activity. There are two ways to handle this requirement.

Approach 1 – Do Nothing

Yes, you read it correctly, in the function just write your code without doing anything special to capture the exception i.e. do not put any exception block. Workflow will automatically capture the exception and display appropriate error code and error message against the activity where the error was encountered in Activity History.

Approach 2 – Use WF_CORE API’s

Add an exception block in your code and call WF_CORE.CONTEXT API, the syntax for which is as follows. The first two parameters for this functions are the package name and the procedure name, from third parameter onwards you can pass any argument. This can be used to set any values which are specific to the procedure and which can help in debugging.

procedure CONTEXT (pkg_name  IN VARCHAR2,

                   proc_name IN VARCHAR2,

                   arg1      IN VARCHAR2 DEFAULT '*none*',

                   arg2      IN VARCHAR2 DEFAULT '*none*',

                   arg3      IN VARCHAR2 DEFAULT '*none*',

                   arg4      IN VARCHAR2 DEFAULT '*none*',

                   arg5      IN VARCHAR2 DEFAULT '*none*');

For example the call can be as follows.

BEGIN

 ...

EXCEPTION

  WHEN OTHERS THEN

             wf_core.CONTEXT (pkg_name    => 'XXARM_ADDRESS',

                              proc_name   => 'ADDRESS_AR_OUTGOING_PROCESS',

                              arg1        => pv_itemtype,

                              arg2        => pv_itemkey,

                              arg3        => pn_actid,

                              arg4        => pv_funcmode,

                              arg5        => ‘ERROR :'|| SQLCODE || ‘ ‘ || SUBSTR(SQLERRM,1,300));

    RAISE;

END;

In the above call an interesting point to note is the arguments passed in fields from arg1 to arg4. If you note these are the values which the workflow passes to the function, so now you have the same values and it is very easy to debug the function. In arg5 we can capture the SQL error that was encountered.

If the control goes in the exception block as defined, an entry is added to the error stack to provide context information that helps locate the source of an error. In the workflow monitor you can view the details of the above error  by navigating to ‘Activity History’ and clicking on the ‘Error’ link in the Status column on the activity which has got error.

Continuous Integration - For Oracle Apps?

What is Continuous Integration? 

Continuous Integration is a software development practice where members of a team integrate their work frequently, usually each person integrates at least daily - leading to multiple integrations per day. Each integration is verified by an automated build (including test) to detect integration errors as quickly as possible.

Why should you use Continuous Integration in your project?

When embarking on a change, a developer takes a copy of the current code base on which to work. As other developers submit changed code to the source code repository, this copy gradually ceases to reflect the repository code. The longer a branch of code remains checked out, the greater the risk of multiple integration conflicts and failures becomes when it is reintegrated into the main line. When developers submit code to the repository they must first update their code to reflect the changes in the repository since they took their copy. The more changes the repository contains, the more work developers must do before submitting their own changes.

Eventually, the repository may become so different from the developers' baselines that they enter what is sometimes called "integration hell", where the time it takes to integrate exceeds the time it took to make their original changes. In a worst-case scenario, developers may have to discard their changes and completely redo the work.

Continuous integration involves integrating early and often, so as to avoid the pitfalls of "integration hell". The practice aims to reduce rework and thus reduce cost and time.

Principles of Continuous Integration or Activities that make effective Continuous Integration

Maintain a code repository (i.e. a version control system) – This is the most basic requirement without this it would be impossible to manage multiple versions of code.
Automate the build – Getting code sources into running systems can be a tedious and error prone process without automation. Tools such as ‘make’, ‘ant’ help you with this.
Make your build self testing – This is the most important step to evaluate whether your build was successful. Traditionally a build means compiling, linking, and all the additional stuff required to get a program to execute. A program may run, but that doesn't mean it does the right thing. A good way to catch bugs more quickly and efficiently is to include automated tests in the build process. Tools such as ‘XUnit’ can be used for this activity. For end-to-end kind of testing tools such as ‘Fitnesse’ can be used.
Everyone commits to the baseline every day and Every commit (to baseline) should be built - By committing regularly, every committer can reduce the number of conflicting changes. Checking in a week's worth of work runs the risk of conflicting with other features and can be very difficult to resolve. Early, small conflicts in an area of the system cause team members to communicate about the change they are making. More importantly this makes it easier to identify the area in the code which might have caused the bug to be introduced.
Keep the build fast - The build needs to complete rapidly, so that if there is a problem with integration, it is quickly identified.
Test in a clone of the Production Machine – This is so that you identify bugs in the kind of environment in which the code is finally supposed to run.

Where can I find more information?

Continuous Integration concept was proposed by Martin Fowler and his website has an excellent essay on this topic. http://www.martinfowler.com/articles/continuousIntegration.html

Can this be used in Oracle Apps projects?

As described above, the need for Continuous Integration arises when multiple developers work have to work on the same code. In case of Oracle Apps, most of the source files are such that at a time only one person can work on it for e.g. Forms (FMB), Reports (RPT), Workflow (WFT) etc.  (These are binary source files and it is not easy for the someone to identify the changes between two different versions, hence I say that at a time only one person can work on it. In contrast source files written in text format, for e.g. Java program lend themselves easily to be worked on by multiple people at the same time). Although there are files such as PL/SQL packages which are written in text format, but they are rarely required to be worked on by multiple people and there aren’t frequent updates required to them.

Also looking at the big picture, any development work that happens in Oracle Apps does not involve developing something from scratch, where many people are working to build the same system, it is always a bolt on to the basic technology framework of Oracle Apps. This reduces the issues faced while integrating new developments, which are normally encountered in projects using technologies such J2EE, .NET etc.

I therefore believe, Continuous Integration would be more appropriate to use in a product development project using technologies such as J2EE or .NET. Complex product development projects normally require frequent updates to functionality, leading to new releases, where the using Continuous Integration would really help.