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.
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.
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.
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.
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.
