# Oracle Database

Connect to Oracle Database for enterprise analytics

Oracle Database is a leading enterprise relational database management system known for scalability, reliability, and advanced features. Scoop connects directly to Oracle, enabling AI-powered analysis on your enterprise data.

## Connection Details

| Setting | Value |
|---------|-------|
| Default Port | 1521 |
| Connection Type | TCP/IP (TNS) |
| SSL | Optional |

## Prerequisites

Before connecting Scoop to Oracle:

1. **Oracle Database** instance (11g or later)
2. **Read-only user** with SELECT permissions
3. **Network access** from Scoop's IP addresses

## Create a Read-Only User

Connect to your Oracle database as a DBA and run:

```sql
-- Create the read-only user
CREATE USER scoop_user IDENTIFIED BY "your_secure_password";

-- Grant basic connection privileges
GRANT CREATE SESSION TO scoop_user;

-- Grant SELECT on specific tables
GRANT SELECT ON schema_name.table_name TO scoop_user;

-- Or grant SELECT on all tables in a schema
-- First, generate GRANT statements:
SELECT 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO scoop_user;'
FROM all_tables
WHERE owner = 'YOUR_SCHEMA';

-- Then execute the generated statements
```

For read access to all tables in a schema, you can also create a role:

```sql
-- Create a read-only role
CREATE ROLE scoop_readonly;

-- Grant SELECT on all tables (run as schema owner)
BEGIN
  FOR t IN (SELECT table_name FROM user_tables) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO scoop_readonly';
  END LOOP;
END;
/

-- Grant the role to the user
GRANT scoop_readonly TO scoop_user;
```

## Whitelist Scoop IP Addresses

Configure your firewall to allow incoming connections on port 1521 from Scoop's IP addresses.

For **Oracle Cloud (OCI)**:
1. Go to your Autonomous Database
2. Navigate to **Network** settings
3. Add an **Access Control Rule** for Scoop's IP addresses

Contact Scoop support for the current list of IP addresses to whitelist.

## Connect in Scoop

1. Navigate to **Datasets** and click **New Dataset**
2. Select **Database** as your source
3. Choose **Oracle** from the database type dropdown
4. Enter your connection details:
   - **Server Host**: Your database hostname or IP
   - **Port**: 1521 (or your custom port)
   - **Database**: Your service name or SID
   - **Username**: scoop_user
   - **Password**: The user's password
5. Click **Test Connection** to verify
6. Enter your SQL query and proceed

## Connection URL Format

If using a JDBC URL directly:

Using service name:
```
jdbc:oracle:thin:@//hostname:1521/service_name
```

Using SID:
```
jdbc:oracle:thin:@hostname:1521:SID
```

Using TNS descriptor:
```
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service_name)))
```

## Best Practices

- **Use bind variables** in queries to leverage Oracle's query cache
- **Create indexes** on frequently filtered columns
- **Use EXPLAIN PLAN** to analyze query performance
- **Consider partitioning** for large tables

## Troubleshooting

### ORA-12541: TNS:no listener
- Verify the hostname and port are correct
- Check that the Oracle listener is running
- Ensure the port is open in your firewall

### ORA-01017: Invalid username/password
- Verify the username and password are correct
- Check if the password is case-sensitive (Oracle 11g+)
- Ensure the user account is not locked

### ORA-00942: Table or view does not exist
- Verify the table name is spelled correctly (Oracle uses uppercase by default)
- Check that SELECT was granted on the table
- Use fully qualified names: `SCHEMA.TABLE_NAME`

### ORA-01031: Insufficient privileges
- Verify the user has CREATE SESSION privilege
- Check that SELECT was granted on required tables
- Ensure any required roles are granted