Retrieving data without specifying table owner

From: Jeff Van Dusseldorp <jeff.van_at_bigfoot.com>
Date: Thu, 11 Nov 1999 15:54:57 GMT
Message-ID: <lFBW3.4468$T4.1217661@news1.rdc1.on.wave.home.com>

I'm working on the conversion of a SQL Anywhere database to Oracle. In SQL Anywhere, if you select from a table name and don't specify the owner, it will still find the table as long as it is visible to you.

Now, good programming technique would probably insist that you specify the table owner in each select anyway, but I'm left with miles of legacy code that didn't. If I can avoid rewriting each SQL script, it would make me happy.

Is there anyway to make Oracle find the tables without specifying the owner name.

I'll give some pseudo-code examples below to describe my problem in more detail.

////////////////////////////////////////

SQL Anywhere:

GRANT CONNECT TO SystemOwner IDENTIFIED BY password; GRANT DBA TO SystemOwner;
GRANT GROUP TO SystemOwner;
CREATE TABLE SystemOwner.Student

(

    Name CHAR(30)
    );

GRANT CONNECT TO SampleUser IDENTIFIED BY password; GRANT DBA TO SampleUser;
GRANT MEMBERSHIP IN GROUP SystemOwner to SampleUser;

CONNECT SampleUser;
SELECT * FROM Student;

The select will return 0 rows (that's good).

////////////////////////////////////////

Oracle:

GRANT CONNECT TO SystemOwner IDENTIFIED BY password; GRANT DBA TO SystemOwner;
CREATE TABLE SystemOwner.Student

(

    Name CHAR(30)
    );

GRANT CONNECT TO SampleUser IDENTIFIED BY password; GRANT DBA TO SampleUser;

CONNECT SampleUser/password;
SELECT * FROM Student;

The following error will occur:
ERROR at line 1:
ORA-00942: table or view does not exist

Thanks for reading. Any help would be appreciated

Jeff Van Dusseldorp
PlannSoft Received on Thu Nov 11 1999 - 09:54:57 CST