IKM BIAPPS SQL Target Override

This is a unique IKM in that it allows you to specify a custom SQL statement to execute against a target table. This SQL statement will execute once for each source record returned by the interface. For a given execution of this SQL statement, values from the source record may be referenced as parameters in the SQL.

This IKM has a variety of different use cases; however, because it does not implement an interface in the typical manner of ODI interfaces, it should be reserved only for implementing system defined logic that will never be customized.

If the custom SQL is not suitable for all target technologies, then platform specific-SQL statements can be given as alternatives. This IKM will run the SQL in the option corresponding to the target technology on execution if available. Otherwise, the SQL in the generic option will be used.

Prerequisites

  • Implements non-customizable system logic.

  • If the generic SQL Override is not suitable for a particular platform, perhaps for performance or incompatibility reasons, then a platform specific SQL Override option must be provided.

  • SQL Statements must begin with "INSERT", "UPDATE" or "DELETE" keywords.

  • You must ensure hint placeholders are included in the SQL.

  • You must ensure all required indexes are in place.

Referencing Source Data

To use data from the source data in the SQL override:

  • Map the source data you want to reference to one of the target columns on the interface.

  • Reference it in the SQL Override using the syntax :TARGET_COLUMN.

Options for Functionality

SQL Override Options — A generic SQL Override option is available. Also, platform-specific options are provided for cases where there are variations in syntax or performance for different target technologies.Prerequisites for using this option are as follows:

  • Begin SQL statements with "INSERT", "UPDATE" or "DELETE" keywords.

  • If the generic SQL Override is not suitable for a particular platform, for example, due to performance or incompatibility reasons, then a platform-specific SQL Override option must be provided.

Options for Performance Tuning

  • Hints — This IKM allows the passing of hints into the generated SQL. See My Oracle Support document (ID 1963225.1) titled Oracle Business Intelligence Applications Version 11g Performance Recommendations.

  • Alter Session List — Applies a list of alter session commands to the session used by the KM. Commands should be separated by a semi-colon and without the "ALTER SESSION" prefix. Each command should be prefixed SRC or TGT depending on whether it should be executed on the source connection (relevant if using an LKM) or the target connection. For example:

    SRC set TRACEFILE_IDENTIFIER='ODI_TRACE'; SRC set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; TGT set TRACEFILE_IDENTIFIER='ODI_TRACE_TGT';