Re: Referencing objects in a schema

David,
At least one way to do this, if it will work in your situation, is to use a user variable to hold the schema name. All code would have to be written to reference tables as &&schema_name..table_name. Then when the user logs into SQL, the login.sql could either do an SQL> define schema_name=<schmema> or ask the user for the schema they desire to use for this session.

NOTE: doing it this way would require the two (2) periods seperating the schema variable from the table name as illustrated above.

HTH
Jim Gregory

>==========David Odmark, 9/30/96==========
>
>Here's the problem: We are trying to simulate multiple
>independent "databases"
>inside one instance of the Oracle Server (v7.1) by storing
>replications of all
>tables inside different schemas (e.g. we set up two users, each
>of whom owns
>an identical set of objects). The idea is that application
>users, each with
>his/her own username and password, can log in to our app and
>then log in to
>different "databases" by internally switching all SQL references
>to point to
>the objects in a particular schema.
>
>The problem arises when we use views or stored
>procedures/functions. Since the
>functions themselves belong to the "database", not to the user,
>we also must
>create private synonyms to every one of these objects (there are
>hundreds).
>This is very time consuming.
>
>Is there any way to reference all objects belonging to a
>particular schema in
>one step? That is, can user Scott log in to Oracle as Scott, and
>use all of
>user David's objects, without having to a)establish a private
>synonym for each
>object or b)explicitly reference David's schema in each SQL statement?
>
>

Opinions expressed are mine and do not reflect those of my company or clients. Received on Tue Oct 01 1996 - 00:00:00 CEST