# How to Integrate Retool with Oracle Database

- Tool: Retool
- Difficulty: Advanced
- Time required: 30 minutes
- Last updated: April 2026

## TL;DR

Connect Retool to Oracle Database by adding an Oracle Resource in Retool's Resources tab and configuring the host, port, SID or service name, and Oracle credentials. For Oracle Cloud Autonomous Databases, upload your wallet ZIP for mTLS authentication. Once connected, write SQL and PL/SQL queries in the query editor to build admin dashboards, reports, and data management tools on top of your Oracle estate.

## Build Internal Tools on Oracle Database with Retool

Enterprise organizations with Oracle Database at the core of their operations frequently need internal tools that go beyond what Oracle APEX or SQL Developer offer: custom admin panels for operations teams, data correction workflows for support staff, and executive dashboards combining Oracle data with SaaS metrics. Building these tools traditionally requires dedicated application developers writing Oracle Forms or custom web apps — a process that can take months. Retool reduces this to hours by providing a direct Oracle connector with a drag-and-drop UI builder.

With a Retool–Oracle integration, enterprise teams can build paginated admin panels over large Oracle tables, expose complex Oracle views as read-only reports for non-technical users, and provide safe write-back forms for data correction workflows — with Retool's GUI mode preventing raw SQL injection into production. PL/SQL stored procedures can be called directly from Retool queries, allowing teams to reuse existing Oracle business logic without rewriting it as application code.

Oracle's enterprise deployment realities — firewalled on-premise servers, Oracle Cloud Autonomous Databases behind wallet-based mTLS, and multi-schema environments — are all handled by Retool's Oracle Resource configuration. Self-hosted Retool deployments running inside the same VPC as Oracle eliminate the need for IP whitelisting entirely, making this the preferred architecture for security-conscious enterprise teams.

## Before you start

- An Oracle Database instance accessible from Retool: either a publicly reachable host (for Retool Cloud with IP whitelisting) or a host within the same VPC (for self-hosted Retool deployments)
- Oracle connection details: hostname or IP, port (default 1521), and either the SID (legacy Oracle systems), service name (recommended for Oracle 12c+), or a full TNS connection string
- A database user account with appropriate SELECT/INSERT/UPDATE/DELETE privileges on the schemas you intend to query from Retool
- For Oracle Cloud Autonomous Database: the wallet ZIP file downloaded from the Oracle Cloud Console (under your Autonomous Database instance → DB Connection)
- If using Retool Cloud: Oracle's firewall or security list must whitelist Retool's published IP ranges (35.90.103.132/30 and 44.208.168.68/30 for us-west-2)

## Step-by-step guide

### 1. Configure the Oracle Database Resource in Retool

Navigate to the Resources tab in Retool's left navigation. Click Add Resource. In the resource type picker, scroll to the Databases section and click Oracle.

The Oracle resource configuration form presents several connection options. Choose the approach that matches your Oracle deployment:

**Option A — Standard connection (SID or service name):**
- Host: Enter the hostname or IP address of your Oracle Database server.
- Port: Enter 1521 (default) or the port your Oracle listener is configured on.
- SID or Service Name: Toggle the radio button to 'Service Name' (recommended for Oracle 12c and later) or 'SID' for older installations. Enter the appropriate value — for example, service name 'ORCL' or SID 'ORCL'. For Oracle 19c+ with multitenant architecture, use the PDB service name rather than the CDB SID.
- Username and Password: Enter the Oracle user credentials. Avoid connecting as SYSDBA from Retool; create a dedicated application user with scoped privileges.

**Option B — TNS connection string:**
- Enable the 'Use TNS connection string' toggle.
- Paste the full TNS descriptor, for example: `(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))`. This format is required for RAC configurations and SCAN addresses.

**Option C — Oracle Cloud Autonomous Database (wallet):**
- Enable 'Use SSL/mTLS wallet'. Upload the wallet ZIP file downloaded from Oracle Cloud Console. Retool extracts the wallet credentials automatically. Enter only the database username and password.

In the 'Resource name' field, enter a descriptive name such as 'Oracle ERP Production' or 'Oracle DW Staging'. Click Save Changes.

**Expected result:** The Oracle resource appears in the Resources tab with a green Connected indicator. If connection fails, Retool displays the Oracle error code (ORA-XXXXX) which indicates whether it is a credentials issue (ORA-01017), host unreachable, or TNS resolution failure.

### 2. Write your first SQL query against Oracle

Open your Retool app and click + New query in the Code panel. Select your Oracle Resource from the dropdown. Retool's query editor switches to SQL mode by default for database resources.

Write a basic Oracle SQL query to verify connectivity and understand the schema. Oracle uses slightly different SQL syntax than PostgreSQL or MySQL — pay attention to these Oracle-specific behaviors:

1. **ROWNUM vs. FETCH FIRST**: Oracle uses `WHERE ROWNUM <= 100` (older syntax) or `FETCH FIRST 100 ROWS ONLY` (Oracle 12c+) for limiting results. Avoid using `LIMIT` — it does not exist in Oracle SQL.

2. **Dual table**: `SELECT SYSDATE FROM DUAL` is the Oracle equivalent of `SELECT NOW()`. Use it to test that queries execute.

3. **Schema prefix**: In multi-schema environments, prefix table names with the schema owner: `SELECT * FROM SCOTT.EMP`. The user you connected with may not own the tables — check DBA_TABLES for ownership.

4. **Date format**: Oracle stores dates with time components by default. Use `TO_DATE('2024-01-01', 'YYYY-MM-DD')` for literal dates, and `TO_CHAR(date_col, 'YYYY-MM-DD HH24:MI:SS')` to format dates for display.

BindVariables use Retool's `{{ }}` syntax and are converted to Oracle bind variable placeholders (`:param`) automatically by Retool's query engine — this prevents SQL injection via Oracle's prepared statement mechanism.

Name the query 'getOrders' and enter a query appropriate to your schema. Click Run to test it.

```
-- Oracle SQL query with pagination using OFFSET/FETCH (Oracle 12c+)
SELECT
  o.order_id,
  o.order_date,
  c.customer_name,
  o.order_total,
  o.status,
  TO_CHAR(o.created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_formatted
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= TO_DATE('{{ dateFilter.startDate }}', 'YYYY-MM-DD')
  AND o.status = {{ statusSelect.value === 'ALL' ? "'%'" : "'" + statusSelect.value + "'" }}
ORDER BY o.order_date DESC
OFFSET {{ (pagination.page - 1) * pagination.pageSize }} ROWS
FETCH FIRST {{ pagination.pageSize }} ROWS ONLY
```

**Expected result:** The query returns rows from your Oracle table in the query result panel. The data appears as an array of objects in the query's .data property, ready to be bound to Retool components.

### 3. Handle Oracle-specific data types in transformers

Oracle data types require careful handling when displaying data in Retool components. Several Oracle types do not map cleanly to JavaScript values and need transformer functions to convert them into displayable strings.

**Oracle DATE and TIMESTAMP**: Oracle's DATE type includes a time component (unlike SQL Server's DATE which is date-only). Retool receives these as JavaScript Date objects or ISO strings. Format them consistently using a query transformer.

**Oracle NUMBER**: Large Oracle NUMBER values (BIGINT equivalents) are sometimes returned as strings by the Oracle JDBC driver to prevent precision loss. If a column contains numeric strings rather than JavaScript numbers, parse them explicitly in a transformer.

**Oracle CLOB/BLOB**: Character Large Objects (CLOB) containing document text may be returned as partial strings if they exceed the driver's buffer. For CLOBs you need to display, use `DBMS_LOB.SUBSTR(clob_column, 4000, 1)` in your SQL to retrieve the first 4,000 characters, which is the Oracle VARCHAR2 maximum and fits within Retool's field display.

**Oracle NULL behavior**: Oracle treats empty strings ('') as NULL — unlike most databases. If users submit empty form fields to Oracle, they may unintentionally set columns to NULL. Add a transformer check: `value === '' ? null : value`.

Create a transformer attached to your main Oracle query that standardizes data types for display in the Table component.

```
// Transformer: normalize Oracle query results for Retool display
const rows = data;
return rows.map(row => ({
  ...row,
  // Format Oracle DATE columns
  order_date: row.order_date
    ? new Date(row.order_date).toLocaleDateString('en-US')
    : 'N/A',
  // Parse Oracle NUMBER returned as string
  order_total: row.order_total
    ? parseFloat(String(row.order_total)).toFixed(2)
    : '0.00',
  // Truncate CLOB preview (full CLOB handled by DBMS_LOB.SUBSTR in SQL)
  notes_preview: row.notes
    ? String(row.notes).substring(0, 200) + (String(row.notes).length > 200 ? '...' : '')
    : '',
  // Oracle empty string = NULL normalization
  customer_name: row.customer_name || 'Unknown'
}));
```

**Expected result:** The Table component shows correctly formatted dates, properly parsed numbers, and truncated CLOB previews. Oracle NULL values display as 'N/A' or empty strings rather than JavaScript null values that would break Table sorting.

### 4. Execute PL/SQL stored procedures and anonymous blocks

One of Oracle's most powerful features is PL/SQL — procedural SQL that supports variables, conditionals, loops, and stored procedures. Retool's Oracle Resource supports PL/SQL execution in the query editor, opening up Oracle's existing stored procedure library as callable functions in your Retool app.

To call a stored procedure, create a new query and write a PL/SQL anonymous block:

```plsql
BEGIN
  schema_name.procedure_name(
    p_customer_id => {{ selectedCustomerId.value }},
    p_new_status => '{{ statusSelect.value }}',
    p_updated_by => '{{ current_user.email }}'
  );
  COMMIT;
END;
```

For procedures with OUT parameters (return values), use a PL/SQL block that binds the output to a variable and selects it:

```plsql
DECLARE
  v_result VARCHAR2(200);
BEGIN
  pkg_orders.calculate_discount(
    p_order_id => {{ selectedOrderId }},
    p_result => v_result
  );
  -- Retool cannot directly capture OUT params; write to a temp context
  -- or use a function that RETURNS a value instead
END;
```

For Oracle functions that RETURN a value (preferred over procedures for queries), use a SELECT wrapper:

```sql
SELECT pkg_orders.get_customer_credit_limit({{ customerId }}) AS credit_limit
FROM DUAL
```

Set 'Run query on page load' to Off for all write procedures. Wire them to form Submit buttons with confirmation modals. For DDL operations (ALTER TABLE, CREATE INDEX) — which require COMMIT to be unnecessary — Retool will execute them but warn that they cannot be rolled back.

For complex Oracle integrations involving multiple PL/SQL packages, cross-schema joins, and Oracle-specific performance tuning, RapidDev's team can help architect and build enterprise Retool solutions.

```
-- Call Oracle PL/SQL stored procedure with parameters
BEGIN
  pkg_customer_mgmt.update_customer_status(
    p_customer_id => {{ table1.selectedRow.customer_id }},
    p_new_status  => '{{ statusSelect.value }}',
    p_reason      => '{{ reasonInput.value }}',
    p_updated_by  => '{{ current_user.email }}',
    p_updated_at  => SYSDATE
  );
  COMMIT;
END;
```

**Expected result:** The PL/SQL block executes without error and the 'On success' event handler triggers. For procedures that modify data, the source table query refreshes and shows the updated values.

### 5. Build a paginated Oracle admin table with safe write-back

Large Oracle tables (millions of rows) require careful pagination to avoid query timeouts and memory issues in Retool. Build a properly paginated admin panel using Retool's pagination components alongside Oracle's OFFSET/FETCH syntax.

On the canvas, drag a Table component and set its Data to `{{ getOrders.data }}`. In the Table settings, enable 'Server-side pagination' — this deactivates client-side pagination and shows Retool's built-in pagination control at the bottom of the table.

Create a pagination variable by adding a state variable in Retool: in the State panel, create a Number variable named 'currentPage' with default value 1. Update your Oracle query to use `{{ currentPage.value }}` in the OFFSET calculation.

Wire the Table's 'Page change' event handler to update the currentPage variable: `currentPage.setValue(newPage)`. The table re-queries automatically when currentPage changes.

For write operations, use Retool's GUI mode (not raw SQL) to prevent destructive operations:
- Click the query's 'Mode' dropdown and switch from 'SQL' to 'GUI'.
- GUI mode presents a form interface for INSERT, UPDATE, and DELETE operations.
- Retool validates GUI mode operations and prevents table-wide UPDATE or DELETE without a WHERE clause.

Add a Form component wired to a GUI-mode UPDATE query for the selected table row. Restrict the Form to show only the columns ops staff should edit — not all columns. Add a 'Required changes reason' text input that inserts into an audit_log table in the on-success handler.

For count-based pagination (showing 'Page 1 of 483'), run a separate COUNT query: `SELECT COUNT(*) AS total FROM orders WHERE status = '{{ statusSelect.value }}'` and display the result: `Page {{ currentPage.value }} of {{ Math.ceil(getOrderCount.data[0].total / 50) }}`.

```
-- Oracle paginated query with total row count
-- Main data query
SELECT
  order_id,
  customer_id,
  order_date,
  order_total,
  status,
  TO_CHAR(order_date, 'DD-MON-YYYY') AS order_date_display
FROM orders
WHERE status LIKE {{ statusSelect.value === 'ALL' ? "'%'" : "'" + statusSelect.value + "'" }}
ORDER BY order_id DESC
OFFSET {{ (currentPage.value - 1) * 50 }} ROWS
FETCH FIRST 50 ROWS ONLY;

-- Count query (separate query named getOrderCount)
SELECT COUNT(*) AS total
FROM orders
WHERE status LIKE {{ statusSelect.value === 'ALL' ? "'%'" : "'" + statusSelect.value + "'" }};
```

**Expected result:** The admin table loads the first 50 rows efficiently. Navigating pages re-queries Oracle with the correct OFFSET. The page counter shows the correct total page count. The edit form submits changes via GUI mode with a reason field, and the table refreshes on success.

## Best practices

- Create a dedicated Oracle database user for Retool with only the privileges required for your specific use case (SELECT on reporting schemas, SELECT/INSERT/UPDATE on operational schemas). Never connect Retool as SYSDBA, SYS, or SYSTEM — these accounts have unrestricted access to all Oracle objects and metadata.
- Use Oracle service names rather than SIDs for connection configuration in Retool. SIDs are deprecated in Oracle 12c+ and do not support Oracle Real Application Clusters (RAC) or Data Guard standby failover. Service names provide connection-time load balancing and failover automatically.
- Store Oracle credentials in Retool Configuration Variables (Settings → Configuration Variables) marked as secret. Reference them as `{{ environment.variables.ORACLE_PASSWORD }}` in the resource configuration rather than hardcoding passwords in the resource settings, where they are visible to other Retool administrators.
- Use Retool's GUI mode for write queries (INSERT, UPDATE, DELETE) against production Oracle tables. GUI mode enforces a WHERE clause requirement and validates the operation before execution — preventing full-table updates that are easy to trigger accidentally in SQL mode.
- Always add FETCH FIRST N ROWS ONLY or ROWNUM constraints to Oracle queries in Retool. Without row limits, SELECT queries on large Oracle tables can return millions of rows, consuming Retool's 100MB query result size limit and causing timeout or memory errors.
- Run performance-critical queries through Oracle views or materialized views rather than writing complex joins directly in Retool query editor. This offloads query optimization to Oracle DBAs, keeps Retool SQL readable, and allows DBAs to add indexes or rewrite logic without touching Retool apps.
- Use Oracle's prepared statement (bind variable) mechanism, which Retool enables by default, to share execution plans across similar queries with different parameter values. This reduces Oracle's hard parse overhead and improves performance for high-frequency Retool queries.
- For self-hosted Retool connecting to on-premise Oracle, use SSH tunneling or VPC peering rather than opening Oracle's port 1521 to the internet. Oracle Database should never be directly internet-accessible — all Retool Oracle connections should traverse a private network path.

## Use cases

### Build an Oracle data correction and audit panel for operations teams

Create a Retool app that lets operations staff search Oracle tables for records matching certain criteria, view the details in a form, make controlled edits via Retool's GUI mode (which prevents destructive SQL), and submit changes with an audit log entry. The panel restricts which columns can be edited, shows before/after values in a change summary, and requires a reason field before committing updates.

Prompt example:

```
Build a Retool Oracle admin panel with a search Text Input querying an Oracle customer table, a Table showing matching records, a Form for editing the selected record (name, status, and tier fields only), a required reason Text Area, and a Save button that updates via GUI mode and logs the change to an audit_log table.
```

### Build an Oracle ERP report dashboard combining multiple schemas

Create a Retool dashboard that queries Oracle ERP tables across multiple schemas — for example, joining order management (OE schema), accounts receivable (AR schema), and inventory (INV schema) data — and displays the combined data in Charts and Tables. Finance managers can filter by date range, business unit, and product category, and export the results. The dashboard replicates reports that previously required Oracle Reports or custom OBIEE configurations.

Prompt example:

```
Build a Retool ERP dashboard with a date range picker, a business unit Select component, a Table showing open receivables joining AR and OE schema tables, a bar Chart showing monthly revenue by product category from INV and OE schemas, and a CSV export button.
```

### Expose Oracle stored procedures as a self-service ops tool

Create a Retool interface that allows operations staff to trigger Oracle PL/SQL stored procedures — such as batch recalculation jobs, data migration procedures, or end-of-day processing — through a controlled UI with input forms, execution confirmation, and output display. This replaces ad-hoc SQL Developer sessions and DBA assistance for routine procedures that ops teams run regularly.

Prompt example:

```
Build a Retool tool for running Oracle stored procedures: a Select component for choosing the procedure, dynamically rendered input fields for procedure parameters, a Confirm and Run button, a result display showing the procedure's output parameters, and a log table recording who ran what and when.
```

## Troubleshooting

### Oracle Resource shows 'ORA-12541: TNS:no listener' or 'Connection refused'

Cause: Retool cannot reach the Oracle listener on the configured host and port. This is either a network connectivity issue (firewall blocking port 1521, wrong host/port), the Oracle listener is not running, or for Retool Cloud deployments, Retool's IP ranges have not been whitelisted in the Oracle server's firewall.

Solution: Verify the Oracle listener is running on the server with `lsnrctl status` (run by a DBA). Confirm the host and port values in Retool match exactly. For Retool Cloud, whitelist Retool's published CIDR ranges in your Oracle server's network access rules or security group. For private Oracle instances, use Retool's SSH Tunnel option — configure a bastion host in the resource settings that can reach Oracle on the internal network.

### Queries fail with 'ORA-01017: invalid username/password; logon denied'

Cause: The Oracle username or password entered in the Retool resource is incorrect, the user account has been locked (after failed login attempts), or the password contains special characters that were not properly escaped.

Solution: Confirm credentials by connecting directly to Oracle using SQL Developer or sqlplus with the same username and password. If the account is locked, a DBA must run `ALTER USER username ACCOUNT UNLOCK;`. For passwords containing special characters (@ # $ etc.), ensure the password is entered verbatim in the Retool resource field — Retool passes the password directly without additional escaping.

### Oracle query returns 'ORA-01008: not all variables bound' when using Retool template expressions

Cause: Retool converts `{{ }}` expressions to Oracle bind variables. If a `{{ }}` expression evaluates to undefined or null, Oracle receives an unbound variable and throws ORA-01008. This commonly happens when a component (e.g., a Select or Table) has not yet loaded its default value when the query runs.

Solution: Add a null-coalescing fallback to all `{{ }}` expressions in Oracle queries. For string values, use `{{ someInput.value || 'defaultValue' }}`. For numeric values, use `{{ Number(someInput.value) || 0 }}`. Set a default value on Select and other input components so they always have a valid value on page load. Alternatively, set 'Run query on page load' to Off and trigger the query only after confirming all inputs have values.

```
-- Safe Oracle query with null-coalescing fallbacks
SELECT * FROM orders
WHERE
  status = {{ statusSelect.value || 'ACTIVE' }}
  AND order_date >= TO_DATE(
    {{ dateFilter.startDate || '2020-01-01' }},
    'YYYY-MM-DD'
  )
FETCH FIRST {{ Number(pageSizeInput.value) || 50 }} ROWS ONLY
```

### Oracle Cloud Autonomous Database connection fails with 'SSL handshake failed' or wallet errors

Cause: The wallet ZIP uploaded to Retool is missing, expired, or was downloaded from the wrong Oracle Cloud region or tenancy. Oracle Cloud Autonomous Databases require mTLS authentication using a wallet that matches the specific database instance.

Solution: Download a fresh wallet ZIP from Oracle Cloud Console: navigate to your Autonomous Database instance → DB Connection → Download Wallet. Select 'Instance Wallet' (not 'Regional Wallet') for the highest security. Ensure you download the wallet from the correct compartment and database. In Retool's Oracle resource configuration, remove the existing wallet, upload the fresh ZIP, and re-enter the wallet password. The service names available for Oracle Cloud databases are listed inside the wallet's tnsnames.ora file.

## Frequently asked questions

### Does Retool support Oracle Real Application Clusters (RAC)?

Yes. To connect to Oracle RAC, use the TNS connection string format in Retool's Oracle resource rather than a simple host/port/SID. A RAC TNS descriptor includes multiple address entries (one per RAC node) and LOAD_BALANCE=YES for connection-time load balancing. Paste the full TNS descriptor from your tnsnames.ora file into the Retool resource's TNS connection string field.

### Can Retool execute Oracle PL/SQL stored procedures?

Yes. Use Retool's SQL query editor to write PL/SQL anonymous blocks (BEGIN ... END;) that call stored procedures and packages. For procedures with OUT parameters, write the output to a log table or use a SELECT wrapper calling an Oracle function (RETURN type) instead, since Retool cannot directly capture Oracle OUT parameters. Retool executes PL/SQL over the same JDBC connection as regular SQL queries.

### How do I connect Retool to Oracle Cloud Autonomous Database?

Download the instance wallet ZIP from Oracle Cloud Console (Autonomous Database → DB Connection → Download Wallet → Instance Wallet). In Retool's Oracle resource configuration, enable the wallet authentication option, upload the wallet ZIP, enter the wallet password, and provide the database username and password. The service names available for your database are listed inside the wallet's tnsnames.ora file — use the high (for OLTP) or low (for reporting) service name depending on your use case.

### Why do my Oracle DATE columns show incorrect times in Retool?

Oracle's DATE type stores both date and time, and the JDBC driver serializes them as UTC timestamps. Retool displays them using the browser's local timezone by default, which can create apparent discrepancies if your Oracle server uses a different timezone (e.g., server stores 14:00 UTC but browser shows 10:00 AM EST). Use TO_CHAR(date_column, 'YYYY-MM-DD HH24:MI:SS') in your Oracle SQL to return date values as strings in a consistent format, bypassing timezone conversion entirely.

### What Oracle user permissions does Retool need?

For read-only reporting, the Retool Oracle user needs GRANT SELECT on each table or view being queried. For full CRUD admin panels, add INSERT, UPDATE, and DELETE grants on the relevant tables. For PL/SQL procedure execution, grant EXECUTE on the relevant packages or procedures. Avoid granting DBA, SYSDBA, or any system privilege roles — they give unrestricted access to all Oracle objects including system tables. Use Oracle schema-level grants (GRANT SELECT ON schema.table TO retool_user) rather than system-level grants.

### Does Retool work with Oracle 11g as well as newer versions?

Retool's Oracle connector supports Oracle 11g through Oracle 21c. Note that Oracle 11g does not support the FETCH FIRST N ROWS ONLY pagination syntax (available only from Oracle 12c). For Oracle 11g, use the ROWNUM pagination pattern: wrap your query in a subquery and filter with WHERE ROWNUM <= N. Contact Retool support if you encounter Oracle 11g-specific JDBC driver issues, as some character set and connection encoding behaviors differ from 12c+.

---

Source: https://www.rapidevelopers.com/retool-integrations/oracle-database
© RapidDev — https://www.rapidevelopers.com/retool-integrations/oracle-database