Contents

Title and Copyright Information

Preface

• Purpose
• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Part I Application Programming Interfaces

1 Public Views for the Runtime Environment

• Warehouse Builder Runtime Repository Public Views
• Deployment Auditing Views
• Execution Auditing Views

2 Public Views for the Design Environment

• Warehouse Builder Design Repository Public Views
• General Model Views
• Data Model Views
• Flat Files Views
• Collections Views
• Function Model Views
• Configuration Model Views
• Deployment Model Views
• Mapping Model Views
• Process Flow Model Views
• Data Profiling Views
• Data Rules Views
• User Defined Object Views
• Expert Views
• Business Intelligence Views
• Real Time Views
• Scheduling Views
• Security Views
• Others

3 Using SQL*Plus to Schedule and Execute Jobs

• Managing Jobs Using SQL Scripts
• Starting ETL Jobs in SQL*Plus
• Scheduling ETL Jobs in Oracle Enterprise Manager
  • The SQLPLUS_EXEC_TEMPLATE SQL Script
  • The WB_RT_API_EXEC.RUN_TASK Function
• Managing a Control Center

4 Implementing Best Practices

• What Are Experts?
  • User's View of an Expert
  • Developer's View of an Expert
  • How Are Experts Different From Process Flows?
• How to Create Experts
• Creating an Expert Object
• Adding Tasks to the Expert Canvas
• Adding Nested Experts to the Expert Canvas
• Adding Transitions to the Expert Canvas
• Passing Data Values Among Tasks
  • Binding Input Parameters
  • Using Constants
  • Using Variables
• Validating, Generating, and Starting Experts
• Creating a Development Environment
• Publishing Experts
• Running an Expert From a Batch File

Part II OMB Plus Scripting Language

5 Introduction to OMB Plus

• About the OMB Scripting Language
  • Using OMB Plus
    • Launching OMB Plus
    • Connecting to a Repository
    • Getting Help for OMB Plus Commands
  • Writing OMB Plus Commands
    • Specifying Values
    • Special Characters
    • Escaping Special Characters and Writing Complex Arguments
    • Specifying Paths
    • Predefined Tcl Procedures
  • Running Scripts in OMB Plus
    • Locating Errors in Scripts and Multi-line Commands
• OMB Plus Commands
  • Metadata Manipulation Language (MML) Commands
  • Examples
    • Warehouse Builder Metadata Objects
  • Shell Commands
    • OMBHELP
    • OMBENV
  • Administrative Commands
  • Navigation Commands
    • OMBCC
    • OMBDCC
    • OMBLIST
  • Service Commands
    • Synchronizing Cached Data with Repository Objects
    • Changing the User Mode
• Sample OMB Plus Scripts
  • Updating a Design Repository
  • Reporting on Repository Objects
  • Finding Invalid Objects
  • Using OMB Plus to Navigate Repositories
• New to OMB Plus in This Release
  • Commands Introduced in This Release
  • Changes to the OMB Plus Syntax

6 OMB Commands

• OMBCAC
• OMBCC
• OMBCOMMIT
• OMBCOMPARE SNAPSHOT
• OMBCOMPILE
• OMBCONN
• OMBCONNECT
• OMBCONNECT CONTROL_CENTER
• OMBCONN CONTROL_CENTER
• OMBCOPY
• OMBDAC
• OMBDCC
• OMBDEBUG MAPPING
• OMBDEFINE ASSOCIATION_DEFINITION
• OMBDEFINE CLASS_DEFINITION
• OMBDEFINE COMPONENT_DEFINITION
• OMBDEFINE DOMAIN_DEFINITION
• OMBDEFINE FOLDER_DEFINITION
• OMBDEINSTALL OWB_REPOSITORY
• OMBDEINSTALL WORKSPACE
• OMBDEPLOY
• OMBDERIVE
• OMBDESCRIBE ASSOCIATION_DEFINITION
• OMBDESCRIBE CLASS_DEFINITION
• OMBDESCRIBE MODEL
• OMBDISC
• OMBDISCONNECT
• OMBDISCONNECT CONTROL_CENTER
• OMBDISC CONTROL_CENTER
• OMBDISPLAYCURRENTMODE
• OMBENV
• OMBEXPORT
• OMBEXPORT MDL_FILE
• OMBGRANT DEFAULT_OBJ_PRIV
• OMBGRANT OBJ_PRIV
• OMBGRANT ROLE
• OMBGRANT SYS_PRIV
• OMBHELP
• OMBIMPACT
• OMBIMPORT
• OMBIMPORT MDL_FILE
• OMBIMPORT METADATA_LOCATION
• OMBINSTALL OWB_RAC
• OMBINSTALL OWB_REPOSITORY
• OMBINSTALL WORKSPACE
• OMBLINEAGE
• OMBLIST
• OMBLIST ALL OBJ_PRIVS
• OMBLIST ALL SYS_PRIVS
• OMBLIST DEFAULT_OBJ_PRIVS
• OMBLIST ICONSETS
• OMBLIST OBJ_PRIVS
• OMBLIST ROLES
• OMBLIST SNAPSHOT
• OMBLIST SYS_PRIVS
• OMBLIST USERS
• OMBLOCK
• OMBMLSUPDATE
• OMBMOVE
• OMBPROFILE
• OMBREDEFINE ASSOCIATION_DEFINITION
• OMBREDEFINE CLASS_DEFINITION
• OMBREGISTER LOCATION
• OMBREGISTER USER
• OMBRESTORE SNAPSHOT
• OMBREVERT
• OMBREVOKE DEFAULT_OBJ_PRIV
• OMBREVOKE OBJ_PRIV
• OMBREVOKE ROLE
• OMBREVOKE SYS_PRIV
• OMBROLLBACK
• OMBSAVE
• OMBSEED
• OMBSHOW
• OMBSTART
• OMBSTATS
• OMBSTOP
• OMBSWITCHMODE
• OMBSYNCHRONIZE
• OMBTRANSLATE EXTRACT
• OMBTRANSLATE MERGE
• OMBUNDEFINE ASSOCIATION_DEFINITION
• OMBUNDEFINE CLASS_DEFINITION
• OMBUNLOCK
• OMBUNREGISTER CONTROL_CENTER
• OMBUNREGISTER LOCATION
• OMBUNREGISTER USER
• OMBVALIDATE

7 OMBALTER to OMBALTER DRILL_TO_DETAIL

• OMBALTER
• OMBALTER ACTIVITY_TEMPLATE_FOLDER
• OMBALTER ACTIVITY_TEMPLATE
• OMBALTER ADVANCED_QUEUE
• OMBALTER ALTERNATIVE_SORT_ORDER
• OMBALTER ANALYZE_ACTION_PLAN
• OMBALTER BUSINESS_AREA
• OMBALTER BUSINESS_DEFINITION_MODULE
• OMBALTER BUSINESS_PRESENTATION_MODULE
• OMBALTER CALENDAR
• OMBALTER CALENDAR_MODULE
• OMBALTER CHANGE_DATA_CAPTURE
• OMBALTER CMI_DEFINITION
• OMBALTER CMI_MODULE
• OMBALTER COLLECTION
• OMBALTER CONFIGURATION
• OMBALTER CONNECTOR
• OMBALTER CONTROL_CENTER
• OMBALTER CORRECTION_MAPS_ACTION_PLAN
• OMBALTER CORRECTION_SCHEMA_ACTION_PLAN
• OMBALTER CUBE
• OMBALTER DATA_AUDITOR
• OMBALTER DATA_PROFILE
• OMBALTER DATA_RULE
• OMBALTER DATA_RULE_MODULE
• OMBALTER DEPLOYMENT_ACTION_PLAN
• OMBALTER DEPLOYMENT
• OMBALTER DIMENSION
• OMBALTER DRILL_PATH
• OMBALTER DRILL_TO_DETAIL

8 OMBALTER EXPERT to OMBALTER QUEUE_TABLE

• OMBALTER EXPERT
• OMBALTER EXPERT_MODULE
• OMBALTER EXTERNAL_TABLE
• OMBALTER FLAT_FILE
• OMBALTER FLAT_FILE_MODULE
• OMBALTER FUNCTION
• OMBALTER GATEWAY_MODULE
• OMBALTER ICONSET
• OMBALTER IMPORT_ACTION_PLAN
• OMBALTER ITEM_FOLDER
• OMBALTER LIST_OF_VALUES
• OMBALTER LOCATION
• OMBALTER MAPPING
• OMBALTER MATERIALIZED_VIEW
• OMBALTER MDL_ACTION_PLAN
• OMBALTER NESTED_TABLE
• OMBALTER OBJECT_TYPE
• OMBALTER ORACLE_MODULE
• OMBALTER PACKAGE
• OMBALTER PLSQL_RECORD_TYPE
• OMBALTER PLSQL_REF_CURSOR_TYPE
• OMBALTER PLSQL_TABLE_TYPE
• OMBALTER PLUGGABLE_MAPPING_FOLDER
• OMBALTER PLUGGABLE_MAPPING
• OMBALTER PRESENTATION_TEMPLATE
• OMBALTER PROCEDURE
• OMBALTER PROCESS_FLOW
• OMBALTER PROCESS_FLOW_MODULE
• OMBALTER PROCESS_FLOW_PACKAGE
• OMBALTER PROFILE_REFERENCE
• OMBALTER PROJECT
• OMBALTER QUEUE_PROPAGATION
• OMBALTER QUEUE_TABLE

9 OMBALTER REAL_TIME_MAPPING to OMBALTER VIEW

• OMBALTER REAL_TIME_MAPPING
• OMBALTER REGISTERED_FUNCTION
• OMBALTER ROLE
• OMBALTER SAP_MODULE
• OMBALTER SEQUENCE
• OMBALTER SNAPSHOT
• OMBALTER STREAMS_CAPTURE_PROCESS
• OMBALTER STREAMS_QUEUE
• OMBALTER TABLE_FUNCTION
• OMBALTER TABLE
• OMBALTER TIME_DIMENSION
• OMBALTER TRANSPORTABLE_MODULE
• OMBALTER USER
• OMBALTER VARYING_ARRAY
• OMBALTER VIEW

10 OMBCREATE to OMBCREATE MAPPING

• OMBCREATE
• OMBCREATE ACTIVITY_TEMPLATE_FOLDER
• OMBCREATE ACTIVITY_TEMPLATE
• OMBCREATE ADVANCED_QUEUE
• OMBCREATE ALTERNATIVE_SORT_ORDER
• OMBCREATE ANALYZE_ACTION_PLAN
• OMBCREATE BUSINESS_AREA
• OMBCREATE BUSINESS_DEFINITION_MODULE
• OMBCREATE BUSINESS_PRESENTATION_MODULE
• OMBCREATE CALENDAR
• OMBCREATE CALENDAR_MODULE
• OMBCREATE CHANGE_DATA_CAPTURE
• OMBCREATE CMI_DEFINITION
• OMBCREATE CMI_MODULE
• OMBCREATE COLLECTION
• OMBCREATE CONFIGURATION
• OMBCREATE CONNECTOR
• OMBCREATE CONTROL_CENTER
• OMBCREATE CORRECTION_MAPS_ACTION_PLAN
• OMBCREATE CORRECTION_SCHEMA_ACTION_PLAN
• OMBCREATE CUBE
• OMBCREATE DATA_AUDITOR
• OMBCREATE DATA_PROFILE
• OMBCREATE DATA_RULE
• OMBCREATE DATA_RULE_MODULE
• OMBCREATE DEPLOYMENT_ACTION_PLAN
• OMBCREATE DEPLOYMENT
• OMBCREATE DIMENSION
• OMBCREATE DRILL_PATH
• OMBCREATE DRILL_TO_DETAIL
• OMBCREATE EXPERT
• OMBCREATE EXPERT_MODULE
• OMBCREATE EXTERNAL_TABLE
• OMBCREATE FLAT_FILE
• OMBCREATE FLAT_FILE_MODULE
• OMBCREATE FUNCTION
• OMBCREATE GATEWAY_MODULE
• OMBCREATE ICONSET
• OMBCREATE IMPORT_ACTION_PLAN
• OMBCREATE ITEM_FOLDER
• OMBCREATE LIST_OF_VALUES
• OMBCREATE LOCATION
• OMBCREATE MAPPING

11 OMBCREATE MATERIALIZED_VIEW to OMBCREATE VIEW

• OMBCREATE MATERIALIZED_VIEW
• OMBCREATE MDL_ACTION_PLAN
• OMBCREATE NESTED_TABLE
• OMBCREATE OBJECT_TYPE
• OMBCREATE ORACLE_MODULE
• OMBCREATE PACKAGE
• OMBCREATE PLSQL_RECORD_TYPE
• OMBCREATE PLSQL_REF_CURSOR_TYPE
• OMBCREATE PLSQL_TABLE_TYPE
• OMBCREATE PLUGGABLE_MAPPING_FOLDER
• OMBCREATE PLUGGABLE_MAPPING
• OMBCREATE PRESENTATION_TEMPLATE
• OMBCREATE PROCEDURE
• OMBCREATE PROCESS_FLOW
• OMBCREATE PROCESS_FLOW_MODULE
• OMBCREATE PROCESS_FLOW_PACKAGE
• OMBCREATE PROJECT
• OMBCREATE QUEUE_PROPAGATION
• OMBCREATE QUEUE_TABLE
• OMBCREATE REAL_TIME_MAPPING
• OMBCREATE REGISTERED_FUNCTION
• OMBCREATE ROLE
• OMBCREATE SAP_MODULE
• OMBCREATE SEQUENCE
• OMBCREATE SNAPSHOT
• OMBCREATE STREAMS_CAPTURE_PROCESS
• OMBCREATE STREAMS_QUEUE
• OMBCREATE TABLE_FUNCTION
• OMBCREATE TABLE
• OMBCREATE TIME_DIMENSION
• OMBCREATE TRANSPORTABLE_MODULE
• OMBCREATE VARYING_ARRAY
• OMBCREATE VIEW

12 OMBRETRIEVE to OMBRETRIEVE FLAT_FILE

• OMBRETRIEVE
• OMBRETRIEVE ACTIVITY_TEMPLATE_FOLDER
• OMBRETRIEVE ACTIVITY_TEMPLATE
• OMBRETRIEVE ADVANCED_QUEUE
• OMBRETRIEVE ALTERNATIVE_SORT_ORDER
• OMBRETRIEVE ANALYZE_ACTION_PLAN
• OMBRETRIEVE BUSINESS_AREA
• OMBRETRIEVE BUSINESS_DEFINITION_MODULE
• OMBRETRIEVE BUSINESS_PRESENTATION_MODULE
• OMBRETRIEVE CALENDAR
• OMBRETRIEVE CALENDAR_MODULE
• OMBRETRIEVE CHANGE_DATA_CAPTURE
• OMBRETRIEVE CMI_DEFINITION
• OMBRETRIEVE CMI_MODULE
• OMBRETRIEVE COLLECTION
• OMBRETRIEVE CONFIGURATION
• OMBRETRIEVE CONNECTOR
• OMBRETRIEVE CONTROL_CENTER
• OMBRETRIEVE CORRECTION_MAPS_ACTION_PLAN
• OMBRETRIEVE CORRECTION_SCHEMA_ACTION_PLAN
• OMBRETRIEVE CUBE
• OMBRETRIEVE DATA_AUDITOR
• OMBRETRIEVE DATA_PROFILE
• OMBRETRIEVE DATA_RULE
• OMBRETRIEVE DATA_RULE_MODULE
• OMBRETRIEVE DEPLOYMENT_ACTION_PLAN
• OMBRETRIEVE DEPLOYMENT
• OMBRETRIEVE DIMENSION
• OMBRETRIEVE DRILL_PATH
• OMBRETRIEVE DRILL_TO_DETAIL
• OMBRETRIEVE EXPERT
• OMBRETRIEVE EXPERT_MODULE
• OMBRETRIEVE EXTERNAL_TABLE
• OMBRETRIEVE FLAT_FILE

13 OMBRETRIEVE FLAT_FILE_MODULE to OMBRETRIEVE VIEW

• OMBRETRIEVE FLAT_FILE_MODULE
• OMBRETRIEVE FUNCTION
• OMBRETRIEVE GATEWAY_MODULE
• OMBRETRIEVE ICONSET
• OMBRETRIEVE IMPORT_ACTION_PLAN
• OMBRETRIEVE ITEM_FOLDER
• OMBRETRIEVE LIST_OF_VALUES
• OMBRETRIEVE LOCATION
• OMBRETRIEVE MAPPING
• OMBRETRIEVE MATERIALIZED_VIEW
• OMBRETRIEVE MDL_ACTION_PLAN
• OMBRETRIEVE NESTED_TABLE
• OMBRETRIEVE OBJECT_TYPE
• OMBRETRIEVE ORACLE_MODULE
• OMBRETRIEVE PACKAGE
• OMBRETRIEVE PLSQL_RECORD_TYPE
• OMBRETRIEVE PLSQL_REF_CURSOR_TYPE
• OMBRETRIEVE PLSQL_TABLE_TYPE
• OMBRETRIEVE PLUGGABLE_MAPPING_FOLDER
• OMBRETRIEVE PLUGGABLE_MAPPING
• OMBRETRIEVE PRESENTATION_TEMPLATE
• OMBRETRIEVE PROCEDURE
• OMBRETRIEVE PROCESS_FLOW
• OMBRETRIEVE PROCESS_FLOW_MODULE
• OMBRETRIEVE PROCESS_FLOW_PACKAGE
• OMBRETRIEVE PROFILE_REFERENCE
• OMBRETRIEVE PROJECT
• OMBRETRIEVE QUEUE_PROPAGATION
• OMBRETRIEVE QUEUE_TABLE
• OMBRETRIEVE REAL_TIME_MAPPING
• OMBRETRIEVE REGISTERED_FUNCTION
• OMBRETRIEVE ROLE
• OMBRETRIEVE SAP_MODULE
• OMBRETRIEVE SEQUENCE
• OMBRETRIEVE SNAPSHOT
• OMBRETRIEVE STREAMS_CAPTURE_PROCESS
• OMBRETRIEVE STREAMS_QUEUE
• OMBRETRIEVE TABLE_FUNCTION
• OMBRETRIEVE TABLE
• OMBRETRIEVE TIME_DIMENSION
• OMBRETRIEVE TRANSPORTABLE_MODULE
• OMBRETRIEVE USER
• OMBRETRIEVE VARYING_ARRAY
• OMBRETRIEVE VIEW

14 OMBDROP

• OMBDROP
• OMBDROP ACTIVITY_TEMPLATE_FOLDER
• OMBDROP ACTIVITY_TEMPLATE
• OMBDROP ADVANCED_QUEUE
• OMBDROP ALTERNATIVE_SORT_ORDER
• OMBDROP ANALYZE_ACTION_PLAN
• OMBDROP BUSINESS_AREA
• OMBDROP BUSINESS_DEFINITION_MODULE
• OMBDROP BUSINESS_PRESENTATION_MODULE
• OMBDROP CALENDAR
• OMBDROP CALENDAR_MODULE
• OMBDROP CHANGE_DATA_CAPTURE
• OMBDROP CMI_DEFINITION
• OMBDROP CMI_MODULE
• OMBDROP COLLECTION
• OMBDROP CONFIGURATION
• OMBDROP CONNECTOR
• OMBDROP CONTROL_CENTER
• OMBDROP CORRECTION_MAPS_ACTION_PLAN
• OMBDROP CORRECTION_SCHEMA_ACTION_PLAN
• OMBDROP CUBE
• OMBDROP DATA_AUDITOR
• OMBDROP DATA_PROFILE
• OMBDROP DATA_RULE
• OMBDROP DEPLOYMENT_ACTION_PLAN
• OMBDROP DEPLOYMENT
• OMBDROP DIMENSION
• OMBDROP DRILL_PATH
• OMBDROP DRILL_TO_DETAIL
• OMBDROP EXPERT
• OMBDROP EXPERT_MODULE
• OMBDROP EXTERNAL_TABLE
• OMBDROP FLAT_FILE
• OMBDROP FLAT_FILE_MODULE
• OMBDROP FUNCTION
• OMBDROP GATEWAY_MODULE
• OMBDROP ICONSET
• OMBDROP IMPORT_ACTION_PLAN
• OMBDROP ITEM_FOLDER
• OMBDROP LIST_OF_VALUES
• OMBDROP LOCATION
• OMBDROP MAPPING
• OMBDROP MATERIALIZED_VIEW
• OMBDROP MDL_ACTION_PLAN
• OMBDROP NESTED_TABLE
• OMBDROP OBJECT_TYPE
• OMBDROP ORACLE_MODULE
• OMBDROP PACKAGE
• OMBDROP PLSQL_RECORD_TYPE
• OMBDROP PLSQL_REF_CURSOR_TYPE
• OMBDROP PLSQL_TABLE_TYPE
• OMBDROP PLUGGABLE_MAPPING_FOLDER
• OMBDROP PLUGGABLE_MAPPING
• OMBDROP PRESENTATION_TEMPLATE
• OMBDROP PROCEDURE
• OMBDROP PROCESS_FLOW
• OMBDROP PROCESS_FLOW_MODULE
• OMBDROP PROCESS_FLOW_PACKAGE
• OMBDROP PROJECT
• OMBDROP QUEUE_PROPAGATION
• OMBDROP QUEUE_TABLE
• OMBDROP REAL_TIME_MAPPING
• OMBDROP REGISTERED_FUNCTION
• OMBDROP ROLE
• OMBDROP SAP_MODULE
• OMBDROP SEQUENCE
• OMBDROP SNAPSHOT
• OMBDROP STREAMS_CAPTURE_PROCESS
• OMBDROP STREAMS_QUEUE
• OMBDROP TABLE_FUNCTION
• OMBDROP TABLE
• OMBDROP TIME_DIMENSION
• OMBDROP TRANSPORTABLE_MODULE
• OMBDROP VARYING_ARRAY
• OMBDROP VIEW

15 OMU Commands

• OMUALTER
• OMUCOMPILE
• OMUCONFIGURE
• OMUCONNECT
• OMUCONTROLCENTERJOBS
• OMUCREATE
• OMUDATAVIEWER
• OMUDEPLOY
• OMUDERIVE
• OMUEXPORT MDL_FILE
• OMUIMPACT
• OMUIMPORT
• OMUIMPORT MDL_FILE
• OMULINEAGE
• OMULIST
• OMUPROMPT
• OMUPROPERTIES
• OMUSELECTSOURCE
• OMUSELECTTARGET
• OMUSHOWCHANGECONSOLE
• OMUSTART
• OMUSTARTJOB
• OMUSTART EXPERT
• OMUVALIDATE

A Additional and Optional Usages

• Using Control Files to Import and Export Metadata
  • Creating MDL Control Files
    • Keywords Used to Import Metadata
    • Examples of Control Files Used to Import Metadata
  • Exporting Metadata Using OMB Plus
  • Importing Metadata Using OMB Plus
  • Accessing Transformation Modules Using OMBPlus
    • Predefined Transformations
    • Accessing Predefined Transformations Using OMBPlus
    • Custom Transformations
    • Public Custom Transformations
    • Custom Transformations that Belong to a Particular Project
• Working with Mappings and Operators
  • Defining Expressions in Mappings
  • Default Group Names and Attribute Names
• Accessing Transformation Modules
  • Predefined Transformations
    • Accessing Predefined Transformations Using OMBPlus
  • Custom Transformations
    • Public Custom Transformations
    • Custom Transformations that Belong to a Particular Project
• Running OMB Plus in Oracle JDeveloper
  • Installing OMB Plus in Oracle JDeveloper
  • Opening the Syntax Highlighting Editor in JDeveloper
  • Invoking Keyword Auto Completion
  • Invoking the OMBPlus Interpreter
  • Viewing the OMBPlus Console
  • Viewing Help Documentation