Re: different schemas, same instance, same report

On Jan 11, 2:10 pm, kimberly.shaf..._at_gmail.com wrote:
> I am trying to figure out what or how to ask one of our system dbas the
> following:
>
> Say that we have three different schemas in one instance
> test_at_test1
> dev_at_test1
> qa_at_test1
>
> I want to run the same sql query against each schema, but my only
> options as of now are to
> 1) Either log in directly as the user (which I don't want my clients to
> do)
> 2) Log in and change the schema name prefaces in the report, i.e.
> select * from test.tablename or select * from dev.tablename.
>
> >From you experts, what are my simplest options? Is it possible to havea readonly user granted select privileges on all tables where the same
> sql query can be used depending on the login?
>
> Thanks so much.

Look at
ALTER SESSION SET CURRENT_SCHEMA=... You will have to issue
ALTER SESSION SET CURRENT_SCHEMA=test;
SELECT * FROM tablename;
-- selects rows from table test.tablename ALTER SESSION SET CURRENT_SCHEMA=dev;
SELECT * FROM tablename;
-- selects rows from table dev.tablename

HTH
Thomas Received on Thu Jan 11 2007 - 13:40:12 CST