CREATE PUBLIC SYNONYM Student FOR SystemOwner.Student
Jeff Van Dusseldorp <jeff.van_at_bigfoot.com> wrote in message
news:lFBW3.4468$T4.1217661_at_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 - 10:06:42 CST
