Oracle

faz's Oracle connector uses the python-oracledb driver in thin mode, so you don't need to install Oracle Instant Client. Queries use Oracle SQL syntax including FETCH FIRST n ROWS ONLY for the Guardrails row cap.

Quick example

databases:
  - name: <database>          # used as --database <database> in queries
    type: oracle
    host: <host>
    port: 1521
    database: <service-name>  # PDB / service name (e.g. ORCLPDB1)
    username: <username>
    password: <password>

permissions:
  - database: <database>      # must match `name:` above
    access: R
    # access codes:
    # R    read only           — SELECT, EXPLAIN
    # W    write only          — INSERT, UPDATE, DELETE (no SELECT)
    # RW   read + write
    # RA   read + append       — SELECT, EXPLAIN, INSERT
    # RWA  read + write, no DELETE
    # A    admin (incl. DDL)

See Permissions for the full model, per-table overrides, and the operation matrix.

faz query "SELECT * FROM <table> WHERE ROWNUM <= 5" --database <database> --table <table>

uv run faz query "SELECT * FROM <table> WHERE ROWNUM <= 5" --database <database> --table <table>

python -m faz query "SELECT * FROM <table> WHERE ROWNUM <= 5" --database <database> --table <table>

Configuration

The DSN is built as host:port/database. For multitenant Oracle, database should be the PDB name (e.g. ORCLPDB1), not the CDB.

Capabilities

• SQL with Oracle-specific syntax: FETCH FIRST n ROWS ONLY, ROWNUM, CONNECT BY, MERGE, hierarchical queries.
• The Guardrails stage uses sqlglot's oracle dialect when emitting the row-cap rewrite, so LIMIT n becomes FETCH FIRST n ROWS ONLY correctly.
• Schema discovery via ALL_TABLES / ALL_TAB_COLUMNS.
• DDL (CREATE, ALTER, DROP, TRUNCATE) — only when the policy grants A.
• Thin-mode driver — no Oracle client libraries to install.

Limitations

• No bulk fetching tuning — Oracle's array-fetch size defaults are used. For very large result sets, results are loaded fully into memory before the Guardrails row cap applies.

• No PL/SQL anonymous blocks. A BEGIN ... END; block doesn't fit the safety pipeline's per-statement model. Use plain SQL.

• Stacked statements are blocked at the connector level. A query string with more than one top-level statement separated by ; is rejected before it reaches Oracle — defense-in-depth against stacked-query injection. Even if RBAC would allow each statement individually, the connector refuses to send the combined string. Only literal ;-separated stacking counts; complex single statements pass through fine.

  Blocked — two top-level statements separated by ;:

  SELECT * FROM <table-1>; DELETE FROM <table-2>;

  Allowed — single complex statements (UNION, subqueries, CTEs, CONNECT BY, MERGE, INSERT ... SELECT). RBAC checks every table they touch:

  -- UNION ALL across two SELECTs
  SELECT <col> FROM <table-1>
  UNION ALL
  SELECT <col> FROM <table-2>;
  
  -- Subquery in FROM
  SELECT * FROM (SELECT * FROM <table> WHERE ROWNUM <= 100) sub;
  
  -- CTE / WITH clause
  WITH recent AS (SELECT * FROM <table>)
  SELECT * FROM recent;
  
  -- Insert-from-select — one statement, RBAC checks BOTH tables
  INSERT INTO <archive-table> SELECT * FROM <source-table>;

  Submit stacked statements as separate faz query calls — one statement per call.

Troubleshooting

ORA-12541: TNS:no listener — Oracle listener isn't running, or you've got the wrong port. Verify with tnsping.

ORA-01017: invalid username/password — credentials are wrong. Try connecting with sqlplus to confirm.

ORA-12514: TNS:listener does not currently know of service — database isn't the right service name. Run lsnrctl services on the Oracle host to list registered services and PDBs.

ORA-00942: table or view does not exist during a query — the connecting user lacks privileges on the table. Grant explicitly: GRANT SELECT ON owner.table_name TO app_reader;.

For the broader troubleshooting flow, see Connection failed.

Related

• Databases overview — cross-connector basics.
• PostgreSQL — sibling SQL connector.
• faz.yaml — the full config schema.