Re: Change SCHEMA in Stored Procedure

gilgantic_at_yahoo.com said...
> How do you change the SCHEMA name in a stored procedure? Below are snippet of
> my code. I try using ALTER SESSION SET, but having problems. What's the solution.
>
> CREATE OR REPLACE PROCEDURE test (schema_name IN VARCHAR)
> AS
> /** Define variables **/
> CURSOR a IS
> SELECT * FROM A;
> BEGIN
> .
> .
> ALTER SESSION SET current_schema = schema_name;
> .
> .
> END;
>

I am not sure if this is the same thing you're trying to accomplish (since you don't tell us what business issue you're trying to solve), but can't you use private synonyms?

Each user has a series of synonyms like:

  create synonym a for schema.a;

That way, your procedure just needs to reference table A in its cursor ... and each user will redirect A to SCHEMA.A thru their private synonym. To point A to a different schema, then I just have to change my synonym.

-- 
/Karsten
DBA > retired > DBA