Re: Dynamice SQL in Oracle

On Sat, 14 May 2005 08:26:32 -0700, DA Morgan <damorgan_at_x.washington.edu> wrote:

>Andy Hassall wrote:
>
>> On Fri, 13 May 2005 22:24:07 -0700, DA Morgan <damorgan_at_x.washington.edu>
>> wrote:
>>
>>>True but you are ignoring the point. There is NEVER a reason do create
>>>a table within a PL/SQL block. EVER!
>>
>> If an application is modifying its own schema in such a way, then yes, agreed,
>> it's almost certainly a sign of poor design.
>>
>> But there do exist legitimate cases for use of "dynamic DDL" in upgrade
>> scripts run in maintenance cycles, as one of several possible approaches to
>> handling upgrading an application from some arbitrary older version to the
>> current one.
>
>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:

(1) Have separate upgrade scripts, v1->v2, v2->v3, and maybe a consolidated v1->v3. No need for dynamic SQL here, they're just straight SQL and DDL scripts. Customer's DBA selects the script that applies and runs it.

(2) Have a single upgrade script, which determines what it needs to modify based on the version it's being run against. If run against v1, it creates SPROCKET. If run against v2 it adds the new column to SPROCKET.

 If you want to do conditionals with just SQL*Plus, you have to use PL/SQL. If you want to do DDL within conditionals in PL/SQL, you have to use dynamic SQL. You could always use another tool, e.g. shell scripts or Perl to handle the branching for different versions, but you're then introducing more dependencies - PL/SQL can do it, so it's reasonable to use that. It's one of many approaches, but does require dynamic DDL.

 One motivation for this is to handle errors. If something goes wrong, you want to be able to resume the upgrade. You either do this with an application wrapped around the upgrade scripts, which keeps track of how far it's got, or make the upgrade process look for things that need changing and only perform those actions; so if an upgrade fails half-way through, it'll skip over the first half when resumed since it's already done.

 Hard to get right, but essential for large applications.

>2. This hardly ever requires remote access to the server.

 I assume this is something to do with Mark's reply since it doesn't seem to have anything to do with this?

 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 ;-)

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