Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table my_table (id, col1) as
    select 1, 'abc123' from dual
1 rows affected

DECLARE
    column_exists NUMBER;
BEGIN
    SELECT COUNT(*) INTO column_exists
    FROM user_tab_cols
    WHERE table_name  = UPPER('my_table')
    AND   column_name = UPPER('col2');
    dbms_output.put_line ('column_exists='||column_exists); 
    IF (column_exists = 0) THEN
        EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD (col2 CLOB)';
        EXECUTE IMMEDIATE 'UPDATE my_table SET col2 = col1';
        dbms_output.put_line ('column added rowcount='||sql%rowcount); 
    ELSE 
        dbms_output.put_line ('nothing to do'); 
    END IF;
END;
/
1 rows affected

dbms_output:
column_exists=0
column added rowcount=1

select * from my_table


ID
COL1
COL2


1
abc123
abc123


DECLARE
    column_exists NUMBER;
BEGIN
    SELECT COUNT(*) INTO column_exists
    FROM user_tab_cols
    WHERE table_name  = UPPER('my_table')
    AND   column_name = UPPER('col2');
    dbms_output.put_line ('column_exists='||column_exists); 
    IF (column_exists = 0) THEN
        EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD (col2 CLOB)';
        EXECUTE IMMEDIATE 'UPDATE my_table SET col2 = col1';
        dbms_output.put_line ('column added rowcount='||sql%rowcount); 
    ELSE 
        dbms_output.put_line ('nothing to do'); 
    END IF;
END;
/
1 rows affected

dbms_output:
column_exists=1
nothing to do