Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ODBC cannot access Oracle tables under different schemas in one session

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

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sat, 3 Dec 2005 15:29:54 -0500
Message-ID: <Ra2dna3EIsLZnQ_enZ2dnUVZ_tOdnZ2d@comcast.com>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:d6n3p1livtdgj2ag57ckqakceehfbm4h80_at_4ax.com...
> On 3 Dec 2005 18:48:06 +0100, "Vincenzoni Attilio"
> <aaa.avincenzoni_at_hotmail.com> wrote:
>
>>I'm an user, not IS guy, and i'm not sure whether i'm on topic. I have an
>>Access / Oracle problem, where Access people couldn't help.
>>
>>Until recently, several branches of our department either had their own
>>8.1.7 Oracle server, or they had separated instances (SIDs) on a bigger
>>host. The oracle database structure, table names etc, is the same in every
>>branch.
>>
>>In my workstation (W2000, Access 97) i had multiple ODBC names, each
>>referring to the proper server / SID of every branch. Within MS Access 97,
>>I
>>linked via ODBC tables from every server, and by pushing a command button,
>>I
>>could run a series of queries interrogating the same table of every
>>branch, one after another, and could consolidate their data into a local
>>table for subsequent analysis.
>>
>>Recently, the IS people set up a single department host running Oracle 9,
>>where every branch has their own tables under separated schemas. They all
>>refer to the same server and database name, then according to the id/pw
>>with
>>which they log on the server, they see their own tables.
>>
>>From Access, i again can see every branch's tables, by linking the server
>>and giving the proper id/pw when promped for. Hower, i am no longer able
>>of
>>running a series of queries, for after i have taken the data from the
>>first
>>table, when i try to query the second table under another schema, ODBC
>>reports an Oracle error that "the table name does not exist". However, if
>>i
>>exit Access, reenter, and manually run the query, the table is seen and
>>the
>>query completes successfully.
>>
>>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.
>>
>>Why does this happen and how can I run my queries under a single Access
>>session?
>>
>>Thank you for any insight you could provide.
>>
>>VA
>>
>>
>>
>>
>>
>>
>>
>>
> Apparently the data is in schema_1, schema_2, etc and you log in as
> the schema owner.
> 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'
> 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.
> 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.
> 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.
> If you would like to use synonyms you are going to have one set of
> synonyms per schema. This may be a nightmare.
>
> --
> Sybrand Bakker, Senior Oracle DBA

and if you can't get the Oracle DBAs to change the permissions for you (although if you have the schema passwords, you should be able to do it yourself... ) another work-around is to do some VB coding in Access to programatially connect to each schema via ADO -- your solution will depend on which skillsets are available and willing to help.

++ mcs Received on Sat Dec 03 2005 - 14:29:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US