Hi Hoek, that's the problem with over simplifying my description. Apologies for that.
My most recent response to BluShadow shows that there is more than one schema used in the proc, which must be 'switched' to different schemas on demand. Therefore I don't think that altering current_schema would work, but I'm open to being shown up for being hard of thinking...
You can do that 'switch' using the method that Arild provided
Can't you simply have schema as a parameter in your procedure?
exec mypackage.myreportprocedure ( dataschema => 'user1', ....
That's arguably clearer/ more readable than using synonyms which sometimes change?
You would use ONE dynamic SQL statement to 'switch' to the schema passed by the parameter. using the code that Hoek provided.