Skip to content

oracle.mdx

Latest commit

 

History

History
154 lines (108 loc) · 7.44 KB

oracle.mdx

File metadata and controls

154 lines (108 loc) · 7.44 KB
Raw
title Oracle Database
description Connect to Oracle Database with TablePro

Oracle Database Connections

TablePro supports Oracle Database 11.1 and later. It speaks the Oracle TNS wire protocol directly in Swift, so no Oracle Instant Client or other external library is required. This covers Oracle Database instances running on-premises, in Docker, or Oracle Cloud.

Oracle 10g and earlier are not supported: they use the older O3LOGON handshake that the pure-Swift driver does not implement.

Servers behind a RAC SCAN listener, shared server, or a load balancer are supported: TablePro follows the listener's redirect to the instance that serves the session. Servers that require Native Network Encryption (SQLNET.ENCRYPTION_SERVER or SQLNET.CRYPTO_CHECKSUM_SERVER set to REQUIRED) are also supported; TablePro negotiates AES encryption with a SHA crypto-checksum, the same as SQL Developer and DBeaver.

Install Plugin

The Oracle driver is available as a downloadable plugin. When you select Oracle in the connection form, TablePro will prompt you to install it automatically. You can also install it manually:

  1. Open Settings > Plugins > Browse
  2. Find Oracle Driver and click Install
  3. The plugin downloads and loads immediately - no restart needed

Quick Setup

Download Basic package for macOS from Oracle Click **New Connection**, select **Oracle**, enter host/port/username/password/service name, click **Create**

Connection Settings

Field Default Notes
Host localhost
Port 1521 Listener port
Service Name - Required. Check tnsnames.ora if unclear. To connect by SID instead, set Connection Type to SID and enter the SID.
Username - Requires username/password auth (no OS auth)
Native network encryption Off Advanced. Turn on only if the server requires encrypted traffic (SQLNET.ENCRYPTION_SERVER = REQUIRED). Leave off for servers that merely accept encryption, which connect in clear text like Oracle's own clients.

Example Configurations

Local (Oracle XE): host localhost:1521, user system, service XEPDB1

Docker: gvenzl/oracle-xe:21-slim image, same config as local

Remote: Standard host/port/credentials, service name from DBA

Oracle Cloud (ADB): Port 1522, service name format mydb_tp, requires TLS wallet download from Oracle Cloud Console

SSL/TLS: OracleNIO has no TLS fallback. Preferred connects in plain TCP (the SSL pane shows a warning). Use Required for TCPS to Oracle Autonomous Database, Verify CA with a CA certificate path for strict validation. See SSL/TLS for details.

Connection URL

oracle://user:password@host:1521/service_name

See Connection URL Reference for all parameters.

Features

Schema Selection: Each user is a schema. Switch with ⌘K. Shows available schemas and objects.

Table Info: Structure (columns, types, nullability, defaults), indexes (B-tree, bitmap), foreign keys, DDL via DBMS_METADATA.

Query Editor: SQL/PL/SQL support. Pagination uses OFFSET/FETCH syntax:

-- Paginated results
SELECT *
FROM hr.employees
ORDER BY employee_id
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

-- Approximate row count (fast, uses statistics)
SELECT num_rows
FROM all_tables
WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

-- View definition
SELECT text
FROM all_views
WHERE owner = 'HR' AND view_name = 'EMP_DETAILS';

-- List all tables in the current schema
SELECT table_name
FROM user_tables
ORDER BY table_name;

Schema Editing: ALTER TABLE with double quotes for case-sensitive names. Supports ADD, RENAME COLUMN, MODIFY, DROP, CREATE INDEX.

Foreign Keys: Displayed in structure. Manual query: SELECT c.constraint_name FROM all_constraints c WHERE constraint_type = 'R' AND owner = 'HR'

Authentication: Username/password only (no OS auth or wallet). Create user: CREATE USER app_user IDENTIFIED BY "Password1!"; GRANT CREATE SESSION, SELECT ANY TABLE TO app_user;

Auth Compatibility

TablePro authenticates via OracleNIO, a pure-Swift implementation of the Oracle TNS wire protocol (no Oracle Instant Client needed).

dba_users.password_versions Status
12C only (recommended) ✓ Supported
11G 12C ✓ Supported
10G 11G 12C ✓ Supported
10G 11G (legacy migration) ✓ Supported
10G only (deprecated) ✓ Supported, but consider rotation
External / Kerberos / LDAP-managed ✗ Not supported. File an issue.

10G hash is supported for compatibility with legacy environments. It uses DES-based hashing without modern salting and is deprecated by Oracle. We recommend rotating affected accounts under modern auth so password_versions contains only 11G or 12C:

ALTER USER <USER> IDENTIFIED BY <new-secure-password>;
SELECT username, password_versions FROM dba_users WHERE username = '<USER>';

If the connection fails with "Unsupported Password Verifier", check password_versions first. "Server Version Not Supported" means the database is older than 11.1 (10g or earlier), which the driver cannot connect to.

Column Type Support

Oracle type Display
VARCHAR2, NVARCHAR2, CHAR, NCHAR, LONG, CLOB, NCLOB, JSON Text as stored
NUMBER Exact integer up to Int64 range; decimals up to 17 digits of precision
BINARY_FLOAT, BINARY_DOUBLE Native Swift Float/Double
DATE yyyy-MM-dd
TIMESTAMP ISO-8601 in UTC with fractional seconds
TIMESTAMP WITH TIME ZONE ISO-8601 in the host's local time zone with explicit offset
TIMESTAMP WITH LOCAL TIME ZONE ISO-8601 in the host's local time zone with explicit offset
INTERVAL DAY TO SECOND D HH:MM:SS plus fractional seconds when non-zero (up to 9 digits, trailing zeros trimmed)
INTERVAL YEAR TO MONTH Y-MM
RAW, LONG RAW, BLOB Lowercase hex, truncated to 4 KB
ROWID As stored
BOOLEAN true / false
BFILE <bfile> placeholder (locator only)

Columns with types not yet supported render as <unsupported: type> rather than crashing or corrupting data. Report unsupported types via GitHub Issues.

Troubleshooting

Connection refused: Check listener running (lsnrctl status), verify port 1521 open, if Docker check docker start oracle-xe

Invalid service name: Verify service exists: SELECT value FROM v$parameter WHERE name = 'service_names'; List services: lsnrctl services

Instant Client not found: Download Basic package, extract to /usr/local/oracle/instantclient, set DYLD_LIBRARY_PATH

Connection dropped during handshake: The server closed the connection mid-login. The error dialog shows the handshake phase it stopped at (for example advancedNegotiation, dataTypeNegotiation, or authentication). Check for a firewall, VPN, or proxy that resets traffic, and confirm the host and port reach the listener directly. If the phase is around negotiation and the server requires native network encryption, turn on the Native network encryption option; if you turned it on for a server that only accepts encryption, turn it back off.

Limitations: Username/password only, BFILE shows locator metadata only (content fetch via DBMS_LOB not supported), PL/SQL limited to anonymous blocks.