Accessing tables from multiple databases using dblink

building semantic layer
1

hi
I have searched the forumtopics but could not find how we can use dblinks to access tables from mutiple databases.
I have one database as orcl and other as aris and i want tables from both.
I have created a dblink on orcl to access the tables from aris over dblink.

Pls let me know how to use the same in designer?

Regards

Rajiv1107 (BOB member since 2007-01-25)

2

As far as I understand it, if you have the right permissions you should be able to see the tables from Designer?

What can you see?

Mak 1 :uk: (BOB member since 2005-01-06)

3

Hi

You can create derived table with the DB link like Select *from emp@dblinkname.

Regards

Siva.M

looksmee :uk: (BOB member since 2006-02-08)

4

I can run this query in ORACLE and get correct results:

select * from SCHEMA_US.Sometable@stl_pcfm;

When I use this statement to create a derived table in IDT, I get:

ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from STL_PCFM

In both cases I am logged in as myself. Any ideas n why this would happen?

popsdrown (BOB member since 2007-04-27)

5

Your ID may not have create table permissions on the remote DB.

Can you show us your create table SQL too please? (looking specifically for which schema you specify for the table)

kbrazell :us: (BOB member since 2003-08-19)

6

Are you the user defined in the universe connection?

select * is bad practice but that’s a different topic :wink:

Have you tried simply inserting the table?