Re: ODBC cannot access Oracle tables under different schemas in one session

You just need to create a separate ODBC Data Source Entry for every Schema.
Than you should be able to access your schemas parallel. Connections are "expensive" and even Access keeps them open for one DSN as long as possible.

Regards

Justin

On 3 Dec 2005 21:47:51 +0100, "Vincenzoni Attilio" <aaa.avincenzoni_at_hotmail.com> wrote:

>"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> ha scritto nel messaggio
>
>>>In other words, i cannot access tables under different schemas from a
>>>single session of Access / ODBC. I must exit and reenter Access in order
>>>to
>>>shift to another schema and query their tables, which is most unpractical.
>
>> Apparently the data is in schema_1, schema_2, etc and you log in as
>> the schema owner.
>
>Yes, that is - ODBC logs on for me.
>
>Under Access, first i set up the tables once, by linking them and providing
>the proper credentials. Subsequently i see such tables as any Access table,
>and i don't worry any further about the logon process. I click and the table
>opens, i run a query, the table gets accessed and the data are shown.
>
>My understanding was that after ODBC has opened a table and got the data, it
>should release the table and close the connection. Thus, when i ask
>schema_1.emp, ODBC logs onto oracle as the schema_1 owner. When i ask
>schema_2.emp, ODBC ought to logon as the schema_2 owner, having closed the
>previous connection.
>
>This is what it used to do, when different "emp" tables were resident on
>different databases. Now that the "schema_x.emp" tables all reside on the
>same database, ODBC won't drop the connection from one query to another.
>This might be an ODBC desired feature aimed at optimizing Access. I wonder
>if it could be turned off.
>
>> As schema_2 never granted select privilege to schema_1, and schema_1
>> also doesn't have a synonym on schema_2, you get 'ora-942 table or
>> view does not exist'
>
>Apparently so: ODBC tries to access schema_2, still being logged in as
>schema_1.
>Looks as i am not able to force logoff. Hey maybe I could insert dummy
>queries referring to another database between querying a schema and another.
>This way ODBC should drop the connection and subsequently re-logon with
>different credentials.
>
>> Obviously, the best solution is merging all data in *one single
>> schema* adding an extra column to all individual tables, denoting the
>> original schema. Whoever want to see the original data simply adds
>> and schema = '1' to their query.
>
>Database architectture is beyond my control and i think beyond the control
>of info guys as well, it is dictated by the application provider.
>
>> The second best solution (better : workaround) is setting up a
>> reporting user (which doesn't own any tables), and all the individual
>> schema's grant the select privilege on all affected tables to that
>> reporting_user.
>
>this sounds feasible.
>
>> You will then be able to run your queries by simply prefixing the
>> schemaname to all table names, ie schema_1.emp, schema_2.emp etc.
>
>Actually, within A97 tables are already named this way.
>
>thank you for you help.
>VA
>
>
>
>
Received on Thu Dec 08 2005 - 06:57:17 CST