Re: dbms_sql.parse Question

amogh wrote:
> I would want to just parse it if I'm writing an application
> to parse user inputed DDLs and add them to a SQL script.
> I would'nt want to add incorrect DDLS into the script. Right
> now I will have to drop each of these objects since they
> would be created by the parse procedure.
>
> I feel this is a valid need.

It may look like a valid need until you evaluate it a bit further:

you validated a DDL statement *on this particular database* and found it is valid, so you stored it in a script. The script is then executed against a *different database*, where it may not be valid. Net result? Your script possibly ruined that another database, because your DDL is not valid there. Best case is when it wasn't executed at all. Even if ithey're mere outlines, these outlines may lock suboptimal plans for the database they are actually created on, as compared to the database they were initially validated against (and I should stress that outlines are very special objects; simply parsing them do not validate them in terms of performance - you are only ensuring they are syntactically correct, but you are not ensured they lock the most optimal plans; this is something only a human can decide (yet? :)...)

This being said, if you still think you need a way to parse-but-not-execute DDL then I'm afraid your only option is to write your own statement parser, as Oracle doesn't provide a user-invokable way of parsing DDL without executing it. This is by design, no workarounds. And I doubt there's a database out there that is designed differently. DDL is very special language, it has very special purpose, and there's no way around.

Dixi,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Apr 07 2006 - 15:13:10 CDT