Oracle Thin Dynamic Account

Overview

You can configure your accounts in SnapLogic using the Designer or Manager. Use this account type to connect Oracle Snaps with data sources that use Oracle accounts. This account uses dynamic values and enables you to specify account properties as expressions that reference Pipeline parameters.

Expression-enabled authentication fields, such as Username, Password, and Client Secret, support Secrets Management, a SnapLogic add-on that allows you to store endpoint credentials in a third-party secrets manager, such as AWS Secrets Manager, Azure Key Vault, or HashiCorp Vault. During validation and execution, pipelines obtain the credentials directly from the secrets manager. Learn more: Configure Accounts to use secrets.

Important: JDBC Driver upgrade and behavior change

The Oracle JDBC Driver is upgraded from OJDBC6 JAR (v11.2.0.4) to OJDBC10 JAR (v19.20.0.0) in the latest distribution in October 2023 and deployed to the stable distribution in the November 2023 release (after the Snaplex upgrade). The latest JDBC driver upgrade is backward-compatible. Learn more: Oracle JDBC driver and JOOQ upgrades.

You can consume this driver upgrade with the 434patches23000 Snap Pack version.

This JDBC driver upgrade has resulted in specific behavior changes that include errors, status codes, and success and failure messages. Learn more about the behavior changes to ensure your migration to the upgraded driver is seamless.

Prerequisites

Kerberos authentication for Oracle setup in the Windows/Linux environment.

Known Issues

None.

Account settings

Legend:
  • Expression icon (): Allows using JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if enabled). If disabled, you can provide a static value. Learn more.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field/Field set Description
Label*

String

Required. Unique name for the account.

Example: Oracle123DB
JDBC URL Source

Dropdown list

Required. The source of the JDBC URL that you want to use.

  • Snap Generated: The JDBC URL that SnapLogic generates using the details you provide in the Account Settings popup.
  • User Provided: The JDBC URLs that you provide directly into the Custom JDBC URL field. If you select this option, the Hostname, Port Number, and Database Name fields are replaced by the Custom JDBC URL field.

Default value: Snap Generated
Account properties*

Required. Enter the information to create a connection to the database.
Hostname

String/Expression

Required if JDBC URL Source is Snap Generated. The server address to which the application must connect.

If you need to connect to an on-premise server, specify the domain name or the IP address.

Example: oratestdb2.cwztruwzzvnq.us-east-1.rds.amazonaws.com or 190.159.0.124
Port number

String/Expression

Required if JDBC URL Source is Snap Generated. The database server's port number to which the application must connect.

Default value: 1521
Database name

String/Expression

Required if JDBC URL Source is Snap Generated. The database name to which the application must connect.

Example: MYDB
Custom JDBC URL

String/Expression

Required if JDBC URL Source is User Provided. The JDBC URLs that you want to use to connect to your Oracle account.

Use this option if you want to use complex JDBC URLs (involving multiple hosts and advanced options) to connect to your Oracle instance. For example, if you want to connect to Oracle DataGuard:

jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=on)(ADDRESS_LIST=(LOAD_BALANCE=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=OracleTest)))
Username

String/Expression

The user name that is allowed to connect to the database. It is used as the default username when retrieving connections. The user name must be valid in order to set up the data source.

Example: TECTONIC
Password

String/Expression

The password used to connect to the data source. It is used as the default password when retrieving connections. The password must be valid in order to set up the data source.
JDBC JARs

List of JDBC JAR files to be loaded. Click the add icon to add a JDBC Driver, click the remove icon to remove a JDBC Driver.
JDBC driver

String

The Oracle JDBC Driver is migrated from ORAJDBC6 (11.2.0.4 version) to OJDBC10 JAR (19.20.0.0 version). However, you can specify a custom JDBC driver to use. Click the Database icon to upload an existing JAR file from the SLDB. If this property is left blank, a default JDBC driver is loaded.

Behavior Change: This JDBC driver upgrade has resulted in specific behavior changes within the Snap Pack. These changes include errors, status codes, and success and failure messages. The latest JDBC driver upgrade is backward-compatible.

When you use the relative path for the JDBC JAR file in accounts across different projects, the pipelines fail with the JAR FILE NOT FOUND error. Therefore, you must use the absolute path for the JDBC JAR file to run your pipelines successfully.

Only v11.2.0.4.0 of a JDBC driver supports user-defined types.

Example: Project Spaces/my_diagnostic_space/my_diagnostic/sqljdbc4-2.0.jar
JDBC driver class*

String

Required. The name of the JDBC driver to use.

Default value: oracle.jdbc.OracleDriver
Database specifier type*

Dropdown list

Specify the database specifier type to use. The selected option decides which format of URL for JDBC is to be used internally.

  • SID: Uses the jdbc:oracle:thin@HOST:PORT:DBNAME format.
  • Service name: Uses the jdbc:oracle:thin@//HOST:PORT/DBNAME format.

Default value: Service name
SSL/TCPS

Checkbox

Opt for or against connecting through SSL to the Oracle instance.

If selected, the following format of URL is used internally:

jdbc:oracle:thin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<HOST>)(PORT=<PORT>))(CONNECT_DATA=(<SID/SERVICE_NAME>=<DATABASE_NAME>)))

For more information, refer to Configure Oracle Client for SSL/TLS (TCPS) Connection.

Default value: Not Selected
Authentication method

Dropdown list

Choose the authentication method to use when connecting to Oracle. The available options are:

  • User ID and Password
  • Kerberos: Kerberos authentication for Oracle supports both Windows and Linux-based databases and also supports Service account and User impersonation Kerberos authentication. When the authentication method is Kerberos, for service account, you must leave the Username and Password fields empty and add the following property to the URL Properties: oracle.net.kerberos5_cc_name = <Path to cache file>

Default value: User ID and Password
Kerberos config path

String/Expression

Appears when the Authentication method is Kerberos.

Specify the path to the Kerberos configuration file. Kerberos authentication for Oracle supports both Windows and Linux-based databases and also supports Service account and User impersonation Kerberos authentication.

Example: /opt/app/oracle/dbhome/network/admin/krb5.conf
Advanced properties

Specify advanced properties to connect to the database.
Auto commit

Checkbox/Expression

Opt for or against setting the auto-commit property for a database.

  • When selected, each of the batches is committed immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back.
  • When not selected, the Snap execution output is committed only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view.

Default value: Selected
Batch size*

Integer/Expression

Required. Set the number of statements that the Snap must execute at a time.

Select queries are not batched. Using a large batch size could use up the JDBC placeholder limit of 2100.

Default value: 50

Example: 100
Fetch size*

Integer/Expression

Required. Set the number of rows that the Snap must fetch at a time when executing a query.

Large values could cause the server to run out of memory.

Default value: 100
Max pool size*

Integer/Expression

Required. Set the maximum number of connections that a pool must maintain at a time.

Default value: 50
Max lifetime (minutes)*

Integer/Expression

Required. Set the maximum time (in minutes) of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.

Default value: 30

Example: 35
Idle timeout (minutes)*

Integer/Expression

Required. Set the maximum time (in minutes) that a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.

Default value: 5
Checkout timeout (milliseconds)*

Integer/Expression

Required. Set the time in milliseconds to wait for a connection to be available when the pool is exhausted.

If you provide 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.

Default value: 10000
URL properties

Specify input regarding URL properties.
URL property name

String/Expression

Name for the URL property.

Example: Batch_Refresh_Time
URL property value

String/Expression

Value for the URL property.

Example: 10
Note: Click Validate after entering the required details to ensure that all fields have been filled accurately. Click Apply to save the settings.

Troubleshooting

Error Reason Resolution
Unable to obtain Principal Name for authentication Caused by: javax.security.auth.login.LoginException. Ensure to provide a valid ticket cache path /tmp/krb5cc_Administrator and have enough permissions.
The service in the process is not supported. Operation unavailable. Ensure to create the Kerberos ticket for the user using okinit -f.
Got minus one from the read call. Keytab is not valid. Ensure to create a new keytab file and place it in %ORACLE_HOME%/network/admin.
The service in the process is not supported. An Invalid name provided (Mechanism level: KrbException: Cannot locate default realm) Ensure to provide valid krb5.conf path in the account settings.
TNS:listener does not currently know of the service requested in the connect descriptor Oracle service is down. Ensure the Oracle service is up and running.
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Listener refused the connection with the following error: ORA-12505, TNS: The listener does not currently know of the SID given in the connect descriptor.

For Linux environment:

USE_SID_AS_SERVICE_<YOUR_LISTENER_NAME>=on

For Windows environment:

USE_SID_AS_SERVICE_<YOUR_LISTENER_NAME>=on