Extracting Data from Oracle InForm with Python

Extracting subject data from Oracle Health Sciences InForm rarely fails the way a Medidata Rave pull does — there is no live REST firehose to throttle you. Instead the trap is subtler: InForm’s reportable data lives in the Reporting & Analysis Operational Data Store (ODS), a replica refreshed on a schedule, so a naive pipeline reads the ODS as if it were the transactional system of record and quietly ships stale or half-refreshed data into the warehouse. Clinical data managers and Python ETL engineers hit this the first time a nightly extract lands rows for a subject whose CRF was locked hours earlier but whose ODS refresh had not yet completed. This page is the InForm-specific extraction primitive inside Deterministic Python ETL for EDC Data Extraction, within the broader Automated EDC Ingestion & Sync Pipelines discipline. Done correctly, each extract is keyed on the ODS refresh watermark, mapped from InForm’s form/section/item hierarchy into a CDISC ODM shape, and hash-attested so it stays defensible under 21 CFR Part 11 when an inspector asks which refresh produced a given value.

InForm ODS Extraction Flow at a Glance #

The extractor authenticates read-only, gates the whole run on whether the ODS has actually refreshed since the last watermark, pulls only the delta, remaps the study hierarchy, and commits the new watermark atomically with an audit hash.

Root Cause: Why InForm Extraction Differs from Rave #

Rave hands you a live REST surface (RWS) and punishes you with sessions and rate limits. InForm hands you a database replica and punishes you with staleness and schema drift. Confusing the two is the single most common InForm extraction defect.

The reportable copy of InForm data lives in the Reporting & Analysis ODS, an Oracle schema that Cognos-based reporting queries against. The ODS is not the transactional store where investigators enter data; it is refreshed from that store on a configured cadence — near-real-time on some deployments, batched on others. That means the currency of any row you read is bounded by the last completed refresh, not by wall-clock time. Key incremental extraction on “now” or on a subject-level modified date without accounting for refresh completion, and you will pull a window the ODS has not finished populating and lose records that arrive in the next refresh.

There are two supported ways in. The first is a read-only query against the ODS using a reporting service account and a database driver (python-oracledb); this is the fastest path to bulk subject data and the one this page centers on. The second is the InForm web services — the ItemData / Clintrial-style services and native ODM-XML export — reached over HTTP with an Oracle SSO (Oracle Access Manager) cookie or service credentials; that path is authoritative for ODM structure but heavier for large deltas. Either way, InForm’s data is organized as Study → Site → Subject → Visit → Form → Section → Item, which does not map one-to-one onto Rave’s StudyEvent → Form → ItemGroup → Item: an InForm section is the analogue of an ODM ItemGroup, and this remap is where most silent field losses hide. The relational-versus-ODM reconciliation itself is covered in CDISC ODM vs CDASH Schema Mapping.

Step-by-Step Implementation #

Each step is a single-responsibility building block; compose them into one extraction worker per study deployment.

1. Open a read-only ODS session with a dedicated service account #

Connect with a reporting account that has SELECT-only grants on the ODS views. Pin the session read-only and set the session time zone explicitly so every timestamp you read is unambiguous.

# 21 CFR Part 11 relevance: extraction principal must be attributable; we use a
# dedicated read-only reporting account and record it, never a shared human login.
import os
import oracledb

def open_ods_session() -> oracledb.Connection:
    conn = oracledb.connect(
        user=os.environ["INFORM_ODS_USER"],          # SELECT-only reporting account
        password=os.environ["INFORM_ODS_PASSWORD"],
        dsn=os.environ["INFORM_ODS_DSN"],            # host:port/service_name of the ODS
    )
    with conn.cursor() as cur:
        # Read-only guard: any accidental DML raises instead of mutating the ODS.
        cur.execute("SET TRANSACTION READ ONLY")
        # Force UTC so ODS timestamps are compared on one clock, never server-local.
        cur.execute("ALTER SESSION SET TIME_ZONE = 'UTC'")
    return conn

For the web-service path instead of direct SQL, authenticate to the InForm HTTP endpoint with an OAM SSO cookie or service credentials and request an ODM-XML export; the cookie is an access-control event and must be logged the same way as the DB login.

2. Read the ODS refresh watermark and gate the whole run on it #

Before pulling any subject data, ask the ODS when it last completed a refresh. If that timestamp has not advanced past your stored watermark, exit as a no-op — re-pulling an unchanged replica only burns time and muddies the audit trail.

# ALCOA+ requirement: Contemporaneous — currency is bounded by the ODS refresh,
# so the watermark is the refresh completion time, not the wall clock of the run.
from datetime import datetime, timezone

def latest_ods_refresh(conn: oracledb.Connection) -> datetime:
    with conn.cursor() as cur:
        # RSD_REFRESH_END marks the last fully completed ODS load for the study.
        cur.execute(
            "SELECT MAX(refresh_end_utc) FROM rpt_ods_refresh_status "
            "WHERE study_oid = :study AND status = 'COMPLETED'",
            study=os.environ["INFORM_STUDY_OID"],
        )
        (ts,) = cur.fetchone()
    return ts.replace(tzinfo=timezone.utc)

def should_run(conn, stored_watermark: datetime | None) -> datetime | None:
    refreshed_at = latest_ods_refresh(conn)
    if stored_watermark is not None and refreshed_at <= stored_watermark:
        return None                                   # ODS unchanged -> no-op exit
    return refreshed_at

3. Query the delta keyed on the ODS refresh timestamp #

Select only rows whose refresh timestamp falls in the half-open window (stored_watermark, refreshed_at]. Pin an ORDER BY on the natural key so the read is deterministic and replayable, and pass the deployed study version through so downstream mapping can pin its metadata.

# ALCOA+ requirement: Complete + Consistent — a half-open, ordered delta window
# guarantees every refreshed row is pulled exactly once and re-reads are identical.
def fetch_delta(conn, lo: datetime | None, hi: datetime) -> list[dict]:
    sql = (
        "SELECT subject_oid, site_oid, visit_refname, form_refname, "
        "       section_refname, item_refname, item_value, item_datatype, "
        "       study_version, refresh_ts_utc "
        "FROM rpt_item_data "
        "WHERE study_oid = :study "
        "  AND refresh_ts_utc <= :hi "
        + ("  AND refresh_ts_utc > :lo " if lo else "")
        + "ORDER BY subject_oid, visit_refname, form_refname, "
          "         section_refname, item_refname"
    )
    binds = {"study": os.environ["INFORM_STUDY_OID"], "hi": hi}
    if lo:
        binds["lo"] = lo
    with conn.cursor() as cur:
        cur.execute(sql, binds)
        cols = [c[0].lower() for c in cur.description]
        return [dict(zip(cols, row)) for row in cur]

4. Map the InForm form/section/item model to an ODM shape #

Translate each ODS row into the ODM tuple your warehouse expects. The InForm section becomes the ODM ItemGroupOID; the visit RefName becomes the StudyEventOID. Resolve RefNames against the metadata for the row’s own study_version, never a global one, so a deployment that renamed an item does not silently null it out.

# 21 CFR Part 11 relevance: the mapping is version-pinned so a value is always
# attributable to the exact study deployment (Attributable + Accurate) it came from.
def to_odm_row(r: dict, meta: "StudyVersionMetadata") -> dict:
    version = r["study_version"]
    return {
        "StudyOID":      os.environ["INFORM_STUDY_OID"],
        "SiteOID":       r["site_oid"],
        "SubjectKey":    r["subject_oid"],
        "StudyEventOID": meta.event_oid(version, r["visit_refname"]),
        "FormOID":       meta.form_oid(version, r["form_refname"]),
        "ItemGroupOID":  meta.group_oid(version, r["section_refname"]),  # section -> ItemGroup
        "ItemOID":       meta.item_oid(version, r["item_refname"]),
        "Value":         _coerce(r["item_value"], r["item_datatype"]),
        "StudyVersion":  version,
        "RefreshTs":     r["refresh_ts_utc"],
    }

5. Hash the raw batch, upsert idempotently, and commit the watermark #

Stamp the batch with a SHA-256 over its raw rows before any transform, upsert on the ODM natural key so a replay is a no-op, and advance the watermark only after the data and audit record are durable.

# ALCOA+ requirement: Original + Enduring — the hash fixes the exact bytes read,
# and the watermark advances only after data + audit are committed together.
import hashlib, json

NATURAL_KEY = ("StudyOID", "SiteOID", "SubjectKey", "StudyEventOID",
               "FormOID", "ItemGroupOID", "ItemOID")

def batch_hash(rows: list[dict]) -> str:
    canonical = json.dumps(
        [[r[k] for k in (*NATURAL_KEY, "Value", "RefreshTs")] for r in rows],
        sort_keys=False, separators=(",", ":"), default=str,
    )
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()

def commit_batch(dst, rows: list[dict], watermark: datetime) -> str:
    digest = batch_hash(rows)
    with dst.begin():                                 # one transaction: rows + watermark
        dst.upsert("item_data", rows, on=NATURAL_KEY)  # replay-safe on the natural key
        dst.set_watermark("inform_ods", watermark, batch_sha256=digest)
    return digest

Verification and Audit Trail #

An InForm extractor is GxP-relevant software: “the extract ran” must be provable from the log. Emit one immutable record per batch to a write-once store, and make automated runs distinguishable from manual replays so an inspector can reconstruct which ODS refresh produced which value.

To confirm the pipeline, assert three properties against a seeded ODS fixture: a run against an unchanged refresh timestamp exits as a no-op and leaves the watermark untouched; a run across a new refresh pulls exactly the rows in (lo, hi] and no more; and re-running an identical window yields the same batch_sha256 with zero net upserts. Genuine discrepancies the mapping surfaces — an item RefName absent from the deployed version’s metadata, for instance — feed Automated Clinical Query Generation rather than aborting the batch, and cleaned rows flow on into Pandas DataFrames for Clinical Data Cleaning.

Edge Cases and Vendor-Specific Gotchas #

ODS refresh latency versus real time. The ODS is a scheduled replica, not the live transactional store, so a value entered at a site is invisible to your extract until the next refresh completes — sometimes minutes, sometimes hours later. Never present ODS-derived data as real time, and never key deltas on entry time; key them on refresh completion. A study that genuinely needs low-latency reads is a case for the transactional web services, not for shortening the ODS window.

Study versioning and deployment drift. InForm study designs are versioned and deployed (via Central Designer), and a deployment can rename a form, add a section, or change an item’s datatype mid-study. A mapping pinned to a single “current” metadata snapshot will null out or mis-type rows that belong to an earlier version. Resolve every RefName against the study_version carried on the row itself, and treat the metadata cache as version-keyed, not global.

Timezone of ODS timestamps. ODS timestamp columns may be stored in server-local time, UTC, or a study-configured zone, and the difference is invisible until a daylight-saving transition silently drops or double-pulls a refresh window. Force TIME_ZONE = 'UTC' on the session, store the watermark in UTC, and never compare a naive local timestamp against it.

Frequently Asked Questions #

Related #

• Deterministic Python ETL for EDC Data Extraction — the parent extraction discipline this InForm reader plugs into.
• Automating Medidata Rave Data Pulls with Python — the equivalent pattern for a live-REST EDC, and the contrast that explains InForm’s constraints.
• CDISC ODM vs CDASH Schema Mapping — reconciling InForm’s form/section/item hierarchy into an ODM relational schema.
• Handling API Rate Limits in Clinical Sync — pacing the InForm web services when you supplement ODS reads with HTTP calls.
• Automated EDC Ingestion & Sync Pipelines — the parent reference for this discipline.