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

Don't violate the rules of normalization. Put identical data into a single table. Then comes the question of how to access it. One way, as already mention is with VPD using DBMS_FGAC. Another is to use the SET_CLIENT_INFO and READ_CLIENT_INFO capabilities of DBMS_APPLICATION_INFO. You will find a demo showing how to do this at: http://www.psoug.org/reference/dbms_applic_info.html.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org