Read an Oracle view as a context stream

This page is for an administrator who has a view in an Oracle database of their own — a resource plan, a project portfolio, a timesheet extract, whatever their organization keeps there — and wants Prism to answer questions from it.

You will author a short YAML document called a source manifest that tells Prism which view to read, what each column means, and what Prism is allowed to say about it. Prism ships one to start from: the Resource plan recipe in Admin → Context streams, a worked example of a plan view with every line marked as the engine's or yours. Nothing here needs Tetrate: you enter the manifest and the credential in Prism's own admin surface, and the connection is between your appliance and your database.

Budget an hour, most of it spent with whoever owns the view.

Read Take a source live first if you have not connected any source yet. Its Oracle section covers the probe, the verdict and what each refusal code means; this page is the layer above it — the document you write — and assumes the appliance is already running.

Read in place, or copied — decide first

An Oracle view can be a context stream in two ways, and the recipe is the first:

	Read in place (kind: query-through)	Copied on a schedule (kind: snapshot)
What your database sees	One statement per question, bounded by the source's statement timeout	One statement per scheduled copy, and nothing between copies
What Prism keeps	Nothing	A dated copy of the view per run, for as long as keep says
What an answer describes	The view as it stands when the question is asked	The latest copy, by default — and any earlier copy, when a question asks
Choose it when	You want today's plan, and your database can take a statement per question	You want to ask how the view changed — "how did the plan move between March and June"

Start with the recipe as shipped — read in place — unless you already know you want history. Section 8 is the copied form, with a complete document to start from.

1. What to ask your DBA for

What	Detail
One view	Not a table and not a schema. Prism reads exactly one view, and what is in it is the whole of what Prism can ever see — which makes the view your access control rather than a setting you have to trust us with
A local database user for the appliance	One service account for the whole appliance, never one per reader. It appears in your audit trail as itself. A password, not a wallet — see the two limits in Take a source live
A role granted SELECT on the view, and nothing else	Granted to that user. The grant is the control: every session Prism opens begins SET TRANSACTION READ ONLY, which blocks DML and not DDL, so it is defence in depth over a read-only grant and never a substitute for one
The view in that user's default schema, or a synonym to it	There is no schema setting on the source, deliberately — see section 2
Host, port and service name, and a network path to the listener	One Oracle Net connection, outbound from the Prism namespace to your listener's port. Prove the egress before you register anything
A row identity column, if the view can carry one	Not required. It lets a fan-out on a join be seen, and a copied stream pages on it

If your database requires Native Network Encryption, that is one chart value (oracle.mode: thick) rather than a finding: see When a database requires Native Network Encryption. Pre-flight an Oracle view is the script your DBA runs against the view with the same manifest you will register, and it is the fastest way to find a missing grant or a misspelt column before Prism does.

2. The four connection values

You enter these on the source in Admin → Context streams, not in the chart.

Value	What it is
db_host	The hostname of your Oracle listener
db_port	The listener's port — 1521 unless your estate moved it
db_service	The service name, not an SID and not a TNS alias. tnsnames.ora is not consulted
db_user	The service account. Configuration, not a secret: it appears in your audit trail and in every authentication failure, and somebody has to be able to read it to fix one

The credential is that account's password, entered on the source's Credential tab and never in the manifest.

Keep these four names in your manifest. The form you fill in is built from the recipe you opened, so it submits the recipe's parameter names; a manifest that calls one of them something else is refused at Register.

There is no schema value, deliberately. A session opens in the service account's own default schema, so the view lives there or the account has a synonym to it. A schema name is the one value that differs per estate in a way a recipe cannot carry, and a value nobody remembers to set is a worse failure than a synonym somebody creates once.

3. What the manifest needs your view to have

Two things are structural. Everything else is yours to declare.

Needed	Why
A person identifier your other systems also know — most often an employee number	Every per-person figure is keyed on it, and it is what ties a row here to the same person's pull requests and issues, through your organizational context stream. It must be issued and never reused; a name is not one
A time column	Every metric windows and trends on one. In a plan it is the period the hours are planned for

Two more are worth having, and the example carries both:

• A row identity, if the view can carry one — see section 1.
• An edition column, if the view holds more than one version of the same period — an approved plan and a forecast, say. Summing across them double counts, so the example makes every question name one (required_filters). If your view is cut to one edition, delete that requirement with the column.

Oracle's spelling rule is the one to get right. An unquoted identifier is folded to upper case, so employee_id and "EMPLOYEE_ID" are the same column. A column created with a quoted mixed-case name — "Project Name" — is a different column from PROJECT_NAME, and has to be written with its quotes, exactly. The example quotes every column so the rule is visible on every line.

Oracle has no boolean column. A flag arrives as 'Y'/'N' or as 1/0 depending on who built the view. The example's is_active expression accepts both, so a numeric flag cannot quietly read false for everybody; copy it for any flag of yours.

4. The worked example, and which parts are yours

Open the Resource plan recipe in Admin → Context streams and choose Review the manifest: the whole document is there, with a comment on every line an administrator decides about. It uses the vocabulary of Connect your Snowflake directory:

• STRUCTURAL — the engine needs it. Delete it and something stops working, and the comment beside it says what.
• YOURS — yours to decide: the view, every column as your view spells it (each sql:), and whether an optional column exists at all.

There is no OURS line: the example is fictional, so every column is either the engine's or yours. The fields, and the column of the example view each reads:

Field	Reads (sql:)	Marker	What it is
row_key	"ROW_KEY"	YOURS	The view's own row identity; hidden from answers
employee_id	"EMPLOYEE_ID"	STRUCTURAL	The person the row plans, as an employee id
email	"EMAIL_ADDRESS"	YOURS	A second identifier; hidden, so it is never a grouping key
manager_id	"MANAGER_ID"	YOURS	The person's line manager, as a grouping key
is_active	a CASE over "IS_ACTIVE"	YOURS	Whether the person is marked active — see section 3
team	"TEAM_NAME"	YOURS
project_id	"PROJECT_ID"	YOURS
project_name	"Project Name"	YOURS	Quoted, because the column was created mixed-case
period_start_date	"PERIOD_START"	STRUCTURAL	The month the hours are planned for; the time field
scenario	"SCENARIO"	YOURS	Which edition of the plan a row belongs to
planned_hours	"PLANNED_HOURS"	YOURS	Hours planned; never described as hours worked, cost or spend

Three metrics are built on them — allocations (plan rows), people (distinct people with a plan row) and planned_hours (a sum) — and each windows on period_start_date.

What to change, in order:

1. source: — your view's name, bare. Unquoted, so Oracle folds it to upper case; a view created with a quoted lower-case name needs the quotes.
2. Every sql: — your column, as your view spells it.
3. Delete the fields your view does not have, and every reference to them: a field named in a metric's show_underlying_values, in required_filters or in a verification question has to leave those lists in the same edit, or the document is refused at load and the message names the list. A marker says whose line it is, not that deleting it is free.
4. Add the columns you want to ask about, each with an explicit sql:.
5. policy.source_label and stream: — what a reader is told a figure came from. Name what the data is (Resource plan, Project portfolio), not the database it lives in.
6. policy.verification.questions — see section 7.

name: clarity is the recipe's key and stays as shipped when you only edit the model; a source may be registered under a name of your own, in either form.

5. Check every column exists — before you register anything

A passing probe proves the session opened and the credential was accepted, and says so in exactly those words — it names no object of yours. So run this in your own session, as the service account:

-- 1. The view is visible to this account at all.
SELECT COUNT(*) FROM resource_plan_v;

-- 2. Every column you named in `sql:` exists. One entry per field. Unquoted
--    names are stored in upper case; a quoted mixed-case one is stored as
--    written. Through a synonym, ask for the view it points at.
SELECT column_name, data_type
  FROM all_tab_columns
 WHERE table_name = 'RESOURCE_PLAN_V'
   AND column_name IN ('ROW_KEY', 'EMPLOYEE_ID', 'EMAIL_ADDRESS', 'MANAGER_ID',
                       'IS_ACTIVE', 'TEAM_NAME', 'PROJECT_ID', 'Project Name',
                       'PERIOD_START', 'SCENARIO', 'PLANNED_HOURS')
 ORDER BY column_name;

-- 3. The editions the view holds — what every question will have to name.
SELECT scenario, COUNT(*) FROM resource_plan_v GROUP BY scenario;

-- 4. How many rows carry the person identifier. This is the ceiling on every
--    per-person answer Prism will give you.
SELECT COUNT(*) AS plan_rows, COUNT(employee_id) AS with_a_person
  FROM resource_plan_v;

Query 2 must return one row per column you listed. A missing column simply does not appear, so a result with ten rows where you listed eleven is the finding. If query 1 fails instead, the usual cause is the grant or the synonym rather than a column name.

6. Register it, and what the Test proves

Sign in as a super admin and open Admin → Context streams. Pick the Resource plan recipe, open Review the manifest, and replace the document with yours. Enter the four connection values and the password, then Test.

Then approve the source, and only then enable it — enable is refused from probed. Take a source live says what a passing probe does and does not prove, and what each refusal code means.

Run the Test on the source's Manifest tab once the view is cut. It compiles every column the manifest names into a statement, has Oracle parse each one against your view, runs each with no rows returned, and reports every column that did not resolve with the view's real column list beside it — before the source can be enabled. That is the check that turns a misspelt column into a refusal naming the field, rather than an empty figure.

7. Verify it — with your questions, against your figures

The recipe ships three example questions written for the fictional plan, and you should replace them with yours. Good ones are the ones where you already know the number:

• Which scenarios does the view hold, and how many rows of each? — against query 3 above.
• How many hours are planned for each team over the last ninety days? — against your planning system's own report for the same months.
• Which projects have the most hours planned, month by month? — a project you know is fully staffed must appear.

Ask them on the verification screen and mark each correct, wrong or skipped. Only a question you have marked correct earns the badge a reader sees beside an answer. Every question names a scenario, because the example requires one.

enabled is not verified. Enabling a source means this installation demonstrably opened a session to your database. Whether the figures are right is this section, and it is not something the appliance can decide.

8. If you want the plan's history: the copied form

A read-in-place source answers for the view as it stands. To ask how it changed, the stream has to keep dated copies: kind: snapshot, with an ingest block that says what to copy, when, and for how long. The document below is the same example view in that form — the same connection, the same column spellings, the same fields — and it is the one to start from. The ingest suite executes this exact document against a real Oracle, so it is kept working rather than kept in step by hand.

Register it in this form from the start, under whatever name you choose. The kind of a registered stream cannot change afterwards — its Manifest tab refuses a document of another kind — so a read-in-place stream you already have is deleted and registered again in this form, not edited into it.

Store the stream's password on the source. The ingest job copies the document the stream holds, under whatever name you gave it, from the first scheduled run after you enable it. A database password is only ever read from the source itself, never from the job's own configuration. Take a source live has the rest of that rule.

What differs from the read-in-place recipe, and why:

• ingest.streams[0].query.sql is the one statement a copy runs. It selects every column the model reads, each aliased to the field's name, because the copy lands in Prism under those names. paginate pages the view on row_key, so on this form the row identity is structural: a view without one cannot be copied in pages.
• incremental — schedule is when the copy is taken (cron, UTC), and keep is how long copies are kept. Size it before you choose: each copy is the whole view, so a weekly copy kept two years is about a hundred and five copies of it. Limits sizes it, and the pre-flight warns from five copies.
• columns is what lands, one entry per alias.
• The model reads the copy, not the view: source: is the table the copy lands in, each field is a column of it by name, and two fields are added — as_of, the day a copy was taken, and is_latest_snapshot, which the model applies by default so a question reads the latest copy and counts each row once. A question that means "between two copies" names that field in its own filters and groups by as_of.
• policy.freshness is present, because there is now a run that can be late or return nothing. A run that returns no rows is a failure, never an empty copy: the previous copy stands.

apiVersion: prism.tetrate.io/source/v0
name: clarity                            # keep it: the ingest job chooses how to copy from this name
kind: snapshot                           # STRUCTURAL: a dated copy per run

connection:                              # identical to the recipe's, and it cannot change after you register
  auth:
    type: database
    credential_ref: oracle_password
    username_parameter: db_user
  credential_mode: service
  tier: enterprise
  rate_limit: {requests_per_minute: 0}   # one statement per copy; nothing to pace
  database:
    engine: oracle
    auth_mode: password
    mode: thin
    host_parameter: db_host
    port_parameter: db_port
    service_name_parameter: db_service
    read_only_role: PRISM_READER
    statement_timeout: PT10M             # the ceiling on the one statement a copy runs
  parameters:
    db_host: {type: string, required: true}
    db_port: {type: integer, default: 1521}
    db_service: {type: string, required: true}
    db_user: {type: string, required: true}

ingest:
  streams:
    - name: resource_plan
      table: resource_plan_copy          # where the copies land in Prism; named again in the model below
      query:
        # YOURS: your view, and every column as your view spells it, aliased
        # to the name it lands under.
        sql: |
          SELECT
            r."ROW_KEY"        AS row_key,
            r."EMPLOYEE_ID"    AS employee_id,
            r."EMAIL_ADDRESS"  AS email,
            r."MANAGER_ID"     AS manager_id,
            CASE WHEN UPPER(TRIM(TO_CHAR(r."IS_ACTIVE")))
                      IN ('Y', 'YES', 'T', 'TRUE', '1') THEN 1 ELSE 0 END
                               AS is_active,
            r."TEAM_NAME"      AS team,
            r."PROJECT_ID"     AS project_id,
            r."Project Name"   AS project_name,
            r."PERIOD_START"   AS period_start_date,
            r."SCENARIO"       AS scenario,
            r."PLANNED_HOURS"  AS planned_hours
          FROM resource_plan_v r
        paginate: {type: keyset, keys: [row_key], page_size: 5000}   # STRUCTURAL: pages on the row identity
      incremental:
        mode: snapshot
        schedule: "0 4 * * 1"            # YOURS: Mondays at 04:00 UTC
        keep: P90D                       # YOURS: a quarter of weekly copies
      columns:
        - {name: row_key,           from: row_key,           type: string, primary_key: true, nullable: false}
        - {name: employee_id,       from: employee_id,       type: string, nullable: false, identity: employee_id}
        - {name: email,             from: email,             type: string, identity: email}
        - {name: manager_id,        from: manager_id,        type: string, identity: employee_id}
        - {name: is_active,         from: is_active,         type: boolean}
        - {name: team,              from: team,              type: string}
        - {name: project_id,        from: project_id,        type: string}
        - {name: project_name,      from: project_name,      type: string}
        - {name: period_start_date, from: period_start_date, type: date, nullable: false}
        - {name: scenario,          from: scenario,          type: string}
        - {name: planned_hours,     from: planned_hours,     type: number}

model:
  datasets:
    - name: resource_plan
      source: resource_plan_copy         # the stream's `table:`, and named again in `is_latest_snapshot`
      stream: Resource plan              # YOURS: the name a reader sees beside every figure
      primary_key: [row_key, as_of]      # one row per view row PER COPY
      default_filters:
        - is_latest_snapshot: true       # questions read the latest copy unless they name this field
      required_filters:
        - {scenario: {is_null: false}}
      ai_context: >-
        The resource plan, copied from the view once a week. One row per person
        per project per month per scenario PER COPY, and questions read the
        LATEST copy unless they say otherwise, so a row count is a count of
        allocations in that copy and never of people; use the `people` metric
        to count people. To compare copies, pass the filter
        {"is_latest_snapshot": {"is_null": false}} and group by `as_of`.
        Every question names a `scenario`, because a plan and a forecast of the
        same month are both in the view.
      fields:
        - {name: row_key, type: string, hidden: true}
        - {name: employee_id, type: string, identity: employee_id}
        - name: email
          type: string
          identity: email
          hidden: true
          description: Work email address. Used to resolve identity; not available as a grouping key.
        - {name: manager_id, type: string, identity: employee_id}
        - {name: is_active, type: boolean}
        - {name: team, type: string}
        - {name: project_id, type: string}
        - {name: project_name, type: string}
        - name: period_start_date
          type: date
          time_intervals: [MONTH, QUARTER, YEAR]
        - {name: scenario, type: string}
        - {name: planned_hours, type: number}
        - name: as_of
          type: date
          time_intervals: [DAY, WEEK, MONTH]
          description: the day this copy was taken
        - name: is_latest_snapshot
          type: boolean
          sql: (as_of = (SELECT MAX(as_of) FROM resource_plan_copy))
      metrics:
        allocations:
          type: count
          time_field: period_start_date
          unit: plan rows — one per person, project, month and scenario
          show_underlying_values: [employee_id, project_name, period_start_date, scenario, planned_hours, as_of]
        people:
          type: count_distinct
          sql: employee_id
          time_field: period_start_date
          unit: people with at least one plan row in the window
        planned_hours:
          type: sum
          sql: planned_hours
          time_field: period_start_date
          unit: hours planned, for the scenario the question names
          show_underlying_values: [employee_id, project_name, period_start_date, scenario, planned_hours, as_of]
  relationships:
    - {name: resource_plan_person, from: resource_plan.employee_id, to: person,
       via: employee_id, cardinality: many-to-one, verified: false}

policy:
  source_label: Resource plan
  min_n_applies_to: [count, count_distinct, sum, average, min, max, median, percentile]
  freshness: {expect_within: P8D, empty_is_failure: true}
  verification:
    status: connected_unverified
    questions:
      - prompt: >-
          Which scenarios does the latest copy hold, and how many plan rows of
          each?
        query:
          source: clarity
          dataset: resource_plan
          metrics: [allocations]
          dimensions: [scenario]
          filters: [{scenario: {is_null: false}}]
          sort: ["-allocations"]
          limit: 50
      - prompt: >-
          How many hours were planned for each team, copy by copy, under the
          scenario you treat as the plan of record?
        query:
          source: clarity
          dataset: resource_plan
          metrics: [planned_hours]
          dimensions: [team, scenario, as_of]
          filters: [{scenario: {is_null: false}}, {is_latest_snapshot: {is_null: false}}]
          sort: [{by: as_of, dir: asc}]
          limit: 100
  legacy_tools: []

billing: {pack: Resource plan}

If something is wrong

Symptom	Where to look
The copy loads nothing and the job keeps running	A stalled copy: from 0.16.0 wait for the bound and read the line it writes; without a statement_timeout, a stalled fetch can take about two hours to end by default, and connecting has no bound of its own: the per-source limit (ingest.engine.sourceDeadlineSeconds) stops the copy and the tick goes on to the next source, but in thick mode a connect that never returns keeps the tick's pod from exiting until the job's deadline
The probe refuses with an ORA- or DPY- code	Take a source live — each code, and what it rules out
DPY-3001	Your database requires Native Network Encryption: set oracle.mode: thick in the chart values
The Manifest tab's Test names a column	The column list beside it is your view's real one; fix the sql: and test again
Every question is refused asking for a scenario	The dataset requires one — name it, or delete the requirement if your view holds one edition
A copied stream reads not ingested	The line under it names the part of the document this release does not implement — ask us
An answer is refused because the stream is read in place	A question scoped to "people on team X" needs rows Prism holds; the copied form answers it
A copy is refused with a code this page does not list	When the code is not in this list

When the code is not in this list

Prism never prints your server's own error message. The message can quote the statement, the login and sometimes a value. The message is also the fastest way to find the cause, so read it yourself, in a session set up the way the copy sets one up. Sign in as the service account and run:

ALTER SESSION SET TIME_ZONE = 'UTC';
ALTER SESSION SET CURRENT_SCHEMA = <your schema>;   -- only if the manifest names one
SET TRANSACTION READ ONLY;
<the stream's SELECT, with the window values filled in>;

The copy runs the same settings in this order.