DEV Community: Saad Akram

The Oracle Catalog View That Knows What You're About to Be Billed For

Saad Akram — Thu, 24 Sep 2026 09:52:00 +0000

DBA_FEATURE_USAGE_STATISTICS has been quietly tracking your license exposure the whole time. Most teams find out what it says during an audit instead of before one.

Ask most DBA teams what their Oracle license exposure looks like and you'll get a shrug, a guess, or a story about the last audit. That's strange, because the answer is usually already sitting in their own database, in a dictionary view almost nobody queries on purpose: DBA_FEATURE_USAGE_STATISTICS.

Oracle Database tracks, on its own, whether separately-licensed options and management packs have ever been used — Partitioning, Advanced Compression, the Diagnostics and Tuning Packs, and dozens of others. It isn't a third-party monitoring tool watching for this. It's built into the engine, has been for a long time, and it's the same data source Oracle's own License Management Services reviews draw from during a formal audit. The uncomfortable part: usage doesn't need to be a deliberate, ongoing decision to count. A feature a DBA switched on once, years ago, to test something, can still show up as detected today.

What we read, and how

pgrecon's offline extraction script pulls this view — read-only, alongside the edition banner and CPU count — as part of the same SELECT-only run that produces everything else. If the account running the script doesn't have the catalog role needed to see it, the file that would have held it simply comes back empty. The run doesn't fail, and nothing gets guessed in its place; it's the same never-crash, never-silently-skip rule that governs the rest of the tool.

The fixed-price assessment builds a license-exposure section from what comes back: a map from each detected feature to the separately-licensed option it evidences, whether the usage is current or only historical, and a processor-based count of what that usage would represent if licensed. It also flags something more specific and more useful than a generic warning — a high-severity line specifically when a database running Standard Edition shows usage of a feature that Standard Edition cannot license at any price. That's not a maybe. If the usage is real and the edition is Standard, that's a compliance conversation regardless of intent.

What it deliberately doesn't do

It never states a currency figure. Not a range, not an estimate, nowhere in the tool's output. That isn't caution for its own sake — a generic number would just be wrong, because actual exposure depends on your specific contract terms, your edition, your core counts, and pricing that varies enormously between organizations. A figure that looks precise but isn't grounded in your actual agreement is worse than no figure at all; it's a false sense of certainty. Pricing is a conversation for the engagement, next to the actual contract. What the assessment gives you instead is the fact pattern — what's been used, on what edition, currently or historically — so that conversation starts from evidence instead of a guess.

The point isn't to alarm anyone

It's that this isn't hidden information requiring forensic discovery. It's a standard Oracle catalog view, readable with a SELECT statement any DBA can run and review before anyone else sees the output. The only real choice is whether you read it on your own schedule, or on an auditor's.

pgrecon's extraction script and its license-exposure logic are documented at muzzammil242.github.io/pgrecon. If you want the full board-ready version of this - grade, findings, and license exposure computed from your own database's usage statistics - that's the fixed-price assessment: book one at DevCrafter AI.

We Converted Oracle's CONNECT BY to PostgreSQL WITH RECURSIVE — Then Verified It Row by Row Against Oracle

Saad Akram — Mon, 14 Sep 2026 08:47:39 +0000

Getting the SQL to parse isn't the same thing as getting it right. We loaded the same data into both databases and diffed the output.

CONNECT BY is one of the most-used constructs in Oracle PL/SQL estates and one of the least portable. It's Oracle's own syntax for hierarchical queries — org charts, bill-of-materials trees, category hierarchies — and PostgreSQL has no direct equivalent. There's no CONNECT BY keyword to translate into. The entire query has to be re-derived as a recursive common table expression, and Oracle's pseudo-columns (LEVEL, SYS_CONNECT_BY_PATH) have to be reconstructed by hand inside it, because PostgreSQL doesn't have them either.

A typical Oracle hierarchical query:

SELECT empno, ename, mgr, LEVEL,
       SYS_CONNECT_BY_PATH(ename, '/') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

And its PostgreSQL equivalent:

WITH RECURSIVE org_chart AS (
  SELECT empno, ename, mgr, 1 AS level,
         '/' || ename AS path
  FROM emp
  WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, oc.level + 1,
         oc.path || '/' || e.ename
  FROM emp e
  JOIN org_chart oc ON e.mgr = oc.empno
)
SELECT * FROM org_chart;

That translation is well documented and not hard to get syntactically right. Getting it semantically right — same rows, same evaluation order, same edge cases — is a different problem, and it's the one that actually matters, because a query that parses but returns the wrong tree is worse than a query that honestly refuses to convert at all.

The scope, stated plainly

pgrecon converts a specific, provable subset of CONNECT BY: one table, one PRIOR equality, projections of plain columns, LEVEL, and SYS_CONNECT_BY_PATH with a literal separator. START WITH filters the starting rows, and any WHERE clause outside the hierarchy applies after it, matching Oracle's own evaluation order. NOCYCLE, ORDER SIBLINGS BY, joins inside the hierarchical query, and PRIOR over an expression instead of a plain column all decline by name instead of getting a best-effort guess.

The actual proof

Syntax correctness was never the bar we cared about. So we ran both queries — the original on a live Oracle database, the converted version on the PostgreSQL schema pgrecon produced — against the same loaded data, and compared the output directly:

empno|ename |mgr  |level|path
7839 |KING  |     |1    |/KING
7566 |JONES |7839 |2    |/KING/JONES
7654 |MARTIN|7566 |3    |/KING/JONES/MARTIN
7788 |SCOTT |7566 |3    |/KING/JONES/SCOTT

Identical, row for row: employee number, name, manager, level, and the full path string built by SYS_CONNECT_BY_PATH. Not "looks right." Not "passed a spot check." The same rows, in the same order, read back from two different database engines running two different queries that are supposed to mean the same thing.

Why we don't skip this step

It would be easy to treat "does it parse" as good enough and move on — most tools do, because standing up a live Oracle instance to check every conversion is slower and more annoying than trusting the transpiler. But a recursive CTE that's subtly wrong — an off-by-one on LEVEL, a path built in the wrong direction, a starting condition that includes one row too many — will still run without error. It just won't be your org chart anymore. The only way to know the difference is to ask the original database and compare, which is what we did, and what we'd rather keep doing on every release than assume still holds.

The full conversion scope, and the residue rules for what it declines, live at muzzammil242.github.io/pgrecon. For an inventory of every hierarchical query in your own schema — which ones convert and which need a human — the fixed-price assessment covers it: book one at DevCrafter AI. Try it yourself at The github repository.

The Empty-String Trap: The Oracle Bug That Breaks Silently on PostgreSQL

Saad Akram — Mon, 07 Sep 2026 11:01:00 +0000

Oracle has treated '' as NULL since long before PostgreSQL existed. Almost nothing that migrates naively survives that difference intact.

Try this on Oracle:

INSERT INTO customers (name, middle_name) VALUES ('Ana', '');
SELECT COUNT(*) FROM customers WHERE middle_name IS NULL;
-- returns 1

Now run the same two statements on PostgreSQL. The count comes back 0. Nothing is broken — both databases are behaving exactly as designed. They just disagree, fundamentally, about what an empty string is. Oracle has treated '' as NULL since long before PostgreSQL existed as a project. PostgreSQL, correctly by the SQL standard, treats them as two different things: one is the absence of a value, the other is a value that happens to be zero characters long.

Nobody sets out to depend on this. It just happens, quietly, in code that was only ever tested against Oracle.

Where it actually bites: concatenation

The clearest place this surfaces is string concatenation. Oracle's || operator treats a NULL operand as if it were an empty string and skips it:

-- Oracle
SELECT 'Old sal: ' || :old_value FROM dual;
-- :old_value is NULL -> returns 'Old sal: '

PostgreSQL's || does the opposite, and it's the standard-compliant behavior: concatenating anything with NULL returns NULL.

-- PostgreSQL, naive port
SELECT 'Old sal: ' || old_value;
-- old_value is NULL -> returns NULL

We hit this ourselves converting a trigger that logs old and new values on update. Ported literally, a line that should have printed Old sal: printed Old sal: <NULL> instead — technically valid PostgreSQL, semantically wrong, and exactly the kind of thing that passes every test that doesn't happen to touch a NULL column.

The fix, and what it actually preserves

Every concatenation chain pgrecon emits gets wrapped as NULLIF(concat(...), '') instead of a plain || chain. concat() ignores NULL arguments the way Oracle's || does, which covers the common case. NULLIF restores the one case Oracle does return NULL for: every operand empty, because in Oracle an all-empty result of || is itself '', and '' is NULL. After the fix, the same trigger prints Old sal: — matching Oracle's own output exactly, including on the all-null case, which is where a simpler fix like CONCAT_WS still gets it wrong: it returns '', not NULL.

We checked this the same way we check everything else: applied to a live PostgreSQL database and executed, not just inspected. WHERE a || b = c || d on data where both sides can be all-NULL is a real query shape, and it matches the wrong rows if this isn't handled correctly — so we ran it, before the fix and after, and confirmed the after.

One honest gap remains, and we'd rather say so than leave it implied: RAISE NOTICE '%', x prints the literal text <NULL> when the whole wrapped expression is NULL, where Oracle's equivalent prints a blank line. It's cosmetic — output formatting on a debug log line, not a value anywhere a query result depends on — and it's on the list, not swept under it.

Why this matters beyond one operator

|| is just the visible case. The same NULL-versus-empty-string identity quietly changes the meaning of DECODE, of WHERE column = '', of any report that counts "blank" fields. An assessment that doesn't check for this will tell you a migration is riskier or safer than it actually is — not because anyone lied, but because Oracle and PostgreSQL agree on almost everything except this, and "almost everything" is exactly where the surprises hide.

pgrecon's free tier ships this rewrite by default: muzzammil242.github.io/pgrecon has the install command and the full benchmark. If you want a written, line-by-line inventory of where your own schema depends on Oracle's NULL semantics, that's exactly what the fixed-price assessment surfaces — book one at DevCrafter AI.

Zero Errors Isn't a Benchmark Score. It's a Design Law.

Saad Akram — Mon, 31 Aug 2026 14:25:06 +0000

Anyone can get lucky once. Here's the actual mechanism that keeps pgrecon's output at zero apply errors release after release — including a bug it found in itself along the way.

A benchmark result is a snapshot. Run it again next week, on a different schema, and a tool that scored well can score badly — unless zero errors isn't the result of a benchmark at all, but a rule the software refuses to break.

That's the distinction that matters here. pgrecon's rule is simple to state and expensive to keep: never emit a SQL statement PostgreSQL itself would reject. Not "try to be correct." Not "warn if unsure." Refuse, and name the reason, if the tool can't prove the output is right.

The mechanism, not the marketing
Here's what actually enforces that rule: every conversion pgrecon writes gets applied to a real, running PostgreSQL 16 database before it ships — not as a one-off benchmark stunt, but as a standing step in how the tool gets built. If a statement doesn't apply cleanly, it doesn't ship. There's no clever validator catching problems after the fact. The release process itself is the validator.

That process finds real bugs, and it's more convincing to show one than to describe the discipline in the abstract.

While converting triggers from a test schema, one of pgrecon's own outputs failed to even create. PostgreSQL refuses, at CREATE TRIGGER time, a WHEN clause that reads the new row on a DELETE trigger — because there is no new row on a delete. Oracle allows exactly this, and quietly evaluates the condition as false, row by row. The fix took under an hour: move the condition out of the WHEN clause and into the trigger body as a guard, so PostgreSQL accepts the CREATE and the trigger still fires on precisely the rows Oracle would have fired on. After the fix, it applied at zero errors and fired correctly under live inserts, updates, and deletes — verified by running them, not assumed from reading the code.

That's one bug from one afternoon. The same benchmark sweep that produced [the nine-schema comparison in our last post] surfaced several more in the same sitting — an Oracle-specific timestamp function passing through unmapped, an internal index Oracle generates on its own getting emitted as if it were user DDL, arithmetic that silently drifted from Oracle's exact decimal math to floating point. Every one got caught the same way: applied to real PostgreSQL, failed or misbehaved, fixed, reapplied, confirmed. None of them shipped.

What zero errors doesn't claim
It's worth being exact here, so here's the line as written for exactly this purpose, unchanged: "Across nine benchmark schemas, pgrecon emitted zero statements PostgreSQL rejected — and everything it cannot prove is a named line in a residue report. That does not mean everything converts: about half of all objects convert mechanically, above 90 percent on business-shaped schemas, and the gap is dominated by Oracle packages and object types, which no tool converts mechanically."

Zero errors and honest residue are two separate promises, and pgrecon only gets to make the first one by taking the second one seriously. A tool that guesses when it's unsure can post a lower error count simply by refusing less — right up until someone runs the output and finds out which guesses were wrong. The discipline here is refusing more, not less, and proving the difference on a live database every time, not just when a benchmark happens to be watching.

pgrecon is Apache-2.0: https://github.com/Muzzammil242/pgrecon has the source, the benchmark, and the install command. When the free tier hits its limit and you need a real number for what a migration will cost, the fixed-price assessment is the next step — book one at DevCrafter AI.

Oracle to PostgreSQL Migration Tools: A Live Database Benchmark

Saad Akram — Tue, 25 Aug 2026 09:30:06 +0000

We Ran Five Oracle-to-PostgreSQL Converters Against the Same Live Database. Only One Had Zero Errors.
Nine schemas, five tools, one PostgreSQL 16 database, and a definition of “error” nobody gets to argue with: a statement PostgreSQL itself rejects.

Every Oracle-to-PostgreSQL converter claims to convert your schema. Almost none of them tell you what happens when you actually run the output against a real database. So we did it for them.

Over the last two weeks we took nine Oracle schemas — Oracle’s own HR, OE, and CO sample schemas, four well-known open-source PL/SQL projects (utPLSQL, PLJSON, Logger, and Alexandria), and two lab schemas built to be nasty on purpose — and ran every one through five Oracle-to-PostgreSQL converters, including pgrecon, the tool my cofounder Muzzammil has spent the year building. Then we took every tool’s output and applied it, statement by statement, to a live PostgreSQL 16 database.

An error, in this benchmark, has exactly one definition: a statement PostgreSQL itself rejects. Not a missing feature. Not a stylistic complaint. A CREATE, an ALTER, or an INSERT that the target database refuses to run. That’s a low bar and a hard one — it’s the same bar a real migration hits in production, whether a tool warned you about it or not.

The numbers

Two of those deserve a sentence each. Ora2Pg’s output file carries a directive that stops the whole load at the first error — understandable behavior for a tool that expects you to fix things as you go, but it meant we had to remove that directive and pre-create an extension its output silently assumes, just to see the real count underneath. EDB Migration Toolkit’s number is generous to it: for a schema with 49 packages, it emitted exactly one statement — CREATE SCHEMA — and printed a success message. Zero attempts reads a lot like zero errors if nobody checks.

What “zero” doesn’t mean
It doesn’t mean pgrecon converts everything. Across the nine schemas, about half of all objects converted mechanically — above 90 percent on schemas shaped like a normal business database, well below that on the two schemas that exist to showcase Oracle object types and PL/SQL packages, which is exactly the part nothing converts mechanically yet. The gap is real, and it’s named: every object we didn’t convert shows up as one line in a residue report, with a reason, instead of silently vanishing or shipping broken.

That’s the part of this benchmark that actually matters. A tool that emits nothing invalid but also converts nothing would score zero errors too — the number only means something next to how much got attempted. Which is why we’re publishing the schemas, the tool versions, and the method, not just the table.(https://muzzammil242.github.io/pgrecon/benchmark.html)

If a number in there looks wrong to you, that’s a fair reaction — reproduce it. The schemas are public, the tool versions are named, and pgrecon’s own conversion output gets applied to a live PostgreSQL database before every release, not just for this post.

pgrecon is Apache-2.0 and free to run: https://github.com/Muzzammil242/pgrecon has the install command, the full write-up, and the source. If Oracle costs are on your organization’s roadmap this year, a fixed-price assessment is the cheapest way to find out what a real migration would cost — book one at DevCrafter AI.