Re: Question: How to handle duplicate table names?

Todd Bealor <tbealor_at_secret.mda.ca> wrote:

>I would appreciate hearing from anyone who has any insight into
>handling the use of duplicate table names. In the production
>version of the applictation this will not be a problem as the
>tables will be accessed via SQL*net thus having different remote
>database extensions.
>
>The problem is that we are building a prototype using one database.
>While it is possible to place the tables into separate table spaces,
>I have not come across any mechanism for specifying which table space
>to look for a table. The only differentiation mechanism that I can
>find in Oracle is the use of the table owner as a prefix to the table
>name.
>
>i.e. GROUP1.STATUS_CODES
> GROUP2.STATUS_CODES
>
>With this approach we would have to transform all of the table names
>in the application programs when we port them to the actual production
>server. i.e. GROUP1.STATUS_CODES --> STATUS_CODES_at_SITE1 etc...
>
>Any alternative solutions would be greatly appreciated.

   In prototype and production version use synonyms:

   For example:

      In prototype:
           create synonym POJAMA for GROUP1.STATUS_CODES;
           create synonym PEOPLE for GROUP2.STATUS_CODES;

      In production:
           create synonym POJAMA for STATUS_CODES_at_SECRET.MDA.CA;
           create synonym PEOPLE for STATUS_CODES_at_TOPSECRET.MDA.CA;

   In both cases your then can use:

        select P.SIZE, E.NAME
        from POJAMA P, PEOPLE E
        where SIZE = 'ONE SIZE FITS ALL'
          and P.NUM = E.NUM;                       :-)

Valery Received on Fri Jun 07 1996 - 00:00:00 CEST