Accessing tables on multiple schema

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 Received on Fri Aug 17 2007 - 06:49:40 CDT