Connecting to Oracle to Execute a Transformation Map

LANSA Composer

Connecting to Oracle to Execute a Transformation Map

When the Transformation map runs, it will use JDBC to establish the connection to the Oracle database on your network. This connection is initiated from the server computer on which the transformation map executes.

LANSA Composer prepares the JDBC connection according to the Database Configuration that you provide as a parameter to the Transformation Map. You must define the Database Configuration with the correct details necessary to establish the particular database connection to be used.

Example Database Configuration

The following sample values assume that you are using an Oracle  JDBC driver to connect to the Oracle database on your network:

Database driver class

Following are class names commonly used with the Oracle JDBC drivers:

For Oracle 10g and earlier:
oracle.jdbc.driver.OracleDriver

For Oracle 11i and later:
oracle.jdbc.OracleDriver

Database connection string 

The form of the connection string for the Oracle JDBC driver depends on the driver and the type of connection you wish to make.  For example:

For the JDBC Thin Driver, or Server-side Thin Driver:

jdbc:oracle:thin:@<database>

where <database> is either a string of the form //<host>:<port>/<service_name>, or a SQL*net name-value pair, or a TNSEntryName.  For example, the following is a sample JDBC connection string to connect to the Oracle database named ORA0101 running on the server named TEST0101:

jdbc:oracle:thin:@//TEST0101/ORA0101

 

To make a remote connection with the JDBC OCI (Oracle call interface) driver:

jdbc:oracle:oci:@<database>

where <database> is either a TNSEntryName or a SQL*net name-value pair defined in tnsnames.ora.

 

Further considerations

  • The Database Configuration should specify the username and password of the user authorized to perform the database transactions.
  • If necessary, you can download the Oracle JDBC Driver appropriate to your environment from this location:

JDBC/UCP Download Page

  • The name of the .jar that implements the Oracle JDBC driver depends on the version and features of the JDBC driver that you have downloaded.  For example, the base JDBC driver for use with Java 1.5 is ojdbc5.jar.  You may need to copy the file to the /jar directory of your JSM instance and restart the Java Service Manager before using the driver with Transformation Maps in LANSA Composer.
  • Refer to relevant Oracle resources for more information on the available Oracle JDBC drivers, their installation and configuration requirements and the keywords and values that can be specified in the JDBC connection string.  Following are some useful references:

Oracle JDBC Frequently Asked Questions

Oracle JDBC FAQ

Top Ten Oracle JDBC Tips | O'Reilly Media

ConnectionStrings.com - Forgot that connection string? Get it here!