# Oracle

Oracle in Ptah - what renders, plans and reads back on the 23 and 21 release lines, bare identifiers, type mappings, object types, PL/SQL routines, and versioned migrations.

Source: https://docs.ptah.run/edge/databases/oracle/

Oracle renders, plans, connects and reads a live catalog, against two measured
release lines, 23 and 21. A schema Ptah applies reads back as itself on both:

```text
ptah schema inspect --db-url oracle://user:pass@host:1521/FREEPDB1 > live.hcl
ptah schema diff --from oracle://user:pass@host:1521/FREEPDB1 --to live.hcl
Schemas are synced, no changes to be made.
```

Comparing a **declared file** against a live Oracle catalog does not converge
yet. Tables, columns and indexes fold across the case difference between a
declaration and the catalog; constraint names do not, so an unnamed `CHECK`
declared as `orders_total_check` and stored as `ORDERS_TOTAL_CHECK` is dropped
and re-added on each apply. Tracked by
[#1875](https://github.com/stokaro/ptah/issues/1875).

## Identifiers and type mappings

Identifiers are written **bare**, which is Oracle-only among the engines here
and is forced by the engine rather than chosen. Oracle folds an unquoted name to
upper case and preserves a quoted one, so a name has two spellings, and a
declaration has to agree with every expression that references it. A `CHECK` or
a generated expression is author text Ptah does not rewrite, so the declaration
is what moves:

```sql
CREATE TABLE q ("view_count" NUMBER(10) CHECK (view_count >= 0))   -- refused
CREATE TABLE b (view_count NUMBER(10) CHECK (view_count >= 0))     -- accepted
```

A name Oracle refuses bare — a reserved word such as `size`, `comment` or
`user`, or a name carrying a character outside a plain identifier — is quoted on
both sides instead, so the two still agree.

Two type mappings are worth knowing because Oracle has no direct equivalent:
`BOOLEAN` becomes `NUMBER(1)`, with `true` and `false` written as `1` and `0`,
and an enum becomes `VARCHAR2(255)` with a `CHECK` listing its values.

## What the 23 and 21 lines do differently

The `IF [NOT] EXISTS` guards are a 23-line feature. On 21 they are refused, and
the capability preset for that line reflects it, so a plan for a 21 server omits
them.

**Domains are a 23-line feature too**, and the only object where the two lines
differ rather than the spelling of a guard. Oracle 23 has a real `CREATE
DOMAIN`; Oracle 21 answers `ORA-00901` to it and has no `ALL_DOMAINS` view at
all. So a declared domain is planned, rendered, read back and compared on 23,
and on 21 it is refused before any SQL — a column declared with a type the
target cannot create would be left naming something the server has no
definition of.

A Go annotation declares an Oracle domain or object type with
`dialects="oracle"`: a `domain` or `composite` declaration without a scope is
a PostgreSQL type, and an Oracle plan refuses it. An SQL file or an HCL
document read for an Oracle target keeps its `CREATE DOMAIN` statements and its
`domain` and `composite` blocks as Oracle's, and so does an HCL block whose
`dialect` attribute names `oracle`. The SQL reader takes the statements Ptah
writes, `CREATE DOMAIN IF NOT EXISTS` and `CREATE OR REPLACE TYPE ... AS
OBJECT`, so a rendered schema reads back. `schema inspect` writes the blocks of
an Oracle database without a `dialect` attribute, since the document is
written for Oracle.

A plan creates the domains, then the object types, each by name, with a type
another one names first, before the tables, and drops them after the tables.
A declared comment is written as a SQL comment line before the creation:
Oracle stores no comment on a domain or an object type.

A domain is compared by its base type and its `NOT NULL`. The base type is
compared in the spelling the catalog keeps, so `INTEGER` and `NUMBER(*,0)`,
`TIMESTAMP` and `TIMESTAMP(6)`, `FLOAT` and `FLOAT(126)`, or `VARCHAR(10)` and
`VARCHAR2(10)` are one type each. Its `CHECK` and default are written when the
domain is created and never compared, since the catalog stores its own
spelling of both. A plan that changes a domain's base
type or `NOT NULL` is refused, naming the domain. Oracle has no statement for
either change: `ALTER DOMAIN` changes a domain's `DISPLAY`, `ORDER` and
annotations only, `DROP DOMAIN` answers `ORA-11502` while a column uses the
domain, and `DROP DOMAIN ... FORCE` takes the domain off its columns. Declare a
domain under a new name and type the columns by it, or drop and create the
domain by hand.

Domain reads preserve numeric precision and scale, including `NUMBER(*,0)`.
National character types use their character count, so `NVARCHAR2(10)` remains
ten characters when the catalog reports twenty bytes. `ALL_DOMAIN_COLS` has no
`CHAR_USED`, so a `VARCHAR2` or `CHAR` length whose bytes exceed its characters
reads as a length in characters, `VARCHAR2(10 CHAR)`; where the two are equal
the unit cannot be told and the length reads bare. The view reports a
`BOOLEAN` domain as `UNDEFINED`, and Ptah reads it as `BOOLEAN`; a domain over
another type the view reports as `UNDEFINED` is recorded as one the read
cannot describe.

A domain with a `DISPLAY` or `ORDER` expression or with annotations is
recorded as found and not described, since the domain model has no place for
them: described without them, it would read back and replay as a domain that
has lost them. A declaration that names such a domain plans nothing for it,
and the comparison reports it as undecided.

Two of Oracle's catalog behaviors decide whether a domain converges. A domain declared `NOT NULL` grows a `CHECK` of its
own, named by the server and numbered per database, and Ptah reads the
nullability off the column instead so the plan does not carry that constraint
back and forth. And `DROP TABLE` is rendered with `PURGE`: a dropped table
keeps its dependencies in the recycle bin, and a plan that drops a table and
then the domain its column was typed by answers
`ORA-11538 ... has dependent objects in the recycle bin` halfway through. The
alternative, `DROP DOMAIN ... FORCE`, is worse — with a live dependent it
succeeds and silently untypes the column.

A column a domain types is read as the domain. The column catalog reports
such a column by its base type, `NOT NULL` where the domain is, with the
domain's default, and with a copy of each domain constraint on the table. Ptah
reads `DOMAIN_NAME` from `ALL_TAB_COLS` and `ALL_CONSTRAINTS` and leaves those
facts to the domain, so a table whose columns a domain types plans nothing once
it is applied, and `schema inspect` writes the column with the domain as its
type. A `NOT NULL` the column states itself stays: Oracle keeps it as a
constraint of the column. A default the column states itself cannot be told
apart from the domain's, since Oracle refuses a different one (`ORA-11501`),
so it reads as the domain's. A column typed by a domain of another schema, or
by one the read cannot describe, keeps its base type.

`ptah db drop-all` drops the account's domains after its tables and object
types. A domain made by `CREATE FLEXIBLE DOMAIN` names the domains it chooses
from, and `DROP DOMAIN` answers `ORA-11502` for one of those while it exists,
so each domain is dropped after the domains that reference it. The cleanup
writes `DROP DOMAIN` without `FORCE`, which would take the domain off any
column still using it.

The cleanup plan `ptah db drop-all` reports is the cleanup's own list: every
table, view, materialized view, sequence, synonym, trigger, type and domain
the account owns, Ptah's revision tables included, each with the statement the
cleanup runs, in the order it runs them. A materialized view keeps its rows
in a table of its own name, and that table goes with the view rather than as
a drop of its own.

## Object types

**A composite type is Oracle's object type**, and the spelling is the whole
difference. PostgreSQL's `CREATE TYPE t AS (a NUMBER)` is *accepted* here and
creates nothing usable — `USER_TYPES` reports `ATTRIBUTES 0` with
`INCOMPLETE YES`, `USER_OBJECTS` reports `INVALID`, and the driver returns no
error at all. Ptah writes `CREATE OR REPLACE TYPE t AS OBJECT (...)`, which is
the statement that creates one, and reads it back from `ALL_TYPES` and
`ALL_TYPE_ATTRS`.

An object type is compared by its attributes, in order, names without regard
to case and types in the spelling the catalog reports, so `NUMERIC(5,2)` and
`DECIMAL(5,2)`, `VARCHAR(10)` and `VARCHAR2(10)`, or `TIMESTAMP` and
`TIMESTAMP(6)` agree. One whose attributes changed is replaced with the same statement,
which Oracle refuses with `ORA-02303` while a column uses the type.

Object attribute reads preserve `DECIMAL` precision and scale, including a
negative scale. An unconstrained `FLOAT` remains `FLOAT`. A `TIMESTAMP` keeps
its fractional precision, an `INTERVAL` its two precisions, and a `VARCHAR2` or
`CHAR` length its unit, which `ALL_TYPE_ATTRS` reports in `CHAR_USED`. The
catalog names the time zone forms `TIMESTAMP WITH TZ` and
`TIMESTAMP WITH LOCAL TZ`, which no statement accepts, and the read writes
them out as `TIMESTAMP(6) WITH TIME ZONE` and
`TIMESTAMP(6) WITH LOCAL TIME ZONE`.

The read describes the subset the model can carry, and records the rest as
found and not described rather than flattening it:

- an object type with a method, a subtype, a `NOT FINAL` or `NOT INSTANTIABLE`
  type, a collection type (`VARRAY`, `TABLE OF`) and an incomplete shell;
- on 23, a domain of several columns, an enumeration domain (`AS ENUM`) and a
  domain made by `CREATE FLEXIBLE DOMAIN`.

Describing one by its attribute list or its first column alone would say a
replay produces the same type when it produces a different one. A declaration
that names such a type plans nothing for it: the comparison reports it as
undecided, because `CREATE OR REPLACE TYPE` would replace the type the schema
holds. `schema inspect --format sql` writes a comment for each one.

Replacing a type a column uses answers `ORA-02303` and changes nothing — the
server declining to leave that column naming a shape it no longer has, which
is kept rather than forced.

## Synonyms

A synonym is declared with `//ptah:schema:synonym` or the HCL `synonym` block,
as on SQL Server, and the same declaration renders on both targets. Ptah
writes `CREATE SYNONYM`, reads the schema's own synonyms back from
`ALL_SYNONYMS`, and plans a changed target as a drop and a create. Oracle
records the target's owner even when the statement left it out, so a declared
`orders` and a stored `APP.ORDERS` are one target for a connection whose schema
is `APP`. A synonym through a database link is left as it is: a declaration has
no place for the link, so it is neither dropped nor declared again. Public
synonyms belong to `PUBLIC` and are not read.

Go export preserves synonym names, schemas, targets, and declaration comments.
The exported annotation binds the alias to Oracle and SQL Server. Export refuses
incomplete coverage and an explicit target scope the annotation cannot preserve.

## Keys that defer their check

A primary key and a `UNIQUE` can be `DEFERRABLE`, with `INITIALLY DEFERRED`
or `INITIALLY IMMEDIATE`, as a foreign key can. Ptah writes the clause and
reads it back from `ALL_CONSTRAINTS`, measured on Oracle Free 23. A primary
key Oracle named itself is compared through the table's column flags, which
cannot defer, so a deferrable one is compared as the table's key instead.
Name a deferrable `UNIQUE`: Oracle names an unnamed one `SYS_C<n>`, which
differs in every database. A change of deferral is a drop and an add.

## Functions and procedures

**Functions and procedures** are rendered, read back and planned on both lines.
Their body is PL/SQL, which is what the declaration says:

```go
//ptah:schema:function name="fn_double" params="p IN NUMBER" returns="NUMBER" language="plsql" body="BEGIN RETURN p * 2; END;"
```

A declaration that omits `language=` is in PL/SQL, the target's own language,
and one that omits `security=` runs as `AUTHID DEFINER`, the server's own
default.
One that declares another language, such as `plpgsql`, is named by the renderer,
which creates nothing rather than writing a body this server cannot run.
Two other shapes are named the same way: a parameter default, because
`ALL_ARGUMENTS` reports that one exists and never what it is, so a routine
created with one would be replanned on every run. And `volatility="STABLE"` is
refused, because Oracle reports determinism as `YES` or `NO` only — `IMMUTABLE`
is the `DETERMINISTIC` clause and `VOLATILE` is its absence, and there is no
third cell that does not either lie to a function-based index or diff forever.

A standalone routine has no overloads. A schema that declares two functions, or
two procedures, of one name with different parameters is refused before any
statement is planned. Ptah writes `CREATE OR REPLACE`, and on Oracle 23.26 the
second statement replaces the first without an error: one routine is left,
holding the second body, and a call with the first one's arguments fails with
`PLS-00306`. A function and a procedure of one name are left to the server,
which refuses the second with `ORA-00955` and keeps the first.

The semicolon that closes a PL/SQL block belongs to the block rather than to the
client, which is why Oracle's own tooling ends one with a `/` on the next line.
A `CREATE` handed to the server without it returns **no error at all** and
leaves the object `INVALID`: `USER_TRIGGERS` still reports such a trigger
`ENABLED`, and `USER_PROCEDURES` omits the routine. Ptah keeps that
semicolon on every statement it sends.

## Versioned migrations

`ptah migrations up`, `down` and `status` run against Oracle, with either
revision table format. The revision table has an Oracle spelling of its own:
`NUMBER` and `CLOB` columns, `DEFAULT` written before `NOT NULL`, and a PL/SQL
block that creates the table only when it is absent, because the 21 line has no
`IF NOT EXISTS`. Oracle stores an empty string as `NULL`, so the text columns of
that table are nullable. The spelling is measured on the 23 line; on the 21 line
it rests on the capability preset.

Each statement of a migration commits as it runs, under `--tx-mode file` as
under `none`. Oracle commits before every schema statement, and Ptah opens no
transaction around the body, so when a statement fails, the statements before
it stay applied and the revision row counts them. See
[what a failed body records](../../versioned/apply/). For the same reason
`--tx-mode all` is refused.

`--migrations-engine` is refused on Oracle, because the revision table has no
engine clause to carry it. Ptah takes no advisory lock on Oracle, so two
`ptah migrations up` runs against one schema are not serialized: run one at a
time.

## Open work

What remains for Oracle is tracked by
[#1875](https://github.com/stokaro/ptah/issues/1875) and
[#1920](https://github.com/stokaro/ptah/issues/1920).

## Next steps

- Which release lines are declared and at what support level: [Database support matrix](../support-matrix/).
- Capability keys per dialect: [Capabilities](../../reference/capabilities/).
- Declaring functions, types, and constraints in Go sources: [Go annotation reference](../../reference/go-annotations/).