Skip Headers

Oracle® Warehouse Builder Scripting Reference
10g Release 1 (10.1)

Part Number B12152-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

1 Introduction

OMB Plus is a flexible, high-level command line metadata access tool for Oracle Warehouse Builder . Use OMB Plus to create, modify, delete, and retrieve object metadata in Warehouse Builder design and runtime repositories.

This chapter contains the following topics:

About the OMB Scripting Language

The Warehouse Builder scripting language, known as OMB Plus, is an extension of the Tcl programming language. With OMB Plus, you can write the syntactic constructs such as variable support, conditional and looping control structures, error handling, and standard library procedures.

Use OMB Plus to create, modify, delete, and retrieve object metadata in Warehouse Builder design and runtime repositories.

OMB Plus enables you to edit Warehouse Builder repository metadata using a scripting interface. You can use this scripting interface to:

Using OMB Plus

To use OMB Plus, first launch OMB Plus and then connect to a repository. Type all commands and keywords in OMB Plus in uppercase.

Launching OMB Plus

To launch the OMB Plus console, follow the instructions specific to your operating system.

  • UNIX: At the command prompt, enter:


  • Windows: From the Start Menu, navigate to Warehouse Builder, located within Oracle-OUI . Select OWB OMB Plus from the list of menu items.

Connecting to a Repository

From the OMB Plus console, enter:

OMBCONNECT <repos>/<password>@<host>:<port>:<service_name> 

In the expression above, <repos> is the name of the design-time repository, <host> is the machine on which the repository is installed, and <servicename> is the name of the database that contains the repository.

Writing OMB Plus Commands

An OMB Plus command can span multiple lines. In a multiple line OMB Plus command, you must use only a backward slash (\) at the end of each line.

Keep in mind the following points when you execute OMB Plus commands:

  • Do not use a number sign(#) in the middle of an OMB Plus command.

  • Do not use a backward slash (\) when you specify the full path for the commands that use the full path, such as, OMBIMPORT, OMBVALIDATE, OMBLOG, and so on. For example, in the following commands, the log file is not created.

    set OMBLOG c:\my_project\omb_logfile.log (On Windows)
    set OMBLOG \home\my_project\omb_logfile.log (On Unix)

    On Unix, use a slash as the path separator. For example, the following command creates a log file.

    set OMBLOG /home/my_project/omb_logfile.log

    On Windows, you can use either a forward slash(/) or two backward slashes (\\) as a path separator. Alternately, you can use a backward slash in the path, but in this case, enclose the entire filename in curly braces. The following are examples of commands that you can use to create a log file.

    set OMBLOG c:/my_project/omb_logfile.log
    set OMBLOG c:\\my_project\\omb_logfile.log
    set OMBLOG {c:\my_project\omb_logfile.log}
  • Do not use a semi-colon(;) in a quoted string. A semi-colon is special character that is used to separate two commands. Thus, using a semi-colon in a quoted string results in an error. The work around you can use for this is to escape the semi-colon character by putting a backward slash (\) in front of the semi-colon. For example,


  • You can set the value of a Boolean configuration property, for example the IS_DELIMITED property in the example, using any one of the following values: TRUE, FALSE, YES, NO, 1, or 0. When you set the value to TRUE, FALSE, YES, or NO, enclose the value in single quotes. When you specify numeric values for a configuration property, do not enclose the values in single quotes.

Running Scripts in OMB Plus

You can write scripts and run them in OMB Plus. For examples of scripts you can write, see "Sample OMB Plus Scripts".

Inside the interactive shell, type source test.tcl where ‘test' is the name of the script you want to run.

At the command line, type test.tcl for scripts on UNIX and OMBPlus.bat test.tcl for scripts on Windows operating systems.

Locating Errors in Scripts and Multi-line Commands

OMB Plus reports only the first error it encounters while executing a command. As soon as it encounters the first error, it stops processing the command and exits reporting the error.

When an error occurs during the execution a multi-line OMB Plus command, the error message that is displayed does not specify the exact line at which the error occurred. To determine the line at which the error occurred, use the following command immediately after you encounter an error:

OMB+> puts $errorInfo

Predefined Tcl Procedures

You can use the predefined Tcl procedures in OMB Plus:

  • OMBToSettableString: Use this procedure when setting string values that contain single quotes that need to be escaped. The input for this procedure us a Tcl string and the output is a Tcl string with all single-quotes escaped.

  • OMBToTypeObjListString: This procedure converts an input two-dimensional list to a comma-separated string. For example, the procedure converts input in the form of

    {{<object_type> <name>} ... }


    "<object_type> <name>,...".
  • OMBPageBreak: This procedure displays the input string as a sequence of pages, with a pause after each page. When the output of a command is more than the page height, it may be difficult for screen reading software (used for accessibility) to read the whole text. This procedure may be used to break the output of a command into pages.

    The two inputs to the OMBPageBreak command are the number of lines to be displayed in a page and the string that is to be split into pages. The string may be the output of an OMB Plus command. For example, the following command displays the output of the OMBHELP OMBCREATE command with 10 lines per page.


    To display the next 10 lines of the output, press <Enter> on your keyboard.

    The OMPageBreak procedure is available for every OMB Plus session.

OMB Plus Commands

The sections that follow describe the types of commands that comprise the OMB Scripting Language.

Metadata Manipulation Language (MML) Commands

OMB Plus enables you to create, modify, delete, and retrieve object metadata in Warehouse Builder design and runtime repositories. OMB Plus commands work within the context of a first class object. For a list of first class objects, see "Warehouse Builder Metadata Objects".

Table 1–1 lists the standard command names for MML.

Table 1-1 Metadata Manipulation Language Commands

Metadata Manipulation Language (MML) Description
OMBCREATE Creates a first class object.
OMBDROP Deletes a first class object.
OMBCREATE Modifies a first class object.
OMBRETRIEVE Retrieves information from a first class object.

OMB Plus executes commands like OMBCREATE, OMBCREATE, and OMBDROP within a nested transaction.

OMB Plus interprets clauses within a single command one by one, as illustrated by the following example:

      ADD UNIQUE_KEY 'UK1' \ 
         SET REF COLUMNS ('C1_NEW', 'C2') 

In the above example, OMB Plus renames column C1 to C1_NEW when parsing the modify_column clause. In the last line, use the new name for the column, C1_NEW, to specify the referenced columns for the new unique key. For more details about synchronization of cached data, see "Synchronizing Cached Data with Repository Objects"

The OMBCREATE and OMBRETRIEVE commands synchronize only the first content object that they are currently working on. The OMBCREATE command synchronizes only the parent folder.


The following example lists the high-level scripting command syntax definitions for the OMBCREATE command:

OMBCREATE <fco_type> <fco_name> ( [ rename_clause ] [ properties_clause ] [ [ sco_add_clause_for_alter ] | [ sco_modify_clause ] | [ sco_delete_clause ] ]* )1
rename_clause ::= RENAME TO <new_name>
sco_add_clause_for_alter ::= ADD <sco_type> <sco_name> [ OF parent_sco_clause ] [ AT POSITION <position> ] [ properties_clause ] [ references_clause ]* 
sco_modify_clause ::= MODIFY <sco_type> <sco_name> [ OF parent_sco_clause ] ( [ rename_clause ] [ move_to_clause ] [ properties_clause ] [ references_clause ]* )1
move_to_clause ::= MOVE TO POSITION <position>
sco_delete_clause ::= DELETE <sco_type> <sco_name> [ OF parent_sco_clause ] 

In the above example, the number 1 following a group of clauses enclosed by ( ) brackets indicates that you must specify at least one of the clauses.

You can specify a particular Warehouse Builder object by tracing the aggregation relationship from its parent first class object. You can also capture the association relationships by the references clauses. For example, getSCOClause, where sco_type is the second class object type.

Each action, create, alter, drop, or retrieve works only on the properties and the immediate children of the currently specified object. For example, the retrieve command on a table only enables you to access the properties of the table and the lists of column and constraint names owned by that table. To drill down to the detailed descriptions of the columns and constraints, you can call retrieve on these objects respectively.

The following statement retrieves the datatype and length for a column in a view:


When you set and retrieve properties using the set_properties_clause and the get_properties_clause, you can type the property names in any order.

Physical names are used as object identifiers in scripting. Business names represent an object property. Business names are not used to identify objects. You can identify a cross-component first class object by a path notation.



../<module_name> <fco_name>

String values, including object names and string property values, must be enclosed in single quotes.

Warehouse Builder Metadata Objects

Use OMB Plus to access and manipulate the following Warehouse Builder objects, also known as first class objects:

Table 1-2 Warehouse Builder Metadata Objects

  • Advanced Queues
  • Functions
  • Process Flows
  • Collections
  • Gateway_Modules
  • Process FLow Modules
  • Connectors
  • Locations
  • Process Flow Packages
  • Cube Tables
  • Mappings
  • Projects
  • Deployment Action Plans
  • Materialized Views
  • Runtime Repository Connections
  • Dimension Tables
  • Object Types
  • SAP Modules
  • External Tables
  • Oracle Modules
  • Sequences
  • Flat File
  • Packages
  • Snapshots
  • Flat File Modules
  • Procedures
  • Tables

For Oracle Modules, you can access only those Oracle modules designated as warehouse modules. You cannot access Oracle source modules using OMB Plus.

Shell Commands

Shell commands provide you with an interactive interface to run all Warehouse Builder scripts and standard Tcl commands. OMB Plus shell commands include: OMBHELP, OMBCC, OMBDCC, and OMBENV.


Use the OMBHELP command to display help on Warehouse Builder commands. The help describes the purpose of the command, the syntax in BNF format, and each of the keywords or options. For complex commands such as OMBCREATE, OMBCREATE, and OMBRETRIEVE, you can specify an optional fco_type parameter. OMBHELP then displays the detailed syntax for that particular parameter type. Each command also provides specific options that enable you to display sub-sections of the help page.

The syntax for OMBHELP is:

help ::= OMBHELP <command_name> [ <command_specific_options> ] [DETAIL]

For example, OMBHELP OMBCONNECT displays the following:

To connect to OWB repository.
OMBCONNECT <user>/<password>@<host:port:SID>
   <user> is the OWB repository user name
   <password> is the OWB repository user password
   <host> is the name or IP address of the OWB repository host machine
   <port> is the numeric port for OWB repository database listener 
   <SID> is the unique database identifier for OWB repository database
   The connection to OWB repository will be established in single user mode.

If you type OMBHELP <command_ name> followed by [DETAIL], OMB Plus displays the command purpose, prerequisites, syntax, descriptions for each keyword and parameter, and examples of how to use the command.

The OMBHELP command synchronizes only the only the FCO that you are currently working on.


The syntax for OMBENV is:

environment ::= OMBENV

This command lists the values for all Warehouse Builder-specific environment variables. Table 1–3 lists the environmental variables. To set an environmental variable, use the Tcl set command. Use unset to unset an environmental variable.

Table 1-3 Warehouse Builder Environment Variables

Environment Variable Meaning Possible Values
OMBTIMER Enables timing on each Warehouse Builder scripting command. The time is logged to a log file and to the console or shell. A Tcl boolean value.
OMBLOG Stores the filename for Warehouse Builder log file. A valid filename including its path.
OMBPROMPT Indicates whether OMB Plus will update the command prompt each time the you call OMBCC. A Tcl boolean value.
OMBCONTINUE_ON_ERROR Ignores errors that occur in any command that is part of a script and moves to the next command in the script. A Tcl boolean value.

Administrative Commands

Use these commands to perform administrative jobs on a Warehouse Builder repository. The following commands are available: OMBCONNECT, OMBDISCONNECT, OMBCOMMIT, and OMBROLLBACK.

connect ::= OMBCONNECT <username>/<password>@<host>:<port>:<sid>
disconnect ::= OMBDISCONNECT
commit ::= OMBCOMMIT
rollback ::= OMBROLLBACK

Navigation Commands

You can use the following commands to navigate the Warehouse Builder repository in the same way you navigate a UNIX file system.


This command enables users to change context Up and Down the Warehouse Builder navigation tree. For example, when you type ... the current context changes to the parent context. However, if the current context is a modified project, an error message prompts you to commit or rollback your changes.


This command shows you the current context and the context type. The syntax for OMBDCC is:

display_current_context ::= OMBDCC


The child first class objects for folders are listed under OMBLIST. Using this command on folders describes only the folder properties. Note also that the list command allows name matching by regular expression. If you do not include the regular expression, then OMBLIST displays all objects sorted alphabetically.

The generic syntax for OMBLIST in a folder context is:

list_folder ::= OMBLIST ( <child_type1_plural> | …| <child_typeN_plural> ) [ name_in_regexp ] 
name_in_regexp ::= a name in regular expression.

For example, under the root context you have:

list_root ::= OMBLIST PROJECTS [ name_in_regexp ]

The OMBLIST command synchronizes all parent-child relations in the navigation tree.

Service Commands

Service commands perform services like batch operations on Warehouse Builder metadata. Table 1–4 contains a list of service commands and their descriptions.

Table 1-4 Service Commands

Command Description
OMBCOMPILE Use this command to compile folders or first class objects such as tables, views, sequences, dimensions, and cubes.
OMBDEPLOY This command provides deployment service.
OMBIMPORT This command provides the metadata import service. You can only invoke the OMBIMPORT command from the root context.

The four available modes are: CREATE_MODE (CREATE), REPLACE_MODE (REPLACE), UPDATE_MODE (UPDATE), and MERGE_MODE (INCREMENTALUPDATE). The default mode, if not specified in the command, is CREATE_MODE.

OMBVALIDATE Use this command to validate folders or first class objects such as tables, views, sequences, dimensions, and cubes.

Synchronizing Cached Data with Repository Objects

When you start an OMB Plus session, data about the objects is fetched from the OWB repository and cached in the OMB Plus session. The cached data is synchronized with the data from the repository at certain predefined intervals.

The extent to which the objects are synchronized depends on the OMB Plus command that you execute. For example, some commands synchronize all the parent-child relationships in the navigation tree, whereas some commands synchronize only the first class object that they are currently working on.

  • The OMBLIST command synchronizes all the parent-child relationships in the navigation tree.

  • The OMBCREATE and OMBRETRIEVE commands synchronize only the first class object that they are currently working on.

  • The OMBCREATE command synchronizes only the parent folder.

Consider the following example on synchronization of cached data. You open an OMB Plus session as well as the OWB Client. In the OWB Client, you delete a flat file module called FFM1. You then undelete FFM1 and commit the changes. In the OMB Plus session, you perform the sequence of operations listed below. The details of the result of the operation and the logic behind the result is explained below.


    The context is changed to the module FFM1.


    The flat file is not created because OMBCREATE synchronizes the parent folder. When the synchronization is performed, the parent folder is not found in the cache.


    FFM1 is listed as one of the modules. This is because OMBLIST synchronizes all the parent-child relationships in the navigation tree.


    The flat file is created. This is because the undelete is now reflected in the cache because of the OMBLIST command.

How to Read Syntax Diagrams

Syntax diagrams are drawings that illustrate valid SQL syntax. To read a diagram, trace it from left to right, in the direction shown by the arrows. Commands and other keywords appear in UPPERCASE inside rectangles. Type them exactly as shown in the rectangles. Parameters appear in lowercase inside ovals. Variables are used for the parameters. Punctuation, operators, delimiters, and terminators appear inside circles.

If the syntax diagram has more than one path, you can choose any path to travel. For example, Figure 1-1 shows a syntax diagram that indicates you can specify either ADD,MODIFY,or DELETE:

Figure 1-1 Syntax Diagram with Multiple Paths

Description of alterTableConstraintClauses.gif is in surrounding text

If you have the choice of more than one keyword, operator, or parameter, syntax diagrams display the options in a vertical list. For example, in the syntax diagram shown in Figure 1-2, you can specify one or more of the multiple parameters in the stack:

Figure 1-2 Syntax Diagram with Multiple Parameters

Description of addActivityClause.gif is in surrounding text

Required Keywords and Parameters

Required keywords and parameters can appear singly or in a vertical list of alternatives. Single required keywords and parameters appear on the main path - that is, on the horizontal line you are currently traveling.

If multiple keywords or parameters appear in a vertical list that intersects the main path, one of them is required. You must choose one of the keywords or parameters, but not necessarily the one that appears on the main path. In the example shown in Figure 1-3, select one of the displayed settings:

Figure 1-3 Syntax Diagram with Multiple Choices for a Required Parameter

Description of propertyValue.gif is in surrounding text

Optional Keywords and Parameters

If keywords and parameters appear in a vertical list above the main path, they are optional.

Syntax Loops

Loops enable you to repeat the syntax within them as many times as you like. In the example in Figure 1-4, you can choose one property value and repeatedly to choose another. Separate your selections by commas.

Figure 1-4 Syntax Diagram with a Syntax Loop

Description of propertyValueList.gif is in surrounding text

Sample OMB Plus Scripts

The body of the Scripting Reference contains examples for almost every reference topic. This section contains lengthy examples that are appropriate in the context of a single OMB command statement. These examples are intended to provide uninterrupted the series of steps that you would use to take advantage of particular Warehouse Builder functionality. They do not replace the syntax diagrams and semantics found for each individual OMB command statement in the body of the reference.

This section includes sample scripts for the following tasks:

Updating a Design Repository

One possible use case is to perform mass update on repository metadata. Users can write the following script to add a primary key with local column ID for each table with name beginning in EDW inside the module MY_MODULE:

foreach tableName [OMBLIST TABLE EDW*] { \
OMBCREATE TABLE '$tableName' \

We can build even more powerful and useful script using if-then-else:

foreach tableName [OMBLIST TABLE EDW*] { \
set columnList [OMBRETRIEVE TABLE '$tableName' GET COLUMNS]; # Use lsearch to search for a name in a list 
if {[lsearch $columnList 'ID'] == -1} {
      OMBCREATE TABLE '$tableName' \
         ADD COLUMN 'ID' \
               ('NUMBER', 10, 'true');

The above script checks the list of tables which name begins with EDW whether each of them contains an ID column. If not, it will create an ID column for that table. Hence, executing the above script will guarantee that all tables with names beginning in EDW will have the ID column defined.

Reporting on Repository Objects

Another common use is for reporting purpose. The following script displays the properties of the table T1 and its column definitions on standard output:

#Displaying metadata of a table
puts -nonewline "Please enter the table name: " gets stdin tableName
puts ""
puts "Report on $tableName"
puts "======================================="
puts "Physical name = $tableName"
puts "Logical name = [lindex [OMBRETRIEVE TABLE '$tableName' GET \ PROPERTIES(BUSINESS_NAME)] 0]"
puts "Description = [lindex [OMBRETRIEVE TABLE '$tableName' GET \ PROPERTIES(DESCRIPTION)] 0]"
puts "---------------------------------------"
set columnList [OMBRETRIEVE TABLE '$tableName' GET COLUMNS]
set i 1
foreach colName $columnList {
set dt [lindex [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET \ PROPERTIES(DATATYPE)] 0]
   if { $dt == "VARCHAR2" } {
      puts "Column $i: $colName datatype=VARCHAR2 length=[lindex $prop 0] \
           not_null=[lindex $prop 1]"
   } elseif { $dt == "NUMBER" } {
      set prop [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' \
      puts "Column $i: $colName datatype=NUMBER precision=[lindex $prop 0] \
          scale=[lindex $prop 1] not_null=[lindex $prop 2]"
   } elseif { $dt == "DATE" } {
      set prop [OMBRETRIEVE TABLE '$tableName' COLUMN '$colName' GET \ PROPERTIES(NOT_NULL)]
      puts "Column $i: $colName datatype=DATE not_null=[lindex $prop 0]"
   } # end else
   incr i

A sample output is like the following:

Physical name = T1
Logical name = Table 1
Description = This is my first table.
Column: ID datatype=NUMBER precision=0 scale=0 not_null=1
Column: NAME datatype=VARCHAR2 length=30 not_null=1
Column: VALUE datatype=VARCHAR2 length=100 not_null=0 

Finding Invalid Objects

Users can also take advantage of the validation service provided by scripting, like this:

set tableList [OMBLIST TABLES];
foreach tableName $tableList {
   if { [OMBCOMPILE TABLE '$tableName]' == "Invalid." } {
      set context [OMBDCC];
      puts "Table $context/$tableName is invalid.";

The above script will tell users which table is invalid under the current module.

Using OMB Plus to Navigate Repositories

Another scenario we present below is for a disabled user that relies on OMB Plus interactive shell (and also some ADA screen reading software) to navigate through a Warehouse Builder repository:

OMB+> OMBCONNECT owb/owb@localhost:1521:dev901
Context changed.
Context changed.
Context changed.
Context changed.