Re: Poor man's data warehousing - Was: PLSQL: how to test if a table exists?

jhking wrote:

> You may well be right. I (fortunately) learn new things every day. One
> of the issues that lead me down the execute immediate path is that the
> client wants to be able to add sites (currently at 40) without having to
> call me in to write a bunch of code.
> The process is run a sql*plus script to create the dblink and add a row
> to a site table to enable processing. If you'd care to share a pointer
> or two about how to do that 40 site processing and allow user adds to
> the list of sites, I'd be very interested to hear it.
> Just in case this sounds sarcastic when you read it I'm not trying to be
> a smart guy here. You may well have insights I don't and I'd
> appreciate any of them.
> I had considered writing a code generating package so they could fire
> the script and get a new set of packages for the new site, but I was
> afraid I'd wind up with 300 tables * 40 sites = 12,000 packages and 300
> new ones every time the client added a site. The 300 packages are scary
> enough, but those I did create via a some tables and generating the
> per-table packages.
> Daniel Morgan wrote:
>

>> jhking wrote:
>>
>>> That having been said I worked recently in a poor man's data 
>>> warehouse environment where I was gathering data from several dblinks 
>>> to remote sites with 300-odd nearly identical tables.  Using a 
>>> data-driven, execute immediate style approach was the only way to get 
>>> the data gathering part done in a reasonable amount of development time.
>>
>>
>>
>>> <snipped>
>>
>>
>>
>> So you say.  ;-)
>>
>> I think Sybrand, and I almost certainly, would argue that your 
>> assumption that it was the only way to do it in a reasonable amount of 
>> time is not valid.
>>
>> Perhaps it is the only way you know how. But that may not hold true 
>> for others. I would suggest a re-examinination of your assumptions.

I'd like to know a bit more about this situation. What do you mean when you say "nearly identical tables"? Seems to me that any difference means they are completely different. True? False?

How about constraints? Do they need to be enforced on the polled data? Is the data after it is polled read only or updatable?

And finally what made you write this:
"300 tables * 40 sites = 12,000 packages and 300 new ones every time the client added a site."?

A package contains multiple procedures and functions. Why wouldn't one site = one package?

And is there some reason you've rejected using materialized views?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)