12c Direct SQL Query from Multiple Sources

User_2NI9B

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

Have you tried to use a DB link?

[Deleted User]

Maybe take a step back and think about the following:

Are you doing the right thing here? Are you using the tool as it is supposed to be used and for the purpose for which it was designed?

This is an analytics platform. Not a notepad with SQL interpretation.

User_2NI9B

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.

[Deleted User]

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.

Joel

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.