Oracle Forms, Reports & PL/SQL: Where the Logic Lives

Part 1 made the case that the decision to move off Oracle Forms is really a decision about the logic underneath it. This part makes that concrete. Before you can choose a target architecture or scope a project, you have to know what an Oracle Forms estate is actually made of — and where, specifically, the system you depend on really lives. The answer surprises people, and getting it wrong is the most common reason Forms migrations ship the wrong behavior.

The three tiers, named precisely

An Oracle Forms application is conventionally drawn as three layers:

• The Forms tier — the screens. Each form (.fmb source, .fmx runtime) is built from blocks (usually mapped to database tables or views), items (the fields), and triggers (PL/SQL that runs on events). This is the part users touch and the part everyone pictures when they say “the application.”
• The Reports tier — Oracle Reports (.rdf). This is the document and batch-output engine: invoices, statements, picking lists, regulatory filings. It is less visible than the screens but often carries logic and formatting that other parties depend on exactly.
• The Oracle Database — tables, views, and a large body of PL/SQL: packages, procedures, functions, and database triggers. This is where the data lives, and — this is the point of the whole part — where most of the logic lives too.

The diagram makes them look like equal thirds. They are not. By volume of business rules, the database tier is usually the largest by a wide margin, and the forms are a comparatively thin presentation layer on top of it.

Inside the Forms tier: triggers, blocks, and the illusion of completeness

Open a form and you will find PL/SQL scattered through it as triggers tied to runtime events: WHEN-VALIDATE-ITEM to check a field, WHEN-BUTTON-PRESSED for actions, POST-QUERY to populate derived fields after a fetch, WHEN-NEW-FORM-INSTANCE for setup, and dozens of others. There is real logic here — field validation, navigation rules, conditional enabling and disabling, small calculations.

The trap is that this looks like the whole application. A team can read every form trigger, conclude they understand the system, and rebuild faithfully — only to discover in production that the new screens are missing rules they never saw, because those rules were never in the forms. The forms call into the database, and the database is where the rules they call actually run. Reading the forms alone gives you a confident, incomplete picture, which is worse than an obviously incomplete one.

Inside the database tier: where the system actually is

The substance is in PL/SQL stored in the Oracle Database, and it comes in a few shapes that matter differently in modernization:

• Packages and procedures. The application’s real workhorses. A package groups related procedures and functions — order processing, pricing, eligibility, posting to a ledger. The forms (and Reports, and batch jobs, and integrations) all call these. This is the shared business logic of the enterprise, and it is shared precisely because more than one consumer depends on it.
• Functions. Reusable calculations and lookups embedded in queries and procedures — the kind of logic that ends up duplicated and subtly inconsistent if you are not careful.
• Database triggers. PL/SQL attached to data events — BEFORE INSERT, AFTER UPDATE, and so on — that fire whenever the data changes, regardless of what changed it. They enforce integrity, write audit rows, cascade updates, and apply rules the database guarantees even when a form is not involved. They are the easiest layer to overlook and among the most dangerous to lose, because their whole purpose is to be invisible and unconditional.

The defining property of this tier is that it is not bound to the UI. The same package is called by a form, a report, a nightly batch job, and very possibly a partner integration you forgot existed. That is why you cannot modernize the database logic by walking screen to screen — you have to understand the logic on its own terms, as services with multiple consumers.

The Reports tier: separable, but a parity surface

Oracle Reports deserves its own mention because it is easy to defer and easy to underestimate. It produces the documents the business and its counterparties rely on — and those documents frequently have exact requirements: a total that must reconcile to the penny, a layout a regulator expects, a statement format a customer recognizes. Reports runs on the same Fusion Middleware 12c lifecycle as Forms, so it shares the support clock. It can be modernized independently onto a modern reporting or document-generation stack, but its outputs are a parity surface: “looks about right” is not the bar; matching figures and format is.

Why this anatomy decides everything that follows

Once you see the estate this way, the rest of the series stops being a menu of disconnected options and becomes a sequence of consequences:

• The logic must be recovered before it can be rebuilt. Most of it is undocumented and lives in the heads of a thinning group of people. Recovering it from the code — as a living specification of what the system actually does — is the first real act of modernization, covered in Part 4.
• The UI and the logic are separate decisions. Because the rules are not in the screens, you can rebuild the UI on any modern target without first deciding the fate of every package — and you can modernize the data tier on its own schedule. Parts 5 through 7 take those targets one at a time.
• The database is a system, not a store. Treating the Oracle Database as “just where the data sits” is the error that produces a beautiful new front end calling into the same un-modernized logic, having moved nothing that mattered.
• The consumers are plural. A package called by forms, reports, batch, and integrations cannot be quietly changed for one consumer. This is why surfacing the logic as explicit, well-bounded services — rather than copying it into the new UI — is the move that actually pays off.

The honest version of the estate map

A real Forms estate is rarely tidy. You will find logic duplicated between a form trigger and the package it calls, with the two subtly disagreeing. You will find database triggers nobody remembers writing, doing something load-bearing. You will find a “temporary” report that became a monthly regulatory dependency. You will find packages with dead branches for products discontinued a decade ago, sitting next to branches that run every night. None of this is a sign of a badly built system; it is what any system looks like after fifteen or twenty years of real use. The job of discovery is not to judge it — it is to map it accurately, including the parts that are inconvenient, before anyone commits to a target.

Where this leads

You now have the map: forms and their triggers on top, Reports alongside, and the real weight of the system in the PL/SQL packages, functions, and database triggers underneath. The next question is strategic, not technical. Given an estate shaped like this, what should you build — and what should you buy or simply re-platform? Part 3, Build, Buy, or Re-platform: The Oracle Forms Decision, lays out the options honestly: rebuild as a custom application, replace with a packaged product, re-platform onto a low-code target, or stay put — and the conditions under which each is the right call for a system whose value is mostly in its bespoke logic.