Thursday, January 22, 2015

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

No comments:

Post a Comment