Wednesday, October 17, 2012

OBIEE: Where'd my SQL Go? Part II

Back in July I wondered where my SQL went when I deployed an opaque view to the database. I found it.

Recently, I was introduced to a person at the client site I'll just call The Process Queen™. I had written some code (pipelined table function) that was to be deployed to the database, she was making helping me get it into SVN and showing me how they deployed code. The Process Queen™

In that regard, I wanted to get everything that we could into SVN. Those opaque views (now deployed as database views) were a perfect candidate.

The client impressed me with their deployment tools, I hadn't seen anything like it. I wish I could say more, of course...perhaps I can talk The Process Queen into a guest post?

Anyway, so there I was, following my own guide Where'd My SQL Go? and then I noticed this

and this.

Notice those SQL statements are different. Apparently in OBIEE you can add a SQL statement in to each and every database type that you want.

An interesting, if not scary, find. Which begs the question, why would you need this kind of functionality?


Alex said...

Scary indeed (especially the fact that they use opaque views ;-)

For the use case: Think of an RPD that is used on top of different databases (e.g. cheap mysql for, Oracle 11g for test and Exadata for prod).

The BI Apps RPD uses that technique btw.

have a nice day


oraclenerd said...


The opaque (now deployed) views were, as you know, a way to get around some deficiencies in the model.

I had that same thought, used for different databases, but hadn't considered it through the dev/qa/prod environments. Interesting.

Still scary, but again, the opaque views aren't the "normal" way to go about things so...