Can I check if table or column exists before altering it? [duplicate]

Question

I'm working on some Oracle DB problem. Is there any possibility, to check if table or column exists, before I want to modify it? If it doesn't, the code should not execute.

I've already tried to check if this table exists, by counting it but it seems to don't work with ALTER.

declare 
    w NUMBER;
begin
    select count(*) into w from ALL_TABLES where TABLE_NAME='TAB';
     if(w>0) then
        alter table TAB
            add COLUMN_1 varchar(20);
     end if;
end;

It seems to don't work with ALTER statement. Also, I wanted to check the same for adding column (if column not exists).

Answer 1

You have to use ALL_TAB_COLUMNS:

declare 
    t NUMBER;
    w NUMBER;
begin
    -- checking if table exists
    select count(*) into t from ALL_TABLES where TABLE_NAME='TAB';

    -- checking if column does not exist
    select count(*) into w 
    from ALL_TAB_COLUMNS 
    where TABLE_NAME='TAB' AND COLUMN_NAME = 'COLUMN_1';

     if (t>0) AND (w=0) then
        EXECUTE IMMEDIATE 'alter table TAB add COLUMN_1 varchar(20)';
     end if;
end;
/

db<>fiddle demo

Answer 2

You need to add EXECUTE IMMEDIATE to your code :

DECLARE 
    w NUMBER;
BEGIN
    SELECT COUNT(*) INTO w FROM all_tables WHERE table_name='TAB';
     IF w>0 THEN
       EXECUTE IMMEDIATE 'ALTER TABLE tab ADD column_1 VARCHAR(20)';
     END IF;
END;