Re: Validating dynamic SQL

In article <3EEA150A.93017FF4_at_exxesolutions.com>, damorgan_at_exxesolutions.com says...
> Jeremy wrote:
>
> > In article <1b0kevsdjnq0gl6pnpk5f5d9q5v12df2ju_at_4ax.com>,
> > gooiditweg_at_sybrandb.demon.nl says...
> > > On Fri, 13 Jun 2003 16:42:00 +0100, Jeremy
> > > <newspostings_at_hazelweb.co.uk> wrote:
> > >
> > > >The idea is that this allows
> > > >good deal of flexibility in a certain area of the app.
> > >
> > >
> > > No. You would end up with a fully unscalable system.
> >
> > No you don't.
> >
> I'm not sure what you will create. But, I expect, scalable and fast, will
> not be among its attributes. People that think they are smarter than Oracle
> rarely are.

The situation is this: I know the app, the database structure and the likely data volumes. I know the nature of the query that would be used to poulate the table TAB1. TAB1 is a temporary table which will simply store the PK of rows that require further processing by the core app.

Access to store the SQL in the database will be given to only the highest-privileged app users (Sysadmin/DBA).

The need for something like this is because we have a very flexible system which each customer can implememnt differently. To put it in simple terms, our users are going to populate a table (of known structure) with data from other tables (also of known structure). What we do not know until implementation of the app at each customer is the rules which will govern the data selection. We simply cannot know. So the idea is to provide a framework within which those criteria can be supplied by the user (or more accurately by the DBA/Sysadmin user based on the user's requirements) and stored in a SQL library of sorts.

I don't think I am 'smarter than Oracle' and have nothing to prove. My purpose is simply to provide the app functionality that my customers need using technology that I respect.

As another poster has pointed out elsewhere in this thread, Oracle do similar things themselves - writing for example SQL generators - and these are the Apps dev people. Now granted they are generating the SQL based on combining bits of data and therefore have control of what the completed SQL statement may look like.

I appreciate everyone's contributions to this thread....

cheers!

-- 

jeremy