Oracle Database

Connect Oracle Database to Fresco BI. Fresco BI reads your tables and views read-only and imports them into a Data Table.

Feature	Supported
Import	Yes
DirectQuery	No
SSL/TLS	verify-full (default) or disable
SSH tunnel	No
Cloud	Yes
On-premises (via Gateway)	Yes

Prerequisites

• A password user (user name and password). Wallets and TNS aliases are not used: Fresco BI connects with host, port and a service name or SID.
• The listener host and port, and the service name (or the SID on older databases). On a multitenant database, use the service of the pluggable database (PDB) that holds your data.
• For TLS (the default), a TCPS listener. It often listens on port 2484, or 1522 on Autonomous Database.
• An administrator account to run Step 1.

Step 1: Create a read-only user

Connect to the database (the PDB on a multitenant database) as an administrator:

CREATE USER fresco_reader IDENTIFIED BY "<strong-password>";
GRANT CREATE SESSION TO fresco_reader;
GRANT SELECT ON <schema>.<table> TO fresco_reader;

Repeat the last grant for each table and view to read. On Oracle Database 23ai and later, one grant covers a whole schema, including tables created later:

GRANT SELECT ANY TABLE ON SCHEMA <schema> TO fresco_reader;

On older versions, grant every current table and view of a schema at once (run again after new tables are added):

BEGIN
  FOR o IN (SELECT object_name FROM all_objects
            WHERE owner = '<SCHEMA>' AND object_type IN ('TABLE', 'VIEW')) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON "<SCHEMA>"."' || o.object_name || '" TO fresco_reader';
  END LOOP;
END;
/

Do not grant SELECT ANY TABLE without ON SCHEMA, DBA or any write privilege. Fresco BI lists only the schemas that hold tables or views the user can see, and hides Oracle-maintained schemas.

Step 2: Allow network access

Cloud. The listener must be reachable from the internet. Allow inbound connections from Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on the listener port (1521, or your TCPS port such as 2484). For example, add an ingress rule in the OCI security list or network security group, or an inbound rule in an AWS security group. On Autonomous Database, add the addresses to its access control list.

On-premises (via Gateway). For a database inside your own network, choose On-premises (via Gateway) as the connection type. The panel then switches to the Gateway guide.

An SSH tunnel is not available for Oracle.

Step 3: Fill in the form

The Oracle form is not in the app yet. A source has these settings:

• Host (host): the listener’s DNS name or public IP.
• Port (port): 1521 by default. Use your TCPS port with TLS (often 2484, or 1522 on Autonomous Database).
• Service type (service_type): service_name (default) or sid for older databases.
• Service (service): the service name or SID. Letters, digits and _ . $ # -, up to 128 characters.
• User (user) and Password (password): the user from Step 1.
• SSL mode (ssl_mode): verify-full (default) or disable. See below.
• CA certificate (ca_pem, optional, verify-full only): the CA that signed the server certificate, in PEM. Empty uses the system trust store.
• Server certificate DN (server_cert_dn, optional, verify-full only): match the certificate subject DN instead of the host name, for example CN=db,O=Acme.
• Statement timeout (statement_timeout_seconds, optional): 10 to 7200 seconds per database call. Empty means the task’s own time limit only.
• Connection type: Cloud, or On-premises (via Gateway) with a gateway cluster.

SSL modes

Mode	What it does
verify-full (default)	TCPS: encrypts, checks the CA and the host name (or the certificate DN)
disable	Plain TCP, no encryption

The CA certificate and the certificate DN apply only to verify-full. TLS 1.2 or later is required.

Troubleshooting

• The server rejected the user name or password. Check the user and password, and that the account is not locked or its password expired.
• The database user cannot read this object. The user lacks CREATE SESSION or SELECT on the table: run the grants in Step 1.
• The database or object was not found on the server. The listener does not know this service name or SID. Check Service type and Service (on a multitenant database, use the PDB service).
• No connection could be opened to this host and port. Check the host, the port, and that the firewall allows Fresco BI’s IP. No listener on that port, or the firewall rejects it.
• The server did not answer in time. Usually a firewall that drops packets, or a wrong host or port. Allow Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on the listener port.
• The SSL connection could not be set up with the chosen mode. Check the SSL mode and the CA certificate. With verify-full, use the TCPS port, paste the right CA, and enter the host name in the certificate (or set the certificate DN).
• The host name could not be resolved (DNS). Check the Host field. Use a public DNS name or IP.
• The host is in a blocked range (private, loopback or metadata). A database inside a private network needs an On-premises connection through a Gateway. Choose On-premises (via Gateway).
• A size or concurrent-connection limit was exceeded. The database hit its session or process limit, memory limit, or a read ran out of undo (“snapshot too old”): try later or ask the DBA to raise the limit.