Oracle

Adding and configuring an Oracle connection within Qualytics empowers the platform to build a symbolic link with your schema to perform operations like data discovery, visualization, reporting, syncing, profiling, scanning, anomaly surveillance, and more.

This documentation provides a step-by-step guide on how to add Oracle as both a source and enrichment datastore in Qualytics. It covers the entire process, from initial connection setup to testing and finalizing the configuration.

By following these instructions, enterprises can ensure their Oracle environment is properly connected with Qualytics, unlocking the platform's potential to help you proactively manage your full data quality lifecycle.

Let’s get started 🚀

Oracle Setup Guide

Qualytics connects to Oracle through the Oracle JDBC Thin driver. It uses JDBC metadata APIs to discover schemas, tables, columns, and primary keys. Qualytics automatically filters out Oracle system schemas (SYS, SYSTEM, DBSNMP, OUTLN, APPQOSSYS, CTXSYS, MDSYS, OLAPSYS, ORDDATA, ORDSYS, WMSYS, XDB, LBACSYS, DVSYS, AUDSYS, and others) during schema discovery.

Minimum Oracle Permissions (Source Datastore)

Example: Source Datastore User (Read-Only): Schema-Level Access

Replace <schema_name> and <password> with your actual values.

-- Create a dedicated read-only user
CREATE USER qualytics_read IDENTIFIED BY "<password>"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

-- Grant connection privileges
GRANT CREATE SESSION TO qualytics_read;

-- Option A: Grant read access to ALL tables (broad access)
GRANT SELECT ANY TABLE TO qualytics_read;

-- Option B: Grant read access to a specific schema (restrictive access)
-- Run this for each table you want Qualytics to access:
-- GRANT SELECT ON <schema_name>.<table_name> TO qualytics_read;

Example: Source Datastore User (Read-Only): Using a Role

For organizations that prefer role-based access control:

-- Create a custom read-only role
CREATE ROLE qualytics_read_role;

-- Grant SELECT on all tables in the target schema
BEGIN
  FOR t IN (SELECT table_name FROM all_tables WHERE owner = UPPER('<schema_name>'))
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON <schema_name>.' || t.table_name || ' TO qualytics_read_role';
  END LOOP;
END;
/

-- Assign the role to the Qualytics user
GRANT qualytics_read_role TO qualytics_read;

Troubleshooting Common Errors

Detailed Troubleshooting Notes

Authentication Errors

The error ORA-01017: invalid username/password indicates that the credentials are incorrect.

Common causes:

• Incorrect password: the password does not match. Oracle passwords are case-sensitive by default.
• Account locked: the account has been locked due to too many failed login attempts. Unlock with ALTER USER <user> ACCOUNT UNLOCK.
• Password expired: the password has expired per the user's profile settings.

Permission Errors

The error ORA-00942: table or view does not exist can mean either the object truly does not exist or the user lacks SELECT access to it.

Common causes:

• Missing SELECT grant: the user does not have SELECT on the target table. Oracle does not distinguish between "table not found" and "no permission" for security reasons.
• Schema not specified: the table exists in a different schema and the user is querying without the schema prefix.
• Synonym not created: the user expects to access the table without the schema prefix, but no synonym exists.

Connection Errors

The error ORA-12505: TNS:listener does not currently know of SID or ORA-12514: TNS:listener does not currently know of service means the connection identifier is incorrect.

Common causes:

• Wrong SID or Service Name: the value does not match the database instance configuration.
• Listener not running: the Oracle listener process is not started on the server.
• Wrong host or port: the host or port (default 1521) does not match the Oracle server configuration.

Add a Source Datastore

A source datastore is a storage location Qualytics connects to so it can profile, scan, and monitor data. Adding Oracle as a source lets Qualytics query it through the Oracle JDBC Thin driver and run quality operations on the tables it discovers.

Before you start, review the Minimum Oracle Permissions the connecting user needs.

Field reference

The Add Datastore page shows the sections below when Oracle is selected. When reusing an existing connection, the Connection Properties and Secrets Management sections come already filled in and read-only: Qualytics has already validated those credentials, so you fill in only the Datastores Extraction and the Datastore Properties. To change a saved connection's credentials, edit the connection through the Manage Connections page; edits there apply to every datastore that reuses the connection.

Connection Properties

These fields define the Oracle database Qualytics connects to and the account it connects with. They belong to the connection: when reusing an existing connection, they come already filled in and read-only.

Connect By

Oracle accepts either a system identifier or a service name. Setting Method changes the field shown below it, so pick the tab that matches your instance. These fields also belong to the connection: already filled in and read-only when reusing one.

Secrets Management

This group is optional: use it only if you want Qualytics to pull credentials from a secrets manager instead of typing them into the form. Turn on HashiCorp Vault to show the fields below. Despite the label, any secrets manager that exposes a compatible REST API works, not only HashiCorp Vault; see Secrets Management. It also belongs to the connection: read-only when reusing an existing connection.

Datastores Extraction

Pick the schema or schemas Qualytics should read from. You fill these in on both flows.

Datastore Properties

Common fields for every source datastore, shown below the Datastores Extraction section. You fill these in on both flows.

Steps

There are two ways to set up the connection: reuse a connection you already saved (Existing Connection) or create a new one from scratch (New Connection). The tabs below walk through each option; pick the one you want to follow. Each field is described in the Field reference above.

API Payload Examples

Creating a Source Datastore

This section provides sample payloads for creating an Oracle datastore. Replace the placeholder values with actual data relevant to your setup.

Endpoint: /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "schema": "oracle_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection": {
        "name": "your_connection_name",
        "type": "oracle",
        "host": "oracle_host",
        "port": 1521,
        "username": "oracle_username",
        "password": "oracle_password",
        "parameters": {
            "authentication_type": "SID",
            "sid": "orcl",
            "protocol": "TCP"
        }
    }
}

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "schema": "oracle_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection": {
        "name": "your_connection_name",
        "type": "oracle",
        "host": "oracle_host",
        "port": 1521,
        "username": "oracle_username",
        "password": "oracle_password",
        "parameters": {
            "authentication_type": "SERVICE_NAME",
            "service_name": "orclpdb1",
            "protocol": "TCP"
        }
    }
}

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "schema": "oracle_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection_id": 123
}

# Step 1: Create a Connection
qualytics connections create \
    --type oracle \
    --name "your_connection_name" \
    --host ${ORACLE_HOST} \
    --port 1521 \
    --username ${ORACLE_USER} \
    --password ${ORACLE_PASSWORD}

# Step 2: Create a Source Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --schema your_schema

Link an Enrichment Datastore to a Source Datastore

Oracle cannot host an enrichment datastore, so link one that lives on a supported connector:

Endpoint Details: /api/datastores/{datastore-id}/enrichment/{enrichment-id} (patch)