declare
v_table_name Varchar2(31):='EMP'; -- Input table name
v_column_name varchar2(31):='ENAME'; -- Input column name
v_data_type varchar2(15):='varchar2(30)'; -- data type
v_exist number;
begin
select count(*) into v_exist from user_tab_cols where table_name=v_table_name and column_name=v_column_name;
if v_exist > 0 then
execute immediate 'Alter table '||v_column_name||' add ('||v_column_name ||' '||v_data_type||')';
end if;
end;

Whoa!!! Dynamic, programmatic DDL always scares me (even migrations in ActiveRecord scare me). I can't imagine why people would want to do stuff like this on a regular basis. It's a database, not a bunch of flat files!

I can see this can be usefull when running database releases into the database when it needs to be re-runable so that the release can be tested or additional objects added to the release. I have used a similar proc when releasing code and have not found a way of making a release proc rerunable without having these types of checks. Tom if you have a method/techneque to do this or you would do this a complely differnt way I would like to hear it

tom can it possible to alter table with the following he is using wrong table name.

execute immediate 'Alter table '||v_column_name||' add ('||v_column_name ||' '||v_data_type||')';

Tom,

Features like 'drop table IF EXISTS' does not exists in Oracle, So, often we need to use dynamic SQL to make scripts re-executable for Test / Live deployment. As a part of enhancements and maintenance work, there are needs when we create scripts and then pass-it to support to deploy them to Test database and Live database. But, there are number of cases when the scripts might fail or should be killed in between because it is taking unexpected time.

eg. Support killed the session because the one of the script containts 'add column with default value'. It was OK on development because of small data on DEV as compare to others. So, they now re-run the deployment scripts and see lots of errors on the screen (which are not actually errors, it occurs because of re-execution.

To, avoid this, we use execute immediate like following :

SQL> host type add_col.sql
set feedback off
begin
  execute immediate('alter table emp drop column new_column');
exception when others then
  if sqlcode = -00904 then
     null;
  end if;
end;
/
set feedback on

alter table emp add new_column number(10);


SQL> @add_col.sql

Table altered.

SQL> @add_col.sql

Table altered.

SQL> @add_col.sql

Table altered.

What is your take on this?

Dear "Reader",

what is the difference between

exception when others then
  if sqlcode = -00904 then
     null;
  end if;
end;

and

exception when others then
   null;
end;

?

Thanks for pointing my mistake... sorry for that. the exception handling can be re-written in a "proper" way.

.. so I re-write the SQL

sql>host type add_col.sql
set serveroutput on
set feedback off
declare
  invalid_column exception;
  PRAGMA exception_init(invalid_column, -00904);
begin
  execute immediate('alter table emp drop column new_column');
exception when invalid_column then
  dbms_output.put_line('Adding column for first time');
end;
/
set feedback on

alter table emp add new_column number(10);

sql>

Please post this code on forum, it's my code which is fastest way to determine a field exist in a table

 Create function exist(v_table in varchar2, v_col in  varchar2) 
 Return integer is
 Res integer:= 0;
 Begin
   Begin
      Execute immediate 'select ' || v_col || ' from '|| v_table;         
      Res:=1;
      Exception when other then null;
   End;
Return (res);
End;