Re: Accessing tables on multiple schema

setsun_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
dba_at_nospam.peasland.net
http://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 from http://www.teranews.com