The Oracle source reads tables and views from an Oracle database into the Lake. Use this page to fill in each field of the Oracle form, including the connect method, the driver and the TLS certificate. The steps shared by every SQL engine are on Connect a SQL database.
Prerequisites
An Oracle user with read access to the tables you want to load.
The service name of the database (Oracle 12c and newer, including pluggable databases) or its SID (older versions).
The listener accepts connections from DataMind on its port, usually 1521.
For an encrypted connection: a TCPS listener, and the server certificate if it is not issued by a public authority.
Open Lake and click Add datasource, or open the New menu in the toolbar above the tree.
Choose Oracle in the picker.
Fill in the General, Connection and Datetime steps as described below.
Click Add.
Connection settings
General step
Name (Mandatory)
The name the source carries in the Lake.
At least 3 characters. The field reads Enter name until you type.
Code (Mandatory)
A short code, at least 2 characters, made of letters and numbers only.
It becomes the prefix of the table names the source forms. It fills in from Name while you type; edit it to set your own.
Name and Code cannot be changed after the source is created.
Folder (Optional)
The Lake folder the source is filed under. Leave it empty to keep the source at the top level.
Connection step: Address
Paste connection string (Optional)
Click Paste connection string, paste a connection string, and click Fill.
Accepted forms: EZConnect such as <server>:1521/ORCLPDB1, a JDBC thin URL such as jdbc:oracle:thin:@//<server>:1521/ORCLPDB1, the older <server>:1521:ORCL form, or a full (DESCRIPTION=...) descriptor.
The string fills Host, Port, Connect by and the service name or SID. A password inside it is never read.
Host (Mandatory)
The server name or IP address of the Oracle listener.
Port (Mandatory)
The port the listener accepts connections on.
Default: 1521. A whole number from 1 to 65535.
The port does not change when you turn on SSL/TLS. Enter the port of your TCPS listener yourself.
Connect by (Mandatory)
Service name (default): Oracle 12c and newer, pluggable databases.
SID: older Oracle versions that expose an instance SID.
The label of the next field follows your choice.
Service name or SID (Mandatory)
The service name, for example ORCLPDB1, or the SID, for example ORCL.
Required: an empty field shows "Service name or SID is required".
Connection step: Authentication
Username (Mandatory)
The Oracle user the source signs in with.
Password (Mandatory)
The password of that user.
After you save, the field reads Stored password with Replace beside it. The password is never shown again.
Connection step: Security
SSL/TLS (Optional)
Connects over TCPS and encrypts the connection between DataMind and the server.
Default: off.
Not available with the Instant Client driver. Switch Driver to Built-in under Advanced first.
TLS certificate (PEM, optional) (Optional)
Shown only while SSL/TLS is on.
Needed only when the server certificate is not issued by a public authority.
Paste the certificate, from -----BEGIN CERTIFICATE----- to -----END CERTIFICATE-----, or click Upload .pem. The upload accepts .pem and .crt files.
After you save, the field reads Stored certificate with Replace beside it.
Turning SSL/TLS off clears the certificate.
Connection step: Advanced
Driver (Optional)
Built-in (default): connects directly. Oracle 12.1 and newer, with or without SSL/TLS.
Instant Client: the Oracle native client. Required for Oracle 11g; cannot use SSL/TLS.
The Advanced card opens by itself when Instant Client is selected.
Datetime step
Timezone (Optional)
The IANA timezone the database writes its datetime values in, for example Europe/Berlin.
Leave it empty when the database runs in the same timezone as the platform.
Datetime values (Optional)
Keep as written (wall clock) (default) stores each value exactly as the database shows it.
The other option converts each value from the source timezone into platform time on load.
On the Connection step, click Test connection. The button reads Testing… while it runs.
Connection test passed means DataMind reached the listener and signed in. Connection test failed shows the server's message with a copy button; fix the settings and test again.
On Datetime, check the summary: Name, Server, Service name or SID, Username, Security and Driver.
The test is optional and never saves the source. To edit or duplicate the source later, see Connect a SQL database.
Questions, answered
Should I connect by service name or by SID?
Use Service name for Oracle 12c and newer, and always for a pluggable database. Use SID only for an older server that exposes an instance SID. If you paste a connection string, the form picks the right option for you.
Which driver do I need for Oracle 11g?
Instant Client. The Built-in driver connects to Oracle 12.1 and newer. Instant Client cannot use SSL/TLS, so keep SSL/TLS off for an Oracle 11g source.
Can I paste a TNS descriptor?
Yes. Paste the full (DESCRIPTION=...) text into Paste connection string and click Fill. The form reads HOST, PORT and SERVICE_NAME or SID from it.
Why are my Oracle schemas listed as databases?
Oracle keeps tables per owner, so the Lake lists each owner whose tables the user can read as a database, with the tables beneath it. Grant the source user read access to another owner's tables to see that owner.
Does the certificate's host name have to match Host?
No. The source does not compare the host name in the server certificate with Host. It still needs to trust the certificate: paste it into TLS certificate (PEM, optional) when it is not issued by a public authority.
Can I send data back to Oracle?
Yes. Oracle is also offered as a Writeback destination, with the same connection fields. See Add and manage destinations.