Re: Dynamice SQL in Oracle

From: psoug <damorgan_at_psoug.org>
Date: Sun, 15 May 2005 08:55:00 -0700
Message-ID: <1116172256.238884@yasure>

Andy Hassall wrote:

> I specifically mentioned Oracle's upgrade scripts at the end of my last post,
> because they do use DDL to alter/create objects in EXECUTE IMMEDIATE within
> PL/SQL.
>
> catalog.sql and catproc.sql create views, synonyms and stored procedures.
> These can all be created with the "or replace" option, overwriting the existing
> object. But neither of those scripts create tables or indexes.
>
> If you look in Oracle's actual upgrade scripts that affect such objects,
> you'll find they do use DDL in EXECUTE IMMEDIATE to do this. See for example
> line 832 in $ORACLE_HOME/rdbms/admin/c0902000.sql, and numerous other examples
> in the same file.

Good point. So it would seem that best practice would be to do what Oracle does ... if for no other reason than the fact that it works.

>>> Anyway, if you think DDL within execute immediate is always wrong, then first
>>>place you should look is Oracle's own upgrade scripts... they use it liberally
>>>;-)
>>
>>I never said DDL within EXECUTE IMMEDIATE is always wrong. That has
>>never been my statement. But creating, altering, and dropping objects
>>using PL/SQL is quite another matter entirely. It has no place in a production 
>>application.

>
>
> But it has places in the associated maintenance applications/scripts.
>
> Anyway, how can you separate out "DDL in EXECUTE IMMEDIATE" from "creating,
> altering and dropping objects using PL/SQL" - doesn't leave much?

GRANT and REVOKE. That is enough for me. ;-)

Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond) Received on Sun May 15 2005 - 10:55:00 CDT