1 2 Previous Next 17 Replies Latest reply on Aug 1, 2013 9:12 PM by Pablolee Go to original post
      • 15. Re: Synonyms on the fly

        I think I get you. I'll have to play with that to see if it'll fly.

        Thanks again.

        • 16. Re: Synonyms on the fly

          800049 wrote:


          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.

          • 17. Re: Synonyms on the fly

            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.

            1 2 Previous Next