ENSURE_COLUMN

CREATE OR REPLACE PROCEDURE ENSURE_COLUMN(var_table_name in varchar2, var_column_name in varchar2, var_column_type in varchar2)
AS
 IS_TABLE_EXISTS NUMBER;
 IS_COLUMN_EXISTS NUMBER;
BEGIN
         SELECT count(*) into  IS_TABLE_EXISTS FROM USER_TABLES WHERE TABLE_NAME=var_table_name;
         IF IS_TABLE_EXISTS=0 THEN
            EXECUTE IMMEDIATE 'CREATE TABLE '||var_table_name||' ( '||var_column_name||' '||var_column_type||' ) ';
         ELSE
            SELECT count(*) into  IS_COLUMN_EXISTS FROM user_tab_columns WHERE TABLE_NAME=var_table_name AND COLUMN_NAME=var_column_name;
            IF IS_COLUMN_EXISTS=0 THEN
                 EXECUTE IMMEDIATE 'ALTER TABLE '||var_table_name||' ADD ('||var_column_name||' '||var_column_type||' ) ';
            ELSE
                 EXECUTE IMMEDIATE 'ALTER TABLE '||var_table_name||' MODIFY ('||var_column_name||' '||var_column_type||' ) ';
            END IF;
         END IF;
END;