01-object-fixture.sql

-- Object-browser fixture for the Oracle provider (#789, #765).
--
-- Mounted at /container-entrypoint-initdb.d in database-compose.yml, so gvenzl/oracle-xe
-- runs it through SQL*Plus once, on a FRESH data directory only. An already-initialized
-- container has to be recreated before an edit here takes effect.
--
-- Two things about this file are load-bearing and must not be "tidied":
--
--  1. The `/` terminators. SQL*Plus needs one to end a PL/SQL block, and they are correct
--     HERE because SQL*Plus is what runs this file. They must never be sent through
--     node-oracledb, which takes one statement per execute() and answers ORA-00911 for a
--     trailing terminator.
--  2. APP_BROKEN_PKG does not compile, on purpose. Its spec is VALID and its body is
--     INVALID, which is the state Oracle is in most often and the one the tree has to
--     render: a successful CREATE OR REPLACE can still leave the object INVALID. A run
--     with only a working package cannot tell a working spec-and-body collapse from no
--     collapse at all.
--
-- The scripts run as SYSDBA against CDB$ROOT, so the container switch is the first
-- statement and every object below is schema-qualified. CURRENT_SCHEMA is deliberately
-- not used: it governs name RESOLUTION, and relying on it to decide where a CREATE lands
-- would put the fixture's correctness on an Oracle subtlety instead of on the name.
ALTER SESSION SET CONTAINER = XEPDB1;

-- Two owners, because one owner cannot show that the single-owner confinement is gone.
-- Before #765 every read was hard-scoped to OWNER = <connecting user>, so the app showed
-- exactly one schema with no way to reach another.
CREATE USER app IDENTIFIED BY "Password123!" QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO app;
-- CREATE TABLE is already in RESOURCE, and granting it again is not redundant: measured,
-- CREATE MATERIALIZED VIEW below answered ORA-01031 without this line. Creating a
-- materialized view in another user's schema checks that the OWNER holds CREATE TABLE
-- DIRECTLY, and a privilege held through a role does not satisfy that check.
GRANT CREATE TABLE TO app;

CREATE USER reporting IDENTIFIED BY "Password123!" QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE TO reporting;

-- Tables, one per owner. REPORTING's is granted to APP so the second container answers
-- something rather than being an empty folder that proves nothing.
CREATE TABLE app.app_orders (
  id           NUMBER(10)     NOT NULL,
  customer_id  NUMBER(10),
  total        NUMBER(12, 2)  DEFAULT 0,
  note         VARCHAR2(200),
  CONSTRAINT app_orders_pk PRIMARY KEY (id)
);

CREATE TABLE app.app_customers (
  id    NUMBER(10) NOT NULL,
  name  VARCHAR2(100),
  CONSTRAINT app_customers_pk PRIMARY KEY (id)
);

-- Two rows, and they are part of the deliverable rather than decoration (#789, standing
-- ruling 5i). APP is the connecting user's own schema, so this is the DEFAULT-container
-- click: the tree writes APP.APP_CUSTOMERS and an empty table cannot tell a statement the
-- server accepted from one it rejected, because both answer with no rows. Two is the
-- smallest count that is not one: a single row cannot show that a limit clause left the
-- rows alone.
INSERT INTO app.app_customers (id, name) VALUES (1, 'ada');
INSERT INTO app.app_customers (id, name) VALUES (2, 'grace');

ALTER TABLE app.app_orders ADD CONSTRAINT app_orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES app.app_customers (id);

CREATE INDEX app.app_orders_total_ix ON app.app_orders (total);

-- The #1139 measurement, as a table a live check can read back. `ALL_TAB_COLUMNS.DATA_TYPE`
-- has no length, precision or scale, so the provider builds a column's declaration from
-- DATA_LENGTH, DATA_PRECISION, DATA_SCALE, CHAR_LENGTH and CHAR_USED. One column per row of
-- that rule (docs/providers/oracle.md, section 7). The TIMESTAMP, INTERVAL, DATE, CLOB, BLOB
-- and BINARY_DOUBLE columns are the controls: DATA_TYPE already is their declaration.
-- tests/live/oracle-column-type.ts replays the generated CREATE TABLE and compares the new
-- table's ALL_TAB_COLUMNS rows with these.
--
-- The two UROWID columns are #1209: DATA_TYPE leaves out their size, and a bare UROWID is
-- created with DATA_LENGTH 4000. This image is 21c, which has no VECTOR type, so the live
-- guard creates its own vector table on 23ai or later.
CREATE TABLE app.column_types (
  c_varchar2       VARCHAR2(20),
  c_varchar2_byte  VARCHAR2(20 BYTE),
  c_varchar2_char  VARCHAR2(20 CHAR),
  c_nvarchar2      NVARCHAR2(10),
  c_char           CHAR(2),
  c_char_char      CHAR(3 CHAR),
  c_nchar          NCHAR(3),
  c_raw            RAW(16),
  c_number         NUMBER,
  c_number_p       NUMBER(10),
  c_number_ps      NUMBER(12, 2),
  c_number_star    NUMBER(*, 2),
  c_number_neg     NUMBER(5, -2),
  c_integer        INTEGER,
  c_float          FLOAT(10),
  c_float_default  FLOAT,
  c_timestamp      TIMESTAMP(3),
  c_timestamp_tz   TIMESTAMP(6) WITH TIME ZONE,
  c_interval       INTERVAL DAY(3) TO SECOND(2),
  c_date           DATE,
  c_clob           CLOB,
  c_blob           BLOB,
  c_binary_double  BINARY_DOUBLE,
  c_urowid         UROWID(100),
  c_urowid_default UROWID
);

CREATE TABLE reporting.report_daily (
  report_day  DATE NOT NULL,
  orders      NUMBER(10),
  CONSTRAINT report_daily_pk PRIMARY KEY (report_day)
);

-- Two rows in the OTHER owner, which is the cross-container click: APP reads these through
-- the GRANT below, and the statement the tree writes has to be REPORTING.REPORT_DAILY
-- rather than the bare name. Written as DATE literals so the rows do not depend on the
-- session's NLS_DATE_FORMAT.
INSERT INTO reporting.report_daily (report_day, orders) VALUES (DATE '2026-09-01', 10);
INSERT INTO reporting.report_daily (report_day, orders) VALUES (DATE '2026-09-02', 20);

GRANT SELECT ON reporting.report_daily TO app;

CREATE OR REPLACE VIEW app.app_order_summary AS SELECT 1 AS id FROM dual;

CREATE MATERIALIZED VIEW app.app_revenue_mv AS SELECT 1 AS id FROM dual;

CREATE SYNONYM app.app_orders_syn FOR app.app_orders;

CREATE SEQUENCE app.app_invoice_seq;

CREATE OR REPLACE FUNCTION app.app_order_total(p_id NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END;
/

CREATE OR REPLACE PROCEDURE app.app_touch_order(p_id NUMBER) IS BEGIN NULL; END;
/

CREATE OR REPLACE PACKAGE app.app_orders_pkg IS
  FUNCTION total(p_id NUMBER) RETURN NUMBER;
END app_orders_pkg;
/

CREATE OR REPLACE PACKAGE BODY app.app_orders_pkg IS
  FUNCTION total(p_id NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END;
END app_orders_pkg;
/

-- Deliberately invalid: the acceptance run must see a VALID spec beside an INVALID body,
-- which is the state Oracle is in most often and the one a tree must render.
CREATE OR REPLACE PACKAGE app.app_broken_pkg IS
  FUNCTION total RETURN NUMBER;
END app_broken_pkg;
/

CREATE OR REPLACE PACKAGE BODY app.app_broken_pkg IS
  FUNCTION total RETURN NUMBER IS BEGIN RETURN no_such_column; END;
END app_broken_pkg;
/

CREATE OR REPLACE TRIGGER app.app_orders_trg BEFORE INSERT ON app.app_orders
  FOR EACH ROW BEGIN NULL; END;
/

-- A trigger APP owns whose base table belongs to REPORTING. ALL_TRIGGERS separates OWNER
-- from TABLE_OWNER for exactly this case, and the provider has to decide which one the
-- path hangs off; see docs/providers/oracle.md.
GRANT CREATE ANY TRIGGER TO app;

CREATE OR REPLACE TRIGGER app.report_daily_trg BEFORE INSERT ON reporting.report_daily
  FOR EACH ROW BEGIN NULL; END;
/

-- An INSTEAD OF trigger, whose base object is a VIEW rather than a table.
CREATE OR REPLACE TRIGGER app.app_view_trg INSTEAD OF INSERT ON app.app_order_summary
  BEGIN NULL; END;
/

-- A trigger with NO base object at all: measured, ALL_TRIGGERS gives it TABLE_NAME NULL
-- and BASE_OBJECT_TYPE 'SCHEMA', so it hangs off the container rather than off a table.
-- ADMINISTER DATABASE TRIGGER is required even for the schema-scoped form, because LOGON
-- is a database event.
GRANT ADMINISTER DATABASE TRIGGER TO app;

CREATE OR REPLACE TRIGGER app.app_logon_trg AFTER LOGON ON app.SCHEMA BEGIN NULL; END;
/

-- ---------------------------------------------------------------------------
-- Wrapped PL/SQL, and the units built to defeat each half of the rule (#789).
-- ---------------------------------------------------------------------------
--
-- Measured on Oracle XE 21.3.0.0.0 (gvenzl/oracle-xe): EXECUTE ON DBMS_DDL is already
-- granted to PUBLIC on this image, so APP needs no extra grant to run CREATE_WRAPPED.
--
-- WHY THESE OBJECTS EXIST. DBMS_METADATA.GET_DDL answers a wrapped unit with the
-- encoder's obfuscated bytes and raises nothing, so a provider that hands that text to an
-- editor shows something that is not a definition and cannot say so. The detection rule is
-- the header POSITION: the token immediately after the closing double quote of the object's
-- quoted name is the bare keyword `wrapped`, and the next physical line is the wrap format
-- marker matching ^[a-z][0-9]{6}$ (`a000000` on 21.3.0.0.0).
--
-- The four plain units below are the point of this block and must not be "tidied". Three of
-- them are VALID, COMPILING functions built to defeat the naive TEXTUAL rule: one ends its
-- first source line with the token `wrapped`, one carries the wrap format marker on its
-- second line, and APP_CONJ_DEFEATER does both at once. The fourth, APP_ZERO_ARG, is the
-- control for the header position itself and the paragraph below says why. What none of the
-- four can imitate is the header position, because GET_DDL writes the object name inside
-- double quotes and what follows it is decided by the PARSER: a plain unit admits only `(`,
-- RETURN, IS or AS there. A test that reads only a wrapped unit certifies nothing; these
-- four are what make the predicate non-vacuous, and if a future Oracle ever admits
-- `wrapped` in that position for a plain unit, the assertion over them fails by name.
--
-- APP_ZERO_ARG is the closest PLAIN shape to a wrapped header there is, a zero-argument
-- function whose header carries no parameter list at all, so the token after the quoted
-- name is the bare word `return`. It is the control for the position itself.
--
-- APP_MARKERLESS_HEADER, at the end of this block, attacks the OTHER conjunct and is the one
-- unit here that does not compile. Its own comment carries the measurement.

BEGIN
  DBMS_DDL.CREATE_WRAPPED(
    'CREATE OR REPLACE FUNCTION app.app_wrapped_multi(p NUMBER) RETURN NUMBER IS' || CHR(10) ||
    '  v NUMBER := 2;' || CHR(10) ||
    'BEGIN' || CHR(10) ||
    '  RETURN p * v;' || CHR(10) ||
    'END;');
END;
/

CREATE OR REPLACE FUNCTION app.app_first_line_wrapped(p NUMBER) RETURN NUMBER IS -- wrapped
BEGIN
  RETURN p;
END;
/

CREATE OR REPLACE FUNCTION app.app_second_line_marker(p NUMBER) RETURN NUMBER IS /*
a000000
*/
BEGIN
  RETURN p;
END;
/

CREATE OR REPLACE FUNCTION app.app_conj_defeater(p NUMBER) RETURN NUMBER IS /* wrapped
a000000
*/
BEGIN
  RETURN p;
END;
/

CREATE OR REPLACE FUNCTION app.app_zero_arg RETURN NUMBER IS
BEGIN
  RETURN 1;
END;
/

-- The unit that defeats the OTHER half of the conjunction, and the one object in this block
-- that does NOT compile. The detection rule is `wrapped` in the header position AND the wrap
-- format marker on the next line; the four units above all attack the first conjunct, and
-- until this object existed the SECOND conjunct was asserted by nothing at all, because a
-- real wrapped unit always carries its marker.
--
-- MEASURED on Oracle XE 21.3.0.0.0 (gvenzl/oracle-xe), and it is not what the header
-- position's parser rule would lead you to expect: `wrapped` after the function name is
-- ACCEPTED, because it is the wrap keyword. The unit then fails to compile with
-- `PLS-00753: malformed or corrupted wrapped unit` (one row in `USER_ERRORS`, line 0), the
-- object is created FUNCTION / INVALID, and `DBMS_METADATA.GET_DDL` answers its source
-- verbatim anyway: the header carries the keyword and the next line is `BEGIN`, so the
-- predicate must answer NOT WRAPPED and the reader gets a readable text rather than a
-- manufactured refusal. Do not "fix" the missing marker: the missing marker is the point.
--
-- A `CREATE OR REPLACE` on an object left INVALID is harmless here and is the same state
-- APP_BROKEN_PKG is committed in.
CREATE OR REPLACE FUNCTION app.app_markerless_header wrapped
BEGIN
  RETURN 1;
END;
/

-- A package whose SPEC is plain and whose BODY is wrapped, which is the whole argument for
-- reading PACKAGE_SPEC and PACKAGE_BODY as two parts instead of the bare PACKAGE type. One
-- concatenated CLOB would give a reader neither half honestly: the spec is readable and the
-- body is not, and only two parts can say so.
CREATE OR REPLACE PACKAGE app.app_wrapped_pkg IS
  FUNCTION total(p NUMBER) RETURN NUMBER;
END app_wrapped_pkg;
/

BEGIN
  DBMS_DDL.CREATE_WRAPPED(
    'CREATE OR REPLACE PACKAGE BODY app.app_wrapped_pkg IS' || CHR(10) ||
    '  FUNCTION total(p NUMBER) RETURN NUMBER IS BEGIN RETURN p; END;' || CHR(10) ||
    'END app_wrapped_pkg;');
END;
/

-- A package with a SPECIFICATION AND NO BODY, which is legal on Oracle and is the shape
-- that decides how many parts a package emits. Its missing body is an ABSENCE and not a
-- refusal: there is nothing to refuse, because nobody ever wrote one. The provider emits
-- ONE part for it, and the ALL_OBJECTS second question is what tells that absence apart
-- from a body ORA-31603 refuses to hand over.
CREATE OR REPLACE PACKAGE app.app_spec_only_pkg IS
  FUNCTION total(p NUMBER) RETURN NUMBER;
END app_spec_only_pkg;
/

-- Explicit, rather than relying on SQL*Plus committing on EXIT. Measured on gvenzl/oracle-xe
-- 21.3.0: EXIT does commit, so this line changes nothing today, and it is here because the
-- INSERTs above are the only DML in the file and a fixture whose data survives on a client
-- convention is one image upgrade away from coming back empty.
COMMIT;

EXIT;