By Tim Dexter-Oracle on Sep 25, 2014
Yeah, its been a while, moving on ...
I got a question a week back asking about how BI Publisher could handle dblinks. The customer currently has db links from DB1 to DB2 and uses them in their queries. Could BIP handle the syntax and pass it on to the database in its SQL or could it handle the link another way?
select e1.emp_name , e1.emp_id ,e2.manager from emps e1 , emps@db2 e2 where e1.manager_id = e2.id
Well, there is the obvious way to create the join in BIP. Just get rid of the db link alttogether and create two separate database connections (db1 and db2). Write query A against db1 and query B against db2. Then just create a join between the two queries, simple.
But, what if you wanted to use the dblink? Well, BIP would choke on the @db2 you would have in the sql. Some silly security rules that, no, you can not turn off if you want to. But there are ways around it, the choking, not the security. Create an alias at the database level for the emp@db2, that way BIP can parse the resulting query. Lets assume I create an alias in the db for my db linked table as 'managers'. Now my query becomes:
select e1.emp_name , e1.emp_id ,e2.manager from emps e1 , managers e2 where e1.manager_id = e2.id
BIP will not choke, it will just pass the query through and the db can handle the linking for it.
Thats it, thats all I got on db links. See you in 6 months :)