OBIEE: Where'd my SQL go?

With the introduction of 11g, you can now deploy your opaque views to the database as, well, database views. It will simply wrap up your SQL statement inside a CREATE OR REPLACE VIEW... and run that statement using the connection pool you specified. Of course this means your connection pool has to have privileges to create objects, which may not be the case if you are using "read only" connections.

Anyway, if your development environment is refreshed from production on a regular basis, objects and all, you'll need to redeploy those views. Naturally, you saved that ad-hoc like SQL in subversion or some other source control tool...oops, you didn't?

All is not lost.

First, it's relatively easy, if you have a magnifying glass, to pick out the deployed views, they look like this:



Now, just go into the table properties, General tab and go to the dropdown box:



Select the Select selection (hah!). If you're a bit uptight about putting it in the correct location (like I am), you may have to navigate to the appropriate database version. For me, Oracle 10g R2:



Voilà!

Another option that you could use would be to Copy (Ctrl+C) the object in question and then paste (Ctrl+V) into notepad or some similar tool. Should be fairly easy to spot the SQL.