Tutorial: Oracle

This tutorial walks you through connecting an Oracle database (12c through 26ai) to Faucet and querying it via the REST API.

Prerequisites

Faucet installed and running (faucet serve) — see Getting Started
An admin account created (faucet admin create)
An Oracle Database instance (12c through 26ai) with at least one table
No Oracle Instant Client required -- Faucet uses a pure-Go driver

Step 1: Connect Oracle to Faucet

The DSN format for Oracle:

oracle://username:password@host:port/service_name

Via CLI

bash

faucet db add \
  --name mydb \
  --driver oracle \
  --dsn "oracle://appuser:secret@localhost:1521/XEPDB1"

Via API

bash

TOKEN=$(curl -s http://localhost:8080/api/v1/system/admin/session \
  -H "Content-Type: application/json" \
  -d '{"email":"[email protected]","password":"changeme123"}' \
  | jq -r '.session_token')

curl -X POST http://localhost:8080/api/v1/system/service \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "mydb",
    "driver": "oracle",
    "dsn": "oracle://appuser:secret@localhost:1521/XEPDB1"
  }'

Via Admin UI

Open http://localhost:8080/admin
Navigate to Services
Click Add Service
Select driver: Oracle
Enter the service name and DSN
Click Save

Step 2: Verify the Connection

bash

faucet db test mydb

Or list tables via the API:

bash

curl http://localhost:8080/api/v1/mydb/_table \
  -H "Authorization: Bearer $TOKEN"

Expected response:

json

{
  "resource": [
    {"name": "EMPLOYEES"},
    {"name": "DEPARTMENTS"},
    {"name": "PRODUCTS"}
  ]
}

Note: Oracle table and column names are typically uppercase.

Step 3: Query Your Data

List all records

bash

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?limit=10" \
  -H "Authorization: Bearer $TOKEN"

Filter records

bash

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?filter=DEPARTMENT_ID%20%3D%2010" \
  -H "Authorization: Bearer $TOKEN"

Select specific fields

bash

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?fields=FIRST_NAME,LAST_NAME,EMAIL" \
  -H "Authorization: Bearer $TOKEN"

Paginate

bash

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?limit=10&offset=0&order=HIRE_DATE%20DESC" \
  -H "Authorization: Bearer $TOKEN"

Count records

bash

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?include_count=true" \
  -H "Authorization: Bearer $TOKEN"

Step 4: Insert, Update, and Delete

Insert a record

bash

curl -X POST http://localhost:8080/api/v1/mydb/_table/EMPLOYEES \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"resource": [{"FIRST_NAME": "Jane", "LAST_NAME": "Smith", "EMAIL": "jsmith"}]}'

Note: Oracle does not support PostgreSQL-style RETURNING clauses, so the response echoes the submitted records rather than server-generated values (such as sequences or identity columns). Use a subsequent GET to retrieve auto-generated IDs.

Update a record

bash

curl -X PATCH "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?ids=207" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"LAST_NAME": "Johnson"}'

Delete a record

bash

curl -X DELETE "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?ids=207" \
  -H "Authorization: Bearer $TOKEN"

Oracle-Specific Notes

Schema (Owner)

In Oracle, the schema is the same as the database user (owner). By default, Faucet uses the connected user's schema. To query tables owned by a different user:

bash

faucet db add \
  --name hrdb \
  --driver oracle \
  --dsn "oracle://appuser:secret@localhost:1521/XEPDB1" \
  --schema HR

Service name vs. SID

Oracle databases are identified by either a service name or a SID. The DSN format uses service name by default:

oracle://user:pass@host:1521/SERVICE_NAME

For SID-based connections, use the SID parameter:

oracle://user:pass@host:1521?SID=ORCL

Stored procedures

Faucet can execute Oracle stored procedures:

bash

curl -X POST http://localhost:8080/api/v1/mydb/_proc/UPDATE_SALARY \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"params": {"EMP_ID": 100, "NEW_SALARY": 75000}}'

Oracle procedures are invoked using PL/SQL BEGIN ... END; blocks. Procedures that only perform DML (no result sets) return an empty result.

Pagination

Faucet uses Oracle 12c+ OFFSET ... ROWS FETCH NEXT ... ROWS ONLY syntax for pagination. This requires Oracle Database 12c (12.1) or later. Tested through Oracle 26ai.

Supported features

Feature	Supported
Schema introspection	Yes
RETURNING clause	No
Upsert (MERGE)	No
Stored procedures	Yes
Views	Yes
Parameter style	`:1, :2, :3`
Default schema	Current user
Pagination	OFFSET/FETCH (12c+)

Docker for local development

Run Oracle Database Express Edition locally with Docker:

bash

docker run -e ORACLE_PWD=YourStrong!Passw0rd \
  -p 1521:1521 --name oracle-xe \
  -d container-registry.oracle.com/database/express:latest

Wait for the database to start (this can take a few minutes), then connect:

bash

faucet db add \
  --name localdb \
  --driver oracle \
  --dsn "oracle://system:YourStrong!Passw0rd@localhost:1521/XEPDB1"

Troubleshooting

"ORA-01017: invalid username/password"

Check your username and password. Oracle passwords are case-sensitive. Ensure the user has appropriate permissions (CONNECT and SELECT privileges at minimum).

"ORA-12514: TNS:listener does not currently know of service requested"

The service name in your DSN doesn't match any registered service. Verify the service name with:

sql

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'service_names';

"ORA-12541: TNS:no listener"

Oracle's listener isn't running on the specified host/port. Check that the Oracle listener is started and listening on port 1521 (default).

"connection refused"

Oracle isn't running, the port is wrong, or a firewall is blocking the connection. Verify the host, port, and network connectivity.

Password with special characters

If your password contains @, #, %, or other URL-special characters, URL-encode them in the DSN:

bash

# Password: Pa@ss#1 → Pa%40ss%231
oracle://user:Pa%40ss%231@localhost:1521/XEPDB1

What's Next

Filter Syntax -- query filtering operators
RBAC -- restrict access per role
API Reference -- full REST API documentation
Database Connectors -- all connector DSN formats

Tutorial: Oracle ​

Prerequisites ​

Step 1: Connect Oracle to Faucet ​

Via CLI ​

Via API ​

Via Admin UI ​

Step 2: Verify the Connection ​

Step 3: Query Your Data ​

List all records ​

Filter records ​

Select specific fields ​

Paginate ​

Count records ​

Step 4: Insert, Update, and Delete ​

Insert a record ​

Update a record ​

Delete a record ​

Oracle-Specific Notes ​

Schema (Owner) ​

Service name vs. SID ​

Stored procedures ​

Pagination ​

Supported features ​

Docker for local development ​

Troubleshooting ​

"ORA-01017: invalid username/password" ​

"ORA-12514: TNS:listener does not currently know of service requested" ​

"ORA-12541: TNS:no listener" ​

"connection refused" ​

Password with special characters ​

What's Next ​

Tutorial: Oracle

Prerequisites

Step 1: Connect Oracle to Faucet

Via CLI

Via API

Via Admin UI

Step 2: Verify the Connection

Step 3: Query Your Data

List all records

Filter records

Select specific fields

Paginate

Count records

Step 4: Insert, Update, and Delete

Insert a record

Update a record

Delete a record

Oracle-Specific Notes

Schema (Owner)

Service name vs. SID

Stored procedures

Pagination

Supported features

Docker for local development

Troubleshooting

"ORA-01017: invalid username/password"

"ORA-12514: TNS:listener does not currently know of service requested"

"ORA-12541: TNS:no listener"

"connection refused"

Password with special characters

What's Next