>I have existing application that I am upsizing to oracle.
>
>The problem i have it that The application needs to connect to multiple databases with the same structure.
>
>The users login may have access to several oracle databases.
>
>The upsize wizard creates the remote views without the oracle schema(database) name. Any manually created views have only the schema name they were created from.
>
>What is the proper way to setup the connection to oracle such that one set of views can connect to all of the schemas dependant on what database the user wants to connect to ?
You are going to have a bear of a time doing this. YOu will have significant obstacles regardless of the direction you take.
If each user has to connect to Oracle with their own username and password, this then means you have to have Oracle synonyms on the tables. A synonym can only point to ONE schema. If this is the case, you are hosed.
The only way for this to work is if you only connect to Oracle using the smema owner username and password. Now, you can not distinguish users and grant specific access privileges on the Oracle side.
The only way I see this having a chance of working is if each database is running in a SEPARATE Oracle instance. Before I go explaining how that works, please explain the Oracle database setup.
Mark McCasland
Midlothian, TX USA