Abfangen einer Ausnahme beim Ändern einer Tabelle in Oracle

Question

Ich versuche, einen Befehl in Oracle zu schreiben, der eine Spalte entweder hinzufügt oder ändert, je nachdem, ob sie bereits existiert oder nicht. Im Grunde so etwas wie:

BEGIN

  ALTER TABLE MY_TABLE ADD ( COL_NAME VARCHAR2(100 );

  EXCEPTION WHEN OTHERS THEN
    ALTER TABLE MY_TABLE MODIFY ( COL_NAME VARCHAR2(100) );

END;

Oracle beanstandet jedoch, dass der ALTER-Befehl innerhalb von BEGIN steht. Gibt es eine Möglichkeit, dies mit einem einzigen SQL-Befehl in Oracle zu erreichen?

Gracias.

Answer 1

Um DDL in einen PL/SQL-Block zu setzen, müssen Sie dynamisches SQL verwenden.

Ich persönlich würde zuerst prüfen, ob die Spalte existiert und dann die DDL erstellen. Etwas wie

DECLARE
  l_cnt INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO l_cnt
    FROM dba_tab_cols
   WHERE table_name  = 'MY_TABLE'
     AND owner       = <<owner of table>>
     AND column_name = 'COL_NAME';

  IF( l_cnt = 0 )
  THEN
    EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD( col_name VARCHAR2(100) )';
  ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE my_table MODIFY( col_name VARCHAR2(100) )';
  END IF;
END;

Wenn Sie keinen Zugang haben zu DBA_TAB_COLS können Sie auch verwenden ALL_TAB_COLS o USER_TAB_COLS abhängig davon, in welchem Schema sich die Tabelle befindet und welche Berechtigungen Sie für die Tabelle haben.

Answer 2

Ich habe eine Lösung gefunden, die auf diese Stelle .

DECLARE v_column_exists number := 0;   
BEGIN   
  SELECT COUNT(*) INTO v_column_exists
  FROM ALL_TAB_COLUMNS 
  WHERE TABLE_NAME = 'MY_TABLE'
  AND COLUMN_NAME = 'COL_NAME';

  IF (v_column_exists = 0) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE ADD ( COL_NAME VARCHAR2(200) )';   
  ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE MY_TABLE MODIFY ( COL_NAME VARCHAR2(200) )';
  END IF;
END;