Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Accessing tables on multiple schema

Re: Accessing tables on multiple schema

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 17 Aug 2007 14:59:24 +0200
Message-ID: <46c59bb7$0$226$e4fe514c@news.xs4all.nl>

<setsun_at_gmail.com> schreef in bericht
news:1187351380.884930.65630_at_r23g2000prd.googlegroups.com...
> 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
>

I don't see any other solution here than using dynamic sql, but personally I hate to do so...
I think your DBA has put you up with a very bad design, and some of the posters in this group would suggest he'd be fired, but I suggest you talk to him again and ask for the specific reasons for this design. Maybe he has some valid arguments (?). If he has, I'd like to know them, so please post them here!

One (horrible) solution I have seen once for a similar problem (in this case, not the schema, but the table name was specified by a parameter)
 is something like this one (please don't kill me folks)

Select * from schema1.table_one
where p_parameter = 'schema1'
union * from schema2.table_one
where p_parameter = 'schema2'

Actually, this code is part of a package in which the programmer tried to create the 'universal report'. It took him 6 months and then he was (indeed!) fired.

Shakespeare Received on Fri Aug 17 2007 - 07:59:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US