Re: dblinks

"Eirik Rogne" <eirik.rogne_at_vianova.no> wrote in message news:<avubj6$2qq$1_at_oslo-nntp.eunet.no>...
> Need advice on solving this:
>
> I've got some stored procedures on DB_X which updates some tables on DB_X
> but needs to get data from DB_Y. The use of dblinks works fine but I need a
> solution in which a user can enter connection-data (table DATASOURCES) in a
> table at DB_X som that the stored procedures can use this info to connect to
> a remote database (DB_Y) and get data.
>
> Table DATASOURCES:
> id sid ip port username prw
> aaa test1 123.123.123.123 1521 scott tiger
> bbb test 321.321.321.321 1521 scott tiger
>
> I've tried with dynamically creating and dropping dblinks, but it seems like
> the dblink-name has to exsist in tnsnames.ora at DB_X. My solution can't
> depend on entries in tnsnames.ora at DB_X.
>
> Therefore i'm looking for something like this (but then I've got no
> username/pwd):
>
> select colA from tabA_at_123.123.123.123:1521:test1 where colB=xxx;
>
> suggestions?
>
> -eirik-

You need certain information to connect to a database. Username/password, Host, Port, SID etc. If you don't have username/password then you can create a generic user on DB_Y and grant select priviliges on tables which you know are used. If the users are only connecting to DB_Y then why do you need to create a dblink dynamically?

As for as other connection option goes, one way I could think of to bypass tnsnames.ora file is to use Java thin JDBC driver. However, you would still need DB_Y's Username/Password, Host, Port and SID to create a connect url. If you are on ver 8.1.x and above, you can further explore this option.

Regards
/Rauf Sarwar Received on Mon Jan 13 2003 - 21:16:53 CET