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:

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.

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:

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:

//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. 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 and #1920.

Next steps

• Which release lines are declared and at what support level: Database support matrix.
• Capability keys per dialect: Capabilities.
• Declaring functions, types, and constraints in Go sources: Go annotation reference.