Prerequisites

Oracle Database - Oracle Database 12c or higher with your enterprise data

Database Access - Oracle user account with read permissions and connect privileges

Network Access - Database accessible through TNS listener or direct connection

Oracle Client - Oracle client libraries for secure connectivity

Connection Methods

Choose the connection method that fits your Oracle infrastructure.

TNS Connection

Connect using TNS names and Oracle Net Services configuration.

Direct Connection

Direct connection using host, port, and service name.

Oracle Cloud

Connect to Oracle Autonomous Database or Oracle Cloud Infrastructure.

Connection Guide

Step 1: Access Oracle Integration

Navigate to Integrations in Datapad and select Oracle Database:

Oracle connect screen on Datapad UI

Step 2: Enter Connection Details

Fill in your Oracle connection information:

Oracle connection form

Required Fields:

Host/Server Name
Port (default: 1521)
Service Name or SID
Username
Password
Connection Type (TNS/Direct)

For Oracle Cloud Autonomous Database, use the connection string from your cloud console.

Step 3: Configure Advanced Settings

Set Oracle-specific connection options:

Oracle advanced configuration

Advanced Options:

SSL/TLS Encryption
Connection Pool Settings
Query Timeout
Schema Selection

Create Read-Only User (Recommended)

For security, create a dedicated user with read-only permissions:

-- Create a new user for Datapad
CREATE USER datapad_readonly IDENTIFIED BY "SecurePassword123!";

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

-- Grant read access to specific schemas
GRANT SELECT ON your_schema.your_table TO datapad_readonly;
-- Or grant read access to all tables in a schema
GRANT SELECT ANY TABLE TO datapad_readonly;

-- Grant access to system views for metadata
GRANT SELECT ON DBA_TABLES TO datapad_readonly;
GRANT SELECT ON DBA_TAB_COLUMNS TO datapad_readonly;
GRANT SELECT ON DBA_CONSTRAINTS TO datapad_readonly;

-- For Oracle 12c+ container databases
ALTER USER datapad_readonly SET CONTAINER_DATA=ALL;

Replace schema and table names with your actual objects. Use strong passwords that meet Oracle security requirements and your organization's policies.

Example Queries

Here are some example questions you can ask once your Oracle data is connected:

"Show me the top revenue-generating products across all business units"

"What's our quarterly financial performance by region and division?"

"How many transactions are processed per hour during peak times?"

"Which customers have the highest lifetime value in the enterprise segment?"

"Show me supply chain efficiency metrics by supplier and location"

"What's the trend in operational costs over the last fiscal year?"

💬 Oracle Query Tips

Include schema names for precise targeting in complex enterprise environments

Use Oracle-specific analytical functions for advanced calculations

Ask about hierarchical queries and recursive data structures

Request performance analysis using Oracle execution plans

Behind the Scenes

Datapad connects to your Oracle database using Oracle's native connectivity protocols and generates optimized Oracle SQL that leverages advanced features like analytical functions, hierarchical queries, and Oracle-specific performance optimizations. Our AI understands Oracle's enterprise features and generates queries that take advantage of Oracle's powerful analytical capabilities.

Troubleshooting

Connection failed

If Oracle connection fails:

Verify the Oracle listener is running and accepting connections
Check that the TNS names configuration is correct
Ensure the Oracle client libraries are properly installed
Test connectivity using SQL*Plus or Oracle SQL Developer

TNS resolution errors

If TNS name resolution fails:

Verify the tnsnames.ora file contains the correct service definition
Check that the ORACLE_HOME and TNS_ADMIN environment variables are set
Ensure the Oracle Net Services are properly configured
Try using a direct connection instead of TNS names

Authentication failed

If authentication fails:

Verify the username and password are correct
Check if the user account is locked or expired
Ensure the user has CREATE SESSION privilege
Try connecting with Oracle SQL Developer using the same credentials

Performance issues

If queries are running slowly:

Check Oracle execution plans for query optimization opportunities
Verify that proper indexes exist on frequently queried columns
Consider using Oracle hints for query optimization
Monitor Oracle AWR reports for system performance analysis

Privilege errors

If you encounter privilege errors:

Verify the user has SELECT permissions on target tables and views
Check that system view access is granted for metadata queries
Ensure proper role assignments for read-only access
Contact your Oracle DBA for additional privilege grants if needed

Oracle Database Integration