2

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:
3

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.

| improve this answer | |
  • thanks it worked I had to change one bit though. instead of dab_tab_columns I've changed the table name to ALL_TAB_COLUMNS – Remember_me Feb 17 '15 at 18:19
  • dba_tab_columns has data for every table in the database. all_tab_columns has data for whatever tables the user you're logged in as has access to. Every user has access to all_tab_columns (though each will see different data), not every user has access to dba_tab_columns. I would expect, though, that a user that is installing an application would have full access to the data dictionary. – Justin Cave Feb 17 '15 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.