Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Validating dynamic SQL
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.....
TIA
-- jeremyReceived on Fri Jun 13 2003 - 10:42:00 CDT