Home >

On this page

AX2435

Import source: Oracle

The Source tab of the Import Wizard uses the following settings when importing data from an Oracle database.

Field Description
Source

Select Oracle to read data directly from an Oracle database.

NOTE: The Oracle Data Access Connection software (ODAC) must be installed on the Axiom Software application server in order to use this import option. If you want to import directly from an Oracle database without installing this software on the application server, you can use the OLEDB import source instead.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Software cloud service, then you must specify a remote data connection so that the cloud service can connect to the database server located on your network.

You can select any remote data connection defined in your system, or you can enter an import variable name. For example, you can enter the built-in system variable {DefaultRemoteDataConnection} to automatically use the default remote data connection for your system. For information on how the default remote data connection is determined, see System variables.

Remote data connections are defined in Scheduler. For more information, see Managing remote data connections.

If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Connection

Complete the following connection information for the import source.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

Field Description

Server

The connection parameters for the Oracle server. You can obtain this information from the Oracle TNS Names entry. See the following section for more information.
User

The user name to use to connect to the database.
Password

The password to use to connect to the database.

NOTE: The password must be re-entered whenever any of the other connection properties are changed.

Once you have completed the connection settings, click the Test connection button to test the connection. The Status updates to show either a success message or an error message.

Obtaining the Oracle connection parameters from a TNS Names entry

A typical TNS Names entry for Oracle looks like the following:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHOSTNAME)(PORT=MyPORT))(CONNECT_DATA=(SERVICE_NAME=MyOracleServiceID)))

Axiom requires this information in the following format:

MyHOSTNAME:MyPORT/MyOracleServiceID

Where:

  • MyHostName is the name of the Oracle server machine

  • MyPort is the port number that the server is listening on, typically 1521

  • MyOracleServiceID is the name of the Oracle service running on the host machine

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to be imported to the temptable. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, enter any valid SQL statement to define the data query. You can click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement. For more information, see Creating the SQL SELECT statement.

Variables can be used in the SELECT statement. See Using variables in imports.