Re: Validating dynamic SQL

I can see one advantage to storing your sql in tables, in that as opposed to embedding them within your packages / forms / or reports, you can keep them in a table. If a change is needed, simply update the table. Even keep a date track history of changes if you will. This avoids touching your packages / forms / or reports... which sometimes as I have discovered can lead to problems because you mis-type something you didn't mean to.

I haven't stored SQL in tables yet, but have thought about it. Interesting enough Oracle themselves does this with their Oracle Applications HR Payroll system. They store the WHERE condition in a rules table and then build it on to the queries on the fly with DBMS_SQL commands.

I would guess in our posters case, you simply could query the string for keywords like CREATE or DROP.

On Fri, 13 Jun 2003 22:57:48 +0100, "Paul Brewer" <paul_at_paul.brewers.org.uk> wrote:

>"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
>
>

MylxHQ - Oracle Resource Portal
http://oracle.mylxhq.com Received on Fri Jun 13 2003 - 19:34:43 CDT