Defining a connection to an Oracle database

To access data in an Oracle database, you must define a connection that specifies the server, user name, and password.

Before you begin

  • Verify that the user name that connects to the Oracle database has the authority and privileges to perform the actions of the job.
  • Depending on how you choose to define the connection to the Oracle database, confirm that these Oracle environment variables are set correctly: TNS_ADMIN, ORACLE_HOME, ORACLE_SID, TWO_TASK, and LOCAL.

Procedure

  1. In the job design canvas, double-click the connector stage icon to open the connector properties.
  2. On the Properties page, in the Server field, complete one of the following steps:
    • Click Select to display a list of Oracle services, and then select the Oracle service to connect to. If the list is empty, the connector cannot locate the Oracle tnsnames.ora file. The connector tries to locate the file by checking the TNS_ADMIN and ORACLE_HOME environment variables.
    • Enter the complete content of the connect descriptor in the format that is used in the Oracle tnsnames.ora file.
    • Use the following syntax to enter an Oracle Easy Connect string: host[:port][/service_name]
    • To connect to the default local Oracle service, leave the property blank. The ORACLE_SID environment variable defines the default local service. The TWO_TASK environment variable on Linux or UNIX and the LOCAL environment variable on Microsoft Windows define the default remote service. Selecting an Oracle service is preferable to using the TWO_TASK or LOCAL environment variables.
  3. In the Username and Password fields, specify the user name and password to use to authenticate with the Oracle service.
    By default, the connector is configured for Oracle database authentication. This form of authentication requires that the name and password that you specify match the credentials that are configured for the user in the Oracle database.
  4. Optional: To use an authentication mechanism other than the authentication mechanism that is used by the Oracle server, select the Use external authentication check box.
    For information about enabling external authentication to your Oracle server, see the Oracle documentation.