Connections for INFACore > Connections to source and target endpoints > Oracle
  

Oracle

Create an Oracle connection to read from or write to Oracle.

Feature snapshot

Operation
Support
Read
Yes
Write
Yes

Before you begin

Before you configure the connection properties, you'll need to get information from your Amazon Web Services account.
The following video shows you how to get information from your Amazon Web Services account:
https://infa.media/3fqEO6U

Connection properties

The following table describes the Oracle connection properties:
Property
Description
Connection Name
Name of the connection.
Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -,
Maximum length is 255 characters.
Oracle Subtype
The Oracle connection subtype that you can use to connect to Oracle on-premises or Oracle Autonomous Database.
Select one of the following options:
  • - Oracle ADB. Connects to Oracle Autonomous Database.
  • - Oracle On-premise. Connects to Oracle on-premises.
User Name
User name for the database login. The user name can't contain a semicolon.
Password
Password for the database login. The password can't contain a semicolon.
Host
Name of the machine that hosts the database server.
Port
Network port number used to connect to the database server.
Default is 1521.
Service Name
Service name or System ID (SID) that uniquely identifies the Oracle database.
Specify the SID in the following format to connect to Oracle databases:
SID:<ORACLE_SID>
Schema
Schema used for the Oracle connection.
Code Page
The code page of the database server.
Encryption Method
The method that the Secure Agent uses to encrypt the data exchanged between the Secure Agent and the database server.
Crypto Protocol Version
Cryptographic protocols to use when you enable SSL encryption.
Validate Server Certificate
Validates the certificate that is sent by the database server. If you specify the HostNameInCertificate parameter, the Secure Agent also validates the host name in the certificate.
Trust Store
The location and name of the trust store file.
Trust Store Password
The password to access the contents of the trust store file.
Host Name in Certificate
Host name of the machine that hosts the secure database.
If you specify a host name, the Secure Agent validates the host name included in the connection with the host name in the SSL certificate.
Key Store
The location and the file name of the key store.
Key Store Password
The password for the key store file required for secure communication.
Key Password
The password for the individual keys in the key store file required for secure communication.
Connection Retry Period
Number of seconds the Secure Agent attempts to reconnect to the Oracle database if the connection fails. If the Secure Agent can't connect to the database in the retry period, the operation fails.
Used for all operations. Default is 0.
Metadata Advanced Connection Properties
Additional properties for the JDBC driver to fetch the metadata.
If you specify more than one property, separate each key-value pair with a semicolon.
For example, ConnectionRetryCount=2;
ConnectionRetryDelay=20
To connect to an Oracle database enabled for advanced security, you can specify the Oracle advanced security options for the JDBC driver.
For example, EncryptionTypes=AES256;
EncryptionLevel=accepted;DataIntegrityLevel=accepted;
DataIntegrityTypes=SHA1
Runtime Advanced Connection Properties
Additional properties for the ODBC driver for the runtime.
If you specify more than one property, separate each key-value pair with a semicolon.
For example, charset=sjis;readtimeout=180
To connect to an Oracle database enabled for advanced security, you can specify the Oracle advanced security options for the ODBC driver.
For example, EncryptionTypes=AES256;EncryptionLevel=1;
DataIntegrityLevel=1;DataIntegrityTypes=SHA1;
DataIntegrityTypes=SHA1

Read properties

The following table describes the advanced source properties that you can configure in the Python code to read from Oracle:
Property
Description
Pre SQL
Pre-SQL command to run against the target database before writing data to the target.
Post SQL
Post-SQL command to run against the target database after writing data to the target.
SQL Override
The SQL statement to override the default query generated from the specified source type to read data from the Oracle source.
Schema Name
Overrides the schema name of the target object.

Write properties

The following table describes the advanced target properties that you can configure in the Python code to write to Oracle:
Property
Description
Forward Rejected Rows
Determines whether the transformation passes rejected rows to the next transformation or drops rejected rows. By default, the mapping task forwards rejected rows to the next transformation.
If you select the Forward Rejected Rows option, the Secure Agent flags the rows for reject and writes them to the reject file.
If you do not select the Forward Rejected Rows option, the Secure Agent drops the rejected rows and writes them to the session log file. The Secure Agent does not write the rejected rows to the reject file.
Pre SQL
Pre-SQL command to run against the target database before writing data to the target.
Post SQL
Post-SQL command to run against the target database after writing data to the target.
Update Override
An update SQL statement that updates the data in an Oracle target table.
The update SQL statement you specify overrides the default update statements that the Secure Agent generates to update the target based on key columns. You can define an update override statement to update target tables based on both key or non-key columns. You cannot validate the update SQL statement in the SQL transformation.
In the override statement, you must enclose all reserved words in quotation marks.
Reject File Directory
The directory that stores the rejected files.
Specify the directory where you want to store the rejected files.
Reject File Name
Name of the rejected file that is stored in the reject file directory.
Schema Name
Overrides the schema name of the target object.