# Dialect Desk

> Paste one Oracle table and the SQL your code runs against it, and get every
> behavioural difference PostgreSQL will introduce - sorted by whether it will
> refuse to compile or compile and quietly return a different value.

Site: https://dialect-desk.skillsafe.ai/

## What it is

A single-page app for one sitting of Oracle-to-PostgreSQL migration work. The
work object is a MIGRATION SHEET: which two dialects, the target collation, the
length semantics, the table's row count, then one line per column - its Oracle
type, its nullability, the PostgreSQL type someone has already chosen, and some
real sample values - then the SQL the application actually runs against those
columns.

Everything the page computes it computes in the browser, free, before anything is
spent: the type mapping and what each choice loses, the byte-versus-character
width arithmetic per declared value, every combination of every `||` chain, the
exact loss from a truncating division, the point where a `double precision` stops
holding integers, and the two sort orders with the position at which they diverge.

## The one thing to know

EVERY DIFFERENCE IS EITHER LOUD OR SILENT, AND THEY COST THREE ORDERS OF
MAGNITUDE APART. Loud means the statement will not compile - `NVL`, `DECODE`,
`ROWNUM`, `MINUS`, `FROM DUAL`, `seq.NEXTVAL`, `TRUNC` on a date, an upper-case
quoted identifier. Mechanical, found on the first afternoon from a stack trace,
an hour each. Silent means it compiles and returns a different value, and is
found by a customer in a quarter. 7 of the 13 differences this page
checks are silent.

ORACLE HAS NO EMPTY STRING. `''` IS NULL, so `a || b` ignores it, `= ''` never
matches a row, and a NOT NULL VARCHAR2 enforced non-emptiness for free - and all
three stop being true. For a `||` chain of k column operands the two dialects
agree on 2^k of the 3^k combinations, so `3^k - 2^k` differ: at k=3 that is 19
of 27. `concat()` ignores NULL in both dialects and is the entire fix.

`VARCHAR2(n)` COUNTS BYTES AND `varchar(n)` COUNTS CHARACTERS. A `VARCHAR2(40)`
holds 40 bytes, which is 40 characters of ASCII, 13 of Chinese and 10 of
emoji. `varchar(40)` holds 40 characters of any of them - up to 160 bytes,
4× the old limit. Nothing errors, because a limit getting looser is not
an error. Only `CHECK (octet_length(col) <= 40)` keeps it.

THE TARGET TYPE DECIDES THE ARITHMETIC. `NUMBER(10) -> integer` makes every `/`
truncate toward zero, so `10 / 4` returns 2 where Oracle returned 2.5;
`-> numeric(10)` does not. `NUMBER -> double precision` is exact to 9,007,199,254,740,992
and then starts skipping integers, so `122222222222222222` arrives as `122222222222222220`. An
Oracle DATE carries a time to the second, so `-> date` sets every value to
midnight. FIX THE MAPPING FIRST: it is upstream, so one type changed clears
several findings at once.

`SYSDATE` ADVANCES DURING A TRANSACTION and `now()` does not - `now()` and
`CURRENT_TIMESTAMP` return the time the transaction began.
`statement_timestamp()` is the faithful translation.

ORACLE'S DEFAULT SORT IS BINARY, so every uppercase letter precedes every
lowercase one: `Apricot`, `Banana`, `Zebra`… becomes `Ångström`, `apple`, `Apricot`… under a locale
collation. The first difference is at position 1, inside a page of 10, so a
paginated query returns different rows. `ORDER BY col COLLATE "C"` restores it.

## The lanes

- **plan** — Turn a table and its queries into a sheet. A migration plan usually starts from the DDL, which is the one place none of the interesting differences live: they live in the target types someone chose and in the SQL that runs against them. This takes the table as you describe it - what it holds, what the code does with it, what the tool already picked - and writes the sheet the other four lanes read.
- **read** — Every difference, sorted by whether it announces itself. The paid read of what the free panel computes. Every column's type mapping and what it loses, every expression's behaviour in both dialects, and the one ordering that matches the cost: the statements that will not compile first because they are an afternoon, then the ones that compile and return a different value, because those are the migration.
- **nulls** — The empty string, NULL, and the queries that cannot tell them apart. Oracle has no empty string: `''` is NULL, so `a || b` ignores it, `= ''` matches nothing and NOT NULL enforces non-emptiness for free. PostgreSQL keeps them apart, and all three of those stop being true. This enumerates every combination of every `||` chain on the sheet - not a sample, all of them - and says which rows change and what the one-word fix is.
- **widths** — The widths: a byte limit that becomes a character limit. `VARCHAR2(40)` is forty BYTES in Oracle and `varchar(40)` is forty CHARACTERS in PostgreSQL, so in UTF-8 the same declaration admits up to four times as much data - silently, since nothing errors when a limit gets looser. This counts every declared value four ways, says which ones cross the line, and writes the one-line check that keeps the old limit.
- **decide** — Decide what changes: a type, a query, or the application's mind. Sorts every difference into what a target type fixes before the cutover, what only a rewritten query fixes, and what nothing fixes because the two dialects genuinely disagree about what the data means. The third bucket is the honest one: once `''` and NULL are two values, no cast decides which one the application meant - and each survivor gets the assertion that pins it.

## The findings

26 findings across 5 scopes (sheet, column, widths, nulls, expr): 2 errors, 17 warnings, 7 notes.

- `NOTHING-TO-PORT` (error) — The sheet declares no columns
- `A-COLUMN-HAS-NO-TYPE` (error) — A column declares no Oracle type
- `THE-SHEET-HAS-LINES-THIS-PAGE-COULD-NOT-READ` (warning) — Some lines were not understood
- `THE-SAME-COLUMN-TWICE` (warning) — A column is declared twice
- `THE-LENGTH-SEMANTICS-WERE-ASSUMED` (note) — BYTE length semantics were assumed
- `THE-ROW-COUNT-WAS-ASSUMED` (note) — The table's row count was assumed
- `A-TARGET-TYPE-IS-NOT-THE-VALUE-PRESERVING-ONE` (warning) — A chosen target type does not preserve the values
- `A-DATE-LOSES-ITS-TIME` (warning) — An Oracle DATE mapped to `date` drops the time
- `AN-ID-LOSES-ITS-LAST-DIGITS` (warning) — A number mapped to a binary float stops being exact
- `AN-INTEGER-TARGET-MAKES-DIVISION-TRUNCATE` (note) — An integer target will make division truncate, and no division was shown
- `THE-COLUMN-STOPS-COUNTING-BYTES` (warning) — A byte-counted column becomes a character-counted one
- `A-VALUE-ORACLE-REFUSED-NOW-FITS` (warning) — A value the old column rejected fits the new one
- `A-VALUE-FITS-NEITHER-COLUMN` (note) — A declared value is too long for both dialects
- `THE-BYTE-LIMIT-IS-ONLY-KEPT-BY-A-CHECK` (note) — Keeping the old byte limit takes a CHECK constraint
- `CONCATENATION-STOPS-IGNORING-NULL` (warning) — `||` stops ignoring NULL and starts returning it
- `AN-EMPTY-STRING-STOPS-BEING-NULL` (warning) — The empty string stops being NULL
- `A-NOT-NULL-COLUMN-STARTS-ACCEPTING-A-BLANK` (warning) — A NOT NULL column starts accepting the empty string
- `IS-NULL-STOPS-FINDING-THE-BLANKS` (warning) — `IS NULL` stops finding the blank rows
- `A-QUERY-RETURNS-A-DIFFERENT-VALUE-AND-NO-ERROR` (warning) — Some queries return a different value and no error
- `THE-SORT-ORDER-CHANGES` (warning) — `ORDER BY` returns the rows in a different order
- `THE-FIRST-PAGE-CHANGES` (warning) — The reordering reaches the first page
- `THE-CLOCK-STOPS-INSIDE-A-TRANSACTION` (warning) — `SYSDATE` and `now()` are not the same clock
- `SUBSTR-COUNTS-FROM-A-DIFFERENT-PLACE` (warning) — `SUBSTR` counts positions from a different place
- `THIS-WILL-NOT-COMPILE` (warning) — Some statements will not compile at all
- `EVERY-SILENT-DIFFERENCE-NEEDS-AN-ASSERTION` (note) — Each silent difference needs a test that fails before the fix
- `NOTHING-CHANGES-FOR-THESE` (note) — Some expressions survive the move unchanged

Several are gated on a WITNESS rather than on a shape: the widening needs a
declared value Oracle actually refused, the NOT NULL/blank finding needs a blank
to occur somewhere on the sheet, and `IS NULL` needs the column it names to hold
one. A warning that fires on every usable sheet is furniture - and every
byte-counted Oracle column there has ever been would trip the widening on shape
alone.

## Sheet grammar

A sheet has a header, a `COLUMNS:` block and an `EXPRESSIONS:` block.

```
FROM: Oracle 19c            which dialect it is leaving
TO: PostgreSQL 16           which it is arriving at
COLLATION: en_US.UTF-8      the target database's collation
LENGTH: byte                Oracle's NLS_LENGTH_SEMANTICS (default: byte)
ROWS: 2400000               the table's size, which scales the counts
JOB: what this table is     free text, for the report

COLUMNS:
  name  ORACLETYPE  [null|notnull]  [-> target [bytecheck] [notempty]]
        [values="a|b|<null>|<empty>"]

EXPRESSIONS:
  <the SQL the application runs, one per line>
```

**The column line.** The name and the Oracle type are required; everything else is optional and every one of them changes the answer.

- `notnull` matters because in Oracle a NOT NULL VARCHAR2 could not hold `''`.
- `-> target` is the PostgreSQL type someone has already chosen. Leave it off and this page recommends the value-preserving one instead of judging a choice nobody made.
- `bytecheck` means the target comes with `CHECK (octet_length(col) <= n)`, which is the only way `varchar(n)` can keep a byte limit.
- `notempty` means it comes with `CHECK (col <> '')`, which is how the non-emptiness that Oracle's NOT NULL gave away is put back.
- `values=` are real sample values, pipe-separated. `<null>` and `<empty>` are how the two invisible ones are written. They are what turn a *shape* into a *witness*: without a value that Oracle actually refused, the widening is theory.

**The expression lines** are the SQL as it is written today, one per line, with the clause word kept (`select`, `where`, `order by`) because it is what tells this page an `ORDER BY` is an `ORDER BY`. Trailing semicolons are ignored.

Anything unreadable becomes a stated problem rather than a guess: an unknown header key, a line outside a block, a column with no type, an unreadable type and a duplicate name are each reported with their line number.

## Worked sheets

- **customers** — Eight queries, six of them silently different. 6 columns, 9 expressions, 6 silent, 2 loud.
- **ported** — The same table, ported. 6 columns, 9 expressions, 1 silent, 0 loud.
- **nulls** — 27 combinations, 19 of them different. 4 columns, 5 expressions, 4 silent, 0 loud.
- **widths** — Byte-counted columns, multi-byte values. 5 columns, 3 expressions, 0 silent, 1 loud.
- **numbers** — An 18-digit id through a double. 5 columns, 5 expressions, 1 silent, 0 loud.
- **sort** — Only the order changes, and it changes at row one. 4 columns, 4 expressions, 1 silent, 0 loud.
- **loud** — Eight statements, none of which compile. 3 columns, 8 expressions, 0 silent, 8 loud.
- **clean** — Designed to be portable, and is. 5 columns, 4 expressions, 0 silent, 0 loud.
- **broken** — Lines this page could not use. 2 columns, 2 expressions, 1 silent, 0 loud.

## What it cannot do

What this page cannot do.

- **It reads a sheet, not a database.** It knows the types and the SQL you type in. It cannot see your data, so "how many rows change" is a proportion of the row count you declared, scaled by the sample values you declared - not a count from a table.
- **It cannot run either dialect.** Every claim about Oracle's behaviour comes from the documented semantics, not from execution. Where the arithmetic is checkable in a browser - UTF-8 byte counts, the IEEE-754 boundary, the two orderings, the concatenation truth table - it is checked against the platform's own implementation and the two have to agree. Where it is not, it is a rule, and rules have exceptions your version will know about and this page will not.
- **The collation is approximated.** PostgreSQL's locale collations come from the operating system's ICU or glibc tables, and the ordering here comes from the browser's `Intl.Collator`. The *shape* of the difference - binary order puts every uppercase letter first, a locale does not - is reliable. An exact tie-break between two accented forms is not; verify those against the real database.
- **`NLS` settings can move the ground.** A database created with `NLS_LENGTH_SEMANTICS=CHAR` has no width difference at all, and one with a non-binary `NLS_SORT` has no sort difference. Both are stated as assumptions rather than buried, and both are worth one query to confirm.
- **It does not see PL/SQL, triggers, refcursors or the transaction semantics.** Package state, `WHEN OTHERS`, autonomous transactions, read-consistency differences and Oracle's lack of a real boolean type in older versions are all real migration work and none of it is on this page.
- **A clean sheet means a clean sheet.** It means these columns and these expressions do not differ. It does not mean the migration is safe, because the expressions this page checked are the ones you pasted.

## API

`POST https://api.skillsafe.ai/v1/app-api/dialect-desk/run` with an app session
token. The body IS the input object - there is no `input` wrapper and no
`X-App-Slug` header. Fields: `task` (one of plan, read, nulls, widths, decide), `rules`, `prescan`, plus the lane's own fields.

See https://dialect-desk.skillsafe.ai/api.html for worked requests and https://dialect-desk.skillsafe.ai/tokens.html for the token flow.

## Provenance

Derived from the reviewing-oracle-to-postgres-migration, creating-oracle-to-postgres-migration-bug-report and scaffolding-oracle-to-postgres-migration-test-project skills: https://github.com/github/awesome-copilot. Not affiliated with or endorsed by the
authors of those skills.