Everything Oracle

Developer-friendly alternative to OMB Plus 11g reference guide

Q: The OMB Plus scripting reference is terrible. They’re no examples and the syntax is unreadable. Is there an alternative?

Well, the kindest thing I can say about the “Oracle Warehouse Builder API and Scripting Reference 11g Release 1 (11.1), B31279-01, July 2007” is that it’s “forbidding”. The description of each API command is terse, to say the least. There are no examples of command usage. The “railroad” diagram representations of command syntax – that we find in, say, the “SQL Language Reference” – are absent. And not only is the syntax presented in text form, but it’s formatted in such a way as to make it very difficult to read. If we take even a simple command, like OMBCREATE FLAT_FILE, we have

Create Flat File Syntax - 11g R1 Documentation

In more complicated cases there’s no alternative but to cut and paste the text into an editor and reformat it to disentangle the brackets within brackets.

But is there an alternative? Well, we usually view the past through “rose-coloured spectacles”, but in the case of OMB Plus, it seems the past is the place to be. If we go back in time to version 10g Release 2 of the scripting reference (Oracle Warehouse Builder API and Scripting Reference 10g Release 2 (10.2), B28225-01, June 2006), then, while the syntax is the same as in 11g, we find that there are examples of command usage for each command:

Create Flat File Command Example - 10g R2 Documentation

But, even better, if we go back to 10g Release 1 of the scripting reference (Oracle Warehouse Builder Scripting Reference 10g Release 1 (10.1), Part No. B12152-01, November 2003) we not only get the command examples present in Release 2, but we also get a nice railroad diagram showing the command syntax:

Create Flat File “Railroad” Syntax - 10g R1 Documentation

So if you need to look up a command it’s best to start with version 10g Release 1. Unfortunately, many commands have been added since this version, so it may not contain what you’re looking for. If it doesn’t, then try 10g Release 2. It won’t have the comprehensible railroad diagrams, but it will at least have an example of command usage that may save you the torture of doing battle with the intractable syntax. If 10g Release 2 doesn’t have what you’re looking for then you’re stuck with 11g Release 1 – in which case, as one seasoned OMB Plus developer put it, “Sucks to be you”!

If you’re looking for the older releases of the documentation on the Internet it’s best to search by their part numbers. At present you’ll find them here: 10g Release 1 and 10g Release 2.

So we have the bizarre situation where the quality of the OMB Plus documentation is being downgraded with each release. It’s almost as though Oracle was trying to discourage its client base from using OMB Plus, and was producing the most unfriendly reference guide possible in an effort to further this aim. Assuming that this is not the case – and Oracle doesn’t want us to migrate en masse to Informatica – our best bet is to petition Oracle to reinstate the 10g Release 1 format of the OMB Plus documentation. Smoke signals should be sent skywards in the direction of the Olympian heights. But, remember, the historical precedent is not favourable: the Greek gods have always been indifferent to the welfare of us mere mortals, delighting, instead, to put obstacles in our way!

Can’t find record even when it exists

Q: I’m tearing my hair out. I’m creating a flat file with a single record and then adding fields to the record using separate commands. The flat file creation works okay but when I try to add a field it says the record doesn’t exist. I’ve checked the record name in the create command and it’s exactly the same as the one in the error message. I read and reread the documentation but I can’t see anything wrong. Is it a bug?

You might call it a “bug”. I might call it a “bug”. But, I suspect, the OWB Design Team would call it a “feature”!

To illustrate this “feature” with a simple example, you can try entering in OMB Plus (in the context of a flat-file module) the command:

OMBCREATE FLAT_FILE ‘bad_boy’ \
ADD RECORD ‘chameleon’;

This command will run without a syntax error, so everything appears to be okay. Then try adding a field to the newly created record using the command:

OMBALTER FLAT_FILE ‘bad_boy’ \
    MODIFY RECORD ‘chameleon’ \
    ADD FIELD ‘wantobe’ \
    SET PROPERTIES ( DATATYPE ) VALUES ( ‘CHAR’ );

You’ll be greeted with the error message:

OMB01729: Record chameleon not found.

So you cross-check the spelling of the record name between the two commands, scratch your head, change the record name a few times and try it again, but to no avail. You consult the documentation on OMBCREATE FLAT_FILE and OMBALTER FLAT_FILE, you utter a few imprecations in Middle English, and ... then you start tearing your hair out.

What the error message or the documentation won’t tell you is that when you create a flat file with a single record, OMB Plus automatically, and silently, renames the record to the name of the file. So after you’ve issued the OMBCREATE FLAT_FILE command, the record is actually named “BAD_BOY”, not “chameleon”. Try adding the field again, but this time using the command:

OMBALTER FLAT_FILE ‘bad_boy’ \
    MODIFY RECORD ‘BAD_BOY’ \
    ADD FIELD ‘wantobe’ \
    SET PROPERTIES ( DATATYPE ) VALUES ( ‘CHAR’ );

and all will be well (note, the case is important here; if you try adding a record called “bad_boy”, then you’ll get the same error message as before).

Now, if you’d been using OMBLOG, as I always suggest, then the explanation – bizarre though it may be – would have been forthcoming by way of a log entry following the OMBCREATE FLAT_FILE command:

Flat File BAD_BOY created. Record is renamed to BAD_BOY. Record name of the single-record flat file must be the same as a file name.

This “Oracle oopsie” is clearly the result of a very poor design decision. Either a single record clause should be syntactically invalid for the OMBCREATE FLAT_FILE command, or, at the very least, the warning message that appears in the log file should also appear on the screen. We might also have expected a paragraph in the documentation on the OMBCREATE FLAT_FILE command explaining this counterintuitive behaviour as a prophylactic against a “balding” population of OWB developers. So, on this count, the OWB Design Team’s report card must read, “Could do better”!

Missing role when trying to create a workspace

Q: When I try to create a workspace using OMBINSTALL WORKSPACE I get an OMB01001 error about a missing role. I’ve searched the API and Scripting Reference but it’s not mentioned anywhere?

The role in question is OWB_USER. The Oracle user that appears in the CREDENTIAL clause in the OMBINSTALL WORKSPACE command must have been granted the OWB_USER role. So, first create your workspace owner in SQL*Plus, and grant the OWB_USER role to the workspace owner. Then start OMB Plus and issue the OMBINSTALL WORKSPACE command.

This issue doesn’t arise when creating workspaces using the Repository Assistant, because when the Assistant is asked to create an Oracle user at the same time as creating a workspace it automatically grants the user the OWB_USER role (if you look at the GRANTEE column in DBA_ROLE_PRIVS for a GRANTED_ROLE of OWB_USER you’ll find all the workspace owners and workspace users listed).

And you’re correct, there is no mention of OWB_USER in the “Scripting Reference” (the only place where it’s discussed in the “Warehouse Builder” documentation set is in the “Installation and Administration Guide”, in connection with running a remote workflow instance). It’s existence should, of course, be itemized as a prerequisite for the OMBINSTALL WORKSPACE command in the “Scripting Reference”.

Displaying Tcl variable substitutions in API commands

Q: Many of my API commands in Tcl scripts use variables for things like the workspace owner. Is it possible to see what values have been substituted for them for debugging purposes?

Yes, it is. Set a value for the log file variable; for example:

set OMBLOG c:/scratch/omblog.txt

As OMB Plus API commands are executed, they are appended to the log file after variable substitution has taken place. This makes for an invaluable aid to debugging scripts. The log file is also of value as it displays more detailed error messages than those that appear on the screen.

Can’t drop project even when it exists

Q: I’m trying to delete a project using OMBDROP PROJECT and it says “OMB01049: Object ‘project’ not found”. But when I go into the Design Center it’s there?

This is a rather misleading error message. What it should really say is something along the lines of

Can’t delete project without first navigating to root node

When the current context is within a project and you try to delete any project from the workspace you get the error message you received. So navigate to the root node first with

OMBCC ‘/’

and then issue the OMBDROP PROJECT command.

Of course, logically there’s no reason why what you’re trying to do shouldn’t work. The fact that it doesn’t is an example of a general weakness in the OMB Plus API design: a tendency to ape the sequence of operations that are performed in the Design Center. In the Design Center you must navigate to the relevant node before right-clicking and selecting a task, since otherwise the task would lack the appropriate context. But when scripting a command the information needed to carry out that command is often embedded in the command itself, so that navigating around a “virtual” Project Explorer tree is unnecessary. By following the Design Centre paradigm too closely Oracle has made the API more complicated than is necessary in some places.

Everything Oracle

Home

Everything Oracle

FAQ	OMB	Plus

A collection of bugs, boojums and Oracle oopsies!