Re: cannot call alter table from within block

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.
Received on Wed Aug 02 2000 - 00:00:00 CDT