CODEWITHSUNDEEP
oracle-databaseoracle11galter-table
user968441
user968441

Reputation: 1471

Alter Table if column doesn't exist

In a table, I want to check if a particular column exists or not. If the column does not exist, I want to alter the table and create that column.

I am using Oracle 11g.

Upvotes: 5

Views: 26490

Answers (4)

grokster
grokster

Reputation: 6277

Or, you can ignore the error:

declare
    column_exists exception;
    pragma exception_init (column_exists , -01430);
begin
    execute immediate 'ALTER TABLE db.tablename ADD columnname NVARCHAR2(30)';
    exception when column_exists then null;
end;
/

Upvotes: 3

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

If you just want to add a column if it doesn't exist, just issue an ALTER TABLE ADD (mycolumn ...);. If the statement raises an exception (ORA-01430: column being added already exists in table), the column was already there and you can ignore the exception.

Upvotes: 2

Grzegorz W
Grzegorz W

Reputation: 3517

Try this:

declare p_count NUMBER;

select count(1) int p_count
from ALL_TAB_COLUMNS 
where OWNER = '<SCHEMA_NAME>' 
and TABLE_NAME = '<TABLE_NAME>' 
and COLUMN_NAME = '<COLUMN_NAME>';

IF p_count = 0 THEN
    --add your column
END IF;

Eventually (depending on the rights) You can use user_tab_columns.

Upvotes: 8

Satya
Satya

Reputation: 8881

look into user_tab_columns table to check if the column exists , and do accordingly

Upvotes: 1

Related Questions