Re: Accessing tables on multiple schema

On Aug 17, 9:19 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

David et al

Thanks for the inputs.
I had thought about David/Shakespear's option to add a column to 'master' table (for each identical table in all schema) and identifying records by passing the schema name. But since this application pertains to holding financial data for a Bank catering to different geogrophical zones, and any screwup in inserting the schema name to the master table would result in inconsitency ( and I would be fired! )...

Looking at the options I have, I think I need to design based on INVOKER RIGHTS model,

Thank you very much.
Arun Received on Sat Aug 18 2007 - 08:56:14 CDT