Db advice

Oracle Forms Java Api – JDAPI

A lot of software was written with Oracle Forms 4.5 version and was easily upgraded to Oracle Forms 6i, as they were both client/server implementations.

Converting the client/server version to a web enabled version with Oracle Forms 9 and 10, we needed a tool to do this automatically and repeatingly. Opening 300 or more Forms manually was not an alternative as the Oracle Forms 6i pl/sql code changed daily as the maintenance team was developing issues resolutions.

FormsAPI Master for instance is a commercial product that supports bulk changes to Oracle Forms code. As this product is not for free and also very expensive for a lot of users, we developed our own Forms API tool with the JDAPI functionality which is delivered with the Oracle Developer Suite 10g.

Our own Forms API tool needed the following features :

  • As it was a conversion from Windows Client/server to a Web Forms UNIX environment, the mixed uppercase and lowercase used in Oracle Forms had to be resolved, both internal (objects) as external (filenames)
  • Implementation of typical Client/Server to web issues. Ex. File open dialogue on the client, writing and reading files to and from the client, file transfer, etc. .
  • Bulk changes to the Oracle Forms PL/SQL code in Triggers and program units

 

We implemented the typical client/server to web issues by using the webutil utility. You can download the latest Oracle Developer Suite /Application Server and Webutil here.

Header of the Oracle Forms API tool java source

import java.io.*;
import oracle.forms.jdapi.*;
import java.util.regex.*;

public final class jdapiFormsApi {

public static void main(String[] args) {

Conversion of the Oracle Forms filenames

    // change directory content to case sensitive filenames
File dir = new File (".");
File[] files = dir.listFiles();

for (int k=0;k < files.length; k++)
{
String extension = files[k].getName().substring(files[k].getName().lastIndexOf('.')+1);
String filename = files[k].getName().substring(0,files[k].getName().lastIndexOf('.'));
if (extension.toLowerCase().equals("fmb"))
files[k].renameTo(new File(filename.toUpperCase() + "." + extension.toLowerCase()));
else if (extension.toLowerCase().equals("pll"))
files[k].renameTo(new File(filename.toLowerCase() + "." + extension.toLowerCase()));
else if (extension.toLowerCase().equals("mmb"))
files[k].renameTo(new File(filename.toLowerCase() + "." + extension.toLowerCase()));
else if (extension.toLowerCase().equals("olb"))
files[k].renameTo(new File(filename.toLowerCase() + "." + extension.toLowerCase()));
}

Processing of the Oracle Forms fmb , pll , mmb and olb

First we do a few JDAPI initializations and then we process the Oracle Forms files.ProcessModule detail follows

    Jdapi.setFailLibraryLoad(true);
Jdapi.setFailSubclassLoad(true);
try {
for (int i=0 ; i < args.length ; i++ ) {
ProcessModule(args[i]);
}
}
finally {
Jdapi.shutdown();
}

ProcessModule method definition and variables

  private static void ProcessModule(String filename) {

FormModule fmod;
MenuModule mmod;
PlsqlModule pmod;

JdapiIterator librs, ftrgs, btrgs, itrgs, progunits, blks, itms;

ObjectGroup objgr, obg;
LOV lov;
RecordGroup rgr;
Trigger ftrg, btrg, itrg;
Block blk;
Item itm;
ProgramUnit progunit;
Canvas cnvs;
Window wndw;
Alert alrt;


AttachedLibrary lib, newlib;

AttachedLibrary[] libs;

int num_libs;

JdapiModule jdapmod;

Open the Forms module (Form fmb, Library pll, Menu mmb)

     jdapmod = JdapiModule.openModule(filename);

Get the WEBUTIL.olb object library and position on the MAIN tab, and iterate for the objectgroup in het MAIN tab

    ObjectLibrary objlib = ObjectLibrary.open("WEBUTIL.olb");
ObjectLibraryTab objlibtab = ObjectLibraryTab.find( objlib , "MAIN" );

ObjectGroup WebutilObjgrp = null;
JdapiObject jdobjs;
for (JdapiIterator objlibobj=objlibtab.getOwnedObjects() ; objlibobj.hasNext() ; )
{
jdobjs = (JdapiObject)objlibobj.next();
if (jdobjs instanceof ObjectGroup)
{
WebutilObjgrp = (ObjectGroup)jdobjs;
}
}

Is this an Oracle Forms Form Module

      if (jdapmod instanceof FormModule ) {

For an Oracle Forms Form Module we need to COPY not REFERENCE the WEBUTIL objectgroup

         fmod = (FormModule)jdapmod;
System.out.println("Module name : " + fmod.getName());
// Find the Object group WEBUTIL, if not found then ADD it to the FORM, if found at it again to have the most recent version
try {
objgr = ObjectGroup.find(fmod, "WEBUTIL");
objgr.destroy();
objgr = WebutilObjgrp.clone(fmod, "WEBUTIL");
}
catch (Exception e)
{
objgr = WebutilObjgrp.clone(fmod, "WEBUTIL");
}

Remove the attached libraries and add them again in UPPERCASE

        libs = new AttachedLibrary[100]; // 100 as an extreme limit
librs = fmod.getAttachedLibraries();
String libraryname;
num_libs = 0;
while (librs.hasNext()) {
libs[num_libs++] = (AttachedLibrary)librs.next();
}
for (int i=0; i<num_libs;i++) {
libraryname = libs[i].getLibraryLocation().toUpperCase();
libs[i].destroy();
newlib = new AttachedLibrary( fmod , libraryname );
}

For all subclassed objects the Oracle Forms Form module change the reference Oracle Form Module name to uppercase, for the non referenced objects , change the PL/SQL code as stipulated in the UpdateTrigger or UpdateProgramUnit method

        // OBJECT GROUPS
for (JdapiIterator obgs=fmod.getObjectGroups() ; obgs.hasNext() ; ) {
obg = (ObjectGroup)obgs.next();
if (obg.isSubclassed())
{
if (obg.getParentFilename().indexOf('.') > -1)
obg.setParentFilename(obg.getParentFilename().replaceAll(obg.getParentFilename().substring(0,obg.getParentFilename().indexOf('.')),obg.getParentFilename().substring(0,obg.getParentFilename().indexOf('.')).toUpperCase()));
}
}
// LOVS
for (JdapiIterator lovs=fmod.getLOVs() ; lovs.hasNext() ; ) {
lov = (LOV)lovs.next();
if (lov.isSubclassed())
{
if (lov.getParentFilename().indexOf('.') > -1)
lov.setParentFilename(lov.getParentFilename().replaceAll(lov.getParentFilename().substring(0,lov.getParentFilename().indexOf('.')),lov.getParentFilename().substring(0,lov.getParentFilename().indexOf('.')).toUpperCase()));
}
}
// RECORDGROUPS
for (JdapiIterator rgrs=fmod.getRecordGroups() ; rgrs.hasNext() ; ) {
rgr = (RecordGroup)rgrs.next();
if (rgr.isSubclassed())
{
if (rgr.getParentFilename().indexOf('.') > -1)
rgr.setParentFilename(rgr.getParentFilename().replaceAll(rgr.getParentFilename().substring(0,rgr.getParentFilename().indexOf('.')),rgr.getParentFilename().substring(0,rgr.getParentFilename().indexOf('.')).toUpperCase()));
}
}
// Canvas cnvs;
for (JdapiIterator cnvss=fmod.getCanvases() ; cnvss.hasNext() ; ) {
cnvs = (Canvas)cnvss.next();
if (cnvs.isSubclassed())
{
if (cnvs.getParentFilename().indexOf('.') > -1)
cnvs.setParentFilename(cnvs.getParentFilename().replaceAll(cnvs.getParentFilename().substring(0,cnvs.getParentFilename().indexOf('.')),cnvs.getParentFilename().substring(0,cnvs.getParentFilename().indexOf('.')).toUpperCase()));
}
}
// Window wndw;
for (JdapiIterator wndws=fmod.getWindows() ; wndws.hasNext() ; ) {
wndw = (Window)wndws.next();
if (wndw.isSubclassed())
{
if (wndw.getParentFilename().indexOf('.') > -1)
wndw.setParentFilename(wndw.getParentFilename().replaceAll(wndw.getParentFilename().substring(0,wndw.getParentFilename().indexOf('.')),wndw.getParentFilename().substring(0,wndw.getParentFilename().indexOf('.')).toUpperCase()));
}
}
// Alert alrt;
for (JdapiIterator alrts=fmod.getAlerts() ; alrts.hasNext() ; ) {
alrt = (Alert)alrts.next();
if (alrt.isSubclassed())
{
if (alrt.getParentFilename().indexOf('.') > -1)
alrt.setParentFilename(alrt.getParentFilename().replaceAll(alrt.getParentFilename().substring(0,alrt.getParentFilename().indexOf('.')),alrt.getParentFilename().substring(0,alrt.getParentFilename().indexOf('.')).toUpperCase()));
}
}

// FORM TRIGGERS
ftrgs = fmod.getTriggers();
while (ftrgs.hasNext())
{
ftrg = (Trigger)ftrgs.next();
if (!ftrg.isSubclassed())
{
UpdateTrigger(ftrg);
}
else
{
{
if (ftrg.getParentFilename().indexOf('.') > -1)
ftrg.setParentFilename(ftrg.getParentFilename().replaceAll(ftrg.getParentFilename().substring(0,ftrg.getParentFilename().indexOf('.')),ftrg.getParentFilename().substring(0,ftrg.getParentFilename().indexOf('.')).toUpperCase()));
}

}
}
// BLOCKS FOR BLOCK TRIGGERS
blks = fmod.getBlocks();
while (blks.hasNext())
{
blk = (Block)blks.next();
if (!blk.isSubclassed())
{
btrgs = blk.getTriggers();
while (btrgs.hasNext())
{
btrg = (Trigger)btrgs.next();
if (btrg.isSubclassed())
{
if (btrg.getParentFilename().indexOf('.') > -1)
btrg.setParentFilename(btrg.getParentFilename().replaceAll(btrg.getParentFilename().substring(0,btrg.getParentFilename().indexOf('.')),btrg.getParentFilename().substring(0,btrg.getParentFilename().indexOf('.')).toUpperCase()));
}
UpdateTrigger(btrg);
}
itms = blk.getItems();
while (itms.hasNext())
{
itm = (Item)itms.next();
if (!itm.isSubclassed())
{
itrgs = itm.getTriggers();
while (itrgs.hasNext())
{
itrg = (Trigger)itrgs.next();
if (itrg.isSubclassed())
{
if (itrg.getParentFilename().indexOf('.') > -1)
itrg.setParentFilename(itrg.getParentFilename().replaceAll(itrg.getParentFilename().substring(0,itrg.getParentFilename().indexOf('.')),itrg.getParentFilename().substring(0,itrg.getParentFilename().indexOf('.')).toUpperCase()));
}
UpdateTrigger(itrg);
}
}
else
{
if (itm.getParentFilename().indexOf('.') > -1)
itm.setParentFilename(itm.getParentFilename().replaceAll(itm.getParentFilename().substring(0,itm.getParentFilename().indexOf('.')),itm.getParentFilename().substring(0,itm.getParentFilename().indexOf('.')).toUpperCase()));
}
}
}
else
{
if (blk.getParentFilename().indexOf('.') > -1)
blk.setParentFilename(blk.getParentFilename().replaceAll(blk.getParentFilename().substring(0,blk.getParentFilename().indexOf('.')),blk.getParentFilename().substring(0,blk.getParentFilename().indexOf('.')).toUpperCase()));
}
}
// PROGRAM UNITS
progunits = fmod.getProgramUnits();
while (progunits.hasNext())
{
progunit = (ProgramUnit)progunits.next();
if (!progunit.isSubclassed())
{
UpdateProgramunit(progunit);

}
else
{
if (progunit.getParentFilename().indexOf('.') > -1)
progunit.setParentFilename(progunit.getParentFilename().replaceAll(progunit.getParentFilename().substring(0,progunit.getParentFilename().indexOf('.')),progunit.getParentFilename().substring(0,progunit.getParentFilename().indexOf('.')).toUpperCase()));
}
}

Save the Oracle Forms Form module

        System.out.println("Form is saved as " + fmod.getAbsolutePath());
fmod.save(fmod.getAbsolutePath());
jdapmod.destroy();
}

Detach and attach the pl/sql libraries from the Oracle Forms Menu module and save the menu module

      else
if (jdapmod instanceof MenuModule)
{
mmod = (MenuModule)jdapmod;
System.out.println("Module name : " + mmod.getName());
libs = new AttachedLibrary[100];
librs = mmod.getAttachedLibraries();
String libraryname;
num_libs = 0;
while (librs.hasNext()) {
libs[num_libs++] = (AttachedLibrary)librs.next();
}
for (int i=0;i<num_libs;i++) {
libraryname = libs[i].getLibraryLocation().toUpperCase();
libs[i].destroy();
newlib = new AttachedLibrary( mmod , libraryname );
}
System.out.println("Menu module is saved as " + mmod.getAbsolutePath());
mmod.save(mmod.getAbsolutePath());
jdapmod.destroy();
}

Convert the PL/SQL libraries with the parsePLL method

      else
if (jdapmod instanceof PlsqlModule)
{
System.out.println("Pl sql module : " + jdapmod.getName());
parsePll((PlsqlModule)jdapmod);
}
}

Methods UpdateTrigger and UpdateProgramUnit

These two methods can be changed to your needs as in this example the SHOW_MENU instruction is removed from all triggers and program units and we change the TEXT_IO function in CLIENT_TEXT_IO which is the webutil variant of the native Oracle Forms TEXT_IO function.

  private static String plsqltext;

private static void UpdateTrigger(Trigger trg)
{
plsqltext = trg.getTriggerText();
boolean ignorecase;

while (ReplaceString("SHOW_MENU" , "NULL", ignorecase= true))
{
trg.setTriggerText(plsqltext);
}

while (ReplaceString("TEXT_IO", "TEXXXT_IO", ignorecase = true))
{
trg.setTriggerText(plsqltext);
}

while (ReplaceString("CLIENT_TEXXXT_IO", "CLIENT_TEXT_IO", ignorecase = true))
{
trg.setTriggerText(plsqltext);
}
while (ReplaceString("TEXXXT_IO", "CLIENT_TEXT_IO", ignorecase = true))
{
trg.setTriggerText(plsqltext);
}
}

private static void UpdateProgramunit(ProgramUnit progu)
{
plsqltext = progu.getProgramUnitText();
boolean ignorecase;
while (ReplaceString("SHOW_MENU" , "NULL", ignorecase = true))
{
progu.setProgramUnitText(plsqltext);
}

while (ReplaceString("TEXT_IO", "TEXXXT_IO", ignorecase = true))
{
// System.out.println("Replaced Text_io");
progu.setProgramUnitText(plsqltext);
}

while (ReplaceString("CLIENT_TEXXXT_IO", "CLIENT_TEXT_IO", ignorecase = true))
{
progu.setProgramUnitText(plsqltext);
}
while (ReplaceString("TEXXXT_IO", "CLIENT_TEXT_IO", ignorecase = true))
{
progu.setProgramUnitText(plsqltext);
}
}

Conversion of the PL/SQL Libraries

In this example we recreate the PLL’s via the PLD to an Oracle Forms 10 compliant version. It is possible to change the PL/SQL code, like we did for the program units and the triggers. In this example there is no substitution done in the PLL’s PL/SQL code

    private static void parsePll(PlsqlModule mod) {
boolean pllChanged = false;
StringBuffer pldText = new StringBuffer();
String absolutePath = mod.getAbsolutePath();

// all attached libraries and put them in the PLD text
JdapiIterator attachedLibs = mod.getAttachedLibraries();

while (attachedLibs.hasNext()) {
AttachedLibrary attachedLib = (AttachedLibrary)attachedLibs.next();
String attachedLibLocation = attachedLib.getLibraryLocation();

// converted name of attached lib to lowercase
if (!attachedLibLocation.equals(attachedLibLocation.toUpperCase())) {
attachedLibLocation = attachedLibLocation.toUpperCase();
pllChanged = true;
}

pldText.append(".attach LIBRARY " + attachedLibLocation +
" END NOCONFIRM\r\n");
}

// add an extra newline
pldText.append("\r\n");

// loop all program units and append them to the PLD
JdapiIterator programUnits = mod.getProgramUnits();

while (programUnits.hasNext()) {
ProgramUnit programUnit = (ProgramUnit)programUnits.next();
String programUnitText = convertPlSql(programUnit.getProgramUnitText());


pldText.append(programUnitText);
pldText.append("\r\n");
}

// write the PLD file to disk
String pllFileName = mod.getAbsolutePath();
String pldFileName =

pllFileName.substring(0, pllFileName.length() - 3) + "pld";

// destroy the PlSqlModule object so the file is released
mod.destroy();

// convert the PLD to PLL
try {
File pldFile = new File(pldFileName);
FileWriter pldWriter = new FileWriter(pldFile);
pldWriter.write(pldText.toString());
pldWriter.close();
} catch (IOException e) {
System.out.println("Error writing PLD file");
System.exit(1);
}

Runtime formsCompiler = Runtime.getRuntime();
Process compProcess = null;

try {
String command;
command =
"frmcmp module=" + pldFileName + " output_file=" + pllFileName +
" logon=no module_type=library window_state=minimize parse=yes batch=yes";
compProcess = formsCompiler.exec(command);
compProcess.waitFor();

if (compProcess.exitValue() > 0) {
System.out.println("Error converting PLD to PLL (file locked?)");
}
} catch (Exception e) {
System.out.println("Error execution forms compiler to convert PLD to PLL");
System.exit(1);
}
}

private static String convertPlSql(String plsql) {
// out("PL-SQL = " + plsql, logLevel, false);
// split pl-sql code in separate lines
String[] lines;
String lineSep = "\r\n";
String comments = new String();

plsql = plsql.replaceAll("\n","\r\n");

lines = plsql.split(lineSep);

String newPlSql = "";

for (int iLine = 0; iLine < lines.length; iLine++) {
newPlSql += (lines[iLine] + lineSep);
}

newPlSql =
"/*" + lineSep + "Changes made by the Forms 10 converter:" + lineSep + comments + "*/" + lineSep + newPlSql;

return newPlSql;
}