The Oracle Warehouse Builder Software Development Kit (SDK) is a robust framework to extent the capabilities of Warehouse Builder. On this page, you will find technical information and sample code to help add functionality to the already extensive design, ETL and runtime capabilities of Warehouse Builder. With more than twenty different APIs covering the entire product you will be able to extend the product allowing you to solve these specific requirements for your project in a timely and scalable manner.
The Scripting Language in Warehouse Builder is typically used by the more advanced Warehouse Builder user. This set of web pages is therefore intended for those users who are familiar with Oracle Warehouse Builder and want to become more productive using the scripting language.
An important technology and part of the Software Development Kit (SDK) is the scripting language that allows any user to manipulate the contents of the Warehouse Builder repository and perform actions on it. This effectively means that you can create metadata, update metadata and delete metadata from the Warehouse Builder repository without using the graphical interface to the product. You can also perform common functions such as generating code and deploying this to a database.
The capabilities of the scripting language are roughly equivalent with the capabilities of the graphical user interface. A number of areas are covered with scripting. These are:
Product Setup - Setting up the design and runtime environment
Having a scripting language at your disposal gives you some interesting capabilities:
It allows you to create batch scripts for long running or labor intensive operations
Automate repetitive tasks using scripts
Build metadata generation and creation scripts
All in all you will see increased productivity when you combine the scripting capabilities with your regular Warehouse Builder operations.
Architecture
Before you start using the scripting language it is important to understand the architecture. One crucial thing to keep in mind is that you are dealing with client-server architecture when using the Warehouse Builder scripting components. In this case the server is the Warehouse Builder repository on any database, and the client is your computer holding the complete Warehouse Builder software install.
NOTE: It is quite possible to install the client software on the server itself, allowing you to run as a client on the server. You will need to access the OMB*Plus tool on the server instead of on your client. The client install comes with a TCL compiler and interpreter, allowing you to run these commands.
Like Warehouse Builder's user interface the scripting client is multi-user and it allows you to work in individual sessions using the common security. This for example means that you must commit in one session before changes are available to other clients, regardless whether they are UI or scripting clients. The language Oracle chose for scripting is Java TCL, which allows you to run the scripting commands on any platform certified with Warehouse Builder without porting issues. In a sense it behaves like SQL allowing the same commands to run.
The Warehouse Builder scripting commands are extensions to the standard TCL language. Another key characteristic of TCL is that it is commonly used in many environments. TCL by nature supports procedural language and therefore it is possible to create loops and if-then constructions. This flexibility makes the language very suitable for manipulation and conditional logic on objects.
Before looking at the language constructs in more detail you should understand some basic TCL constructs and concepts.
The first and arguably one of the most important notions is the fact that the backslash (\) is used as a continuation character allowing you to continue your statement on the next line. Another aspect to be aware of is that the commands in TCL are case sensitive. All OMB commands are in uppercase. Also any string literals are evaluated according to the case sensitivity rule. If you name a project MY_PROJECT, you will get a not found message when you try to use ‘My_Project' as a literal string.
It is important to know that you can create programs with generic TCL. The language is procedural and allows you to for example loop, work with variables etc. This functionality can then be combined with OMB commands to work on the repository. For example, you can loop through a list which you create using the OMBLIST command, then apply modifications to the metadata using other OMB commands.
There are many more concepts to be discovered when using OMB commands and the TCL language. To learn more about the TCL language in general take a look at this introduction or at these references.
The scripting language was introduced with Warehouse Builder 9.0.4. This SDK documentation and the examples are written and tested on Warehouse Builder 9.2.0.3.
The information provided here is applicable for all versions after 9.0.4. In 9.0.4 you may find some discrepancies as some commands are changed in some minor aspects. Consult the following table for a quick overview of supported versions:
This section gives you some short examples of some common code used often in Warehouse Builder environments. These are grouped per capability and you can follow the links for more examples and specific applications of these examples.
Installation
If you regularly install software or repositories you can automate the process by using the scripting command to install the Warehouse Builder components. An example to install the design repository is shown here.
OMBINSTALL DESIGNTIME_REPOSITORY owb92/password \
USING CREDENTIAL sys/password@localhost:1521:orcl92
When you install the repository with the Assistant there are many more settings you can choose. These are supported in the scripting commands as well. For these and other commands take a look at the other installation examples provided.
Navigation will become a second nature and you will soon be used to moving around the Warehouse Builder repository in TCL commands. Some of the basic ones are shown here.
Change the context to any level by using the path in single quotes. The navigation in OMB is case sensitive also note the double backslash.
OMBCC 'MY_PROJECT\\SCRIPTING'
Change the context to the Root or Repository level
OMBCC '/'
Displaying metadata will come naturally as well. The commands below are some of the most common ones.
OMBLIST ORACLE_MODULES
When issued in the MY_PROJECT context the output of this command are two modules, MY_WH and SCRIPTING:
The OMBLIST command is context sensitive. For example, you cannot list modules if you are at the root level.
Specifying the fully qualified connect string connects you to the repository.
OMBCONNECT owb92/owb92@localhost:1521:orcl92 USE SINGLE_USER_MODE
In this example you connect in single-user mode, which is not the default but required from some of the activities in the examples relating to the creation of custom object definitions. For more examples on navigation, connecting and displaying metadata visit the navigation examples section of the site.
The most common activity in manipulating metadata is the creation of new or the altering of existing metadata. When creating a table, the default data type is Number with no precision and scale set. Take a look at the OMBCREATE statement below on how to create a simple table with various data types.
Adding a number column to a table and setting some properties:
OMBALTER TABLE 'TEST_TABLE' ADD COLUMN 'COL5' \
AT POSITION 5 \
SET PROPERTIES (DESCRIPTION) VALUES ('COL5 Note')
The examples shown here manipulate metadata on a single object. By combining the power of TCL with the OMB additions you can update multiple objects in one go. For an example of these capabilities and more examples go to the metadata manipulation examples.
A service on the repository is something that can be done generically to (almost) any object. The most common used ones are OMBCOMMIT and OMBGENERATE to either commit or to generate the scripts from the metadata.
For examples on the other service command take a look at the services examples. You can for example generate to file and deploy that to the database and do this within a batch run.
In this example you see the export into a file for your metadata. This can be very useful while automating or creating a back-up strategy for Warehouse Builder metadata.
Note that instead of using 'd:\\backup.mdl' the example above uses '/', which you do not have to escape, making the statement clearer.
For examples on the other service command take a look at the services example section. You can for example loop through objects and work on components from many projects in one go.
The custom object definitions determine the content of the repository. In that sense you are changing the actual information you can store for any object. In the example we will add an owner attribute to the view object. This allows you to store the owner information on a view, keeping track of who owns this view in your team. It is important to realize that this change impacts the entire repository. This cannot be done on a per project basis.
Before you can redefine the structure of an object you must be sure that you are the only user of the repository. Therefore you must connect to the repository in single-user mode. If you are connected in regular mode you will get the "OMB01112: Command OMBREDEFINE can only be run in single user mode." error.
You are not completely free in naming of the extensions. In order for Warehouse Builder to understand these are user-defined you must prefix any property with "UDP_". Failure to do so will generate the "OMB01133: The name of User Defined Property must be prefixed with 'UDP_'." error.
This command results in a new tab in the object property sheet in the user interface, allowing you to view and specify the owner of a view from the UI.
Of course you can also display, modify and remove these definitions within the scripting environment. For more information and examples on this visit the custom object example page.
A number of references are important to learn about scripting and TCL in general. Some of these are related to Warehouse Builder, others are good sources of information for TCL itself.
NOTE: The documentation has the syntax diagrams for all statements and lists all objects that can be manipulated using each specific statement. This is especially helpful with the metadata manipulation commands.
Bookmark
