Oracle Database

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

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:

-- 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:

-- 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