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.

Example :

ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP

Fails if the PROP doesn't exist.

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.

Example :

ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP

Fails if the PROP doesn't exist.

Edit:

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: