Re: DDL in PL/SQL

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.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Jacek Pichla" <jpichla_at_softsystem.pl> wrote in message news:aenhpf$fe8$1_at_news2.ipartners.pl...


> 

> When I launche below piece of code:

> 

> 

> 

> if hasversion = 0 then

>     cid := dbms_sql.open_cursor;

>    dbms_sql.parse(cid,'alter table fsdbversion add (phrversion number(4)

> default 0 not null)',dbms_sql.native);

>    foo := dbms_sql.execute(cid);

>    dbms_sql.close_cursor(cid);

> 

>     update fsdbversion set phrversion = 3740;

> end if;

> 

> 

> I got an error:

> ORA-06550: line 33, column 27:

> PLS-00417: unable to resolve "PHRVERSION" as a column

> 

> the same error I have using <<execute immediate>>, ie:

> 

> if hasversion = 0 then

>     execute immediate 'alter table fsdbversion add (phrversion number(4)

> default 0 not null)';

>     update fsdbversion set phrversion = 3740;

> end if;

> 

> 

> 

> No exception from execute immediate nor  dbms_sql.execute(cid) is raised;

> 

> Please help me how to create a column and update it in same PL/SQL block.

> 

> --

> Jacek Pichla

> 

>