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. If I
spent a year hand-coding for each special case would it be more
economical of database resources, sure. Would that have met the
customer's need for aggregated data ASAP, nope. That being said some of
the tables will wind up with custom code but only as needed when the
performance issues noted below bite.
Sybrand Bakker wrote:
> Tables don't disappear on the fly, unless you have a very whacky
> environment. Checking for existence should be redundant and been dealt
> with by exception handling. Misusing the datadictionary over and over
> again, to check something what doesn't need to be checked will have a
> noticeable impact on your application as the dictionary cache will
> grow and trim your shared pool. Your shared pool however will grow
> because you are using dynamic sql everywhere. Your application will be
> unscalable. Now would you really like the end-user to experience how
> an unscalable application works? Need I say more?
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Thu Dec 04 2003 - 18:45:53 CST
