Database Reference
In-Depth Information
select * from #OWNER#.emp
resolves to
select * from DOUG.emp
In the most common implementations, a workspace is created and associated with a
single underlying database schema. The applications developed in that workspace have
their “parse as” schema set to the only schema associated with the workspace and use
the database objects belonging to that schema.
Where a workspace has more than one schema assigned to it, things can become a
little more complex. You might be tempted to think that if you associate three schemas
with a workspace, any application in that workspace can automatically access the data
in all three schemas. However, you would be mistaken.
Because an application is assigned one—and only one—“parse as” schema, all SQL
statements and PL/SQL calls are executed as that schema. Although the workspace
may be associated with multiple schemas, the application itself isn't. If you want to ac-
cess data in a schema other than the application's “parse as” schema, you must make
sure the correct database-level grants are in place, just as you would when using any
other Oracle tool or development environment.
Take the example shown in
Figure 2-4
, where two tables you wish to join as part of
a SQL statement are owned by separate schemas.
Figure 2-4.
Tables joined across schemas
If your “parse as” schema is
DOUG
, then you must be specifically granted privileges
on the objects in the
JOEY
schema to be able to access it. To do this, you sign on to the
database as
JOEY
(or as a DBA) and grant the appropriate database privileges on
JOEY.DEPT
to
DOUG
.
In this example, if you needed to join the two tables together in a
select
state-
ment, granting the
SELECT
privilege on
JOEY.DEPT
to
DOUG
would suffice. Then
you could write your
select
statement as follows:
