Re: Accessing tables on multiple schema

On Aug 17, 9:41 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> set..._at_gmail.com wrote:
> > Hello Oracle Experts!
>
> > Unfortunately, I am given the task of designing database for a web
> > based application. Basically my Oracle DB would consist of several
> > schema's with identical tables something like the following
>
> > SCHEMA_A
> > TBL_ONE
> > TBL_TWO
> > SCHEMA_B
> > TBL_ONE
> > TBL_TWO
> > SCHEMA_C
> > TBL_ONE
> > TBL_TWO
>
> > and there would be another data retrieval schema consisting of Stored
> > Procs, Functions, Views etc that would act upon one of the above
> > schemas based on user selection.
>
> > SCHEMA_RETRIEVE
> > PROC_GET_FROM_TBL_ONE
> > PROC_GET_FROM_TBL_TWO
>
> > My question is: Is it possible for me to provide the schema name as an
> > input parameter to the procedures so that the procedure resolves the
> > schema and the table names at runtime?
>
> > i.e, is it possible to write something like the following inside the
> > procedure?
>
> > SELECT COUNT(1) INTO tbl_count_one FROM <SCHEMA_NAME>.TBL_ONE ?
>
> > I am aware of the INVOKER RIGHTS concept in Oracle. But I would want
> > to abstain from it as I would not want to mainitain multiple
> > connection states in my web application.
>
> > I am unsure if similar queries have been posted before. I would
> > appreciate if you could suggest a solution or point me to articles
> > suggesting solution to my problem.
>
> > Thank you very much in advance
> > Arun
>
> Instead of these different schemas, why not implement a Virtual Private
> Database (VPD)? You'll have less to manage. Each user will only see
> their own data. And you appplication development will probably be much
> easier.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -
>
> - Show quoted text -

If I read this thread correctly VPD isn't an option as the web application has the ability to process data across schemas, something VPD won't provide. I would tend to agree with the suggestion to add an OWNER or SCHEMA column to the table design to create a 'master' table (instead of three or more identical schemas) to identify whose data is represented, thus the 'owner' could be passed to the stored procedure and only the relevant data would be returned.

David Fitzjarrell Received on Fri Aug 17 2007 - 11:19:10 CDT