Skip Headers
Oracle® Warehouse Builder Sources and Targets Guide
11g Release 2 (11.2)

Part Number E10582-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

1 Connecting to Sources and Targets in Oracle Warehouse Builder

For different types of data sources and targets, Warehouse Builder provides different connection methods.

This chapter introduces the data sources and targets and specific connectivity technologies supported by Oracle Warehouse Builder. It includes the following topics:

Supported Sources and Targets

Table 1-1 lists the data storage systems and applications that Oracle Warehouse Builder 11g Release 2 (11.2) can access. The table lists the supported sources and targets for each type of application.

Table 1-1 Sources and Targets Supported in Oracle Warehouse Builder 11g Release 2 (11.2)

Application Type Supported Sources Supported Targets

Oracle Database

Oracle Database releases 8.1, 9.0, 9.2, 10.1, 10.2, 11.1,11.2

Oracle Database releases 9.2, 10.1, 10.2, 11.1, 11.2

Non-Oracle Databases

  • Any database accessible through Oracle Database Heterogeneous Services, including but not limited to DB2, Informix, SQL Server, Sybase, and Teradata.

  • Any data store accessible through ODBC, including but not limited to Excel and MS Access.

  • Any database accessible through Oracle Database Heterogeneous Services, including but not limited to DB2, Informix, SQL Server, Sybase, and Teradata.

  • Any data source accessible through ODBC, including but not limited to Excel and MS Access.


Delimited and fixed-format flat files.

See "Importing Definitions from Flat Files Using Sampling".

Delimited, fixed-format, and XML format flat files.

Business Applications


Process Flows and Schedules/Oracle Workflow


Oracle Workflow releases 2.6.2, 2.6.3, 2.6.4, 11i

Process Flows and Schedules/Concurrent Manager


Any Oracle Database location, release 10g or later.

To deploy a schedule in Concurrent Manager, Release 11i or 12i is required. However, for both releases, you must select 11i as the version when you create a location in Warehouse Builder.

Business Intelligence/Discoverer


Oracle BI Discoverer Release 10.1, Oracle Business Intelligence Suite Enterprise Edition

Oracle Designer

Oracle Designer 6i, 9i, 10g


Connecting to an Oracle Database

You can directly connect to an Oracle database and import metadata from the available database objects or deploy target objects to the database. To connect to a database on a remote host, you must provide the network credentials to connect to the host. See "Importing Metadata Definitions from Oracle Database" for more information about connecting to an Oracle database.

Oracle Database Heterogeneous Services

Warehouse Builder communicates with non-Oracle systems using Oracle Database Heterogeneous Services and a complementary agent. Heterogeneous Services make a non-Oracle system appear as if it were a remote Oracle Database server. The agent can be an Oracle Database Gateway or the generic Open Database Connectivity (ODBC) agent included with Oracle Database.

Figure 1-1 describes how Oracle Database uses Heterogeneous services to access a remote non-Oracle source.

Figure 1-1 Heterogeneous Services Architecture

Description of Figure 1-1 follows
Description of "Figure 1-1 Heterogeneous Services Architecture"

The Heterogeneous Services component in the database communicates with the Heterogeneous Services agent process. The agent process, in turn, communicates with the remote database.

The agent process consists of agent-generic code and a system-specific driver. All agents contain the same agent-generic code. But each agent has a different driver depending on the type of data being sourced.

Connecting Through Oracle Database Gateways

Oracle Database gateways provide transparent connections to non-Oracle databases. When using a gateway, you can access these non-Oracle databases just as you would an Oracle database, including importing object metadata and accessing data as source and target. See Chapter 4, "Connecting to Non-Oracle Data Sources Through Gateways" for details of connecting to different databases through gateways.

Connecting Through JDBC

To connect to data sources that support Java Database Connectivity (JDBC), you must use the appropriate JDBC driver for that data source. The JDBC driver for a non-Oracle database or other data source may be installed with the product, or may require a separate download or purchase. For detailed information about connecting to various data sources using JDBC, see Chapter 6, "Connecting to Data Sources Through JDBC".

Choosing JDBC or Gateways Connectivity

To connect through JDBC, you must install the appropriate JDBC drivers for the database. Most of these drivers are shipped with the database. They are also developed by third-party vendors and are usually available as free downloads. The database objects, such as tables, that you import through a JDBC connection can be used only in Code Template mappings. These database objects cannot be used with other mappings, such as PL/SQL mappings, that you create in Warehouse Builder.

To connect through a gateway, you must install Oracle Gateway for the specific database. A gateway enables you to access data from non-Oracle databases in the same way that you access data from Oracle Database. The database objects that you import through a gateway connection can therefore be used in any of the mappings that you create in Warehouse Builder.

Connecting Through ODBC

Oracle Warehouse Builder can leverage support for ODBC provided by the Oracle Database to integrate with any data source that supports ODBC connectivity. ODBC provides a generic connectivity that is intended for low-end data integration solutions and the transfer of data is subject to the rules of specific ODBC or object linking and embedding (OLE) database drivers installed on the client computer. You need not install database-specific agents to connect to different data sources. Instead, you can use the generic connectivity agent included with Oracle Database. You must still create and customize an initialization file for your generic connectivity agent. For detailed information about connecting to data sources using ODBC connectivity, see "Connecting to an ODBC Data Source".

Connecting To Enterprise Applications

Using Warehouse Builder, you can also connect to ERP and CRM applications, such as SAP, Oracle E-Business Suite, Peoplesoft, and Siebel.

You can also connect to Oracle's Customer Data Hub (CDH), Universal Customer Master (UCM), and Product Information Management (PIM).

The application adapters for Oracle applications provide additional metadata to simplify ETL design from such sources, but connecting to these sources depends upon underlying database connectivity. For example, metadata extraction from E-Business Suite, which is hosted on Oracle database, is done using database links, while metadata extraction from a Peoplesoft application hosted on a DB2 database depends on having the DB2 gateway installed on your target database.Connecting to an SAP system and extracting data is accomplished using native SAP R/3 ABAP code and data extraction techniques fully supported by SAP.When using the application connectors, the details of the underlying connection technologies for different sources are generally hidden from the user, which simplifies managing the connectivity.

For more information about connecting to Oracle E-Business Suite, Peoplesoft, and Siebel, see Chapter 8, "Importing Metadata from Oracle Enterprise Applications".

For information about extracting data from SAP systems, see Chapter 7, "Extracting Data from SAP Applications".

Choosing a Connection Technology

Choosing among the different connection technologies available depends upon your specific data source type and other specifics of your use case:

Flexible Code Templates for Data Movement

In some cases, the data movement provided by Oracle-to-Oracle database links, gateways, and ODBC may not be sufficient. Oracle Warehouse Builder 11g Release 2 (11.2) also supports a framework that adds more flexible data movement options based on code templates.

When designing an ETL mapping, you can now choose between Oracle-based mappings and code template-based mappings for more flexible connectivity and data movement. Both mappings offer a rich set of data transformation operators.

When using code template-based mappings, you can select code templates to assign alternative data movement methods for individual mappings or specific parts of mappings. Warehouse Builder generates executable code based on the templates you select, and then deploys that code to a Control Center Agent, where it executes.

The JDBC connectivity for data movement depends on using code template-based mappings, and the data movement code actually executes in the control center agent. You can choose alternative templates for other data movement techniques such as bulk data extraction and loading that fit your use case better. You can also construct new templates that implement data movement methods not supported by Warehouse Builder out of the box.

For more information about using code templates, see Chapter 12, "Using Code Templates to Load and Transfer Data". Also see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more details on code templates-based ETL mappings.