Aster Web Data / Connect your data

OracleCommercial

How Aster Web Data uses Oracle Database, and how Oracle's types, empty strings and results shape what clients receive.

If your data is in Oracle Database, Aster Web Data serves it from your existing tables through Oracle's own Rust driver, with no Instant Client to install. Filters, sorts, pages and counts run in Oracle, and each result is Oracle's own, decoded exactly or refused. Where Oracle's answer differs from OData's, Oracle's stands. The difference users notice first is that VARCHAR2 stores an empty string as null; Departures from OData lists the others.

Two choices need care: the TLS setup, because Oracle's ca_file adds to the public roots rather than replacing them, and the version column that gives a set its ETag, because Oracle has no row version Aster can use.

Connecting

Give the connection's parts, with the password from an environment variable or a file:

databases:
  enterprise:
    kind: oracle
    host: database.example.com
    port: 1522                       # the listener's TCPS port
    user: ASTER_APP
    password_env: ASTER_ORACLE_PASSWORD
    database: FREEPDB1               # the service name
    schema: ASTER_APP                # the user that owns the tables
    tls: verify-full
    ca_file: /etc/aster/oracle-ca.pem

In place of the parts, url_env or url_file can hold a JSON document with user, password and an Easy Connect connect_string. Secrets explains the rules, and the configuration reference lists every key with its default. These behave in a way particular to Oracle:

• schema names an existing user that owns the tables. setup never creates or drops a user.
• tls: verify-full connects over TCPS, verifies the certificate chain and the host name, and never falls back to TCP. A verified JSON connection must use Easy Connect.
• ca_file adds its certificates to the driver's public root certificates; it does not replace them. A certificate from any trusted public authority still passes when its host name matches. check prints this and start-up logs it.
• require and verify-ca are refused, because the driver always checks both the certificate and the host name over TCPS. prefer uses TCP for connection parts. Strict TLS needs verify-full. Plain TCP suits a trusted network only.
• read_only: true makes the driver connection refuse every write.

The tests run on Oracle AI Database 26ai (23.26).

Start-up check

When the service opens the database, it reads the ALL_ catalog views for each data source's table, keeping quoted identifiers and the positions of composite keys, and compares the native types and decimal precision and scale with the configuration. A failed check makes only that database's sets unavailable; A database goes down describes what clients see. To run the check on its own, use the check command.

Types

Collections are refused, and a float column cannot be read as a decimal. A timestamp stored with a named region is read through SYS_EXTRACT_UTC, keeping its exact instant. TIMESTAMP WITH LOCAL TIME ZONE is not mapped.

Collation and case

Every session sets NLS_COMP and NLS_SORT to BINARY, NLS_NUMERIC_CHARACTERS to ., and TIME_ZONE to +00:00, so comparison and sorting are binary in AL32UTF8 order, and trailing spaces count. Ordinary columns report USING_NLS_COMP, which $metadata keeps; tables the service creates write no COLLATE clause. A column with another named collation is refused.

tolower and toupper are LOWER and UPPER, with Oracle's character mapping. contains, startswith and endswith are LIKE with the pattern bound and a backslash escape.

Where each operation runs

Each OData operator becomes the Oracle operator with the same meaning:

These run in the service, within the limits on rows it may hold: regular expressions, general casts, and date and time arithmetic Oracle has no exact form for. mod of doubles and comparisons of BLOB values are not supported. Each $expand level is read by key batches, one statement per level.

Errors

Nulls and ordering

ORDER BY names NULLS FIRST or NULLS LAST as OData asks, with the entity key ending every sort. NaN equals itself and sorts after every other number. $count=true runs the page and a COUNT in one read-only snapshot, so the total agrees with the page.

Departures from OData

Where OData 4.01 defines a result and Oracle gives another, Oracle's result stands. These are the places:

• The empty string is null in VARCHAR2, and a substring past the end returns null.
• concat treats null as the empty string: null || 'x' is 'x'.
• trim removes spaces only, not every Unicode whitespace character.
• NUMBER widens past 64 bits. A filter may compare the widened value, and returning it as an integer fails. A decimal division can produce 38 significant digits and fail to read as a 28-digit decimal.
• mod by zero returns the dividend. An integer or decimal div by zero raises ORA-01476.
• BINARY_DOUBLE overflow gives infinity, a division by zero gives infinity or NaN, NaN equals itself and sorts greatest, and round ties go to even.
• Case mapping and a negative substring start follow Oracle's rules.
• ca_file adds to the public roots instead of replacing them, as described under Connecting.

Writes and transactions

• Isolation. Read committed and serializable are available. A read that takes several statements, such as a page with its count, runs in a SET TRANSACTION READ ONLY snapshot.
• Writes by key. An update or delete locks its row with SELECT ... FOR UPDATE, held to the commit, and compares the ETag's token there.
• Inserts use RETURNING ROWID and read the row back, after a query that checks the result types. A multi-row insert runs as single-row inserts in the same transaction.
• setup: recreate is not atomic, because Oracle's DDL commits as it runs.

• ETags. etag: native is refused, and ORA_ROWSCN is not a token, because it changes only when the transaction commits, too late for the write to answer with the new tag. The usual token is a version column Aster advances (versionField: version, a NUMBER(19) column), or one a trigger advances, named in etag: {columns: [version]}:

  ALTER TABLE orders ADD (version NUMBER(19) DEFAULT 1 NOT NULL);
  CREATE OR REPLACE TRIGGER orders_version BEFORE UPDATE ON orders FOR EACH ROW
  BEGIN :NEW.version := :OLD.version + 1; END;
  

  ETags and concurrent edits explains the tokens.

Indexes and sort order

Index the columns your screens filter and sort on, as Indexes describes. In the benchmark, equality, prefix and child IN queries used their ordinary indexes, while a text sort with single-column indexes chose a full scan and a sort.

Scaffold

The scaffold command reads the ALL_ catalog views for the tables, columns, keys, defaults, generated columns and comments, and writes a starting data-source configuration for the columns it can map exactly. Connect your own database walks through it.