Re: ODBC and Oracle

Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote in message news:38C7A9CC.3318966B_at_edcmail.cr.usgs.gov...
> Mike,
>
> > I connect through ODBC using the username JSMITH and want to access
the
> > jsmith.location table. There is another table in the database called
> > demo.location.
> >
> > If I try to create a recordset with "Select * from location" then I get
an
> > error telling me that the table name is ambiguous.
>
> Are you sure this is happening as you wrote it? If you sign on to the
> database (using ODBC or whatever) as the SMITH user and you do SELECT *
> FROM LOCATION, the database will assume you meant the SMITH user, not
> the DEMO user. In fact, a user can not have two different objects with
> the same name. The database forces you to resolve ambiguity.
>
> If you need to select from the DEMO user, you have to explicitly say
> that you want the DEMO user's LOCATION table (DEMO.LOCATION).

Hi Brian,

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.

    "...
      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>")

   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.    ..."

This article does not explicitly state an ODBC connection but it does appear to explain my problems.

I hope that I am overlooking something simple :)

Thanks for your reply,
Mike. Received on Thu Mar 09 2000 - 17:31:51 CST