In this article
Overview
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.
Prerequisites
Kerberos authentication for Oracle setup in the Windows/Linux environment.
Known Issues
None.
Account Settings
| Parameter Name | Data Type | Description | Default Value | Example |
|---|---|---|---|---|
| Label | String | Required. Unique name for the account. | N/A | Oracle123DB |
| JDBC URL Source | String | Required. The source of the JDBC URL that you want to use.
| Snap Generated | N/A |
| Account properties | String | Required. Enter the information to create a connection to the database. | N/A | N/A |
| Hostname | String | Required if JDBC URL Source is Snap Generated. The server address to which the application must connect. This property is expression-enabled. For more information on the expression language, see Understanding Expressions in SnapLogic and Using Expressions. For information on Pipeline Parameters, see Pipeline Properties. If you need to connect to an on-premise server, specify the domain name or the IP address. For example, test.mydbserver.com or 190.159.0.124. | N/A |
|
| Port Number | String | Required if JDBC URL Source is Snap Generated. The database server's port number to which the application must connect. This property is expression-enabled. For more information on the expression language, see Understanding Expressions in SnapLogic and Using Expressions. For information on Pipeline Parameters, see Pipeline Properties. | 1521 | 1521 |
| Database name | String | Required if JDBC URL Source is Snap Generated. The database name to which the application must connect. This property is expression-enabled. For more information on the expression language, see Understanding Expressions in SnapLogic and Using Expressions. For information on Pipeline Parameters, see Pipeline Properties. | N/A | MYDB |
| Custom JDBC URL | String | 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, you can enter a JDBC like the one below:
This property is expression-enabled. For more information on the expression language, see Understanding Expressions in SnapLogic and Using Expressions. For information on Pipeline Parameters, see Pipeline Properties. | N/A | N/A |
| Username | String | 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. This property is expression-enabled. For more information on the expression language, see Understanding Expressions in SnapLogic and Using Expressions. For information on Pipeline Parameters, see Pipeline Properties. | N/A | TECTONIC |
| Password | String | 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. This property is expression-enabled. For more information on the expression language, see Understanding Expressions in SnapLogic and Using Expressions. For information on Pipeline Parameters, see Pipeline Properties. | N/A | N/A |
| JDBC JARs | String | List of JDBC JAR files to be loaded. Click | N/A | N/A |
| 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 JBDC driver to use. Click the Database Only v11.2.0.4.0 of a JDBC driver supports user-defined types. | N/A | N/A |
| JDBC Driver Class | String | Required. The name of the JBDC driver to use. | oracle.jdbc.OracleDriver | oracle.jdbc.OracleDriver |
| Database specifier type | String | Specify the database specifier type to use. The selected option decides which format of URL for JDBC is to be used internally. The valid options are:
| Service name | Service name |
| SSL/ TCPS | String | 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>))) | Not Selected | 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 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. | N/A | /opt/app/oracle/dbhome/network/admin/krb5.conf |
| Advanced properties | N/A | Specify advanced properties to connect to the database. | N/A | N/A |
| Auto commit | String | Opt for or against setting the auto-commit property for a database.
| Selected | Selected |
| Batch size | Integer | 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. | 50 | 100 |
| Fetch size | Integer | 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. | 100 | 100 |
| Max pool size | Integer | Required. Set the maximum number of connections that a pool must maintain at a time. | 50 | 50 |
| Max life time | Integer | 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. | 30 | 35 |
| Idle Timeout | Integer | Required. Set the maximum time (in seconds?) 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. | 5 | 5 |
| Checkout timeout | Integer | 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. | 10000 | 10000 |
| URL Properties | N/A | Specify input regarding URL properties. | N/A | N/A |
| URL property name | String | Name for the URL property. | N/A | Batch_Refresh_Time |
| URL property value | N/A | Value for the URL property. | N/A | 10 |
Troubleshooting
| Error | Reason | Resolution |
|---|---|---|
| Caused by: javax.security.auth.login.LoginException. | Ensure to provide a valid ticket cache path |
| Operation unavailable. | Ensure to create the Kerberos ticket for the user using |
| Keytab is not valid. | Ensure to create a new keytab file and place it in |
| An Invalid name provided (Mechanism level: KrbException: Cannot locate default realm) | Ensure to provide valid |
| Oracle service is down. | Ensure the Oracle service is up and running. |
| 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:
For Windows environment:
|