Sistemas Acadêmicos em Oracle PL/SQL

Engineering decisions

1. 01

   The binary the documentation says cannot cross

   To issue the digital diploma I needed the scanned document, which lives in another database. Oracle's documentation is explicit: a pointer to a binary file selected from a remote table cannot be used locally — the database refuses. The obvious way out would be an intermediate service; the way I found needs none. Instead of reading the remote pointer, I materialize the binary straight into a local temporary table with a single insert statement fed by the remote query: from there the file is local and workable. Then I read it in 1,440-byte blocks — a multiple of three, so base64 padding never corrupts the seams between blocks — and assemble the JSON. A three-byte detail is the difference between a valid diploma and a corrupted file.

2. 02

   I led the migration of the entire application platform

   Migrating a forms-and-reports platform from one major version to the next is not copying files: every module must recompile, and whatever fails to compile has to be understood and fixed — one by one. I led that migration in the academic system and assembled the estate running today: 1,622 forms and 1,052 reports recompiled, revalidated and edited where needed, plus the shared libraries and remote directory configuration. The source archive held nearly twice as many modules, between versions and duplicates; what reached production went through curation. That estate is still live.

3. 03

   Digital diploma: I led the move from paper to a legally valid document

   I led the project that replaced the paper diploma with a digital one — a legally valid document, a regulatory requirement, no room for a wrong field. The package I wrote orchestrates the whole cycle without leaving the database: it validates twenty rules before letting a record out (course hours must match the curriculum matrix, accreditation orders must exist and not be duplicated, the official gazette page must be numeric), builds the JSON and fires the authenticated request to the registrar's API, with an HTTPS wallet and credentials read from an encrypted parameter. Register, query and annul; plus a queue with a five-minute job reconciling each diploma's state. 35 thousand digital diplomas issued.

4. 04

   The report the Provost reads every morning

   The enrollment map — 61 million rows and 182 columns rebuilt by a nightly routine — used to overrun its window. I rewrote the engine to accumulate subtotals in memory, in associative arrays keyed by text, and write once at the end: the whole package holds just two INSERTs. The table audits when each row is born, so the effect is measurable across a 20-year series: the typical generation window fell from 19 to 9 minutes, the p90 from 58 to 24 — the best level in the entire series — and the 8-hour-plus nights, once dozens per year, all but vanished.

5. 05

   Exam grading taken off-screen (2016)

   Distance learning generates exams by the millions and synchronous grading froze the student's experience. I turned the flow into a queue: answers land in a table and a job grades them in the background — what today would be called an async worker, built with the parts Oracle 11g offered. The production table holds 8.2 million exams.

6. 06

   Online entrance exam in lockdown week

   Between March 18 and 22, 2020, the in-person entrance exam ceased to exist. The online exam routine went into production within that same window, reusing the distance-learning exam engine — and admissions never stopped. Years later, the same package gained the CRM integration queue.

7. 07

   ERP migration with a financial lock

   Migrating academic and financial history with the system live takes more than ETL: 25 staging tables with 16 million reconciled rows, curriculum-matrix mapping between the two systems — and a trigger that locks the invoices under migration, so no charge is born wrong during the cutover.

8. 08

   The classic problem, solved once

   A trigger reading the very table that fired it is Oracle's most classic trap (the mutating table). Instead of N scattered workarounds, one central package holds the pattern — per-session in-memory state — and the system's triggers delegate to it. Legacy has architecture too.

9. 09

   Real-money rules

   The enrollment-kit payment slip, a reprint flow that refuses to print bank-rejected invoices, debt renegotiation, automatic enrollment confirmation for scholarship students above 75% completion. Financial rules where a wrong if becomes an undue charge to a real student — the domain where I learned to verify before trusting.

10. 10

    The database as a hub: the integrations, by name

    From inside PL/SQL, the system talks to the whole ecosystem: payroll, LDAP and Google accounts, document management (DMS), the library, the CRM (candidate queue), the regulators (diploma registry, census, national exams) — and it feeds the data platform. Payments get a chapter of their own: Pix, online slip registration at two banks and card recurrence go through Node middlewares that handle the modern TLS the database can't negotiate. And in the opposite direction the database also serves: 108 REST endpoints across 18 modules, with 57 million fully-audited calls.

11. 11

    The Java layer: the student portal on top of the database core

    What the student sees — re-enrollment, certificates, distance-learning exams, invoices, agreements, diploma — is a Java portal that crossed two generations, and I crossed both with it: 195 files and 52.7k lines carry my signature in version control, 72 of them in Spring. The architecture is honest about what lies beneath: the Spring MVC controller takes the request, the DAO calls the PL/SQL packages as stored procedures — direct JDBC, no ORM in the way — and the response returns as JSP. When the portal was rewritten, 28 of my files reappeared in the new generation: I ported my own modules instead of leaving them in the legacy. Along the way the platform moved from the OC4J container to WebLogic and the runtime jumped from Java 1.4 to 6 (with the code kept at level 5, so nothing already running would break); I did not lead the start of that migration — I validated it and made sure everything worked again.

12. 12

    When the test environment disappears

    Until 2023 there were development and staging databases; both were decommissioned and only production remained. When the safety net goes, the process becomes the environment: small changes, a dated changelog in every package header — a pattern I kept for 14 years, and the reason this entire work history could be reconstructed from the system itself — and one question before every apply: "how do I undo this?". It's the same discipline I carry to any stack.

Architecture