I was reading up on my blogs today. A lot of news from the UKOUG conference and it seems I must try to get there some time (next year) as I read nothing but good reviews of the conference and the socialising (socializing in American English? Or is this just Dunglish?). Not to mention the frequent reviews of visits to bars, restaurants and hotels that pop up in the blog posts everywhere. One of the blogs I read by Mark Ritmann mentioned his presentation at UKOUG about best practices for a datawarehouse project, pdf here. I was impressed by the amount of automation using OMB scripting he deemed necessary for the success of a project. Since I am currently working on automation of OWB design/programming using OMB scripting I thought it might come in handy if I do some blogging about my progress. Nothing very fanciful to start with but hopefully useful to anyone who has an interest or a need to start scripting.
Maybe this could even become a series. But, don’t keep your hopes up too much, I tend to get distracted easily. Here goes.
There are several ways to start OMB scripting, each with its own advantages and drawbacks.
Design Center
The easiest one will probably be from the Design Center (in 10g). In standard lay-out that would be the lower right sub-screen:
In this subscreen you have the advantage that you are already connected to the repository and you can directly type in commands like:
OMB+> set OMBPROMPT ON
ON
OMB+> OMBCC '/MY PROJECT'
/MY PROJECT>
What I showed here is the simple command to change the context you are working in and I started with making sure the current context (OMBCC) is shown in the prompt. The context represents the project tree you can browse in the ‘Project Explorer’ on the upper left corner of the screen. Probably very familiar to you already.
You may also have noticed the commands contain mixed case characters. For anyone used to windows that will be an unnerving experience as windows is not case-sensitive and tcl, the language OMBPlus is based on, is very much so. OMBPlus is even kind of picky, not only to case, but also to other language elements like = or ==, () and { }. It will take a bit of getting used to.
As I said, the advantage of this method is that your are already connected. One of the drawbacks in this method is that you can paste parts of scripts in the window and then edit them. But once you have corrected any errors in your commands (that is bound to happen, believe me) you cannot copy the corrected command and paste it back into the editor you copied the commands from. It is one way; paste-into, but not copy-from. That’s enough for now, here comes the second way to start OMBPlus.
OMBPlus.bat (or OMBPlus.sh on unix)
Easily enough started by double clicking the bat-file, or clicking a shortcut to it in the provided menu’s, and then you are presented with the same command prompt from OMB+ only in a separate window:
The first thing you now have to do is to connect to the repository:
OMB+> OMBCONNECT <usrname>/<passwd>@<host>:<port>:<repository database>
Connected.
OMB+> set OMBPROMPT ON
ON
OMB+> OMBCC '/MY PROJECT'
/MY PROJECT>
I have done the same thing as before. Only with the connecting added. And I now have the possibility to copy from the corrected commands I have entered in this shell. This works the same as in any cmd-window in Windows as you have started a .bat file. I won’t go into details about this, I assume them known.
The third method to work with OMBPlus scripting is from inside the Expert Editor. But that is something I’ll hopefully get into in a later post.
Now for some stuff you can use right away.
All fine and neat that there are two (three) ways to connect to OMBPlus, but I have not shown anything you cannot find in the manuals. Be assured, you won’t find much here that is not in the manuals. I am only trying to present it in a way so that you can avoid the pitfalls I have seen. It is after all my blog 
Below is a script I have used on 2 different occasions, pleasing two different people, not counting myself.
OMBCC '/<project>/<oracle module>'
set tblList [OMBLIST EXTERNAL_TABLES]
foreach tbl $tblList {
puts $tbl
OMBALTER EXTERNAL_TABLE '$tbl' SET PROPERTIES \
(LOAD_NULLS_WHEN_MISSING_VALUES) VALUES ('true')
OMBRETRIEVE EXTERNAL_TABLE '$tbl' GET PROPERTIES \
(LOAD_NULLS_WHEN_MISSING_VALUES)
}
I left out the connecting. Then I changed the context to an oracle module. Watch for the case again and don’t forget the surrounding quotes. Next I set a variable called tblList to the output of an OMB command “OMBLIST”. In this case I want to list all external tables in the oracle module as I want to change a property of them. For all the other object types like TABLES, FUNCTIONS, SEQUENCES and PROCEDURES you can do the same OMBLIST. Notice that all the object types are in plural. Something to remember.
I now have a variable that holds all external tables in my oracle module. Next I use a tcl command to loop over the contents of this variable. Within the loop I ‘print’ the name of the external table on the screen with the puts command, just to show some progress. And then finally the real work of changing the property. How you find out which properties you can change and how they are changed is something for later (note to self!).
The OMBALTER command has an object type associated with it, but unlike the OMBLIST command it is now in singular (without the ’s’). Next you provide the name of the object. Within quotes! And here we use the ‘running’ variable from the surrounding loop. Then I provide what kind of operation I want to alter, in this case to set a property. After that comes the ‘\’ character to signal that the command continues on the next line. There I provide that name (or names, separated by commas) and values for this property. When more than one property is set, the values are taken in the same order as the properties are provided.
The last command before the end of the loop is a check whether the changing of the property was successful. I retrieve the value of the property and by doing so the value is displayed. Before showing the output of the script there is one thing I must mention again, being the picky-ness of the tcl language. The { character should always be on the same line as the foreach command and there should be a space before the { character. Otherwise it just won’t work and very strange errors may ensue. Below is a sample output of the script.
/<project>/<oracle module>> set tblList [OMBLIST EXTERNAL_TABLES]
EXT_HLP_COMMUNICATION_TYP EXT_HLP_CONTACT_TYPE_EMAIL EXT_HLP_GEWONNEN_SPEELKANS
EXT_HLP_KANALEN EXT_HLP_MEDIUM EXT_HLP_OCT_STATUS EXT_HLP_PRIJSKLASSE EXT_HLP_PR
OVINCIE EXT_HLP_RETOURCODE EXT_HLP_STREETLIFE_OMS EXT_HLP_TERUGBOEKREDEN EXT_HLP
_TOPIC_EMAIL
/<project>/<oracle module>> foreach tbl $tblList {
> puts $tbl
> OMBALTER EXTERNAL_TABLE '$tbl' SET PROPERTIES \
> (LOAD_NULLS_WHEN_MISSING_VALUES) VALUES ('true')
> OMBRETRIEVE EXTERNAL_TABLE '$tbl'GET PROPERTIES \
> (LOAD_NULLS_WHEN_MISSING_VALUES)
> }
EXT_HLP_COMMUNICATION_TYP
EXT_HLP_CONTACT_TYPE_EMAIL
EXT_HLP_GEWONNEN_SPEELKANS
EXT_HLP_KANALEN
EXT_HLP_MEDIUM
EXT_HLP_OCT_STATUS
EXT_HLP_PRIJSKLASSE
EXT_HLP_PROVINCIE
EXT_HLP_RETOURCODE
EXT_HLP_STREETLIFE_OMS
EXT_HLP_TERUGBOEKREDEN
EXT_HLP_TOPIC_EMAIL
/<project>/<oracle module>>
I think that’s about it for today. Happy scripting.
Recent Comments