Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically Change Schema w/out Dynamic SQL
Dynamically Change Schema w/out Dynamic SQL [message #339778] Fri, 08 August 2008 08:02 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
This is kind of a shot in the dark to see if anyone has any alternative ideas for something.

I am working on a data system in which data snapshots exist as schemas in the DB which each contain identical tables from which I can pull data. I want to have the option to pull my data from a source table in any of the snapshot schemas can anyone think of a way other than an execute immediate or equivalent statement in which I simply sub out the schema name to accomplish this?

Andrew
Re: Dynamically Change Schema w/out Dynamic SQL [message #339781 is a reply to message #339778] Fri, 08 August 2008 08:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could do:

ALTER SESSION SET CURRENT_SCHEMA = schema_1;

-- do your stuff

ALTER SESSION SET CURRENT_SCHEMA = schema_2;

-- do your stuff

Re: Dynamically Change Schema w/out Dynamic SQL [message #339782 is a reply to message #339781] Fri, 08 August 2008 08:13 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
That looks perfect, need to play with it a bit but it would seem like a good idea to me, not sure how it will work with packages and dependencies, but it is a starting point which is all I needed. Thanks.
Re: Dynamically Change Schema w/out Dynamic SQL [message #339783 is a reply to message #339782] Fri, 08 August 2008 08:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
PAckages and procedures will be a problem. They will need to be defined with Invoker rights, rather than with Definer rights, in order to see the tables that you, the calling user can see rather than the tables that the user who compiled the package could see.

See here
and here
Previous Topic: CAN YOU HELP ME GET THE LOGIC
Next Topic: doubt in DBMS_JOB
Goto Forum:
  


Current Time: Mon Dec 05 21:24:26 CST 2016

Total time taken to generate the page: 0.12099 seconds