Oracle

Software Mile works with Oracle databases and the applications built around them – the systems many enterprises still run their core operations on, and which integration projects have to respect rather than route around.

Working With Oracle in the Real World

  • Application development against Oracle databases, with PL/SQL where it belongs
  • Integration between Oracle systems and modern web, mobile, and cloud applications
  • Performance work on queries and data access that have grown slow over years
  • Migration and modernization planning where Oracle is being moved or wrapped

Oracle is often the system of record everything else must talk to. Tell us your Oracle environment and what needs to connect to it.

Should You Migrate Off Oracle, or Fix What You Have?

Licensing cost is the usual reason the question gets asked, and it is a legitimate one. The cost of leaving, though, rarely sits in the database itself. It sits in the application: stored procedure logic, Oracle-specific SQL, sequences and hierarchical queries, scheduled jobs, and every report written against the schema over the years. How much of that exists, and how much of it is still in use, is what separates a contained migration from an open-ended one.

Sometimes the honest recommendation is to stay and address what actually hurts: a handful of slow reports, an unsupported version, or an application layer nobody can change. Before committing either way, confirm your licensing position with Oracle or your reseller. Internal assumptions about entitlement have a way of aging quietly.

How Much Logic Belongs in PL/SQL?

Set-based data work belongs close to the data. Pulling a million rows into application memory to filter them is slower and more fragile than a well-written query, and PL/SQL exists for exactly that. Business workflow is a different matter: rules that change with the business are easier to test, version, and hand to a new developer when they live in application code.

The failure mode worth guarding against is a system where nobody can say where a rule is implemented, because it exists in a trigger, a procedure, and the application at once. Deciding the boundary and writing it down is worth more than any particular choice of where to draw it.

Why Is It Slow, and How Would You Find Out?

Start with measurement. Before adding indexes or hardware, get the execution plan and the actual wait profile for the statement people are complaining about, at the data volumes they are complaining about. The answer is frequently somewhere nobody expected, and sometimes outside the database entirely.

  • Stale statistics. The optimizer chooses badly when it is working from an old picture of the data.
  • Application-side N+1 queries. An ORM issuing thousands of small statements looks like a database problem and is not one.
  • Literals instead of bind variables. Every query becomes a new parse, and the shared pool works harder than the disks do.
  • Indexes added for a problem you no longer have. They still cost on every write, and some are no longer used by anything at all.
  • Growth nobody planned for. A query that was fine on a small table can behave completely differently once it has grown by orders of magnitude, and the answer may be partitioning or archiving.

Oracle the Database, or Oracle the Applications?

The name covers very different products, and which one you mean changes what kind of help you need. The scope of this page is the Oracle database, the custom applications built on it, and integration with packaged Oracle systems from the outside. Configuring or extending E-Business Suite or Fusion applications internally is a separate specialty and is not covered here.

If the question is how to connect an Oracle-backed system to a web or mobile front end, a partner, or a cloud service, that is the work described here and on the REST APIs page.

Connecting Oracle to Modern Applications

Giving each new application its own database account and letting it write directly is the pattern that ages worst. Direct schema access turns every future change to the database into a negotiation with every consumer, and it puts data integrity in the hands of whoever wrote the most recent client.

An API layer in front of the database gives you one place to enforce validation, one place to change when the schema evolves, and something you can log, monitor, and rate limit. It also narrows the blast radius of an eventual database migration to a single component.