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:
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:
Perform complex actions directly in Warehouse Builder, without launching the client user interface.
Define sets of routine operations to be executed in Warehouse Builder.
Perform batch operations in Warehouse Builder.
Automate a series of conditional operations in Warehouse Builder.
To use OMB*Plus, first launch OMB*Plus and then connect to a repository. Type all commands and keywords in OMB*Plus using uppercase.
Whenever you switch between the GUI and OMB*Plus, ensure that you are working on the same project in both the environments. For example, if you switch projects in the Design Center without switching to the same project in the OMB*Plus environment, you may get unpredictable results while executing OMB*Plus commands. To switch projects in OMB*Plus, use OMBCC. To list the current project in the OMB*Plus environment, use OMBDCC.
To launch the OMB*Plus console, follow the instructions specific to your operating system.
UNIX: At the command prompt, enter:
<OWB_HOME>/owb/bin/unix/OMBPlus.sh
Windows: From Start, navigate to Warehouse Builder, located within Oracle-OUI. Select OMB Plus from the list of menu items.
Alternatively, you can also launch OMB*Plus from the Warehouse Builder Design Center. To do this, from the main menu, select View, and then OMB*Plus.
From the OMB*Plus console, enter:
OMBCONNECT <repos>/<password>@<host>:<port>:<service_name> USE WORKSPACE '<wokspace_name>'
In this expression, <repos> is the name of the design-time repository, <host> is the computer on which the repository is installed, <servicename> is the name of the database that contains the repository, and <workspace_name> is the name of the workspace. Note that the workspace name must be provided within single quotes (').
If the workspace and repository names are same, then you do not have to specify the workspace name. You can use the command:
OMBCONNECT <repos>/<password>@<host>:<port>:<service_name>
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 details and an example on how to use OMBHELP, see OMBHELP.
Keep in mind the following points when you execute OMB*Plus commands:
You can set the value of a Boolean configuration property, for example the NOT_NULL 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.
For example:
OMBCREATE TABLE 'EMP' \ ADD COLUMN 'EMPNO' SET PROPERTIES (NOT_NULL) VALUES (1)
Or
OMBCREATE TABLE 'EMP' \
ADD COLUMN 'EMPNO' SET PROPERTIES (NOT_NULL) VALUES ('true')
When you specify numeric values for a configuration property, do not enclose the values in single quotes.
Do not use a number sign (#) in the middle of an OMB*Plus command.
Do not use an exclamation point (!) because it is an illegal character.
Tcl interprets several special characters differently than Warehouse Builder and the Oracle database. Review this and subsequent sections to learn how to properly use the following characters in OMB*Plus: dollar sign ($), backslash (\), bracket ([ ), and semicolon (;).
Dollar sign ($): The dollar sign identifies Tcl variables in Tcl but has no special meaning in Warehouse Builder. Therefore, if you include $ in the name of a Warehouse Builder object, OMB*Plus uses the Tcl convention and displays the $ inside curly brackets such that name$ displays as name{$}. This prevents the name from being misinterpreted as a variable.
Backslash (\): Tcl uses the backward slash to indicate the end of a line in a command that spans multiple lines. Therefore, in a multiple line OMB*Plus command, use only a backslash (\) after each line. This interpretation of the backslash has implications on how you specify full paths in OMB*Plus, as described in "Specifying Paths".
Tcl also uses the backslash as the least preferred method for escaping special characters. Use curly braces as the preferred method as described in "Escaping Special Characters and Writing Complex Arguments".
Semicolon(;): The semicolon separates two commands in Tcl. Using a semicolon in a quoted string results in an error. As a work around, escape the semicolon character by putting a backward slash (\) in front of the semicolon. For example,
OMBCREATE FLATFILE 'FF_DSR_RLE' \
SET PROPERTIES(DATA_FILE_NAME,IS_DEMILITED,CHARACTER_SET,RECORD_DELIMITER,\
FIELD_DELIMITER,FIELD_LEFT_ENCLOSURE,FIELD_RIGHT_ENCLOSURE) \
VALUES('DSR_RLE.dat','TRUE','WE8MSWIN1252','\n','\;','""','""')
Tcl uses curly braces ({) and (}) as preferred method for escaping special characters and writing valid, complex arguments. For the following situations, you can enclose the element in braces and leave the contents alone:
The element contains embedded spaces.
The element contains one of the Special Characters.
The element starts with a brace or double-quote.
There are no characters in the element.
To escape single quotation marks in elements, consider using the procedure OMBToSettableString described in "Predefined Tcl Procedures".
You should consider using the backslash for escaping only in the limited situations that using curly braces results in unmatched braces, the last character of the argument is a backslash, or the element contains a backslash followed by another backslash indicating a new line.
Escaping special characters in passwords in OMB*Plus commands is affected both by Tcl special character handling and special character handling by the database. The affected commands include the following:
OMBCONNECT
OMBCONNECT CONTROL_CENTER
OMBDEINSTALL OWB_RAC
OMBDEINSTALL OWB_REPOSITORY
OMBDEINSTALL OWB_TARGET_USER
OMBEXPORT ENTIRE_REPOSITORY
OMBGETLICENSE
OMBIMPORT ENTIRE_REPOSITORY
OMBINSTALL OWB_RAC
OMBINSTALL OWB_REPOSITORY
OMBINSTALL OWB_TARGET_USER
OMBMLSUPDATE OWB_REPOSITORY
OMBREGISTER USER
OMBSEED
OMBSEEDLICENSE
OMBUPGRADEREPOSITORY
The rules for quoting are as follows:
Because OMB scripting is TCL-based, if password contains: $, [, ], or \, then enclose the whole connection string in TCL brace quotes.
If the password contains special characters such as white space, @, or /, then the password (only) should be quoted using double-quotes.
Table 3-1 contains examples of correctly quoted password strings. In the examples, orcl is the name of the database that contains the repository and rep_user is the name of the repository user.
Table 3-1 Correctly Quoted Password Strings
| Password | Connect String |
|---|---|
|
[hello] |
|
|
hello world |
|
|
@@@@@ |
|
|
///// |
|
|
\\\\\ |
|
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 are invalid and 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 forward 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}
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 is 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-delimited string. For example, the procedure converts input in the form of
{{<object_type> <name>} ... }
to
"<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 in a page.
OMBPageBreak 10 [OMBHELP OMBCREATE]
To display the next 10 lines of the output, press <Enter> on your keyboard.
The OMPageBreak procedure is available for every OMB*Plus session.
You can write scripts and run them in OMB*Plus. For examples of scripts you can write, see Chapter 4, "Sample 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 OMBPlus.sh test.tcl for scripts on UNIX and OMBPlus.bat test.tcl for scripts on Windows operating systems.
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 after 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
The sections that follow describe the types of commands that comprise the OMB scripting language.
Metadata Manipulation Language (MML) Commands: Includes commands for creating, altering, deleting, and retrieving metadata objects.
Shell Commands: Includes help and environment support such as OMBDCC and OMBHELP. Although these commands enable you to control the scripting environment, you cannot use them to edit the metadata.
Administrative Commands: Fits the MML to the Warehouse Builder back end. For example, the commands OMBCONNECT, OMBDISCONNECT, OMBCOMMIT, or OMBROLLBACK.
Navigation Commands: Enable you to navigate the Warehouse Builder repository just as you would navigate a UNIX file system.
Service Commands: Enable you to start Warehouse Builder metadata services such as validation, compilation, deployment, and import or export.
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 3-2 lists the standard command names for MML.
Table 3-2 Metadata Manipulation Language Commands
| Metadata Manipulation Language (MML) | Description |
|---|---|
|
|
Creates a first class object. |
|
|
Deletes a first class object. |
|
|
Modifies a first class object. |
|
|
Retrieves information from a first class object. |
The OMBCREATE, OMBDROP, OMBALTER, and OMBRETRIEVE commands accept only the object name as the main argument. Names identified by absolute or relative path are not accepted. To use these commands you must in the parent context of the object to be created, dropped, altered, or retrieved.
OMB*Plus executes commands like OMBCREATE, OMBALTER, and OMBDROP within a nested transaction.
OMB*Plus interprets clauses within a single command one by one, as illustrated by the following example:
OMBCREATE TABLE 'T1' \
MODIFY COLUMN 'C1' RENAME TO 'C1_NEW' \
ADD UNIQUE_KEY 'UK1' \
SET REF COLUMNS ('C1_NEW', 'C2')
In the preceding 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 preceding 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 data type and length for a column in a view:
OMBRETRIEVE VIEW 'V1' COLUMN 'COL1' \ GET PROPERTIES (DATATYPE, LENGTH)
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.
/<project_name>/<module_name>/<fco_name>
or
../<module_name> <fco_name>
String values, including object names and string property values, must be enclosed in single quotes.
Use OMB*Plus to access and manipulate the following Warehouse Builder objects, also known as first class objects:
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 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, OMBALTER, 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:
OMBCONNECT
Purpose
To connect to OWB repository.
Syntax
OMBCONNECT <user>/<password>@<host:port:SID>
where
<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
Notes:
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 3-3 lists the environmental variables. To set an environmental variable, use the Tcl set command. Use unset to unset an environmental variable.
Table 3-3 Warehouse Builder Environment Variables
| Environment Variable | Meaning | Possible Values |
|---|---|---|
|
|
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. |
|
|
Stores the filename for Warehouse Builder log file. |
A valid filename including its path. |
|
|
Indicates whether OMB*Plus will update the command prompt each time you call |
A Tcl boolean value. |
|
|
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. |
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
Note:
If you are running OMB scripts from OMB*Plus command line, then ensure that you commit (OMBCOMMIT) any changes before exiting the OMB*Plus session. If you exit from the session without a commit, then all the modifications since the previous commit will be lost.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. For example, to switch from the current project to another, use the command:
OMBCC '/PROJECT_NAME'
For example, if the name of the project is MATERIALS, then the command is:
OMBCC '/MATERIALS'
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 ]
The name_in_regexp parameter represents 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 perform services such as batch operations on Warehouse Builder metadata. Table 3-4 contains a list of service commands and their descriptions.
| Command | Description |
|---|---|
|
|
Use this command to compile folders or first class objects such as tables, views, sequences, dimensions, and cubes. |
|
|
This command provides deployment service. |
|
|
This command provides the metadata import service. You can only invoke the The four available modes are: |
|
|
Use this command to validate folders or first class objects such as tables, views, sequences, dimensions, and cubes. |
|
|
Use this command to execute objects from the control center. |
|
|
Exports current metadata, metadata definitions, or snapshot metadata to a metadata loader file. |
|
|
Use this command to fetch the impact of a change for an object. |
|
|
Use this command to fetch the data lineage. |
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.
To synchronize the objects in the repository and the OMB*Plus environment at any point of time, change to the parent context using OMBCC, and then use the command OMBLIST to refresh the objects within that context. For example, to refresh the tables within the parent context, use the command OMBLIST TABLES.
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 anOMB*Plus session and a Design Center session. In the Design Center, 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. The details of the result of the operation and the logic behind the result is explained:
OMBCC FFM1
The context is changed to the module FFM1.
OMBCREATE FLATFILE
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.
OMBLIST FLAT_FILE_MODULE
FFM1 is listed as one of the modules. This is because OMBLIST synchronizes all the parent-child relationships in the navigation tree.
OMBCREATE FLATFILE
The flat file is created. This is because the undelete is now reflected in the cache because of the OMBLIST command.
When you connect to a repository, Warehouse Builder by default connects in the multiple user mode. To switch to single user mode, use the command OMBSWITCHMODE SINGLE_USER_MODE. If however, you are connected to the control center (OMBCONNECT CONTROL_CENTER), then you cannot switch to the single user mode.
If you have long running jobs, for example from process flows, then you might get an error message while trying to connect in single user mode. This is likely to occur when process flows are waiting for user intervention, or when the control center is restarted while process flows are running.
When you are unable to log into the single user mode, run the following script to obtain a list of pending deployment and execution requests for a particular workspace:
owb/rtp/sql/list_requests <WORKSPACE_NAME>
Based on this list, you can decide to either expedite the execution requests or stop those execution requests that need not be run currently.
To expedite execution requests, run the script:
owb/rtp/sql/expedite_exec_request <AUDIT_ID> <WORKSPACE_NAME>
To stop execution requests, run the script:
owb/rtp/sql/abort_exec_request <AUDIT_ID> <RETURN_NO> <RETURN_CODE> <WORKSPACE_NAME>
Once you expedite or stop the pending execution requests, you will be able to log into the single user mode.