To connect to an Oracle server, you need to specify the database server host name or IP address, the port the Oracle server is running on, the service name or SID, the user name and password to log in with. Skyvia supports both on-premise Oracle servers and Oracle Cloud.
For connecting to local Oracle servers (in your local network), Skyvia offers two options: direct connections and agent connections.
In order to use a direct connection, your Oracle server must be available through the Internet. If you are connecting to Oracle server in your local network, allow such connections in your firewall. See the IPs to allow in the How to Configure Local Database Server to Access It from Skyvia topic.
Agent connections don’t have such requirements, but you need to install the Skyvia Agent application in order to use them.
You need to specify the following parameters for Oracle connection:
- Server — name or IP address of the Oracle server host to connect to.
- Port — Oracle server connection port; default value is 1521.
- Connection Syntax — this parameter determines whether to use Service Name or SID to connect.
- Service Name — alias to an Oracle database instance (or many instances) to use.
- SID — unique name for an Oracle database instance.
- User — user name to log in with.
- Password — password to log in with.
- Connect as — this parameter specifies how you want to connect to the Oracle server. This parameter is set to ‘Normal’ by default. Alternatively, you can select administrative privileges, such as SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM.
In Advanced Settings, you can configure processing of text and boolean data, enter timeout intervals, and set up SSL encryption for a connection.
- Unicode - select this checkbox to use Unicode encoding for the connection.
- Trim Fixed Char - select this checkbox if you want Skyvia to trim trailing spaces when reading data from fixed-length string data types (CHAR, NCHAR).
- NUMBER(1,0) As Boolean - select this checkbox if you want Skyvia to treat and display values of NUMBER(1,0) columns as Boolean values (true or false). Otherwise, they are treated and displayed as numbers.
- Command Timeout - specifies the wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn’t include the time necessary to fetch data if the command selects some data.
- Connection Timeout - determines the time (in seconds) to wait while trying to establish a connection before terminating the attempt and reporting an error. Generally, you don’t need to modify it, but in some specific cases, when a connection to the database server is not good, you may try increasing their values.
- Protocol - select the protocol to use - TCP or secure SSL. For the SSL protocol, configure additional parameters:
- SSL Cert — client certificate. Paste the content of the certificate file into the box or click the button to open a multiline editor and paste the content of the certificate file there.
- SSL Key — client private key. Paste the content of the key file into the box or click the button to open a multiline editor and paste the content of the key file there.
- SSL ServerCertDN — a set of parameters for Oracle server certificate check. For example, “SslServerCertDN="C=UA,O=Devart,OU=DevartSSL,CN=TestSSL"“. You can find more about it in Oracle documentation.
Supported Actions and Actions Specifics
Oracle connector supports the following actions:
- Execute Command in Source, Lookup, and Target Data Flow components and in Import and Export tasks in the Advanced mode.
Execute Query in Source Data Flow components and in Import and Export tasks in the Advanced mode.
- Lookup in Lookup Data Flow components.
- Insert in Target Data Flow components.
- Update in Target Data Flow components.
- Delete in Target Data Flow components.