10 Scripting and Automation

This section introduces OMB*Plus and Experts, the scripting and automation features that contribute to making Oracle Warehouse Builder an extensible platform.

This section contains the following topics:

OMB*Plus Concepts

The OMB*Plus scripting language is built around a Java extension of the Tcl language called Jacl. OMB*Plus provides Tcl-based basic language features such as variables, procedures, and control structures. Additionally, Oracle Warehouse Builder-specific command syntax provides scripting commands for batch processing and for manipulating the user interface in Oracle Warehouse Builder:

  • The OMB-prefixed commands ('B' for batch component).

  • The OMU-prefixed commands ('U' for UI component).

You can write syntactic constructs such as variable support, conditional and looping control structures, error handling, and standard library procedures. You can also use Tcl to perform tasks on the host that is running Design Center, such as file system I/O.

A key benefit of OMB*Plus is its extensibility. For example, you can execute SQL*Plus statements and use PL/SQL packages from OMB*Plus.

You can use the OMB*Plus scripting interface to:

  • Perform complex actions directly in Oracle Warehouse Builder, without starting Oracle Warehouse Builder client user interface.

  • Define sets of routine operations to be executed in Oracle Warehouse Builder.

  • Query Oracle Warehouse Builder repository metadata and report or act on the results.

  • Perform Oracle Warehouse Builder batch operations.

  • Automate a series of conditional operations in Oracle Warehouse Builder.

OMB*Plus is accessed as described in these topics:

OMB*Plus from the Design Center

From within Oracle Warehouse Builder you can access OMB*Plus from the Design Center as follows:

  • Select View, then OMB*Plus.

The Design Center displays the OMB*Plus panel.


  • When running OMB*Plus from within the Design Center, OMU commands are disallowed. They can only be used when running OMB*Plus from command line mode.

  • You cannot access public_project or its child objects from the OMB*Plus view. Instead, use OMB*Plus from the command line to access these objects.

OMB*Plus from the Command Line

You start the OMB*Plus console according to the procedure specific to your operating system.

OMB*Plus and Windows Systems

To start OMB*Plus on Windows systems, you run OMB*Plus from the Start menu under the Warehouse Builder program group.

  • Select Start, then Warehouse Builder and Administration, and then OMB*Plus.

To access the Oracle Warehouse Builder repository, you use the OMBCONNECT statement.

OMB*Plus and Linux and UNIX

To start OMB*Plus on Linux and UNIX systems, you run OMBPlus.sh from the command prompt under the Oracle home.

To access the Oracle Warehouse Builder repository, you use the OMBCONNECT statement.

Overview of Experts in Oracle Warehouse Builder

Experts are mini-applications or task-flows that developers build to perform specific tasks in Oracle Warehouse Builder. Experts enable you to capture your knowledge, expertise, and best practices in Oracle Warehouse Builder and make them repeatable. The most basic use for Experts is to guide a user through a sequence of related tasks in Design Center. Experts enable the reuse the graphical UI components and capabilities of Oracle Warehouse Builder. For example, the Flat File Sampler Wizard in Oracle Warehouse Builder may be reused as a component within an Expert, where the developer needs the user to define the structure of a file. The developer can have full control over how the information and metadata that is returned by the wizard is used, and can call other components, write and embed scripts, call SQL or Java, and so forth.

Uses for Experts include:

  • Encapsulating common practices, to ensure that developers implement tasks in a consistent fashion.

  • Automating the performance of complex tasks to increase developer productivity.

  • Creating user interfaces to functionality implemented as OMB*Plus scripts or in Java.

Combined with the OMB*Plus scripting features and other extension mechanisms, Experts facilitate using Oracle Warehouse Builder as a platform for delivering complex, reusable data integration solutions.

This section includes these additional topics about Experts:

See Also:

The chapter "About Experts" in Oracle Warehouse Builder API and Scripting Reference for details and procedures for developing Experts.

Design Environment for Experts

The design environment for Experts is in relation of projects in Design Center, primarily with the Expert Editor, which is similar to the environment for process flows: a graph in which task objects are represented by icons, flow of control is represented by connecting lines, and design activities are performed by drag-and-drop and setting up object properties. Tasks can include creating or modifying objects in your projects, invoking commonly used built-in dialogs, creating custom dialog boxes, and invoking OMB*Plus scripts for complex flow of control and interaction with Oracle Warehouse Builder. Variables are available to preserve state among tasks in an Expert. Nested Experts are also supported, so that logic implemented in one Expert can be called in others.

Experts can also invoke arbitrary Java code in JAR files that you set up to be loaded with Oracle Warehouse Builder. As a result, you can implement new functionality in Java and integrate it into the Oracle Warehouse Builder user interface.

At run time, Experts follow the same paradigm as traditional Oracle Warehouse Builder functionality, in that code (in this case, OMB and OMU scripts) is generated from the model/logical design, and is then executed.

Storage of Experts: Public and Private

In a workspace, Experts can be created in a public Experts folder or in the private Experts folder that is included in each project. Private Experts are typically used only in relation of its parent project. As with other public objects, public Experts are stored in the globals area and can be used across all projects in the workspace. Public Experts typically encapsulate functionality that is useful across different types of projects.

Depending upon how they are implemented, Experts can be invoked in several different contexts:

  • Some Experts are designed to be right-clicked and executed from the Experts or Public Experts folder.

  • Experts can be added to the right-click menus associated with most objects in Warehouse Builder, being added as new menu commands in Design Center. In such a case, the Expert executes in a context where the object from which it was invoked is passed as a parameter.

  • Oracle Warehouse Builder can also be started from the command line in a mode that hides Design Center from the user and runs a single Expert, then exits. These two capabilities allow the creation of simplified, constrained interfaces to Oracle Warehouse Builder functionality for users who are not frequent Oracle Warehouse Builder users or ETL developers.

About Guided Assistance Experts

The Guided Assistance feature enables the creation of a set of Experts in an Oracle Warehouse Builder workspace, such that when a user logs in to the workspace, they are immediately offered a point-and-click list of Experts to choose from to perform one of several common tasks. Placing Experts in a folder called STARTUP in the Public Experts folder causes them to appear in the Guided Assistance Experts list. Guided Assistance Experts can also be accessed through a Help menu item in Design Center. Administrators can use this feature to provide developers on a project a set of Experts and encourage their use for consistency and time-saving on common tasks.

See Also:

The topic "Guided Assistance" under "About Experts" in Oracle Warehouse Builder API and Scripting Reference