Re: Retrieving data without specifying table owner

Thanks Ben. I've implemented your suggestion and it is working well.

You might be right with your argument against using the owner name in select statements. I guess I was just trying to translate the method that the databases seem to be pushing us in with their syntax requirements.

In the example you showed below, can't you simply create different database instances? That's how I'd handle the situation using SQL Anywhere (I'm an Oracle newbie). Is it hard to create multiple database instances on one Oracle server? More accurately, is there some reason why you shouldn't?

It seems a little spooky to issue a select without an owner name (even though that's what I'm doing for now). I imagine you could get into some errors that might be hard to track down. What if a synonym was left out of one of the scripts, or was left pointing to the wrong table?

It also seems like a lot of extra work to create synonyms for each database object for each user, not to mention recreating them each time you want to change the tables you see. Selecting the appropriate database seems a lot easier.

I'm trying to get a feel for this Oracle thing. It's a whole new world. Please comment.

Ben Ryan <benryan_at_my-deja.com> wrote in message news:80ev20$3sl$1_at_nnrp1.deja.com...
> In article <lFBW3.4468$T4.1217661_at_news1.rdc1.on.wave.home.com>,
> "Jeff Van Dusseldorp" <jeff.van_at_bigfoot.com> wrote:
> > Is there anyway to make Oracle find the tables without specifying
> > the owner name.
> >
> > CONNECT SampleUser/password;
> > SELECT * FROM Student;
> >
> > The following error will occur:
> > ERROR at line 1:
> > ORA-00942: table or view does not exist
> >
>
> Creating either a private or public synonym for SystemOwner.Student
> will allow Oracle, when connected as SampleUser, to find Student.
>
> > Now, good programming technique would probably insist that you
> > specify the table owner in each select.
>
> Disagree. When we develop applications we need to have three or
> four "environments" for the purposes of, say, development, QA,
> training and production. Typically, development, QA and Training
> will be in the same instance of Oracle as separate "table owning"
> schemas. Therefore, we do not want to see the owner name specified
> in every select, insert, update and delete statement, even though
> there are three versions of every table that make up the
> application. A user account for QA purposes will have synonyms for
> tables in the QA owning schema, etc.
Received on Thu Nov 11 1999 - 13:16:21 CST