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. The connector requires the Oracle OCI Library, which can be downloaded here.

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 Oracle OCI Library assemblies, which are shipped alongside the connector. 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.

These native libraries can be found in the lib folder inside the installation directory of the connector.

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 Harmony 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.