Oracle alter column if exists

Question

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:

Answer 1

You can use the data dictionary to check whether the column exists before trying to drop it

DECLARE
  l_cnt integer;
BEGIN
  SELECT COUNT(*)
    INTO l_cnt 
    FROM dba_tab_columns
   WHERE owner = <<table owner>>
     AND table_name = 'MY_PROPERTY_LOCK'
     AND column_name = 'PROP';

  IF( l_cnt = 1 )
  THEN
    EXECUTE IMMEDIATE 'ALTER TABLE my_property_lock DROP COLUMN prop';
  END IF;
END;

My bias, though, would be to record somewhere what version of the schema is installed and to determine what DDL to execute based on that. For example, if you have a VERSION table that tells you that the current installed version is 1.4 and you want to drop the column when you move to version 1.5, you can just execute the DROP without worrying that it might not exist.