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

From: jhking <jhking_at_airmail.net>
Date: Fri, 05 Dec 2003 16:21:45 -0600
Message-ID: <bqr0bk$9n6@library1.airnews.net>

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.
>
Received on Fri Dec 05 2003 - 16:21:45 CST