Blog: How to run SQL statements after migrating from Oracle Forms to web applications

At DB Best, we’ve helped several customers move away from Oracle Forms to a modern web-based solution. To accomplish this, developed a totally new Oracle Forms Converter. This tool allows for automating the migration of your Oracle Forms to web single page applications.  In this blog post, I’ll talk about making SQL statements from Oracle Forms compatible with your new application.

The problem

Oracle Forms run different queries and execute SQL statements directly at the connected Oracle Database. These SQL statements are embedded into your Oracle Forms. When you migrate Oracle Forms to the web application, you need to take note of how your new web application executes these queries.

The problem is that you cannot query your database directly from the web application. This means that you need to change the logic of your queries to utilize them in your new web application.

DB Best Oracle Forms Converter in action

DB Best uses our Oracle Form Converter to solve this problem. The resulting JavaScript code executes stored procedures on the database instead of embedded SQL statements.

Let’s take a look at how Oracle Forms Converter converts SQL statements for the source Oracle Form with an embedded SQL statement.

Oracle Form with embedded SQL statement

Oracle Forms Converter creates the following stored procedure to replace this SQL statement.

Stored procedure with converted SQL code

Finally, Oracle Forms Converter generates the following JavaScript file. This file includes a call of this stored procedure, shown below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
const trigger$whenNewFormInstance = {
  name: "WHEN-NEW-FORM-INSTANCE",
  triggerText: async function() {
    await callStoredProcedure(
      "Form_BP2K_F_AR_ADVANCE_APPLY.WHEN_NEW_FORM_INSTANCE_1",
      {
        P_ORGNAME: value => {
          item(":ORGNAME").value = value;
        }
      }
    );
    item(":DISP_USER").value = Global.USERID;
    await callStoredProcedure(
      "Form_BP2K_F_AR_ADVANCE_APPLY.WHEN_NEW_FORM_INSTANCE_2",
      {
        P_DISP_DATE: value => {
          item(":DISP_DATE").value = value;
        },
        P_DISP_TIME: value => {
          item(":DISP_TIME").value = value;
        }
      }
    );
    await SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, WINDOW_STATE, MAXIMIZE);
    await RESIZE_WINDOW(FORMS_MDI_WINDOW, 800, 550);
    await SET_WINDOW_PROPERTY("window1", WINDOW_STATE, MAXIMIZE);
    /* set_window_property('window1',title,'GRANT SYSTEM - LOG ON SCREEN '); */
    Global.ENTER_STATE = null;
  }
};

As a result, there’s not much work left to be done, as the DB Best Oracle Forms converter automates almost 100 percent of the initial effort.