Re: DDL in PL/SQL

Vladimir M. Zakharychev wrote:

> Doing DDL from PL/SQL is generally bad idea - and you already
> faced the consequences (one of the many). SQL in PL/SQL is
> parsed and resolved at compile time, not at runtime. At compile
> time there is yet no column PHRVERSION, thus you are getting
> this error. The only way to avoid this is to use dynamic SQL
> everywhere:
>
> if hasversion = 0 then
> execute immediate 'alter table fsdbversion add (phrversion number(4)
> default 0 not null)';
> execute immediate 'update fsdbversion set phrversion = :ver' using 3740;
> end if;
>
> Another consequence is that as soon as you issue any DDL, your
> transaction is committed (and all work you did before DDL is now
> permanent, even if you hit an error later and would want to rollback,
> everything done before DDL will not be rolled back) even if you don't
> want it to be.
>
>
>

The implied commit can be overcome by encapsling the DDL in a autonomous transaction. The DDL will be there, but all other work can be rolled back.

/Svend Jensen Received on Tue Jun 18 2002 - 14:40:28 CDT