Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Schema Access...A better way? (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Dynamic Schema Access...A better way? [message #343226] Tue, 26 August 2008 12:35 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I am working currently on a system which is designed using schema as snapshots of database data. When working with data in these snapshots it is often required that user be able to pick which snapshot date to use as the data source. I got the idea from here on ORAFAQ to use:
ALTER SESSION SET CURRENT_SCHEMA=<SCHEMA_NAME>

This works fine when I am dealing with data from just one snap-shot application, but once I start dealing with data from more than one this idea no longer works.

The "simplest" solution is to use EXECUTE IMMEDIATE or other dynamic SQL approach, this does get the job done but creates messy hard to follow code, and I believe has performance implications as well.

My possible solution is to create a set-up calling program which creates synonyms for every table I will be using in my main program. Then this program would compile the body of the program which will be doing the work. To demonstrate what I mean see my example code:
CREATE OR REPLACE PACKAGE test1
IS
   PROCEDURE run_me;
END;

CREATE OR REPLACE PACKAGE BODY test1
IS
   PROCEDURE run_me
   IS
      val   NUMBER;
   BEGIN
      SELECT COUNT (*)
        INTO val
        FROM nagel.insp_violation;
   END;
END;

CREATE OR REPLACE PROCEDURE test_valid
AUTHID CURRENT_USER
IS
BEGIN
   EXECUTE IMMEDIATE 'drop synonym nagel.insp_violation';

   EXECUTE IMMEDIATE 'create synonym nagel.insp_violation for mcmis080822.insp_violation';

   EXECUTE IMMEDIATE 'alter package nagel.test1 compile body';

   test1.run_me;
END;

BEGIN
   test_valid;
END;

This does work at least in my tests and keeps the amount of dynamic code to a minimum, but can anyone think of a better way?
Re: Dynamic Schema Access...A better way? [message #343265 is a reply to message #343226] Tue, 26 August 2008 15:41 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Possibly...

Create a view called ALL_SCHEMAS or something, and basically do something like this:-

create or replace view ALL_SCHEMAS_TABLE1
select 'SCHEMA_JAN' as schema_name, a.*
from SCHEMA_JAN.TABLE1 a
union all
select 'SCHEMA_FEB' as schema_name, a.*
from SCHEMA_FEB.TABLE1 a;


etc...

Each time you load a new schema, just re-create the view dymically.

Not sure how the performance will work out on this one - depends on what you are doing, but it neatly gets all your data into a single view with no dynamic sql.

[Updated on: Tue, 26 August 2008 15:41]

Report message to a moderator

Re: Dynamic Schema Access...A better way? [message #343285 is a reply to message #343226] Tue, 26 August 2008 18:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Maybe you could also research PARTITIONING...

It is much like the schema crap you are doing, just with a different set of headaches.

Kevin
Re: Dynamic Schema Access...A better way? [message #343286 is a reply to message #343226] Tue, 26 August 2008 18:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am sorry

Whey I used the word CRAP, I did not mean to suggest what you were doing was crap, just my slang.

Kevin
Re: Dynamic Schema Access...A better way? [message #343534 is a reply to message #343286] Wed, 27 August 2008 08:32 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
Kevin Meade wrote on Tue, 26 August 2008 18:24
Whey I used the word CRAP, I did not mean to suggest what you were doing was crap, just my slang.


No I think you got it about right...it certainly makes working with the data much more difficult than it needs to be. Partitioning would be a good solution to the problem and we have started using it in a couple specific areas....but wide spread use is not here yet and whether it will ever come is doubtful.

The global view idea I like and it should be possible to dynamically create the entire thing. There are a couple hick-ups. First the structure of the underlying tables does change as time goes on and second there will be cascading invalidation once the views are re-created and obviously I want to look at the performance, but I don't think any of these are insurmountable....and I get the bonus of the solution being re-usable by any project.

Thanks for the replies the view was not something I had considered and it seems (at face value anyway) like a pretty good idea.

Andrew
Previous Topic: Accessing Image
Next Topic: Drop ... Cascade Constraints
Goto Forum:
  


Current Time: Tue Dec 06 04:29:19 CST 2016

Total time taken to generate the page: 0.11973 seconds