Connect Oracle Database to Plotly Studio

Step-by-step Oracle Database data connection setup

Oracle Database is a multi-model database management system known for its scalability, security, and enterprise features. It's a leading choice for mission-critical applications requiring high performance and reliability, widely deployed across industries for transaction processing and data warehousing.

Connecting to Oracle Database requires your host address, port, service name, and login credentials. Oracle environments vary significantly across organizations, so confirming your service name and network access with your DBA before starting is worthwhile. Follow these steps to connect to your Oracle data and visualize it with Plotly Studio:

Step 1: Confirm your Oracle instance is running

Oracle Database can be running locally (including via Docker), on a corporate on-prem server, or as a managed cloud service through Oracle Cloud Infrastructure (OCI), AWS RDS for Oracle, or similar. For local development, a quick way to spin up an instance is:

docker run -p 1521:1521 --name oracle-free \
  -e ORACLE_PWD=mysecret \
  container-registry.oracle.com/database/free:latest

Confirm your listener is active and accepting connections on the expected port before connecting from Plotly Studio.

Step 2: Identify your service name

Oracle uses a Service Name (not a database name in the traditional sense) to identify which database instance to connect to within a running Oracle server. This is different from older Oracle setups that used a SID. The python-oracledb driver requires a Service Name and does not support the legacy SID format via Easy Connect syntax.

To find your Service Name, run this query in SQL*Plus or any Oracle SQL client:

SELECT value FROM v$parameter WHERE name = 'service_names';

Common default service names for local/Docker installs include FREEPDB1, XEPDB1, or ORCLPDB.

Step 3: Gather your connection details

Have these values ready before opening Plotly Studio. Your DBA or cloud console can provide them.

Credentials needed

• Host: IP address or hostname of the Oracle server (e.g., localhost or db.example.com)
• Port: typically 1521 (default Oracle listener port)
• Service Name: the Oracle service name for your database instance (e.g., FREEPDB1, ORCLPDB)
• Username: your Oracle database login
• Password: your Oracle database password

Tip: contact us if you need help troubleshooting these steps.

LLM prompts for connecting to Oracle Database

Plotly Studio uses an AI agent to generate and execute the data connection code for you. The prompts below are ready to copy and paste directly into Plotly Studio's data connection chat. Use them to establish a connection, query your data, or do both in one shot. The global context rules are worth saving to your Plotly Studio global context to keep Oracle connections consistent across projects.

Connection prompt

Connect to Oracle Database using the python-oracledb library (the Oracle-
preferred Python driver). Use oracledb.connect() with the provided username,
password, host, port, and service_name parameters. Run in Thin mode (no
Oracle Client libraries required). Upon successful connection, list all
available tables for the connected user by querying the ALL_TABLES or
USER_TABLES data dictionary view.

Query prompt

Using the established Oracle connection, retrieve all rows from the table
[YOUR_TABLE_NAME] owned by [YOUR_SCHEMA_OR_USER]. Execute the query using a
cursor and return results as a pandas DataFrame. Use Oracle SQL syntax only.
Reference the table with its fully qualified name [SCHEMA].[TABLE_NAME] if
accessing tables outside the connected user's own schema.

Example one-shot prompt

Connect to Oracle Database using python-oracledb in Thin mode with the
following credentials:

Host: [YOUR_HOST_OR_IP]
Port: [YOUR_PORT] (default: 1521)
Service Name: [YOUR_SERVICE_NAME] (e.g., FREEPDB1 or ORCLPDB)
Username: [YOUR_USERNAME]
Password: [YOUR_PASSWORD]

Use oracledb.connect(user, password, host, port, service_name) to establish
the connection. Once connected, retrieve all rows from the table
[YOUR_SCHEMA].[YOUR_TABLE_NAME] and return the result as a pandas DataFrame.
Display a preview of the data.

Global context rules

- Always use python-oracledb (import oracledb) as the Oracle connection
library. Do not use the legacy cx_Oracle package.
- Always connect using oracledb.connect() with explicit user, password,
host, port, and service_name parameters.
- Always run in Thin mode (default). Do not call
oracledb.init_oracle_client() unless Thick mode is explicitly required.
- Use Service Name for connections. Do not use SID in Easy Connect syntax,
as it is not supported by python-oracledb in this format.
- The Easy Connect DSN format is: host:port/service_name (e.g.,
localhost:1521/FREEPDB1).
- Always use Oracle SQL syntax. Do not use MySQL, T-SQL, or PostgreSQL-
specific syntax.
- Always use fully qualified table names in the format SCHEMA.TABLE when
accessing objects outside the connected user's own schema.
- To enumerate available tables, query USER_TABLES for the connected user's
own tables, or ALL_TABLES for all accessible tables.
- Always return query results as a pandas DataFrame. Use pandas.read_sql()
with the connection object, or cursor.fetchall() with manual DataFrame
construction.
- Default to adding a FETCH FIRST [N] ROWS ONLY clause (Oracle 12c+) or a
ROWNUM <= N filter (older Oracle) when querying large tables, unless the
user explicitly requests a full pull.
- Always close the cursor and connection after data retrieval.
- Do not log or expose raw password values in any output or error messages.

Troubleshooting and tips

• Service Name vs. SID: The python-oracledb driver uses Service Name, not SID. If your organization still uses SIDs, you'll need to use a full TNS connect descriptor or ask your DBA to provide the equivalent Service Name. Using a bare SID in the Easy Connect string format will not work.
• Thin vs. Thick mode: python-oracledb defaults to Thin mode, which connects directly to Oracle without needing Oracle Client libraries installed. This is the recommended approach for Plotly Studio and works for the vast majority of use cases. Thick mode (which requires Oracle Instant Client) is only needed for advanced features like Oracle Wallet, proxy authentication, or legacy OCI APIs.
• Default port: Oracle's standard listener port is 1521. Non-standard ports are common in enterprise environments — always confirm with your DBA.
• Table name casing: Oracle stores object names in uppercase by default. If you reference a table by name and get an error, try the name in all caps (e.g., SNOTEL_WEATHER_DATA). Oracle is case-sensitive with object names when they were created with quotes.
• Cloud deployments: The connection flow is identical whether pointing at localhost (including Docker) or a cloud-managed instance on OCI, AWS RDS, or Azure. For Oracle Autonomous Database on OCI, connection requires a Wallet file rather than a simple host/port/service name — work with your cloud admin to download the wallet and use wallet-based authentication.
• Permissions: If you connect and fewer tables appear than expected, it's likely a permissions issue. Run SELECT table_name FROM ALL_TABLES WHERE owner = '[SCHEMA]' to check what's accessible to your user. Your DBA can grant additional SELECT privileges as needed.