Thursday Sep 25, 2014

Database Links

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 :)




Friday Sep 18, 2009

Formatting Concatenated Datasources

[Read More]

Wednesday Jun 10, 2009

Data Template Progression

[Read More]

Thursday May 15, 2008

External Data Engines II

[Read More]

Tuesday May 13, 2008

External Data Engines I

[Read More]

Friday May 09, 2008

Get Swimming!

[Read More]

Tuesday Jan 29, 2008

Data Template Building & Performance

[Read More]

Tuesday Oct 23, 2007

BLOB, CLOB, RAW and Looooong

[Read More]

Thursday Oct 18, 2007

Data Dot Defined

[Read More]

Thursday Aug 23, 2007

Call for Templates!

[Read More]

Sunday May 13, 2007

New Look & BIPScriptions

[Read More]

Tuesday Apr 10, 2007

Who's on first?

[Read More]

Thursday Mar 22, 2007

Going out Clobbing?

[Read More]

Setting Organizations for Data Templates

[Read More]

Sunday Feb 18, 2007

Data Templates without grouping

[Read More]

Friday Feb 16, 2007

Joining Queries a la Francais

[Read More]

Wednesday Dec 13, 2006

Hello Data Templates

[Read More]

Wednesday Nov 29, 2006

Data Templates by Example

[Read More]

Wednesday Nov 08, 2006

Introducing Data Templates

[Read More]

Wednesday Nov 01, 2006

Web Service Datasources for XMLP Enterprise

[Read More]
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« September 2015
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today