# Oracle to REST API and MCP Server

Source: https://wiki.faucetdb.ai/tutorial-oracle

**How do I turn an Oracle database into a REST API?** Add the database to Faucet, create an API key, and start the server. Every table is then available at `/api/v1/mydb/_table/<table>` with filters, sorting and pagination, described by an OpenAPI 3.1 spec at `/openapi.json`. AI agents can use the same database through the MCP endpoint at `/mcp`, limited by the same API key's role.

```bash
faucet db add --name mydb --driver oracle \
  --host localhost --user appuser --password-prompt --database FREEPDB1
faucet role create --name reader --verbs GET
faucet key create --role reader          # prints the key once
faucet serve

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?limit=10" \
  -H "X-API-Key: faucet_YOUR_KEY"
```

Prefer a browser? The [admin UI](https://wiki.faucetdb.ai/admin-ui#connect-a-database) does the same with a form and a **Test connection** button. To give Claude, Cursor or another AI agent access, see [Give AI Agents Access](https://wiki.faucetdb.ai/tutorial-oracle#give-ai-agents-access-mcp).

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](https://wiki.faucetdb.ai/getting-started)
- An admin account created (`faucet admin create`)
- An Oracle Database instance with at least one table
- Supported versions: Oracle 12c through 26ai, including Oracle Cloud (OCI), Amazon RDS and Azure
- 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 Admin UI (easiest)

1. Open `http://localhost:8080` and sign in.
2. Go to **Databases** and click **Add database**.
3. Pick **Oracle** and fill in:
   - **Host** and **Port**: for example `localhost` and `1521` (the default; Autonomous Database uses `1522`)
   - **Service name**: for example `FREEPDB1` or `XEPDB1`
   - **Username** and **Password**: special characters need no escaping
   - **TLS**: turn **On** for Oracle Autonomous Database (TCPS)

   To expose another user's tables, open **Access and schema options** and set **Schema** to the owner name.
4. Check the **API name**. It is filled in from the database name and becomes the URL prefix (`/api/v1/<name>`).
5. Click **Test connection**. Faucet connects and reports how many tables it found, or explains what went wrong.
6. Click **Add database**. The REST API and MCP tools are live right away.

See [Admin UI](https://wiki.faucetdb.ai/admin-ui#connect-a-database) for screenshots of this flow.

### Via CLI

With separate connection flags (Faucet builds and escapes the connection string):

```bash
faucet db add --name mydb --driver oracle \
  --host localhost --port 1521 --user appuser --password 's3cret@!' --database FREEPDB1
```

Or with a connection string:

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

::: tip
A server that is already running loads databases added with `faucet db add` on its next start (`faucet stop && faucet serve`). Databases added in the admin UI or through the API are live immediately.
:::

### Via API

```bash
TOKEN=$(curl -s http://localhost:8080/api/v1/system/admin/session \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@example.com","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"
  }'
```

## 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"
```

## Give AI Agents Access (MCP)

`faucet serve` also runs an MCP server at `http://localhost:8080/mcp`. Connect an agent with an API key, and the key's [role](https://wiki.faucetdb.ai/rbac) decides which tables it can read or change. For Claude Code:

```bash
claude mcp add --transport http faucet http://localhost:8080/mcp \
  --header 'X-API-Key: faucet_YOUR_KEY'
```

The agent can then call `faucet_list_tables`, `faucet_describe_table` and `faucet_query` on `mydb`. For Cursor, VS Code, Windsurf, Claude Desktop and ChatGPT, see [MCP Server → Connect an AI agent](https://wiki.faucetdb.ai/mcp-server#connect-claude-code). Use a read-only role for agents unless they need to write.

## 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](https://wiki.faucetdb.ai/filter-syntax) -- query filtering operators
- [RBAC](https://wiki.faucetdb.ai/rbac) -- restrict access per role
- [API Reference](https://wiki.faucetdb.ai/api-reference) -- full REST API documentation
- [Database Connectors](https://wiki.faucetdb.ai/database-connectors) -- all connector DSN formats