Oracle Database Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8895 of the connector.

Get Started

Oracle OCI Version Support

The connector can connect Oracle Database 11.2 or later.

Establish a Connection

Before You Connect

To connect to Oracle, you will first need to update the appropriate environment variable. This variable must contain a folder location that includes the required Oracle OCI Library assemblies. The correct environment variable depends upon your platform:

• Windows: The native libraries should be on your PATH.
• Mac: The native libraries should be on your DYLIB_LIBRARY_PATH.
• Linux: The native libraries should be on your LD_LIBRARY_PATH.

Download the Oracle OCI Library assemblies here (ZIP).

Note for Linux Users: The Linux Oracle OCI libraries used by the connector have additional required dependencies on the AIO and libgcc libraries.

• Debian/Ubuntu:

  sudo apt-get install libaio1
  

  apt-get install libc6 libstdc++6 zlib1g libgcc1
  

• RHEL/CentOS/Fedora:

  sudo yum install libaio
  

  yum install glibc libstdc++ zlib libgcc
  

Here are the corresponding libraries required by the connector:

Connect to Oracle OCI

The following connection types are available:

• Oracle Server
• OracleTNS
• LDAP

Oracle Server

After you have added the appropriate libraries to your PATH, set the following to connect:

• User: The user ID provided for authentication with the Oracle database.
• Password: The password provided for authentication with the Oracle database.
• Port: The port used to connect to the server hosting the Oracle database.
• ServiceName: The service name of the Oracle database. You can obtain this value by querying global_name (select * from global_name) using the Oracle SQL*PLUS command line.
• Server: The host name or IP address of the server hosting the Oracle database.

OracleTNS

Set the following connection properties prior to connecting:

• User: The user ID provided for authentication with the Oracle database.
• Password: The password provided for authentication with the Oracle database.
• DataSource: The Oracle Net Services Name, Connect Descriptor (known also as TNS Connect String), or a connection name that identifies the database to which to connect. You should be able to find these values in your tnsnames.ora file.

For example,

DataSource='(DESCRIPTION=(ADDRESS=(protocol_address_information))(CONNECT_DATA= (SERVICE_NAME=service_name)))'

LDAP

In this scheme, you need to authenticate to the LDAP server. Set the following connection properties to connect:

• User: The user ID provided for authentication with the Oracle database.
• Password: The password provided for authentication with the Oracle database.
• LDAPUri: The LDAP URI used to connect to the LDAP server.
• LDAPUser: The User account for LDAP server.
• LDAPPassword: The password for the LDAP user.

Your connection string should look similar to this:

LDAPUri=ldap://myldap.com:389/SERVICE_EXMPL,cn=OracleContext,dc=example,dc=com; User=ORACLEUSER; Password=OracleUserPassword;

Note that this connection type requires both your Oracle and LDAP usernames and passwords. Your system administrator may allow anonymous LDAP logins, in which case your LDAP username and password are not needed.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Oracle OCI connector.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Schema

Miscellaneous

Authentication

This section provides a complete list of authentication properties you can configure.

ConnectionType

Connection properties to use for authentication. Accepted entries are Server, OracleTNS, LDAP.

Possible Values

Server, OracleTNS, LDAP

Data Type

string

Default Value

Server

Remarks

Together with Password and User, this field is used to choose connection properties that are used to authenticate against the server. Server is the default option. Use the following options:

• Server: Set this to connect with server's by its address, port number and service name.
• OracleTNS: Set this to use TNS record to connect the server.
• LDAP: Set this to connect LDAP server in order to resolve Oracle server connection with TNS record stored by LDAP server.

Server

The host name or IP of the server hosting the Oracle database.

Data Type

string

Default Value

""

Remarks

The host name or IP of the server hosting the Oracle database.

Port

The port used to connect to the server hosting the Oracle database.

Data Type

string

Default Value

1521

Remarks

The port used to connect to the server hosting the Oracle database.

ServiceName

The service name of the Oracle database.

Data Type

string

Default Value

""

Remarks

The service name of the Oracle database, such as XE.

You can obtain this value by querying global_name from the Oracle SQL command line.

User

The Oracle OCI user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the Oracle OCI server.

Password

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

DataSource

Oracle Net Services Name, Connect Descriptor (known also as TNS Connect String), or an easy connect naming that identifies the database to which to connect.

Data Type

string

Default Value

""

Remarks

Oracle Net Services Name, Connect Descriptor (known also as TNS Connect String), or an easy connect naming that identifies the database to which to connect.

LDAPUri

LDAP uri to connect the LDAP server.

Data Type

string

Default Value

""

Remarks

LDAP uri to connect the LDAP server and find out the Oracle's service name stored on LDAP server in the TNS record format.

LDAPUser

The User account for LDAP server.

Data Type

string

Default Value

""

Remarks

Use it when your LDAP server requires authorization to bind. Leave it empty otherwise.

LDAPPassword

The password for the LDAP user.

Data Type

string

Default Value

""

Remarks

Use it when your LDAP server requires authorization to bind. Leave it empty otherwise.

LDAPVersion

The LDAP version used to connect to and communicate with the server.

Data Type

string

Default Value

""

Remarks

Set this property to 2 or 3. The connector connects to a standard LDAP client as specified in RFC 1777, 2251, and other LDAP RFCs.

Wallet

The location of the client's wallet that os required to setup secure TCPS connection.

Data Type

string

Default Value

""

Remarks

The location of the client's wallet that os required to setup secure TCPS connection.

Schema

This section provides a complete list of schema properties you can configure.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\OracleOci Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\OracleOci Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

IncludeSynonyms

Query metadata for synonyms as though they are the original tables.

Data Type

bool

Default Value

true

Remarks

By default, the connector maps synonyms to their parent tables. It queries columns and their primary/foreign key metadata as if they were the regular tables.

If this property is set to false, then the connector does not list table and view synonyms.

Setting this property to false improves metadata performance. Consider doing so if you do not need to request metadata of synonyms automatically.

ShowMetadataDescriptions

Controls whether table and column descriptions are returned via the platform metadata APIs and sys_tables / sys_views / sys_tablecolumns.

Data Type

bool

Default Value

false

Remarks

By default table and column descriptions are not shown, since the Oracle OCI requires an extra join in meta-query beyond what is usually required to read system views describing the object comments.

Enabling this option will show table and column descriptions. This can slow down metadata operations on large datasets.

UseDBAMetadataViews

Query meta data from DBA_.. system views instead of ALL_.. system views.

Data Type

bool

Default Value

false

Remarks

By default, the connector queries meta data for the objects accessible to the current user. If the UseDBAMetadataViews property is set to true, then the connector will query meta data for all respective objects in the database.

This option will not work if current user has insufficient privileges to access DBA_.. system views.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

AllowPreparedStatement

Prepare a query statement before its execution.

Data Type

bool

Default Value

true

Remarks

If the AllowPreparedStatement property is set to false, statements are parsed each time they are executed. Setting this property to false can be useful if you are executing many different queries only once.

If you are executing the same query repeatedly, you will generally see better performance by leaving this property at the default, true. Preparing the query avoids recompiling the same query over and over. However, prepared statements also require the connector to keep the connection active and open while the statement is prepared.

MaxLobSize

The volume in numbers of bytes or UTF-8 chars which is allowed to query by non-parameterized SELECT query.

Data Type

int

Default Value

2000

Remarks

If the MaxLobSize property is set to 0 or negative value, the default of 2000 bytes will be used instead.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

NativeFolder

The path to the directory containing the native dlls the provider works with. This property is effective on the Windows and macOS platforms only.

Data Type

string

Default Value

""

Remarks

The path to the directory containing the native dlls the connector works with. By default connector relies on the standard system policy for loading the dynamic libaries. Apply full path or the path relative to your executable.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

QueryPassthrough

This option passes the query to the Oracle OCI server as is.

Data Type

bool

Default Value

true

Remarks

When this is set, queries are passed through directly to Oracle OCI.

ReconnectTimeout

The sleep time, in seconds, before retrying to reconnect to the server on a maximum idle time exceeded error.

Data Type

int

Default Value

1

Remarks

The ReconnectTries property must be greater than 0 for this value to have any effect.

ReconnectTries

The number of retry to connect server when a maximum idle time exceeded error is reported by server.

Data Type

int

Default Value

0

Remarks

Setting this value will allow the connector to autmotically reconnect if an 'ORA-02396: exceeded maximum idle time' error is received from the server. The default value the ReconnectTries property is set to 0 meaning the request will not be retried.