12c Direct SQL Query from Multiple Sources

Question

Hello Everyone.

I know how to create direct sql query type analyses. You first specify your connection pool and then write down your sql.

What about a direct sql query that's built on multiple databases?

Sort of "Cross-Database Queries" as specified in this-

https://docs.oracle.com/cd/E23549_01/bi.1111/e10540/sqlref.htm#CHDGBCIC

I am struggling to achieve something similar on 12c.

Anyone any idea?

Thanks

Joel Acha · Answer

To be clear, I totally agree with @Christian Berg and DDR should be used only as a last resort. My suggestion was that if you wanted to use a DDR which needs to access more than 1 DB then (assuming you're running against an Oracle DB), then you could use a database link which allows you to access data from database B from database A. Your DBAs might be able to assist you with this.

[Deleted User] · Answer

DB Link: https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm

In effect you push things down to the database since all OBI does is send completely uninterpreted SQL (or any other language) to the connected source and turning its own brain off.

User_2NI9B · Answer

Hi Joel - Not sure what DB link you are referring to. Please elaborate.

Hi Christian - I am completely aware of your concern.

The report requirement is very discrete and it's not worth the effort of modelling the info.

We also don't prefer direct sql, generally. But there are instances where it's better to use it, at least for us.

But anyways, I just want to know how to do what Oracle document is suggesting.