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

"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 Sat Dec 03 2005 - 14:47:51 CST