Re: cannot call alter table from within block

Ditto to Sybrand, you need to do dynamic sql. If you have the Oracle Press Book 'PL/SQL Programming', there is a whole chapter discussing how to do this.

HTH
EP

In article <965168721.954.2.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> You need to use dbms_sql to do this.
> Example code has been posted numerous times, you should be able to
 find it
> in the deja archives.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> <ljunquera_at_my-deja.com> wrote in message
 news:8m7e9e$fdd$1_at_nnrp1.deja.com...
> > Apparently I cannot call an alter table statement from within a
 block.
> >
> > DECLARE
> > lv_col NUMBER;
> > BEGIN
> > BEGIN
> > select count(*)
> > into lv_col
> > from all_tab_columns
> > where table_name = 'EMPLOYEE'
> > and column_name = 'MIDDLE_INITIAL'
> > and owner = 'HR';
> >
> > if lv_col = 0
> > THEN
> > alter table employee add middle_initial varchar(255)
 null;
> > END IF;
> > END;
> > END;
> >
> > How do I script conditional changes to my database?
> >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Aug 02 2000 - 00:00:00 CDT