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.
Thanks for the response rp (I meant to reply sooner, but life gets in the way )
Yes I finally got what the others were getting at and I think that that is the most appropriate method. It only took 3 or 4 people to tell me before I got it! Doh!
Thanks to everyone for their input, appreciate it.