Application Development...Conneting to Multiple Oracle instances

I have been developing an application on VB 6.0 and Oracle 8.0.5 as the back-end. I have been certain problems and need help if possible in tackling the issue, in a much simpler way.

I have to connect to 3 database instances which needs integration, One database is of the application developed by me and the other 2 databases are application developed by a 3rd party.



I require to use 2 or more instances in a select statement, ie., i require to retrieve fields from 2 different instances.



The only solution that i could think of is to create 2database links to each schema which relates to the other 2 schemas

Note : Each schema of the database used is in a different instance.





ie ., Eg:

Databases Instance :prot

Schema :protDSQ

Owner :protDSQ

Tables :site,customer etc.....

Database Link :ex connecting to expr

av connecting to avan



Databases Instance :expr

Schema :exDSQ

Owner :exDSQ

Tables :chapter, subhdg etc....

Database Link :pr connecting to prot

av connecting to avan



Databases Instance :avan

Schema :avanDSQ

Owner :avanDSQ

Tables :vendor,company etc......

Database Link :ex connecting to expr

pr connecting to prot

Statement :

select protDSQ.site.Sitename@pr, avanDSQ.company.name@av, chapter.Chapno from site@pr, company@av , chapter

where ...........etc

The above statement would be executed connecting to expr instance as exDSQ user.



The above way is the only way that strikes me to solve the issue, but is very complicated,

and needs help to solve it in a diff way.

Is there any way of opening multiple sessions simultaneously, if yes how do i do it ?



Further complication arises as

the name of the instances, schemas, users can vary depending on the environment, to which the user connects to...



It will be of great help if anybody who has worked on such issue can help...








Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories