Re: Dynamice SQL in Oracle

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 14 May 2005 16:50:28 -0700
Message-ID: <1116114380.801003@yasure>

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?

> Hard to get right, but essential for large applications.

Larger than SYS? Larger than Oracle Financials GL module? I still disagree that PL/SQL is required.

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

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Sat May 14 2005 - 18:50:28 CDT