Re: Dynamice SQL in Oracle

On Sat, 14 May 2005 16:50:28 -0700, DA Morgan <damorgan_at_x.washington.edu> wrote:

>Andy Hassall wrote:
>
>>>Using your own words "there do exist legitimate cases for use <snipped>
>>>in upgrade scripts" and I agree. If you are running an upgrade script by
>>>all means run an upgrade script. But two points:
>>>
>>>1. This does not EVER require dynamic SQL
>>
>>
>> Consider this situation; there exists three versions of an application:
>>
>> v1: Initial version.
>> v2: Adds a table SPROCKET.
>> v3: Adds a column SPROCKET_WIDGET_SIZE to table SPROCKET.
>>
>> You want an upgrade mechanism to allow the customers who are on either v1 or
>> v2 to go to v3. What are the options? Some of them are:
>
>There is a very simple solution. Go to $ORACLE_HOME/rdbms/admin and take
>a look at catalog.sql, catproc.sql, etc. How does Oracle handle it? Not
>with PL/SQL?

 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.

>> Hard to get right, but essential for large applications.
>
>Larger than SYS? Larger than Oracle Financials GL module?

 No, I think a robust install and upgrade mechanism is essential for applications much smaller than them.

>I still disagree that PL/SQL is required.

 Oracle disagree with you, because that's the approach they've selected (out of several possible approaches, as I keep saying) in several of their own upgrade scripts.

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

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool