Hire Oracle PL/SQL Developers

A surprising share of an enterprise estate’s actual business logic lives in PL/SQL, written over a decade by people who have since left, running at two in the morning. It is rarely on anyone’s architecture diagram and it is invariably on the critical path.

Comprehension: changing code that nobody has tests for

The work divides into three. The first is comprehension: a four-thousand-line package with no tests, which must keep behaving exactly as it does while something inside it changes. Writing characterisation tests around the current behaviour before touching it is slower on day one and is the only approach that does not produce a silent regression.

A characterisation test does not assert that the code is correct. It asserts that the code still does what it did yesterday, which is the only property anybody can safely claim about inherited batch logic, and it is enough to make a change reviewable.

Performance: why reaching for an index first is usually wrong

The second is performance, and it is nearly always the same cause. Row-by-row processing — a cursor loop performing a select and an update per row — is fast enough in test and fails in production because production has forty million rows rather than four hundred. Rewriting it as a set operation is usually a tenfold improvement before anyone looks at an index. Reaching for the index first is the common instinct and rarely the answer.

Where a set rewrite is genuinely impossible, bulk collect with a limit and forall gives most of the benefit while keeping the shape of the original logic. That is a legitimate intermediate step and it should be recognised as one, not presented as a fix.

Reprocessing: the most common cause of a reconciliation break

The third is the interface layer: staging tables, error tables and reprocessing. Batches are re-run — because a file arrived late, because someone fixed a mapping, because the first attempt half-finished. If reprocessing is not idempotent, the second run inserts the same rows again. This is the single most common cause of a reconciliation break we are asked to investigate, and it is entirely preventable at design time.

The preventable version is a business key on the staging table, a merge rather than an insert, and a run identifier that makes it possible to say afterwards which attempt produced which row. None of that is difficult. It is simply not visible in a test that runs the batch once.

What an execution plan conversation tells you about a candidate

Anyone can read a plan and point at a full table scan. The useful signal is whether they can say when a full scan is the right access path, why the optimiser chose it, and what would have to change for it to choose otherwise.

The second signal is statistics. A plan that was fine last month and is not today usually reflects data that has moved rather than SQL that has changed, and a developer who does not think in terms of cardinality estimates will fix it by adding a hint. Hints are how a system becomes unable to adapt to its own growth.

Where PL/SQL should stop

Logic belongs in the database when it is close to the data and has to be transactionally consistent with it. It stops belonging there when it starts making decisions the business disagrees about, because a package is a poor place to argue about a rule and a worse place to change one.

The estates that are hardest to modernise are the ones where pricing, entitlement and approval rules were absorbed into batch packages because that was the fastest route at the time. Knowing where to stop is a judgement, and it is one worth interviewing for.

When you do not need a PL/SQL developer

New systems being built on an application stack should generally not acquire a PL/SQL layer for convenience. If the team’s centre of gravity is elsewhere, logic split across both is logic nobody owns.

Reporting is the other case. If the requirement is analytics, a warehouse and a modelling tool will serve better than another package producing an extract, and the extract will otherwise become a dependency for years.

Where this role is genuinely needed is an existing estate whose overnight batch no longer fits the window, an interface layer that duplicates on rerun, or a migration that has to be proved against the source before anyone signs it.