Short version for the impatient: before you pick an Oracle to PostgreSQL migration tool, ask it for the list of things it refuses to convert. That list is your quote. The converted DDL is the cheap part.<\/p>\n
I read the pgrecon announcement on the PostgreSQL news page<\/a> twice this week. The first read was for the feature list. The second was because the benchmark table made me suspicious, and I wanted to work out whether the suspicion was fair. I haven’t run pgrecon against a real Oracle estate, so treat everything below as a reading of what the project claims plus my own habits from client migrations, not a test report.<\/p>\n If you quote fixed-price database moves for clients, or you’re the person who gets handed one, this is the part of the problem I’d look at first.<\/p>\n Every Oracle migration pitch starts with a demo where tables go in one side and come out the other. Tables are easy. A table is columns, types and constraints, and the mapping is mostly mechanical. The money disappears somewhere else: PL\/SQL packages that keep state between calls, triggers that write audit rows in their own transaction, jobs scheduled inside the database, and application code that quietly depends on how Oracle treats empty strings.<\/p>\n That last one deserves a paragraph because it bites people who think they’ve finished. Oracle treats an empty string as NULL. PostgreSQL does not. A column that has always been “optional” in practice, with the application sending an empty string, behaves one way on Oracle and another on PostgreSQL. Your So the useful question for any tool is not “how much did it convert?” It’s “how clearly did it tell me what it left behind?”<\/p>\n The project describes three stages. Your DBA runs one read-only SQL*Plus script and sends back the spool files. The tool loads them into a local SQLite inventory and never connects to either database. Then a rule engine of 80 rules runs over the inventory, and a converter emits PostgreSQL DDL for what it can prove equivalent.<\/p>\n Two design choices stood out to me.<\/p>\n The first is the offline dump. I like this more than I expected to. A lot of Oracle estates belong to companies where nobody will hand a contractor a connection string in week one. A short, auditable script that a DBA reads line by line and runs with a read-only account is a much easier yes. The project page<\/a> says the script is short enough to audit, and that’s the right claim to make, because it’s the thing a security reviewer will actually check.<\/p>\n The second is the residue report. The page says everything the converter can’t carry faithfully becomes a named line instead of emitted DDL. Their examples are the sort of thing I’d want to see: a view using Read those four lines again. Each one is a different kind of work. The view is an afternoon. The autonomous transaction is a design decision, because you’re choosing between Here’s the flavour of what a converter has to get right. Take the classic Oracle top-N pattern:<\/p>\n The announcement says the converter now turns ROWNUM top-N bounds into Hierarchies are the other one I always check, and the announcement says That one is mechanical for simple cases. It gets uglier with There’s also a quieter item in the announcement: both extraction tiers capture character versus byte length semantics, so Now the part that made me suspicious. The project page has a table of apply errors against a live PostgreSQL 16 after converting nine schemas. pgrecon shows zero. The other tools show 40, 58, 77, 135 and 481, depending on the tool, and the page adds notes such as “PL\/SQL out of scope by design” and “tables, indexes and sequences only”.<\/p>\n I’m not saying the numbers are wrong. The page says the benchmark workflow lives in the repository and is re-run in CI, which is exactly how this should be published, and I’d rather see a reproducible vendor benchmark than none at all. But I’d hold three reservations.<\/p>\n One, the author picked the schemas. Oracle’s HR, OE and CO samples plus a handful of open-source PL\/SQL libraries are a sensible public set, and they’re also a set the author could tune against for months. Your estate isn’t in it.<\/p>\n Two, “apply errors” counts statements PostgreSQL rejected. It doesn’t count statements PostgreSQL accepted that now behave differently. A tool that declines hard cases gets a perfect score on this metric by design, and the page is open about that. Declining is the honest behaviour, and it still isn’t the same thing as converting.<\/p>\n Three, the comparison tools are measured at specific versions on specific dates, and some of the notes describe scope choices, not defects. The CYBERTEC and Dalibo notes say what they cover. Reading “40 errors” next to “0 errors” without those notes would be unfair to both.<\/p>\n The version of this that I do trust is the project’s own statement of what it declines. A tool that publishes its residue is easier to audit than one that publishes a score.<\/p>\n If I had an Oracle estate on my desk tomorrow, this is the order I’d go in. It’s the order that spends the least client money before the first real decision.<\/p>\n First, get the DBA to run the extraction script on a non-production copy with a read-only account, and read the script yourself before they do. If the client’s security team balks at that, you’ve learned something about the schedule.<\/p>\n Second, install the tool with Third, take the residue report and price each line separately, in days, with a name next to it. If a line says “redesign with a queue”, that’s a design meeting and a test plan, not an hour.<\/p>\n Fourth, check the empty-string behaviour by hand. Grep the application code for comparisons against Fifth, apply the converted DDL to a scratch PostgreSQL instance yourself. Don’t take the apply-error count from anyone’s table, including mine. If you want the data-movement side of a database move, I wrote about a MySQL to PostgreSQL one in the row that kept my write freeze<\/a>.<\/p>\n If you take on this kind of work, my portfolio<\/a> lists the database and backend projects I do for clients. For partitioned tables in particular, read the PostgreSQL partitioning chapter<\/a> before you accept any automatic mapping of Oracle interval partitions, because PostgreSQL’s declarative partitioning needs partitions to exist up front.<\/p>\n This week, do one thing: ask whichever tool you’re considering to produce its refusal list for a schema you own, and put a day estimate beside every line. If it can’t produce the list, you have your answer about the quote.<\/p>\n","protected":false},"excerpt":{"rendered":" Oracle to PostgreSQL migration quotes live or die on what the converter refuses to convert. Here is how I would read pgrecon’s residue report and its benchmark.<\/p>\n","protected":false},"author":2,"featured_media":801,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"","rank_math_description":"Oracle to PostgreSQL migration quotes live or die on what the converter refuses to convert. Here is how I would read pgrecon's residue report and its benchmark.","rank_math_focus_keyword":"oracle to postgresql migration","rank_math_canonical_url":"","rank_math_robots":"","footnotes":""},"categories":[237],"tags":[878,877,879,238],"class_list":["post-802","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","tag-database-migration-2","tag-oracle","tag-pgrecon","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/802","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/comments?post=802"}],"version-history":[{"count":0,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/802\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media\/801"}],"wp:attachment":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media?parent=802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/categories?post=802"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/tags?post=802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Why the schema script is the cheap part<\/h2>\n
WHERE col IS NULL<\/code> filters, your NOT NULL<\/code> constraints and your unique indexes all change meaning without a single syntax error. No DDL converter can fix that. Only an assessment that goes looking for it can.<\/p>\nWhat pgrecon says it does<\/h2>\n
JSON_TABLE<\/code> that needs a hand rewrite because PostgreSQL 17 added it with different clause syntax, a trigger that runs an autonomous transaction, a compound trigger, an external file locator column with no counterpart.<\/p>\ndblink<\/code>, a queue, or changing what the audit table means. The compound trigger is a restructure. The file locator is a conversation with whoever owns the files. A flat list with reasons attached is how you turn “it’s about six months” into a number you can defend.<\/p>\nWhat the conversions actually look like<\/h2>\n
-- Oracle\nSELECT * FROM (\n SELECT employee_id, last_name\n FROM employees\n ORDER BY salary DESC\n)\nWHERE ROWNUM <= 10;\n<\/code><\/pre>\n-- PostgreSQL\nSELECT employee_id, last_name\nFROM employees\nORDER BY salary DESC\nLIMIT 10;\n<\/code><\/pre>\nLIMIT<\/code>. Fine, but notice what the tool has to prove. If somebody wrote the lazy version, WHERE ROWNUM <= 10 ORDER BY salary DESC<\/code> with no subquery, Oracle applies the row limit before the sort and returns ten arbitrary rows. A mechanical conversion to LIMIT<\/code> would silently start returning the real top ten. That’s arguably the behaviour the author wanted, and it’s still a change that someone should sign off on. I’d want to know whether the tool flags that shape or just rewrites it.<\/p>\nCONNECT BY<\/code> becomes WITH RECURSIVE<\/code>:<\/p>\n-- Oracle\nSELECT employee_id, manager_id, LEVEL\nFROM employees\nSTART WITH manager_id IS NULL\nCONNECT BY PRIOR employee_id = manager_id;\n<\/code><\/pre>\n-- PostgreSQL\nWITH RECURSIVE tree AS (\n SELECT employee_id, manager_id, 1 AS level\n FROM employees\n WHERE manager_id IS NULL\n UNION ALL\n SELECT e.employee_id, e.manager_id, t.level + 1\n FROM employees e\n JOIN tree t ON e.manager_id = t.employee_id\n)\nSELECT employee_id, manager_id, level FROM tree;\n<\/code><\/pre>\nCONNECT_BY_ISCYCLE<\/code>, SYS_CONNECT_BY_PATH<\/code> and ORDER SIBLINGS BY<\/code>, none of which the announcement mentions. Absence from a press release isn’t evidence of absence from the tool. I’d test exactly those on a sample schema before believing any summary.<\/p>\nVARCHAR2(30 CHAR)<\/code> maps to varchar(30)<\/code>. This matters because PostgreSQL’s varchar(n)<\/code> counts characters. A VARCHAR2(30 BYTE)<\/code> column on a UTF-8 database holds fewer characters than the same number in PostgreSQL. Moving to the looser limit is usually harmless. Moving data the other direction later, if you keep Oracle as a fallback during cutover, is where it hurts.<\/p>\nReading a benchmark the vendor ran<\/h2>\n
How I’d run an assessment this week<\/h2>\n
pip install pgrecon<\/code> and run the report before the converter. Sort findings by severity and read the evidence lines. Don’t skim the summary. A line-level hit on package-level state or on database links is worth more than a hundred green rows.<\/p>\n''<\/code> and for string concatenation over nullable columns. Oracle’s ||<\/code> treats NULL as an empty string, and PostgreSQL’s returns NULL, so first_name || ' ' || middle_name<\/code> quietly starts blanking whole names. A schema dump can’t show you that.<\/p>\n