Versions Compared

Old Version 17

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 18

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

...

Use this account type to connect Oracle Snaps with datasources that use Oracle accounts.

Prerequisites

None.

...

Upcoming

Multiexcerpt include macro
nameOracle JDBC Driver Upgrade
templateDataeJyLjgUAARUAuQ==
pageOracle Snap Pack
addpanelfalse

Prerequisites

None.

Account Settings



StringNN/AN/A
Parameter NameData TypeDescription

Label


Default Value: N/A
Example: Oracle123DB

LabelStringRequired. Unique name for the account.N/AOracle123DB

JDBC URL Source


Default Value: Snap Generated
Example: User Provided

String

Required. Select the source of the JDBC URL. 

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

    Note

    If you select the User Provided option, the Host Name, Port Number, and Database Name fields are replaced by the Custom JDBC URL field.

Snap GeneratedN/A


Account propertiesStringRequired. Enter the information to create a connection to the database.N/A

Hostname


Default Value: N/A

Hostname

Example: oratestdb2.cwztruwzzvnq.us-east-1.rds.amazonaws.com

StringRequired if JDBC URL Source is Snap Generated. The server address to which the application must connect.N/Aoratestdb2.cwztruwzzvnq.us-east-1.rds.amazonaws.com
Port Number

Port Number


Default Value:1521
Example:1521

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

Database name


Default Value: N/A
Example: MYDB

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

Custom JDBC URL


Default Value: N/A

MYDBCustom JDBC URL

Example: Oracle123DB

String

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

Note

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:

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)))

N/A


Username


Default Value: N/A

Username

Example: TECTONIC

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

Password


Default Value: N/A

TECTONICPassword

Example: Oracle123DB

StringThe 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.N/AN/A
JDBC JARs

List of JDBC JAR files to be loaded. Click Image Modified to add a JDBC Driver; click Image Modified to remove a JDBC Driver.

JDBC Driver


Default Value: N/A

Example: N/A

JDBC DriverString

Enter the JDBC jar file to be loaded. Use  to select an existing file. A different driver binary for a driver must have a different name, the same name can not be reused for a different driver. If this property is left blank, a default JDBC driver is loaded.

Warning

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

N/AN/A


JDBC Driver Class

JDBC Driver Class


Default Value:oracle.jdbc.OracleDriver
Example: oracle.jdbc.OracleDriver

StringRequired. The name of the JBDC driver to use.oracle.jdbc.OracleDriveroracle.jdbc.OracleDriver

Database specifier type


Default Value: Service name
Example: Service name

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:

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

SSL/ TCPS


Default Value: Deselected

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 SelectedNot Selected

Advanced propertiesN/ASpecify advanced properties to connect to the database.N/A

Auto commit


Default Value: Selected

String

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.
SelectedSelected
Batch size

Batch size


Default Value: 50
Example: 100

Integer

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

Note

Select queries are not batched.


Warning

Using a large batch size could use up the JDBC placeholder limit of 2100.

50100


Fetch size

Fetch size


Default Value: 100
Example: 100

Integer

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

Warning

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

100100


Max pool size


Default Value: 50
Example: 50

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

Max life time


Default Value: 30
Example: 35

IntegerRequired. 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.3035
Idle Timeout

Idle Timeout


Default Value: 5
Example: 5

IntegerRequired. 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.55
Checkout timeout

Checkout timeout


Default Value: 10000
Example: 10000

Integer

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

Info

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

1000010000


URL PropertiesSpecify input regarding URL properties.N/A

URL property name


Default Value: N/A

URL property name

Example: Batch_Refresh_Time

StringName for the URL property.N

URL property value


Default Value: N/A

Batch_Refresh_TimeURL property value

Example: 10

N/AValue for the URL property.N/A10

Troubleshooting

ErrorReasonResolution
Failed to validate account: Failed to retrieve a database connection. ORA-00604: error occurred at recursive SQL level 1 ORA-12705: Cannot access NLS data files or invalid environment specifiedConfiguring your Windows Registry key value, HKEY_CURRENT_USER\Control Panel\International\LocaleName, in the environment to true or en-US has been observed to work for some Windows-based Groundplex instances. Additional probable solutions can be found here. 

...