Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Validating dynamic SQL

Re: Validating dynamic SQL

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Fri, 13 Jun 2003 22:57:48 +0100
Message-ID: <3eea48df_2@mk-nntp-1.news.uk.worldonline.com>


"Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message news:MPG.195401191e5f0c5a989735_at_news.cis.dfn.de...
> Loking to put a feature into an app which allows a bit of SQL, written
> by a 'developer', to be stored in a table and then executed (via execute
> immediate) from another pl/sql procedure. The idea is that this allows
> good deal of flexibility in a certain area of the app.
>
> The purpose is to allow the developer to, via this piece of 'dynamic'
> sql, populate a named table let's call it 'TAB1'.
>
> The logic that the developer could use to populate the table may be
> complex - so we are going to allow for the 'dynamic' sql to be a pl/sql
> block.
>
> What we need to be able to do is to parse this 'dynamic' SQL when it is
> initially written to ensure that it doesn't drop / alter / create
> objects nor delete/update or otherwise interfere with the database.
>
> So the question is: is there an easy way to establish that the only
> objects subjected to DDL or DML in the 'dynamic' SQL is TAB1?
>
> Even of there is, I suppose there could be calls to OTHER procedures
> which perform other DML....
>
> Alternatively, I wonder if there is a way of setting a restriction
> immediately prior to execution that, until the restriction is unset, no
> DDL is permitted and the only object on which DML may be performed is
> TAB1?
>
> Incidentally, TAB1 will be defined as a temporary table will all rows
> deleted upon commit.
>
> Working in an 8.1.7 environment.....
>

This doesn't seem a sensible approach to me. You should seldom need 'temporary tables' in any version of any relational db, IMHO (Oracle 9i or no 9i - and yes I appreciate this is 8i). What is it exactly that you (or your developers) are trying to achieve? It sounds to me as though the design might need a rethink.

Regards,
Paul Received on Fri Jun 13 2003 - 16:57:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US