| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: ODBC and Oracle
Mike,
> What you have stated is exactly how I would imagine that it would work, and
> if I log in via SQL*Plus then that is indeed what happens. This does not
> appear to happen when I log in via ODBC. I have found the following in an
> MSDN article Q115713. The following is referring to Oracle.
Are you using Microsoft's ODBC drivers or Oracle's? And are you connecting using the Microsoft Access Database Engine as the article suggests?
> "...
> You are the owner of the table and there's a duplicate table name in
> the database that has another owner. You issue this command:
>
> Set DS = DB.CreateDynaset("<tablename>")
I've never created a Dynaset so I can't help you there.
> The database engine resolves this ambiguous reference alphabetically
> by selecting the first owner of a table with the given table name. If
> the other owner happens to be first alphabetically, then you receive
> error 3078:
>
> Couldn't find input table or query "<ownername>.<tablename>"
>
> where <ownername> is the name of the other owner.
> ..."
I like one of the resolutions of this article: "Remove all duplicate tables and synonyms from the Oracle database."
> This article does not explicitly state an ODBC connection but it does appear
> to explain my problems.
It most likely is an ODBC connection that the article is using.
As for solving your problem... Since you can't reference an ambiguous table or synonym name, why not create a unambiguous synonym for the tables? For instance, if I owner SCOTT and owner JOHN that both have a DEPT table, instead of using SCOTT.DEPT and JOHN.DEPT, create two synonyms called SCOTT_DEPT and JOHN_DEPT that point to the respective tables. Then call that "unique" synonym in your code.
I do have to say that this is a unique problem. From what I've read in the article you mentioned, this might only apply to Microsoft products accessing Oracle.
Let me know how it turns out,
Brian
--
 |
 |