Hello I've messed up a certain db migration. I'm more of a postgres person than oracle, and I googled for a while and haven't find a way to do something like
alter if exists.
Basically what I need is to drop db columns if they exist, if they don't exist do nothing.
ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP
Fails if the
PROP doesn't exist.
Tried this, among other things :
declare p_count NUMBER; select count(1) int p_count from ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MY_PROPERTY_LOCK' and COLUMN_NAME = 'PROP'; IF p_count = 1 THEN ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP END IF;
This is the error I get :
begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior The symbol "begin" was substituted for "SELECT" to continue. ORA-06550: line 3, column 21: PLS-00103: Encountered the symbol "P_COUNT" when expecting one of the following: , from into bulk The symbol "," was substituted for "P_COUNT" to continue. ORA-06550: line 10, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: