Search and Replace in Oracle Forms

In Oracle Forms, many times there is a need to search and replace text, for example change the name of some database objects. If there are few forms, the search-replace exercise can be done using Oracle Forms Builder. But if it needs to be done for large number of forms an automated approach, which can be easily applied to the batch needs to be devised. 

Following is some research related to the same:

1) Oracle forms can be converted to text and vice versa (i.e. FMB to FMT and FMT to FMB). However, in the text file all the PL/SQL code gets converted to a cryptic text. This file therefore cannot be used for search & replace.

2) Oracle also provides another option to convert the form to XML format and vice versa (i.e. FMB to XML and XML to FMB). The PL/SQL code in the XML format doesn’t is in readable format and hence we can search and replace text. I was able to change the code in sample form and convert it back to FMB successfully. However, I noticed that there the color of the Canvas changed to a different color. There could be some other hidden after effects as well mainly in the UI.

3) Oracle provides a third option, which is to programmatically modify the form files. This was originally a C language API which was complex to use. But recently Oracle has also provided a Java API for this. As this is programmatic, I hope this to be much cleaner than the XML option. Also, more suited for developing a tool. This needs to be researched further.

Notes from research:

1) To convert FMB to text format (FMT) and vice-versa

Open the form in Form Builder. Go to menu File -> Convert.

2)      Command to convert FMB to XML 

frmf2xml.bat OVERWRITE=YES XXARCUST_2.fmb

3) Command to convert XML to FMB

frmxml2f OVERWRITE=YES USERID=<usr>/<pass>@<db> XXARCUST_2_fmb.xml

4) Useful links

http://oracle.ittoolbox.com/groups/technical-functional/oracle-forms-l/convert-oracle-forms-fmb-to-xml-and-vice-versa-5347307

https://oracleformsinfo.wordpress.com/2011/12/16/batch-script-for-fmb-to-xml-and-xml-to-fmb/

https://docs.oracle.com/cd/E14373_01/migrate.32/e13368/appmgr_forms.htm#CHDEIIHC

http://www.oracle.com/technetwork/developer-tools/forms/overview/technical-overview-130127.pdf

Identification and Removal of Unused Custom Functionality in Oracle EBS - Part 3

7.   PL/SQL Packages, Procedures and Functions

Identification & Verification

The initial list of unused PL/SQL code objects (Packages/Procedures/Functions) can be identified based on the strategy discussed in Identification section above. Also the unused application components (Workflows, Forms and Reports) removed above will give us additional list of PL/SQL code objects that can potentially be removed.

We then need to verify if the PL/SQL code object is not used in any workflow activity, view definition, any other package, oracle form, oracle report or concurrent program executable.

Check if the PL/SQL code object is used in any workflow activity.

SELECT *   FROM wf_activities  WHERE UPPER (function) LIKE UPPER('%<PACKAGE_NAME>%')    AND end_date IS NULL  ORDER BY version

Check if the PL/SQL code object is used in any views, packages, procedures, functions etc.

SELECT ad.name, -- Referencing Object        ad.type, -- Type of referencing object        ad.*   FROM all_dependencies ad  WHERE referenced_name LIKE UPPER('%<PACKAGE_NAME>%')

Check if the PL/SQL code object is used in any concurrent executable

     

SELECT *   FROM fnd_executables  WHERE UPPER (execution_file_name) LIKE UPPER('%<PACKAGE_NAME>%')    AND execution_method_code = 'I' -- ‘I’ for Oracle PLSQL Code

Check if the PL/SQL code object is used in any form

     

Go to $AU_TOP/forms/US grep –ir <PACKAGE_NAME> *.fmb

Check if the PL/SQL code object is used in any report

     

Go to $CUSTOM_TOP/reports/US grep –ir <PACKAGE_NAME> *.rdf

Removal

The package, procedure or function can be removed using the following SQL command.

DROP PACKAGE <PACKAGE_NAME> DROP PROCEDURE <PROCEDURE_NAME> DROP FUNCTION <FUNCTION_NAME>

8.   Database Views

Identification & Verification

The initial list of unused database views can be identified based on the strategy discussed in Identification section above. Also the unused application components removed will give us additional list of views that can potentially be removed.

We then need to verify if the views are not used in any code such as package/procedure/function, oracle form, oracle report, table type value set and flex fields.

Check if the view is used in any other view, package, procedure, function etc.

     

SELECT ad.name, --Referencing Object        ad.type, -- Type of referencing object        ad.*   FROM all_dependencies ad  WHERE referenced_name LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any value set definition

     

SELECT *   FROM fnd_flex_validation_tables  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any descriptive flex field definition

     

SELECT *   FROM fnd_descriptive_flexs_vl  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any key flex field definition

     

SELECT *   FROM fnd_id_flexs  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any form

     

Go to $AU_TOP/forms/US grep –ir <VIEW_NAME> *.fmb

Check if the view is used in any report

     

Go to $CUSTOM_TOP/forms/US grep –ir <VIEW_NAME> *.rdf

Removal

The view can be removed using the following SQL command.

DROP VIEW <VIEW_NAME>

9.   Database Tables

Identification & Verification

The initial list of unused database tables can be identified based on the strategy discussed in Identification section above. Also the unused application components removed will give us additional list of tables that can potentially be removed.

We then need to verify if the tables are not used in any code such as view definition, package/procedure/function, oracle form, oracle report, table type value set and flex field.

Check if the table is used in any views, package, procedure, function etc.

SELECT name,-- Referencing Object        type -- Type of referencing object   FROM all_dependencies  WHERE referenced_name LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any value set definition

     

SELECT *   FROM fnd_flex_validation_tables  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any descriptive flex field definition

     

SELECT *   FROM fnd_descriptive_flexs_vl  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any key flex field definition

     

SELECT *   FROM fnd_id_flexs  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any form

     

Go to $AU_TOP/forms/US grep –ir <TABLE_NAME> *.fmb

Check if the table is used in any report

     

Go to $CUSTOM_TOP/forms/US grep –ir <TABLE_NAME> *.rdf

Check the latest time a DML Commit operation was performed on a table. If the table stores transactional data, this query will give you a good indication of whether the table is used or not.

Note: The following query will not consider SELECTs performed on the table.

     

SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn))   FROM <TABLE_NAME>

Removal

The table can be removed using the following SQL command. It is also a suggested to take a temporary backup of the table data before dropping the table.

CREATE TABLE <TABLE_NAME_BKUP>          AS (SELECT * FROM <TABLE_NAME>); DROP TABLE <TABLE_NAME>;

Identification and Removal of Unused Custom Functionality in Oracle EBS - Part 2

5.   Oracle Forms

Identification & Verification

The initial list of unused forms can be identified based on the strategy discussed in Identification section above.

Additionally, unused forms can also be identified by checking the latest time when each form was accessed by a user. Forms which were accessed long back are probably not in use any more and are likely candidates for removal.

Note: The following query will give latest access time for a form only if -

a.     The value for the profile ’Sign-On:Audit Level’ is set to ‘FORM’ and

b.     The built in ‘FND_STANDARD.FORM_INFO’ is called in the ‘PRE-FORM’ trigger with appropriate values for the form.

SELECT ff.form_name,        MAX(flrf.start_time) last_accessed_time   FROM fnd_form ff,        fnd_login_resp_forms flrf        WHERE ff.form_id = flrf.form_id (+)    AND UPPER(ff.form_name) LIKE 'XX%'  GROUP BY ff.form_name    ORDER BY ff.form_name

 Check if any other form is calling the identified form.

Go to $AU_TOP/forms/US grep –ir <FORM_NAME> *.fmb

Removal

Following steps need to be carried out to remove the form.

1) Delete the <FORM_NAME>.fmb file from to $AU_TOP/forms/US

rm –f <FORM_NAME>.fmb

2)Delete the <FORM_NAME>.fmx file from to $CUSTOM_TOP/forms/US

rm –f <FORM_NAME>.fmx

3) Remove the Form Function from the menu

4) Delete the Form Function

6.   Oracle Reports

Identification & Verification

The initial list of unused reports can be identified based on the strategy discussed in Identification section above.

Additionally, unused reports can also be identified by checking if the corresponding concurrent programs are disabled.

SELECT fe.execution_file_name,        fcp.concurrent_program_name   FROM fnd_executables fe,        fnd_concurrent_programs fcp  WHERE fe.execution_method_code = 'P'    -- ‘P’ for Oracle reports    AND fe.executable_id = fcp.executable_id    AND fcp.enabled_flag = 'N'    AND fe.execution_file_name LIKE 'XX%' -- Assuming custom reports are   -- named starting with XX

For each unused report the corresponding executable and concurrent program can be identified. The concurrent program can then be verified as follows.

Check the last time when the report concurrent program was ran.

Note: This data might not give correct usage picture as normally the concurrent program run data is frequently purged.

SELECT *   FROM fnd_conc_req_summary_v  WHERE program_short_name LIKE UPPER('%<PROGRAM_SHORT NAME>%')   AND TRUNC (actual_start_date) < TRUNC (SYSDATE-365)

Removal

Following steps need to be carried out to remove the report.

1)Delete the corresponding executable and the concurrent program

BEGIN   FND_PROGRAM.DELETE_EXECUTABLE ('<EXECUTABLE_SHORT_NAME>', '<APPLICATION_NAME>');   FND_PROGRAM.DELETE_PROGRAM ('<PROGRAM_SHORT_NAME>', '<APPLICATION_NAME>'); END;

2)Delete the <REPORT_NAME>.rdf file from to $CUSTOM_TOP/reports/US

rm –f <REPORT_NAME>.rdf

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.