Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







MetaBase scripting for the
Oracle data warehouse DBA

Donald K. Burleson
06 Jul 2005
One of the most challenging areas for any Oracle DBA is the management of complex job streams. Fortunately, Oracle has come to the rescue with the dbms_scheduler package and the Oracle Warehouse Builder MetaBase Plus (OMB) scripting language.

For Oracle data warehouse administration, the Oracle Warehouse Builder product offers OMB as a way to integrate all Extract, Transform and Load (ETL) activities by providing built-in tools that provide error-checking, validation and data mappings. It's sort of like a TP monitor (CICS) for the OWB environment.

While Oracle has made great headway with the GUI interfaces in Oracle 10g Enterprise Manager, senior Oracle professionals still feel the need to use more-robust scripting environments. A GUI can't do it all, and advanced operations require more flexibility and sophisticated job control logic:

  • Scheduled execution -- Fire-off job streams at pre-determined times, with pre-defined prerequisite conditions. Ensure that a "missed" job is re-scheduled.
  • Conditional execution -- execute this task based on the status of completed tasks.
  • Error alerts and job stream validation -- The Oracle DBA needs to define the scope of everything that might go-wrong and create user-exits to pause execution until important issues are resolved.

The Oracle data warehouse professional has two choices for managing the ETL for their data warehouse:

  • Do It Yourself (DIY) -- If the Oracle DBA already understands how to create batch streams, do error-checking and enforce conditional execution, writing your own ETL is an option. The Oracle10g dbms_scheduler package can be used to schedule job-streams, or the traditional method of OS shell scripts is sometimes used. The DIY approach has more flexibility and you can choose best-of-breed approaches and techniques, rather than relying on the OWB offerings. Dr. Tim Hall has an excellent book on the jobstream subject titled "Oracle Job Scheduling", a comprehensive reference for performing complex job executions in an Oracle environment.


  • Use Oracle MetaBase -- OMB is a extension of the Tcl language (pronounced "tickle") and it has all of the robust features of any programming language, including complex Boolean operators, variable support, and loops (FOR, WHILE, REPEAT-UNTIL). The Oracle warehouse Tcl extensions are customized into OMB, so you can perform complex data mappings and program job streams. Like Java, OMB is platform independent and it requires no changes to run MetaBase on a Mainframe or a Macintosh.


Of course, MetaBase requires you to use the extra-cost Oracle Warehouse Builder to manage your ETL, but the OWB OMB scripting language is an attractive alternative to the do-it-yourself approach, depending upon your level of ETL experience.

Inside OMB syntax

Let's take a closer look at the OMB environment. OMB is very much like SQL*Plus and it has a list of built-in commands that can be invoked to manage OWB data mappings. These data mappings can be nested (a hierarchical structure) and OMB offers commands to allow you to invoke these mappings from a Tcl program. There are two types of OMB commands:

  • Metadata Definition Language (MDL) -- Like DDL, it allows you to create and drop OMB objects. MDL object types include projects, modules, tables, mappings, and workflow processes.
    • OMBALTER -- This is used to alter the metadata for a Warehouse Builder component. In the example below, note the UNIX-like backslash commend continuation characters:
      ombalter mapping 'MAP_NAME' modify operator 'JOIN' 
      set properties join_condition) 
      values ('fact.empno = emp.empno) 
    • OMBCREATE -- use this command to create a component in OWB, which might be a project, module, table, mapping, or workflow processes.
    • OMBDROP -- Used to drop named objects from the repository.


  • Metadata Manipulation Language (MML) -- Analogous to Oracle DML, the MML commands allow us to alter (add, update, delete) named objects. Some of the MML commands include:
    • OMBCC -- This is the "change context" command and it is used like the UNIX and DOS "cd" command to change OWB directories.
    • OMBCOMMIT -- Commits a transaction
    • OMBCOPY -- This is a handy command for cloning a section of use this command to copy one or more objects of the same type.
      ombcopy 'AN_EXTSTING_MAP' to 'A_NW_MAP' replacement
    • OMBDEPLOY -- This deploys an object to a specified database via the OWB runtime tables.
    • OMBLIST -- the OMBLIST command is like a directory listing command (ls or dir) and it lists all OWB objects under a specific hierarchical tree.
    • OMBRECONCILE -- This is used to reconcile the target OWB metadata definition with the target database metadata definition.
According to the ODTUG paper "Using Oracle Metabase Plus Language To Build And Deploy Mappings And Workflows" using Oracle's MetaBase is an attractive alternative to traditional job stream applications and it shows working examples of Tcl with the MetaBase extensions: Similarly, the example below shows the deployment of OWB process flows to the Oracle Workflow engine. First, it connects to the repository and it changes the context to STUDENT_YEAR_4 module. It then retrieves all the workflow packages starting with a particular naming pattern. The inner loop also controls as to what group of workflow should be deployed.
proc owb_deploy_workflows_all {} {
 set i 1
 foreach ModName $ModList {
    puts "Accessing WF package: $ModName"
    OMBCC '$ModName'
    set J 1
    foreach mapName $mapList {
       puts " deploying: $mapName"
       incr J
    OMBCC '..'
    incr i
 OMBCC '..'


Will OMB take-off as the ETL scripting language of choice for the Oracle warehouse DBA? Since OWB usually comes with a data warehouse license, it's essentially a free tool, and any data warehouse DBA will want the robust features of being able to manage OWB from a non-GUI environment. The only downside, of course, is the requirement to learn the Tcl language.


About the Author

Donald K. Burleson has been a DBA for more than 20 years and specialized in Oracle performance tuning. The author of more than 30 Oracle books, Burleson provides Oracle consulting at and remote Oracle support at



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational