To OBE- or not OBE-, what's OBE- is the question

Had a problem last week. About 20 past 4 in the afternnon (I officially finish at 4pm) on Thursday (5th Jan)I had an email telling me that users couldn't run reports on a particular database. This database sits behind an OLTP app written in Oracle Forms 6i and connects, via a database link, to another database which holds metadata for an instance of Documentum (a document management system from EMC). Users login as themselves identified from their network logon and have permissions on the application schema according to their post.

It turned out that the reports that wouldn't run were ones that accessed the remote database. The database link logs onto the remote database as a user which has permissions on the Documentum schema, which is owned by a different user. I had, that afternoon, done some work on the remote database to give the username used by the database link select permission on some views that had been created since the link was set up.

After much working of the problem and raising a call with Oracle support I found the problem (actually I found the problem about 6 hours before Oracle Support even looked at the call). Turned out that someone had decided to recreate the database link but to create it as a normal link rather than a public one so the users (who logged in as themseleves) couldn't see it.

The more intersting aspect of the problem was that when they tried to run the reports from Query Builder they were getting a message along the lines of "Cannot resolve data table scott.emp@orcl. Select a new data table?" (obviously the table names have been changed to protect the guilty). Even more interesting (in a "May you live in interesting times." sort of way) was that if they tried to edit the query then they would get an error message such as "OBE-15087: Invalid Location: orcl.world". I searched metalink, technet and finally the whole web for that error message, nothuing. The only references to OBE I could find was that it was used as an abbreviation for "Oracle By Example".

Eventually I got feedback from Oracle Support that OBE is an undocumented change to Query Builder, versions 6.0 and above, and replaces the BRW error prefix. It's documented in note 108528.1 [MetaLink login required].

The key problems here seemed to be the "OBE-15087: Invalid Location ..." error isn't well documented and an unfamiliarity with database links on our part. The problem with database links, in my experience, is that they're the sort of thing that you probably create once then forget about, they just work, until they don't. Very few of the systems I've worked with have used database links, those that have it's been a create and forget sort of deal.