SQL & PL/SQL

Hi,

I'm hoping there's a really simple answer to this...

We have an Oracle database containing many schemata, all customer schemata are identical but hold different data pertaining to each customer.

We also have a 'reporting' schema which has select privileges on all the other schemata.

In this reporting schema we have various ref cursor returning procedures, and we use dot notation to reference the required schema - E.g. 'select * from SCHEMA1.Customers'.

What I'd like to do is pass a schema name as a parameter to a procedure so the one procedure could cover all schemata. - E.g. call p_GetCustomers('SchemaNameParameter'). The procedure would then return the customer data for a given schema.

All I've come up with (thus far) is using EXECUTE IMMEDIATE.

E.g. (very simplified)

create or replace procedure p_GetCustomers(pSchema in varchar2) as

vExecuteString varchar2(100);

begin

vExecuteString := 'select * from '|| pSchema ||'.ICustomers';

execute immediate vExecuteString ;

end;

This does the job in extremely simple procedures/functions, but we have some heavy-duty procedures that would make it extremely hard to do the concatenation as they mention the schema all over the place.

What I'd ideally like is something like...

create or replace procedure p_GetCustomers(pSchema in varchar2) as

begin

select * from pSchema.Customers;

end;

But the above won't compile (table or view does not exist...).

I'd be extremely grateful if somebody could advise on this...

Thanking you in advance (and hope!)...

Mike

Oracle 10.g - Windows - Oracle SQL Developer 1.1.2