Pre-flight an Oracle view before you connect it

You are about to give Prism a read-only view over an Oracle database — a resource plan, a directory — and register it as a context stream. This procedure runs, on your side and before anything is connected, the same statements Prism's ingest will run on its first pass, and tells you what would have stopped it. It exists because the alternative is finding out on a call.

Everything it does is a SELECT. It prints each statement before running it and waits for you. Its report holds counts, column names, Oracle type names, timings and verdicts, and never a row, a name, an address or an id. A DBA can read the whole script before running it; it is one file with no dependencies beyond the Oracle driver.

What you need

• The script, scripts/prism_preflight.py, in the chart tarball beside these docs (prism/scripts/prism_preflight.py). Its version matches the release, which matters: it reads the manifest you give it, and the manifest should be the one this release ships.

• The manifest you intend to register. Either the shipped recipe as it is (manifests/preview/clarity.yaml in the same tarball, also shown under Add a context stream on the Context streams page) or your edited copy from the register-time editor. The script takes the SELECT and the column list from that file, so what it tests is what Prism will run — not a copy of it.

• Python 3.9 or later on any machine that can reach the database's listener, and two packages:

  python3 -m venv preflight && . preflight/bin/activate
  pip install oracledb pyyaml

  oracledb is Oracle's own Python driver in thin mode: no Oracle client install, no tnsnames.ora. If your listener requires a wallet (TLS with a client certificate), pass its directory with --wallet and put the wallet password in PRISM_WALLET_PASSWORD. One case needs more than the driver: a database requiring Oracle Native Network Encryption, which also needs Oracle Instant Client on this machine — see When a database requires Native Network Encryption, which the script will point you at by itself if you hit it.

• The account Prism will use. Run it as that account, not as a DBA: a view a DBA can see and the service account cannot is the single most common reason a first pass fails, and only the service account can show it.

Run it

export PRISM_DB_PASSWORD='…'        # the service account's password; never on the command line
python3 prism/scripts/prism_preflight.py oracle \
  --manifest clarity.yaml \
  --host db.example.internal --port 1521 --service ORCLPDB1 \
  --user prism_reader

It pauses before each statement and shows it. Press Enter to run it, or Ctrl-C to stop; --yes runs them all without pausing.

How long to expect, and which step to be patient with. On a view that answers quickly the whole run is a couple of minutes. On a slow view the cost is not spread evenly: the ladder check runs one statement per manager column, and each compares that column against the whole view, so it takes several times a plain SELECT per column.

Measured, on a view returning a single row in about twenty seconds: the full run with eight manager columns declared took seventeen minutes, and under three with those columns removed — for an identical timing figure. That is the script working, not hanging; it prints how many columns it has to check before it starts. Budget for it, or run once without the manager columns if all you need is the timing.

The steps are ordered so the answer that matters most arrives early: timing runs before the expensive checks, so you can read whether the view is fast enough to be queried live without waiting for the rest.

Add --emails addresses.txt to count how many addresses from a list you already hold — the output of the GitHub or GitLab pre-flights, one address per line — appear in the view. The count is what the report carries; the addresses themselves stay in memory and are discarded.

Read the report

Each line is a step and a verdict:

This is a real run, against a 120-row test view seeded for the run from the ingest suite's own fixture (it is seeded on demand, not kept):

[PASS] connect: connected to localhost:11521/FREEPDB1 as PREFLIGHT in 0.13s
[PASS] select: the SELECT runs as this account in 0.03s and returns 45 columns; 45 of 45 declared columns present
[NOTE] types: 1 column(s) arrive as a type outside what the manifest declares — usually fine because the SELECT casts, but read them: [{'column': 'occurrence', 'declared': 'string', 'oracle': 'DB_TYPE_NUMBER'}]
[PASS] counts: 120 rows · 120 distinct people · 120 distinct addresses in 0.02s
[PASS] identity: 0 address(es) shared by more than one person (0 people affected) — none; every address names one person; 0 person(s) carry more than one address
[NOTE] ladder: 8 manager column(s); 7 name people who are not rows in this view (['mgr_lvl_1_id', 'mgr_lvl_2_id', …, 'team_owner_id']) — 'everyone who reports to X' stops at each of them. Two causes: the manager is not a resource in this view (a leaver, a contractor, someone above its scope), or the two columns spell the same id differently. None of them match under any spelling; workday_id ids are 5–5 characters
[PASS] sizing: 120 rows × ~512 B ≈ 0 MiB per copy; snapshot: 105 copies retained (schedule `0 4 * * 1`, keep `P2Y`) ≈ 0.0 GiB in Prism's memory at steady state
[PASS] join: 20 of 21 held address(es) appear in the view (120 distinct in the view) — these are the people a GitHub or GitLab handle can be resolved to
[PASS] timing: a first page of up to 5000 rows ordered by row_key took 0.02s

wrote prism_preflight_oracle_20260907T075100Z.json — send this file back; it holds no row values.

Both NOTE lines there are real: that test view stores occurrence as a number where the recipe declares a string (the walk coerces it), and seven of its eight manager columns name people who are not rows in it under any spelling, which is what the ladder step is for. The sizing line is small only because the view is; the multiplier is the same 105.

And the same view with its manager column zero-padded (0010001 beside a person id of 10001), which is the case that matters most for reporting-line questions:

[FAIL] ladder: 1 manager column(s); 1 name people who are not rows in this view (['resource_manager_id']) — 'everyone who reports to X' stops at each of them. Two causes: the manager is not a resource in this view (a leaver, a contractor, someone above its scope), or the two columns spell the same id differently. LEADING ZEROS: {'resource_manager_id': 120} of those orphans DO match a person once leading zeros are ignored — the manager column and the workday_id column are padded differently, and Prism will not strip zeros from an identifier, so these walk to nobody until the view spells both the same way
       key_id_length: [5, 5]
       columns: {"resource_manager_id": {"filled_rows": 120, "not_in_view": 120, "not_in_view_but_match_ignoring_leading_zeros": 120, "id_length": [7, 7], …}}

No id is printed; the lengths (7 against 5) and the count are what a DBA needs to see the padding.

What each verdict means, and what to do when it is not PASS:

Step	It fails when	What it means
connect	the listener, service name, account or password is wrong	Nothing else can be tested. The message is Oracle's own (ORA-01017 is the password; DPY-6005 is the host, port or service).
select	the view is not there under that name, or the account has no SELECT on it (ORA-00942); or the manifest names a column the view does not have (ORA-00904)	The exact thing the first ingest pass would have died on. Fix the grant, the view name in the manifest's SELECT, or the column, and run again.
types	a NOTE, never a failure	A column arrives as a type outside what the manifest declares — a flag stored as NUMBER, a date as text. The manifest's SELECT usually casts; read the list once so nothing is silently coerced to the wrong value.
counts	a NOTE when the key is not unique per row, or people or addresses are missing	The row key is what the walk pages on; a duplicate key drops rows. A person with no address cannot be joined to GitHub or Jira.
identity	an address is shared by more than one person	Both people resolve to nobody. Prism refuses to guess which of two people an address means, so every figure about either of them is withheld. Fix the view (usually a stale row or a shared mailbox) or accept the gap for those people.
ladder	a NOTE when a manager column names people who are not rows in the view; a FAIL when those ids match once leading zeros are ignored	A non-zero count decides whether "everyone who reports to X" can work on this view at all. Two causes: the manager is genuinely not a row (a leaver, a contractor, someone above the view's scope), or the manager column and the person-id column spell the same id differently — leading zeros are the classic (0012345 beside 12345, and Workday ids are commonly zero-padded), case and whitespace the others. Prism folds case and whitespace and never strips zeros, because an employee id may have significant ones; a zero-padded manager column therefore walks to nobody, and "nobody reports to X" looks exactly like a leader with no reports. The report says how many orphans would match once zeros are ignored, and the id lengths on each side, without printing an id: if that number is non-zero, the view has to spell both columns the same way.
join	none of the held addresses appear in the view	The addresses your GitHub or GitLab hold and the ones the directory holds do not agree — different domains, aliases, personal addresses. No handle will resolve until they do; the per-domain counts in the report say where the gap is.
sizing	a NOTE at 5 GiB or more	Read this one before the first run. A snapshot stream copies the whole view into Prism's memory every run and keeps each copy for incremental.keep; the shipped Clarity recipe runs weekly and keeps two years, which is about 105 copies. At 2.6 million rows a copy that is over 100 GiB. Both keep and the schedule are yours to set in the manifest, and a full database volume stops every source, not this one — see below.
timing	for a query-through manifest, a FAIL at or past 15 seconds and a NOTE past half of that; for a snapshot manifest, a NOTE past a minute	The threshold differs by kind because the budget does. A query-through source is read live on every question, and a question gets mcps.semanticMcp.statementTimeoutSeconds — 15 seconds unless your installer raises it. A first page slower than that means every question against the view times out, so the pre-flight fails rather than passes. Raise the chart value above the figure reported, or make the view faster. The manifest's own connection.database.statement_timeout cannot raise the limit — the engine takes whichever of the two is smaller, so a manifest asking for ten minutes still gets the chart's number. A snapshot manifest is copied by the ingest instead, which that ceiling does not bound, so there a slow page is what to size the schedule against.

A query-through manifest is pre-flighted the same way

A kind: query-through manifest has no ingest block — Prism runs each question against the view live and copies nothing — so the script builds the statement Prism will run from the model instead: SELECT <each field's sql> AS <name> FROM <source>, one per dataset. Every step above applies; the sizing line reports that nothing is copied and there is no volume to size. Give it the same manifest you intend to register, whichever kind it is.

Size the database before the first run

The sizing line is the one to act on before anything is connected, because the thing it describes cannot be undone from Prism's side once it has happened.

A snapshot stream — which is what an Oracle view is — copies the whole view into Prism's memory on every run, stamped with that day's date, and keeps each copy until it is older than incremental.keep. The shipped Clarity recipe runs weekly (schedule: "0 4 * * 1") and keeps two years (keep: P2Y), so at steady state it holds about 105 copies of the view. The script multiplies your row count by a measured 512 bytes per row and by that number of copies, and prints the total. For a resource plan over a large workforce — tens of thousands of people, each with a row per period and scenario — that is tens to hundreds of GiB, and it arrives one week at a time.

Two things follow:

• Tell whoever owns the database volume the number, before the first run. If the volume fills, Postgres stops accepting writes for every source, not only this one, and the ingest for GitHub and Jira stops with it. On a managed service the volume can usually be grown online and never shrunk; on the bundled Postgres it is postgres.storage, which is fixed at install.
• keep and schedule are yours to set, in the manifest, in the register-time editor. The history of copies exists so that a question can be asked "as of" an earlier date; if that is not something you will ask, a keep of P90D holds thirteen weekly copies instead of a hundred and five, and a monthly schedule with P2Y holds twenty-five. Re-applying a manifest with a shorter keep drops the older copies at the next run, so the number can be brought down later — but the space it used is reclaimed inside the database, not returned to the volume.

Send it back

Send the JSON file the script wrote. It holds every verdict above with the counts, column names and Oracle type names behind them, the driver version, the session's database and service name, and nothing from any row. If a step failed, the Oracle error text is in it verbatim, which is what we need to see.

Run it again after any change to the view or the manifest; a second report beside the first is how a change is shown to have worked.

Which driver mode this installation should be in

Thin, unless an Oracle database refuses thin. Thin is the default, and it is what every installation used before 0.14.0. Thick exists for one thing: it is the only mode that can negotiate Oracle Native Network Encryption. If none of your databases requires encryption, or your DBA can offer a TCPS (TLS) listener, stay in thin. The next section says how to tell whether a database requires it.

What thick costs. Each item says whether we measured it or are relaying Oracle's documentation:

• It moves every Oracle source, not only the one that needed it. The setting is installation-wide, because the driver fixes thin or thick for a whole process at its first connection. Measured.
• A password longer than 30 bytes stops working, with the code a wrong password gets. See Passwords longer than 30 bytes before you switch. Measured.
• Some databases cannot be connected at all. The reduced Instant Client in the images supports databases whose character set is US7ASCII, WE8DEC, WE8MSWIN1252, WE8ISO8859P1, WE8EBCDIC37C, WE8EBCDIC1047, UTF8 or AL32UTF8. A database on any other character set is refused at connect with ORA-12737, and the fix is a different image rather than a setting. Ask your DBA for the database character set before you switch, and tell us if it is not on the list. From Oracle's documentation for Instant Client Basic Light; not measured.
• Connection errors are reported differently. With the host, port and service name form a source uses, a listener thick could not reach came back as ORA-12170, where thin says DPY-6005. Measured. Other Oracle ORA- codes can pass through as well, so any runbook of yours keyed on DPY-6005 needs the thick forms too.
• It loads a native client our image scanner cannot see inside. Trivy, the scanner Prism's release gate uses, reports nothing for Oracle Instant Client. We have not tried other scanners. Its security fixes are followed through Oracle's quarterly Critical Patch Updates instead.

What the per-source connection.database.mode: thick is for. It is not the switch, and you do not need it. It declares a requirement, "never connect this source in thin mode". While the installation is thin, the app will not register such a source, apply a manifest to it or test its connection, and every ingest run refuses it. The query service never checks it, so questions answered live from a query-through source, the Manifest tab's Test and verification runs can still open a thin session: it is not a guarantee that such a source is never read in thin mode. It does not check that a session is encrypted either: thick mode encrypts only where your database asks for it. It can only be set when you register a source: a registered source's connection cannot be edited (the change is refused, and the only route is to delete the source and register it again). So do not add it to a source that already connects.

How to see which mode each part of the installation is in. The app, the ingest runs, the query service (which answers questions from a query-through source) and Stream setup's discovery service are separate pods, and each reads the chart value for itself:

• the self-test bundle's config carries oracle_mode_app, the value the app pod read (thin, thick, unset or invalid, or unknown on an install with no source registry);
• on Context streams, an Oracle source's detail shows Oracle driver mode, the mode its connection test used;
• an ingest run's log says oracle session opened in thick mode (PRISM_ORACLE_MODE=thick) when it connects. A run refused for encryption records a reason that names the PRISM_ORACLE_MODE that pod read, in the tick's log and on the source's ingest state row;
• the query service's log says oracle: session opened in thick mode (PRISM_ORACLE_MODE=thick) at its first Oracle session.

If the connection test connects and the scheduled run is refused for encryption, compare the two: the value reached one pod and not the other.

When a database requires Native Network Encryption

Many bank estates encrypt Oracle Net traffic with Oracle Native Network Encryption (NNE) rather than with TLS, and require it. Prism connects with python-oracledb, whose default thin mode cannot negotiate NNE at all — so a database that requires it refuses every connection before a session exists, and no credential, service name or firewall change makes any difference.

How you can tell, and there are three ways.

• The pre-flight tells you. It tries thin, recognises this exact refusal, and retries with Oracle Instant Client by itself. The run says so:

  this database requires Oracle Native Network Encryption, which python-oracledb
  cannot negotiate in thin mode (DPY-3001) — retrying with Oracle Instant Client
  (thick mode)

  --thick skips the first attempt if you already know. Thick mode is the one thing in this procedure that needs Oracle Instant Client on the machine you run it from; Prism's own images carry it, so this is a requirement of the pre-flight and not of the appliance.

• The source's verdict says DPY-3001 on the Context streams page, in a sentence that names encryption and says the credential was not the problem.

• Your DBA can read it off a session that works. In v$session_connect_info, an encrypted session carries a line naming an algorithm — AES256 Encryption service adapter for Linux: … — and, where checksumming is on, SHA256 Crypto-checksumming service adapter for …. Look for the algorithm: the bare Encryption service for … line is present on every session, encrypted or not, so it proves nothing on its own.

What to set. One value, in your chart values, and then upgrade:

oracle:
  mode: thick

That is the whole switch. In a thick installation every Oracle source connects through Oracle Instant Client, whatever its manifest says, so a source you have already registered needs no change.

The manifest's connection.database.mode: thick is optional and does not select anything. It declares that a source must never connect in thin mode: while the installation is still thin, the app will not register it, apply a manifest to it or test its connection, and every ingest run refuses it; the query service never checks it. If the server turns out not to require encryption, a source you have not registered yet needs only the value removed from its document; a registered one keeps its connection, so removing it there means deleting the source and registering it again. If you use it, set the chart value first, and set it only on a source you are registering: a registered source's connection cannot be edited. See Which driver mode this installation should be in.

It is an installation-wide setting, not a per-source one, and that is the driver's constraint rather than our preference: python-oracledb fixes thin or thick for a whole process at its first connection and never revisits it, so one Prism pod cannot hold a thin Oracle session and a thick one. In thick, every Oracle source in the installation connects through Instant Client, including sources whose databases never required encryption. For those, thick mode connects exactly as thin does, with one exception to check before you switch: a password longer than 30 bytes. If you ever need one Oracle source thick and another thin in the same installation, tell us: it is a real limitation with a known answer, and not one you can configure around.

Passwords longer than 30 bytes

Check this before you set oracle.mode: thick. The Oracle Instant Client in Prism's images (19.30) refuses a password longer than 30 bytes with ORA-01017 — the code a wrong password gets — and thin mode accepts the same password. Because the switch is installation-wide, it can stop a source that has connected in thin mode for months, on a database that never required encryption. Rotating the password does not help unless the new one is 30 bytes or fewer.

What was measured.

• Bytes, not characters. 30 bytes connects and 31 bytes is refused. A character outside ASCII takes more than one byte — é is two — so a password of 30 characters that contains one is 31 bytes and is refused, and a password of only 16 characters crosses the line if they are all characters like that.
• The 19.x client, not thick mode as such. Instant Client 19.30, 19.31 and 19.32 all refuse; the 23.x client accepts every length tried. The images ship 19.x on purpose, because it supports older database servers that 23.x does not, so moving to a newer 19.x patch release does not remove this.
• Which databases. A 19c database limits passwords to 30 bytes itself, so an account on one should not already have a longer password. The trap is for a database that allows longer passwords, such as 23ai.

How to tell it from a wrong password. Nothing in the error does: the code, the message and everything else the driver reports are identical for both, so the refusal alone cannot tell you which you have. Test it instead.

• If the database does not require encryption, run this pre-flight twice with the same credential: once as usual, which connects in thin mode, and once with --thick. If the first connects and the second is refused ORA-01017, it is this, not the password.
• If the database requires encryption, thin mode cannot connect at all, so that test is not available. Ask your DBA to set a password of 30 bytes or fewer on a test account and connect with --thick: if that connects and the longer one does not, it is this.

What to do. Before switching, find out the length in bytes of every Oracle source's password — not only the ones that need encryption — and have any longer than 30 bytes changed to 30 bytes or fewer. Then switch.

From 0.15.0 the product names this cause itself. In thick mode, an ORA-01017 refused at logon — on the source's connection test, as an ingest run's failure reason, and in an answer from a query-through source — says that Instant Client refuses a password longer than 30 bytes as though it were wrong, and tells you to check the length before rotating the credential. It says so on every such refusal, including one that really is a wrong password: Prism does not look at your password's length to decide, so it cannot tell you which of the two you have, and this section's tests are still how you find out. On an installation in thin mode no such clause appears. (The ingest run's wording for a refused credential was shortened in both modes at the same time — it had been running past the length a failed source's reason is stored at, so its tail never reached anyone. Same facts, fewer words.)

Two details worth knowing before you read one. The ingest run's wording is shorter than the other two, because a failed source's reason is stored truncated: it says the credential rather than the view, the 30 bytes and what to check, and it leaves the rest — including the reminder that the place to change a credential is Admin → Sources — to this page. That is deliberate: in thick mode the first thing to do is look at the password's length, not go and change it. And if your database requires Native Network Encryption, the thin-mode test above is not available to you; the connection test's own sentence says so and points at the 30-byte test account instead.

Not yet measured: client character sets other than AL32UTF8, 4-byte characters, and the 21c client.

The alternative is a TCPS listener. If your DBA can offer TLS (PROTOCOL = TCPS), the default thin mode connects to it and none of the above applies — the traffic is encrypted by TLS instead of by NNE. Where both are available, either is fine; choose whichever your estate already standardises on.

What changes in the appliance when you set it. The images carry Oracle Instant Client, so nothing is downloaded and there is no client to install. The sessions Prism opens are identical in every other respect — the same read-only grant, the same SET TRANSACTION READ ONLY, the same statement timeout, the same one connection per query. The one visible difference is that error codes from the connection itself become Oracle's own (ORA-12170 for a listener that cannot be reached, where thin says DPY-6005), which is what your DBA will recognise anyway.

What it does not tell you

• Whether the view's columns mean what the manifest says they mean. A column named amount that is a plan rather than an actual passes every step here. That is the view specification conversation, not a script.
• Whether GitHub or Jira hold the same addresses for the people who matter. join counts agreement with a list you supply; the list is only as complete as the pre-flight that produced it.
• Anything about the appliance. This runs on your side against your database. Registering the source, storing the credential and the first pass are the Context streams page's job, and the self test reports them.